一、准备工作：
两台服务器：A: 10.21.239.240
B: 10.21.239.222
MySQL数据库5.5
二、在两台MySQL上创建用户，设置权限
A：mysql> grant replication slave, replication client, reload, super on *.* to 'sync_user'@'10.21.239.222' identified by '123456' with grant option;//用户B访问
B：mysql> grant replication slave, replication client, reload, super on *.* to 'sync_user'@'10.21.239.240' identified by '123456' with grant option;//用户A访问
三、在my.ini上进行相关配置
在[mysqld]下方增加：
A：
server-id=1
replicate-do-db=testsync
replicate-ignore-db=mysql
log-bin=mysql-bin
log-slave-updates
slave-skip-errors=all
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1
B：
server-id=2
replicate-do-db=testsync
replicate-ignore-db=mysql
log-bin=mysql-bin
log-slave-updates
slave-skip-errors=all
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=2
保存后，重启MySQL
四、在MySQL Shell上进行相关配置
1、在A上mysql shel 中执行 show master status;
mysql> show master status;
+--------------------+----------+----------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------- +----------+----------------+-------------------+
| mysql-bin.000002 | 107 | | |
+--------------------+----------+----------------+-------------------+
1 row in set (0.00 sec)
记录下mysql-bin.000002和107
2、在B上执行：stop slave;//停止同步
然后执行：
mysql> change master to master_host='10.21.239.240', master_port=3306, master_user='sync_user', master_password='123456', master_log_file='mysql-bin.000002',master_log_pos=107;
然后执行：
start slave;//开始同步
最后执行：show slave status\G;如果显示以下信息，表示同步设置成功。
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.21.239.240
Master_User: sync_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 107
Relay_Log_File: 7d44cba7defa42f-relay-bin.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: mydb
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 107
Relay_Log_Space: 419
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.02 sec)
ERROR:
No query specified
3、上一步没有问题，则在B上继续执行show master status;
mysql> show master status;
+--------------------+----------+----------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------- +----------+----------------+-------------------+
| mysql-bin.000001 | 107 | | |
+--------------------+----------+----------------+-------------------+
1 row in set (0.00 sec)
记录下mysql-bin.000001和107
4、在A上执行：stop slave;//停止同步
然后执行：
mysql> change master to master_host='10.21.239.222', master_port=3306, master_user='sync_user', master_password='123456', master_log_file='mysql-bin.000001',master_log_pos=107;
然后执行：
start slave;//开始同步
最后执行：show slave status\G;如果显示以下信息，表示同步设置成功。
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.21.239.222
Master_User: sync_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 590
Relay_Log_File: Dean-relay-bin.000003
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: testsync
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 590
Relay_Log_Space: 1037
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
1 row in set (0.00 sec)
ERROR:
No query specified