MySQL主从双向同步复制

MySQL主从双向同步复制
本⽂介绍了mysql主从，实现mysql的双向同步复制。

MySQL⽀持单向、异步复制，复制过程中⼀个服务器充当主服务器，⽽⼀个或多个其它服务器充当从服务器。

主服务器将更新写⼊⼆进制⽇志⽂件，并维护⽇志⽂件的⼀个索引
以跟踪⽇志循环。

当⼀个从服务器连接到主服务器时，它通知主服务器从服务器在⽇志中读取的最后⼀次成功更新的位置。

从服务器接收从那时起发⽣的任何更新，然后封锁并
等待主服务器通知下⼀次更新。

为什么使⽤主从复制？
1、主服务器/从服务器设置增加了健壮性。

主服务器出现问题时，你可以切换到从服务器作为备份。

2、通过在主服务器和从服务器之间切分处理客户查询的负荷，可以得到更好的客户响应时间。

但是不要同时在主从服务器上进⾏更新，这样可能引起冲突。

3、使⽤复制的另⼀个好处是可以使⽤⼀个从服务器执⾏备份，⽽不会⼲扰主服务器。

在备份过程中主服务器可以继续处理更新。

MySQL使⽤3个线程来执⾏复制功能(其中1个在主服务器上，另两个在从服务器上。

当发出START SLAVE时，从服务器创建⼀个I/O线程，以连接主服务器并让主服务器发送⼆
进制⽇志。

主服务器创建⼀个线程将⼆进制⽇志中的内容发送到从服务器。

从服务器I/O线程读取主服务器Binlog Dump线程发送的内容并将该数据拷贝到从服务器数据⽬录中的
本地⽂件中，即中继⽇志。

第3个线程是SQL线程，从服务器使⽤此线程读取中继⽇志并执⾏⽇志中包含的更新。

SHOW PROCESSLIST语句可以查询在主服务器上和从服务器
上发⽣的关于复制的信息。

默认中继⽇志使⽤host_name-relay-bin.nnnnnn形式的⽂件名，其中host_name是从服务器主机名，nnnnnn是序列号。

⽤连续序列号来创建连续中继⽇志⽂件，从000001开
始。

从服务器跟踪中继⽇志索引⽂件来识别⽬前正使⽤的中继⽇志。

默认中继⽇志索引⽂件名为host_name-relay-bin.index。

在默认情况，这些⽂件在从服务器的数据⽬录中被
创建。

中继⽇志与⼆进制⽇志的格式相同，并且可以⽤mysqlbinlog读取。

当SQL线程执⾏完中继⽇志中的所有事件后，中继⽇志将会被⾃动删除。

从服务器在数据⽬录中另外创建两个状态⽂件和。

状态⽂件保存在硬盘上，从服务器关闭时不会丢失。

下次从服务器启动时，读取这些⽂件以确定它已
经从主服务器读取了多少⼆进制⽇志，以及处理⾃⼰的中继⽇志的程度。

下⾯来看⼀下具体的配置：
⼀、准备服务器
由于MySQL不同版本之间的(⼆进制⽇志)binlog格式可能会不⼀样，因此最好的搭配组合是Master的MySQL版本和Slave的版本相同或者更低，Master的版本肯定不能⾼于Slave
版本。

本⽂中，我们假设主服务器(以下简称Master)和从服务器(以下简称Slave)的版本都是5.1.35，操作系统是Centos 4.7。

假设同步Master的主机名为：master(IP:192.168.1.123)，Slave主机名为：slave(IP:192.168.1.124)，2个MySQL的basedir⽬录都是/usr/local/mysql，datadir都
是：/var/lib/mysql。

⼆、设置同步服务器
1、设置同步Master
修改 f ⽂件，在
# Replication Master Server (default)
# binary logging is required for replication
添加如下内容：
#log-bin=/var/log/mysql/updatelog server-id = 1 auto_increment_increment = 2 auto_increment_offset = 1 binlog-do-db=discuz binlog-ignore-db=mysql #log-slave-updates slave-skip-errors=all
注：
slave-skip-errors 是跳过错误，继续执⾏复制操作
多主互备和主从复制有⼀些区别，因为多主中都可以对服务器有写权限，所以设计到⾃增长重复问题
出现的问题（多主⾃增长ID重复）
1:⾸先我们通过A,B的test表结构
2:掉A，在B上对数据表test(存在⾃增长ID)执⾏插⼊操作，返回插⼊ID为1
3:后停掉B，在A上对数据表test(存在⾃增长ID)执⾏插⼊操作，返回的插⼊ID也是1
4:然后我们同时启动A,B，就会出现主键ID重复
解决⽅法：
我们只要保证两台服务器上插⼊的⾃增长数据不同就可以了
如：A查奇数ID，B插偶数ID，当然如果服务器多的话，你可以定义算法，只要不同就可以了
在这⾥我们在A,B上加⼊参数，以实现奇偶插⼊
A：f上加⼊参数
auto_increment_offset = 1 auto_increment_increment = 2
这样A的auto_increment字段产⽣的数值是：1, 3, 5, 7, …等奇数ID了
B：f上加⼊参数
auto_increment_offset = 2 auto_increment_increment = 2
这样B的auto_increment字段产⽣的数值是：2, 4, 6, 8, …等偶数ID了
可以看出，你的auto_increment字段在不同的服务器之间绝对不会重复，所以Master-Master结构就没有任何问题了。

当然，你还可以使⽤3台，4台，或者N台服务器，只要保证
auto_increment_increment = N 再设置⼀下auto_increment_offset为适当的初始值就可以了，那样，我们的MySQL可以同时有⼏⼗台主服务器，⽽不会出现⾃增长ID重复。

重启MySQL,创建⼀个MySQL帐号为同步专⽤
# /usr/local/mysql/bin/mysql -u root -p mysql> GRANT REPLICATION SLAVE ON *.* TO 'back'@'%' IDENTIFIED BY 'back'; mysql>GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO 'back'@'%' IDENTIFIED BY 'back'; mysql> FLUSH 如果想要在Slave上有权限执⾏ "LOAD TABLE FROM MASTER" 或 "LOAD DATA FROM MASTER" 语句的话，必须授予全局的 FILE 和 SELECT 权限：
2、设置同步Slave
修改f⽂件，添加
server-id = 2 auto_increment_increment = 2 auto_increment_offset = 2 master-host = 192.168.1.123 master-user = back master-password = back master-port = 3306 replicate-ignore-db=mysql replicate-do-db=discuz #log-slave-updates s 重启MySQL
3、启动同步
在主服务器master MySQL命令符下：
# /usr/local/mysql/bin/mysql -u root -p mysql> show master status; 显⽰（当然这个是我机器的情况，你的不可能跟我⼀样哈，只是个例⼦）： +------------------+----------+-------------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ig
在从服务器master MySQL命令符下：
# /usr/local/mysql/bin/mysql -u root -p mysql> slave stop; mysql> change master to master_host='192.168.1.123', master_user='back', master_password='back', master_log_file='mysql-bin.000009', master_log_pos=98; mysql> slave start; my 如果都是yes，那代表已经在同步
往表⾥⾯写点数据测试⼀下看是否同步成功，如果不成功，绝对不是你的RP问题，再检查⼀下操作步骤！
4、设置双向同步
修改slave服务器的f，添加
log-bin=/var/log/mysql/updatelog binlog-do-db=discuz binlog-ignore-db=mysql
重启MySQL,创建⼀个MySQL帐号为同步专⽤
mysql> GRANT REPLICATION SLAVE ON *.* TO 'back'@'%' IDENTIFIED BY 'back'; mysql> GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO 'back'@'%' IDENTIFIED BY 'back'; mysql> FLUSH PRIVILEGES ;
修改master服务器的f,添加
master-host = 192.168.1.124 master-user = back master-password = back master-port = 3306 replicate-ignore-db=mysql replicate-do-db=discuz
重启MySQL
在主服务器slave MySQL命令符下：
show master status; +------------------+----------+-------------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+-------------------+------------------+ | mysql-bin.000013 | 98 | discuz | mysql | +-----------------
在服务器A MySQL命令符下：
mysql> slave stop； mysql> change master to master_host='192.168.1.124', master_user='back', master_password='back', master_log_file='mysql-bin.000013', master_log_pos=98; mysql> slave start;
其实也就是A->B单向同步的反向操作！双向同步，就这么简单啦！
提⽰：如果修改了主服务器的配置，记得删除从服务器上的⽂件。

否则从服务器使⽤的还是⽼配置，可能会导致错误。

-----------------------------------------------------------------------------------
注意：关于要复制多个数据库时，binlog-do-db和replicate-do-db选项的设置，⽹上很多⼈说是⽤半⾓逗号分隔，经过测试，这样的说法是错误的，MySQL官⽅⽂档也明确指
出，如果要备份多个数据库，只要重复设置相应选项就可以了。

⽐如：
binlog-do-db=a binlog-do-db=b replicate-do-db=a replicate-do-db=b
补：
#启⽤从库⽇志，这样可以进⾏链式复制 log-slave-updates #从库是否只读，0表⽰可读写，1表⽰只读 read-only=1 #只复制某个表 replicate-do-table=tablename #只复制某些表（可⽤匹配符） replicate-wild-do-table=tablename% #只复制/blog/static/10076891420113192155576/。