MYSQL 双主互备

环境

• CentOS Linux release 7.2.1511 (Core)
• MYSQL (5.7.29-log)

前提

• 预设 mysql 已经安装完毕
• 预设 mysql 服务器 A 的IP地址 192.168.1.88
• 预设 mysql 服务器 B 的IP地址 192.168.1.99
• 预设 mysql 服务 A 和 B 用户名 和 密码 都为 root

操作

• A 服务器的操作

  在配置文件 /etc/my.cnf 中 [mysqld] 配置的最后添加如下配置

  sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
  # 服务ID
  server-id=1888
  # binlog
  log-bin=mysql-bin
  # 自增偏移量
  auto-increment-increment=1
  # 自增起始值
  auto-increment-offset=1
  # binlog记录的数据库名称
  binlog-do-db=test
  # binlog不记录的数据库名称
  binlog-ignore-db=mysql
  # 从主库通过binlog复制过来的数据记录在binlog日志中开关
  log_slave_updates=1
  # 接受的数据包大小
  max_allowed_packet=256M
  

  重启MYSQL服务

  systemctl restart mysqld
  

  登录MYSQL

  mysql -uroot -proot
  

  在 A 服务器上创建用来同步的数据的账户 replb

  grant replication slave on *.* to 'replb'@'192.168.1.99' identified by 'Replb@b1';
  flush privileges;
  

  记录 binlog 文件的名称及位置

  show master status;
  +------------------+----------+----------------+------------------+-------------------+
  | File             | Position | Binlog_Do_DB   | Binlog_Ignore_DB | Executed_Gtid_Set |
  +------------------+----------+----------------+------------------+-------------------+
  | mysql-bin.000001 |      888 | test            | mysql            |                   |
  +------------------+----------+----------------+------------------+-------------------+
  
  

• B 服务器的操作

  在配置文件 /etc/my.cnf 中 [mysqld] 配置的最后添加如下配置:

  sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
  # 服务ID
  server-id=1999
  # binlog
  log-bin=mysql-bin
  # 自增偏移量
  auto-increment-increment=1
  # 自增起始值
  auto-increment-offset=1
  # binlog记录的数据库名称
  binlog-do-db=test
  # binlog不记录的数据库名称
  binlog-ignore-db=mysql
  # 从主库通过binlog复制过来的数据记录在binlog日志中开关
  log_slave_updates=1
  # 接受的数据包大小
  max_allowed_packet=256M
  

  重启MYSQL服务

  systemctl restart mysqld
  

  登录MYSQL

  mysql -uroot -proot
  

  在 B 服务器上创建用来同步的数据的账户 repla

  grant replication slave on *.* to 'repla'@'192.168.1.88' identified by 'Repla@a1';
  flush privileges;
  

  记录 binlog 文件的名称及位置

  show master status;
  +------------------+----------+----------------+------------------+-------------------+
  | File             | Position | Binlog_Do_DB   | Binlog_Ignore_DB | Executed_Gtid_Set |
  +------------------+----------+----------------+------------------+-------------------+
  | mysql-bin.000001 |      888 | test           | mysql            |                   |
  +------------------+----------+----------------+------------------+-------------------+
  
  

  执行数据同步，master_log_file 和 master_log_pos 分别是之前记录的 A 服务器中的 master status 中的 File 和 Position

  stop slave;
  change master to master_host='192.168.1.88',master_user='replb',master_password='Replb@b1',master_log_file='mysql-bin.000001',master_log_pos=888;
  start slave;
  

  查看数据同步状态，确认以下项值都对的话，即为开启同步成功

  • [Slave_IO_State] 的值为 [Waiting for master to send event]
  • [Slave_IO_Running] 和 [Slave_SQL_Running] 的值为 [Yes]

  show slave status\G
  *************************** 1. row ***************************
                 Slave_IO_State: Waiting for master to send event
                    Master_Host: 192.168.1.88
                    Master_User: replb
                    Master_Port: 3306
                    ... ...(略)
                    Slave_IO_Running: Yes
                    Slave_SQL_Running: Yes
                    ... ...(略)
  

• A 服务器的操作

  登录MYSQL

  mysql -uroot -proot
  

  执行数据同步

  stop slave;
  change master to master_host='192.168.1.99',master_user='repla',master_password='Repla@a1',master_log_file='mysql-bin.000001',master_log_pos=888;
  start slave;
  

  查看数据同步状态，确认以下项值都对的话，即为开启同步成功

  • [Slave_IO_State] 的值为 [Waiting for master to send event]
  • [Slave_IO_Running] 和 [Slave_SQL_Running] 的值为 [Yes]

  show slave status\G
  *************************** 1. row ***************************
                 Slave_IO_State: Waiting for master to send event
                    Master_Host: 192.168.1.99
                    Master_User: repla
                    Master_Port: 3306
                    ... ...(略)
                    Slave_IO_Running: Yes
                    Slave_SQL_Running: Yes
                    ... ...(略)
  

操作完成。

Q & A

• 如果MYSQL服务是直接虚拟机克隆的，需要将 /var/lib/mysql/auto.cnf 删除，重启MYSQL服务即可

• 如果MYSQL配置[/etc/my.cnf]文件不生效

  修改/usr/lib/systemd/system/mysqld.service文件如下所示

  # Start main service
  ExecStart=/usr/bin/mysqld_safe --defaults-file=/etc/my.cnf --basedir=/usr
  

  加入[--defaults-file=/etc/my.cnf]

  这里注意[--defaults-file]一定要在所有参数的第一个位置，否则重启mysql服务的时候会卡住，一直在启动中

  加载新的配置文件

  systemctl daemon-reload
  

  重启mysql服务

  systemctl restart mysql