MySQL master-master replication 深入了解

MySQL企业版和社区版提供master－slave异步的replication机制，MySQL cluster提供同步的replication机制，关于cluster replication机制本文不做叙述，Mysql可以配置为互为master－slave，这样就相当于可以做到master－master，配置步骤就是将以下配置在相反的lab上再做一次。

架设步骤：
[事前准备]
确保 Master 与 Slave 之间的数据一致

[Master Server 方面]
设定 Server-id
开启 Binary Log
设定 Replication Slave 权限

[Slave Server 方面]
设定 Server-id
将 Master_Host 设定为 Master Server
启动与检查 Slave Server 的状态

在此范例中我们假设：
[Master Server]
IP 为 192.168.1.1
server-id 为 1
进行数据同步时所使用的帐户信息：
账号: slave_server
密码: 12345678

[Slave Server]
IP 为 192.168.1.2
server-id 为 2

Master/Slave Server 架设

一、Master Server 方面

A.设定 Server-id
首先要设定 server-id。基本上没有什么特别的限制，只要 Master 和 Slave 的 server-id 不一样即可，但其值必需为 1 至 2^32

-1 之间。但为了方便识别，通常我们会把这个值设定为 IP 的最后一组数字，例如若 IP 为 192.168.1.1，则 server-id 设定为 1;

若 IP 为 192.168.1.2，则 server-id 设定为 2。
[mysqld]
server-id=1

B.开启 Binary Log
修改 MySQL Server 的系统配置文件，在 [mysqld] 下方加上 log-bin=mysql-bin，例如：

引用:
[mysqld]
log-bin=mysql-bin

MySQL 的 Binary Log 会将所有对于数据库的修改操作全部记录起来，而 Slave 与 Master 之间进行数据同步的方式很简单，就是

Slave 会把 Master Server 的 Binary Log 拿过来执行，也就是说 Slave Server 会 "重做" 在 Master Server 上发生的各种修改

操作。因此 Master Server 勿必要开启 Binary Log 功能，否则 Master/Slave 架构无法运作。

C.设定 Replication Slave 权限
我们必须要在 Master Server 上做设定，让 Slave 具有可以从 Master Server 上 Copy 数据的权限(正式的说法为 Replication

Slave Priviledges)，所需使用的指令如下：

引用:
GRANT REPLICATION SLAVE ON *.* TO 'slave_server'@'192.168.1.2',
IDENTIFIED BY '12345678'

意思为：
允许 192.168.1.2 这个 IP 使用 slave_server 账号，来进行数据同步(Replication)。
slave_server 这个账号的密码为 12345678。

此时您可以从 Slave Server(192.168.1.2) 使用 mysql client program 进行验证，看是否有正确的开启权限，例如使用以下的指令

：(注意，是在 Slave Server 上进行验证)

引用:
mysql -h 192.168.1.1 -u slave_server -p

接着系统会要求您输入密码，若可以顺利登入即表示设定成功。

二、Slave Server 方面

A.设定 Server-id
在 Slave 我们将其设定为 2：

引用:
[mysqld]
server-id=2

B.将 Master_Host 设定为 Master Server
我们必须要明确的告诉 Slave Server 哪一台 Server 才是 Master Server，使用以下的指令即可：

引用:
CHANGE MASTER TO

MASTER_HOST='192.168.1.1',
MASTER_PORT=3306, MASTER_USER='slave_server',
MASTER_PASSWORD='12345678';
以上可直接写入my.cnf，注意用小写

意思为：
Master Server 是 192.168.1.1
使用 TCP Port 3306 连接
使用 slave_server 这个账号登入
登入时使用的密码为 12345678

C.启动与检查 Slave Server 的状态
设定好后，Master/Slave 机制仍未启动，您必须要使用以下的指令来开启或关闭 Master/Slave 机制：
START slave; (启动 Master/Slave 机制)
STOP slave; (停止 Master/Slave 机制)

当你执行 'START slave;' 后，可使用以下的指令来检查执行状态：

引用:
SHOW SLAVE STATUS /G

执行后应该会看到如下的报表：

引用:
mysql> SHOW SLAVE STATUS /G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.1
Master_User: slave_server
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000019
Read_Master_Log_Pos: 16492717
Relay_Log_File: www-relay-bin.000018
Relay_Log_Pos: 16492854
Relay_Master_Log_File: mysql-bin.000019
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: example
Replicate_Ignore_DB:
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: 16492717
Relay_Log_Space: 16492854
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
1 row in set (0.00 sec)

重点是那三行：

Slave_IO_Running：
是否要从 Master Server 复制 Binary Log 数据，必须为 Yes。

Slave_SQL_Running：
是否要执行从 Master Server 复制过来的 Binary Log 数据，必须为 Yes。

Seconds_Behind_Master：
Slave 的数据落后了 Master 多少秒，执行一段时间后应该会是零。

 

一些有用的总结：

1，测试结果：拔掉网线，在两个mysql上做insert，update，然后插上网线，会自动同步，并且对于update有冲突的情况（update同一条记录），会根据时间点来选择。

2，一台 Master Server 可以拥有很多台 Slave Server；但一台 Slave Server 只可对应到一台 Master Server。

3，Slave Replication Error,

There is another mechanism that you can use, which is the skip counter. You can skip the current transaction that the replication thread is hung on and continue. You can issue the following command in mysql to skip a transaction: 

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; 
mysql> START SLAVE;

4， If you use auto incremeneting id's and you're using Master Master with both masters active you might want to consider setting the following options on Master 1 and Master 2 so that the auto increment values don't end up clashing in the case where an insert happens to occur at exactly the same time on both servers:

Make Master 1 only auto-increment odd numbers by adding this to my.cnf under [mysqld]:
auto_increment_increment= 2
auto_increment_offset   = 1


Make Master 2 only auto-increment even numbers by adding this to my.cnf under [mysqld]:
auto_increment_increment= 2
auto_increment_offset   = 2

This is particularly important if you're using auto increment id columns as primary keys