MySQL中建立Master-Master复制

platinaluo

双向的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 | +----+------------+