Windows mysql 双向同步设置方法 详细篇

A节点操作：

mysql>flush privileges;

B节点操作步骤如上，权限赋予如下：

mysql>flush privileges;

log-bin=mysql-bin #同步事件的日志记录文件

binlog-do-db=ikey_db #提供数据同步服务的数据库日志

binlog-do-db=ikey_log #提供数据同步服务的数据库日志

####同步的数据库

replicate-do-db=ikey_log  #同步的数据库

mysql> show master status;

+------------------+----------+------------------+------------------+

| File             | Position | Binlog_Do_DB     | Binlog_Ignore_DB |

+------------------+----------+------------------+------------------+

| mysql-bin.000001 |       98 | ikey_db,ikey_log |                  |

+------------------+----------+------------------+------------------+

1 row in set (0.00 sec)

mysql> show slave status/G;

*************************** 1. row ***************************

             Slave_IO_State: Waiting for master to send event

                Master_Host: 192.168.1.101

                Master_User: ym

                Master_Port: 3306

              Connect_Retry: 60

            Master_Log_File: mysql-bin.000001

        Read_Master_Log_Pos: 98

             Relay_Log_File: testBBB-relay-bin.000002

              Relay_Log_Pos: 235

      Relay_Master_Log_File: mysql-bin.000001

           Slave_IO_Running: Yes

          Slave_SQL_Running: Yes

            Replicate_Do_DB: ikey_db,ikey_log

mysql> show master status;

+------------------+----------+------------------+------------------+

| File             | Position | Binlog_Do_DB     | Binlog_Ignore_DB |

+------------------+----------+------------------+------------------+

| mysql-bin.000002 |       118 | ikey_db,ikey_log |                  |

+------------------+----------+------------------+------------------+

1 row in set (0.00 sec)

A方向同步的帐户权限

mysql>drop user ym@192.168.1.101;

mysql>flush privileges;

mysql>grant replication slave on *.* to ym@192.168.1.103 identified by 'ym';

mysql>flush privileges;

B方向同步配置项：

mysql>CHANGE MASTER TO

    -> MASTER_HOST='192.168.1.103',   # Master服务器地址

    -> MASTER_USER='ym',

Query OK, 0 rows affected (0.02 sec)

mysql> show master status;

+------------------+----------+------------------+------------------+

| File             | Position | Binlog_Do_DB     | Binlog_Ignore_DB |

+------------------+----------+------------------+------------------+

| mysql-bin.000003 |       98 | ikey_db,ikey_log |                  |

+------------------+----------+------------------+------------------+

1 row in set (0.00 sec)

A方向同步配置项：

mysql>CHANGE MASTER TO

Query OK, 0 rows affected (0.02 sec)

           Slave_IO_Running: Yes

          Slave_SQL_Running: Yes

即处于正常同步状态。

 [j1]执行同步权限的用户

 [j3]同步帐户密码

 [j4]执行同步权限的用户

 [j6]同步帐户密码