MySQL中建立Master-Master复制

双向的master复制,需要在应用解决冲突问题,最好的办法是写操作同时只在一台数据库上更新,另一台作为备份机器
思路,先建立Master/slave,再改为master-mastert模式
为了好称呼,我仍然叫另一个slave

只对数据库db3进行复制
Step 0 建立好两个MYSQL实例
==========================================================================================
我是直接用的rpm包,缺省安装CentOS 5 + MySQL 5.5
由于只有一个机器上,故启动两个实例来模拟2个server,master用端口3202, slave用端口3201
在master上建立数据库db3,并初始化一些数据

Step 1, master端, 建立复制用户
==========================================================================================
CREATE USER 'rep4'@'%.xyz.com' IDENTIFIED BY 'oracle';
GRANT REPLICATION SLAVE ON *.* TO 'rep4'@'%.xyz.com';

在slave端测试连接,验证授权
mysql --host=hwz.xyz.com --port=3302 -urep4 -poracle
mysql> show grants;
+----------------------------------------------------------+
| Grants for rep4@%.xyz.com                                |
+----------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'rep4'@'%.xyz.com' ....|
+----------------------------------------------------------+

Step 2:修改Master配置
==========================================================================================
vi my.cnf
[mysqld]
log-bin=mysql-bin
server-id = 1
Replicate_Wild_Do_Table=db3.*

#innodb建议设置如下参数
innodb_flush_log_at_trx_commit=1
sync_binlog=1

重新启动

Step 3:修改Slave配置
==========================================================================================
Shutdown MySQL
vi my.cnf
[mysqld]
server-id=2
Replicate_Wild_Do_Table=db3.*

先不要启动

Step 4: 建立snapshot备份,master端
==========================================================================================
1. Master端方式数据修改,不要退出,后面还有释放锁;
FLUSH TABLES WITH READ LOCK;

2. 获取Master Binary Log Coordinates (file+Position)
show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000008 |     3704 |              |                  |
+------------------+----------+--------------+------------------+

另外启动一个session,做下面的操作,被挂起
insert into dept (dname) values ('Diabetes');

3. 在slave端备份数据库db3
mysqldump --host=hwz.xyz.com --port=3302 -u root -poracle --databases db3 --lock-all-tables > db3_snapshot.dmp

4. 在前面加锁的session中释放锁:
mysql> UNLOCK TABLES;

5. 前面备挂起的语句执行了,再插入一条
Query OK, 1 row affected (4 min 27.61 sec)
mysql> insert into dept (dname) values ('Sales');
Query OK, 1 row affected (0.04 sec)


Step 5: 建立第一个复制路径master->slave
==========================================================================================
1.Slave端带--skip-slave-start选项启动MySQL
mysqld_safe --defaults-extra-file=my.cnf --skip-slave-start &
2. 导入db3数据
mysql -r root -poracle < db3_snapshot.dmp
查询dept表,确认导出后新插入的2条记录不存在

确保先不要在slave端更新该数据db3

3.配置复制
CHANGE MASTER TO
MASTER_HOST='hwz.xyz.com',
MASTER_PORT=3302,
MASTER_USER='rep4',
MASTER_PASSWORD='oracle',
MASTER_LOG_FILE='mysql-bin.000008',
MASTER_LOG_POS=3704;

show slave status \G

master.info , relay-log.info 和 Relay log files 会在$DATADIR下建立

4. 启动复制
START SLAVE;

5. 确认新插入的数据出现了
select * from dept;


Step 6 Slave端建立专门的复制用户
==========================================================================================
CREATE USER 'rep_slave'@'%.xyz.com' IDENTIFIED BY 'rep4slave';
GRANT REPLICATION SLAVE ON *.* TO 'rep_slave'@'%.xyz.com';

master端验证连接
mysql --host=hwz.xyz.com --port=3201 -urep_slave -prep4slave
mysql> show grants;
+---------------------------------------------------------------------------+
| Grants for rep_slave@%.xyz.com                                            |
+---------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'rep_slave'@'%.xyz.com' IDENTIFIED .... |
+---------------------------------------------------------------------------+

Step 7 Master建立第二条复制路径slave->master:
==========================================================================================
1. slave端获取Master Binary Log Coordinates (file+Position):
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000019 |     3080 |              |                  |
+------------------+----------+--------------+------------------+

2. Master端配置复制信息
CHANGE MASTER TO
MASTER_HOST='hwz.xyz.com',
MASTER_PORT=3201,
MASTER_USER='rep_slave',
MASTER_PASSWORD='rep4slave',
MASTER_LOG_FILE='mysql-bin.000019',
MASTER_LOG_POS=3080;

3. Master端启动复制
start slave;

Step 8: 验证个部分工作正常
==========================================================================================
1. At the master side:
show processlist;           有一个连接的rep4用户,就是binlog dump thread, 2个是system user是slave进程
show master status;
show status like 'slave%'; 
show slave status\G

2. At the slave side:
show processlist;           有2个连接是system user用户是slave进程,一个rep_slave用户是binlog dump thread
show master status;
show status like 'slave%';  stop slave后Slave_running显示为OFF
show slave status\G         这个语句的执行频率不要太高,有bug数据库会hung

Step 9: 复制测试
==========================================================================================
1. at the slave side:
use db3;
insert into dept (dname) values ('Slave');

2. at the Master side:
use db3;
insert into dept (dname) values ('Master');

3. at the slave side:
use mysql;
insert into db3.dept (dname) values ('db3.Slave');

4. at the Master side:
use mysql;
insert into db3.dept (dname) values ('db3.Master');

5. 在两端查询解结果是一样的
select * from db3.dept;
+----+------------+
| id | dname      |
+----+------------+
|  1 | IT         |
|  2 | HR         |
|  3 | CV         |
|  4 | NT         |
|  5 | CRDM       |
|  6 | Diabetes   |
|  7 | Sales      |
|  8 | Slave      |
|  9 | Master     |
| 10 | db3.Slave  |
| 11 | db3.Master |
+----+------------+