MySQL配置主主同步
环境 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 ... ...