MYSQL 主从复制

指定配置文件启动： mysqld --defaults-file="F:\MySQL\MySQL Server 5.5\master.ini"

Master 配置文件:

server-id = 1 #master server id

log-bin=mysql-bin #open binary log

binlog-do-db=test #binary log for db named test
binlog-do-db=test2 #binary log for db named test2


Slave 配置文件：

server-id = 2 #slave server id, be sure it is different with master server id

replicate-do-db=test #only replicate db named test
=================================

Master 配置命令:

1. 创建同步用户slave，密码123456，来自于主机192.168.1.3的连接被许可。

grant replication slave,replication client on *.* to slave@192.168.1.3 identified by '123456';

2. 查询目前master的复制位置，以下为633.

2.1 flush tables with read lock;

2.2 show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 633 | test,test2 |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

2.3 unlock tables;


Slave 配置命令：

1.1 stop slave;

1.2 change master to master_host='192.168.1.6',master_user='slave',master_password='123456', master_log_file='mysql-bin.000001', master_log_pos=633;

1.3 start slave;

1.4 show slave status;

如果看到两个Yes，说明成功，可以试着创建数据表，插入数据进行进一步确认。

如果失败，可以在查询结果中查看错误信息，可以试着stop slave然后start slave恢复。