环境

  • A: 172.21.110.100
  • B：172.21.110.101
  • mysql5.6

创建同步用户

  • A：
grant replication slave on *.* to 'sync'@'172.21.110.101' identified by '123456';
flush privileges;
  • B:
grant replication slave on *.* to 'sync'@'172.21.110.101' identified by '123456';
flush privileges;

服务配置

  • A:
# MASTER DB #
#binlog-do-db                   = m2m
binlog-ignore-db               = mysql,information_schema,performance_schema
auto-increment-increment       = 2
auto-increment-offset          = 1
# SLAVE DB #
#replicate-do-db                = m2m
replicate-ignore-db            = mysql,information_schema,performance_schema
log-slave-updates = ON
  • B:
# MASTER DB #
#binlog-do-db                   = m2m
binlog-ignore-db               = mysql,information_schema,performance_schema
auto-increment-increment       = 2
auto-increment-offset          = 2
# SLAVE DB #
#replicate-do-db                = m2m
replicate-ignore-db            = mysql,information_schema,performance_schema
log-slave-updates = ON

查看2个服务器的日志状态

  • A:
mysql> flush tables with read lock; #防止进入新的数据 
Query OK, 0 rows affected (0.00 sec) 
mysql> show master status\G;
*************************** 1. row ***************************
             File: binlog.000003
         Position: 120
     Binlog_Do_DB: 
 Binlog_Ignore_DB: mysql,information_schema,performance_schema
Executed_Gtid_Set:
  • B:
mysql> flush tables with read lock; #防止进入新的数据 
Query OK, 0 rows affected (0.00 sec) 
mysql> show master status\G;
*************************** 1. row ***************************
             File: binlog.000003
         Position: 120
     Binlog_Do_DB: 
 Binlog_Ignore_DB: mysql,information_schema,performance_schema
Executed_Gtid_Set:

开启同步

  • A:
CHANGE MASTER TO MASTER_HOST='172.21.110.101',MASTER_PORT=3306,MASTER_USER='sync',MASTER_PASSWORD='123456',MASTER_LOG_FILE='binlog.000003',MASTER_LOG_POS=120;
  • B:
CHANGE MASTER TO MASTER_HOST='172.21.110.100',MASTER_PORT=3306,MASTER_USER='sync',MASTER_PASSWORD='123456',MASTER_LOG_FILE='binlog.000003',MASTER_LOG_POS=120;
  • 2台服务器都启动slave进程
mysql> start slave; 
Query OK, 0 rows affected (0.00 sec)

查看同步状态

  • 如存在以下状态，则表示设置成功
mysql> show slave status\G; 
*************************** 1. row *************************** 
... 
... 
Slave_IO_Running: Yes 
Slave_SQL_Running: Yes 
... 
...