MySQL 5.6 不支持多主复制,但可以blackhole存储引擎表模拟多主复制。将server1复制到server2,再从server2复制到备库。如果在server2上为从server1上复制的数据使用blackhole存储引擎,就不会包含任何server1的数据,如下图所示。
一、环境
MySQL 5.6.14
server1的IP 192.168.1.1,my.cnf如下图:
server2的IP 192.168.1.2,my.cnf如下图:
server3的IP 192.168.1.100,my.cnf如下图:
二、建立测试库表
在server1的MySQL上执行:
- create database db1;
- use db1;
- create table t1(a int);
- insert into t1 values (11),(12),(13);
- commit;
在server2的MySQL上执行:
- create database db2;
- use db2;
- create table t1(a int);
- insert into t1 values (21),(22),(23);
- commit;
三、搭建server2到server3的主从复制
在server2的MySQL上执行:
- CREATE USER 'repl'@'%' IDENTIFIED BY '123456';
- GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
在server3上执行:
- mysqldump --single-transaction --databases db2 --master-data=1 --host=192.168.1.2 --user user1 -p123456 --port 6603 > server2.sql
- mysql -u user1 -p < server2.sql
- more server2.sql | grep CHANGE # 找到复制的起始文件和偏移量,用于下面的SQL命令
在server3的MySQL上执行:
- CHANGE MASTER TO
- MASTER_LOG_FILE='mysql-bin.000008',
- MASTER_LOG_POS=7375,
- MASTER_HOST='192.168.1.2',
- MASTER_USER='repl',
- MASTER_PASSWORD='123456',
- MASTER_PORT=6603;
-
- start slave;
- show slave status\G
四、搭建server1到server2的主从复制
在server1的MySQL上执行:
- CREATE USER 'repl'@'%' IDENTIFIED BY '123456';
- GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
在server2上执行:
- mysqldump
- mysql -u wxy -p < server1.sql
- more server1.sql | grep CHANGE # 找到复制的起始文件和偏移量,用于下面的SQL命令
在server2的MySQL上执行:
- CHANGE MASTER TO
- MASTER_LOG_FILE='mysql-bin.000013',
- MASTER_LOG_POS=3499,
- MASTER_HOST='192.168.1.1',
- MASTER_USER='repl',
- MASTER_PASSWORD='123456',
- MASTER_PORT=6603;
-
- start slave;
- show slave status\G
-
-
- select concat('alter table ',table_schema,'.',table_name,' engine=blackhole;') from information_schema.tables where table_schema='db1';
- SET sql_log_bin=0;
- alter table db1.t1 engine=blackhole;
- SET sql_log_bin=1;
五、测试
在server1的MySQL上执行:
- use db1;
- insert into t1 values (111),(121),(131);
- delete from t1 where a = 11;
- update t1 set a=102 where a=12;
- update t1 set a=103 where a=13;
- commit;
在server2的MySQL上执行:
- use db2;
- insert into t1 values (211),(221),(231);
- delete from t1 where a = 21;
- update t1 set a=202 where a=22;
- update t1 set a=203 where a=23;
- commit;
在server3的MySQL上执行:
- use db1;
- select * from t1;
-
- use db2;
- select * from t1;
结果如下图: