如何进行mysql 5.6不停机双主一从搭建

什么是mysql不停机双主一从搭建

MySQL不停机双主一从搭建是指在一台mysql主服务器与从服务器之间搭建一个mysql主服务器,使得在主从服务器进行数据同步时,不需要停机就能保证数据的高可用性。

如何进行mysql不停机双主一从搭建

以下是mysql不停机双主一从搭建的步骤:

  1. 安装MySQL并配置好双主环境
  2.     
          $sudo nano /etc/my.cnf
          server_id=1
          log-bin=master1
          auto_increment_increment=2
          auto_increment_offset=1
          bind-address=0.0.0.0
          mysql> change master to master_host='192.168.0.102', master_port=3306, master_user='slaveuser', master_password='slavepass';
          ... ...
          server_id=2
          log-bin=master2
          auto_increment_increment=2
          auto_increment_offset=2
          bind-address=0.0.0.0
          $sudo /etc/init.d/mysql start
          mysql> CREATE USER 'slaveuser'@'192.168.0.%' IDENTIFIED BY 'slavepass';
          mysql> GRANT REPLICATION SLAVE ON *.* TO 'slaveuser'@'192.168.0.%';     
        
      
  3. 配置双主之间的数据同步
  4.     
          mysql> stop slave;
          mysql> change master to master_host='192.168.0.101', master_port=3306, master_user='slaveuser', master_password='slavepass', MASTER_LOG_FILE='master.000001', MASTER_LOG_POS=107;  
          mysql> start slave;
        
      
  5. 在主服务器和从服务器上都安装并配置好keepalived
  6.     
          $sudo apt-get install keepalived
          $sudo nano /etc/keepalived/keepalived.conf
          ... ...
          virtual_ipaddress {
            192.168.0.200
          }
          ... ...
          virtual_ipaddress_excluded {
            192.168.0.101
          }
        
      
  7. 配置一台机器做为MySQL的热备份,以保证数据的可恢复性
  8.     
          $sudo apt-get install xtrabackup # 安装percona-xtrabackup软件包
          $sudo xtrabackup --backup --user=root --password=yourpassword --target-dir=/mnt/backups/2016-01-01 # 备份mysql数据
          $sudo xtrabackup --prepare --user=root --password=yourpassword --target-dir=/mnt/backups/2016-01-01  # 确认备份数据可用
        
      

如何切换主服务器和备用服务器

切换主服务器和备用服务器的步骤如下:

  1. 将某一主服务器的keepalived服务停掉,并切换为从服务器
  2.     
          $sudo /etc/init.d/keepalived stop
        
      
  3. 如果该主服务器数据偏旧,将其xtrabackup备份导入新的主服务器
  4.     
          $sudo xtrabackup --copy-back --target-dir=/mnt/backups/2016-01-01 # 将xtrabackup备份导入新的主服务器
          $sudo chown -R mysql:mysql /var/lib/mysql # 更改mysql的所有者和所属组
          $sudo /etc/init.d/mysql start # 启动mysql
        
      
  5. 将剩余的双主服务器之间进行数据同步
  6.     
          mysql> stop slave; # 停止从属服务器
          mysql> change master to master_host='192.168.0.102', master_port=3306, master_user='slaveuser', master_password='slavepass', MASTER_LOG_FILE='master.000001', MASTER_LOG_POS=107; # 修改从属服务器指向新的主服务器
          mysql> start slave; # 开始同步
          mysql> SHOW SLAVE STATUS\G; # 查看同步信息
        
      
  7. 更改原备用服务器的keepalived服务IP地址为虚拟IP地址,并开启keepalived服务
  8.     
          $sudo mv /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak # 备份keepalived配置文件
          $sudo nano /etc/keepalived/keepalived.conf # 修改keepalived配置文件
          ... ...
          virtual_ipaddress {
            192.168.0.200
          }
          ... ...
          virtual_ipaddress_excluded {
            192.168.0.102
          }
          ... ...
          $sudo /etc/init.d/keepalived start # 开启keepalived服务
        
      
© 版权声明
THE END
喜欢就支持一下吧
点赞11 分享