Mysql主主（双向）同步配置方案

A服务器：192.168.1.1
B服务器：192.168.1.2

A服务器上的设置：
编辑my.cnf，添加或修改如下内容
#双向同步
server-id=1
log-bin=mysql-bin
relay-log = relay-bin
replicate_do_db=testtongbu
auto-increment-increment=2 #每次增长2
auto-increment-offset=1 #设置自动增长的字段的偏移量，即初始值为2
重启mysql
service mysqld restart
进入Mysql命令行模式，执行下面的命令
GRANT RELOAD,SUPER,REPLICATION SLAVE,REPLICATION CLIENT on *.* to backup@'192.168.1.2' identified by '123456';
flush privileges;
show master status;
#记下File和Position的值 log1

B服务器上的设置：
编辑my.cnf，添加或修改如下内容
#双向同步
server-id=2
log-bin=mysql-bin
relay-log = relay-bin
replicate_do_db=testtongbu
auto-increment-increment=2 #每次增长2
auto-increment-offset=2 #设置自动增长的字段的偏移量，即初始值为2
重启mysql
service mysqld restart
进入Mysql命令行模式，执行下面的命令
GRANT RELOAD,SUPER,REPLICATION SLAVE,REPLICATION CLIENT on *.* to backup@'192.168.1.1' identified by '123456';
flush privileges;
show master status;
#记下File和Position的值 log2

#执行(问号分别替换为刚才log1里面记下的两个值)
CHANGE MASTER TO MASTER_HOST='192.168.1.1', MASTER_USER='backup', master_password='123456', MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=106;
start slave;

#回到A服务器，执行(问号分别替换为刚才log2里面记下的两个值)
CHANGE MASTER TO MASTER_HOST='192.168.1.2', MASTER_USER='backup', master_password='123456', MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=106;
start slave;

正在运行的数据库导出数据时，先锁定库
flush tables with read lock;
导出后解锁
unlock tables;

遇到的错误：
ERROR 1219 (HY000): Error running query on master: Access denied; you need the RELOAD privilege for this operation
在另外一台服务器上执行
mysql> grant RELOAD on *.* to repl@’%’ identified by ‘your_password’;

Error reading packet from server: Access denied; you need the REPLICATION SLAVE privilege for this operation (server_errno=1227)
在另外一台服务器上执行
mysql> grant REPLICATION SLAVE on *.* to repl@’%’ identified by ‘your_password’;