MySQL复制之实践篇

　　本文主要以“一个主库，两个备库”代表“一个主库，多个备库”的拓扑结构来展示MySQL复制的实践过程。

拓扑结构：                                                               

主库创建复制账号：

grant replication slave,replication client on *.* to 'Jet'@'192.168.0.%' identified by 'JetWu@boyaa';

 

复制配置：

主库配置：

log_bin = mysql-bin

server_id = 11（每个服务器的server_id必须在拓扑中是唯一的）

重启mysql，使用命令show master status确认二进制日志文件是否已在主库创建：

备库1配置：

log_bin = mysql-bin

server_id = 21

relay_log = /var/lib/mysql/mysql-relay-bin

重启mysql

备库2配置：

log-bin="WJT-PC-bin.log"

server-id=22

relay_log="D:/wamp/mysqldata/Data/mysql-relay-bin"

重启mysql

 

告诉各个备库如何连接到主库并重放其二进制日志：

change master to master_host='192.168.0.113',master_user='Jet',master_password='JetWu@boyaa', master_log_file='mysql-bin.000001',master_log_pos=0;

 

通过show slave status查看复制状态：

复制启动前：

复制启动后：

 

在各个备库开始复制：

start slave;

 

　　如无意外或错误，至此“一个主库，两个备库”拓扑结构的MySQL复制就已成功启动。主库会将造成数据更改的查询记录入其二进制日志文件中，而备库1、备库2则会读取主库的二进制日志文件并写入各自的中继日志文件中，然后重放其中记录的事件。

 

　　在备库上执行show processlist命令可以看到多了两个名为“system user”的线程，其中一个是负责读取主库二进制日志并写入中继日志的I/O线程，另一个是读取中继日志并重放事件的SQL线程。

　　在主库上则可发现多了两个名为“Jet”的线程，也就是前面在主库上创建的复制账号。

 

基于语句复制：

　　若在备库上将某条记录的某个字段值修改，然后在主库上执行数据修改查询语句，以这一条记录中这个字段原来的值作为修改条件，由于在备库上已经找不到拥有原来字段值的记录了，所以备库上的数据将不改变。

 

主键冲突导致复制中断：

　　先在备库上插入一条记录，然后在主库上插入一条和它主键一样的记录，这时备库上会因为SQL线程在重放事件的时候遇到主键冲突而导致复制中断，主键冲突的记录将不被插入备库中，以后主库执行的数据更改再也无法在备库上重放。从show processlist命令打印出的结果可以看到备库上已经少了SQL线程，从show slave status命令打印出的结果的last_error列可以查看复制中断的原因。这时如果想要将备库重新连上主库，有两种方法：

1. 首先在备库上删除主键冲突的记录，然后再重启备库的mysql服务，或者先执行stop slave，然后start slave即可。

2. 在主库上使用mysqlbinlog工具查看当前二进制日志文件，找到冲突的位置，然后在备库上先stop slave，然后再执行change master to命令将复制位置指定到跳过主键冲突的位置，最后start slave。相对于第一种方法，这种方法不需要删除备库上冲突的记录。