使用blackhole存储引擎表模拟多主复制

  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上执行：

[sql] view plain copy

 

1. create database db1;  
2. use db1;  
3. create table t1(a int);  
4. insert into t1 values (11),(12),(13);  
5. commit;  

在server2的MySQL上执行：

[sql] view plain copy

 

1. create database db2;  
2. use db2;  
3. create table t1(a int);  
4. insert into t1 values (21),(22),(23);  
5. commit;  

三、搭建server2到server3的主从复制

在server2的MySQL上执行：

[sql] view plain copy

 

1. CREATE USER 'repl'@'%' IDENTIFIED BY '123456';  
2. GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';  

在server3上执行：

[plain] view plain copy

 

1. mysqldump --single-transaction --databases db2 --master-data=1 --host=192.168.1.2 --user user1 -p123456 --port 6603 > server2.sql  
2. mysql -u user1 -p < server2.sql  
3. more server2.sql | grep CHANGE # 找到复制的起始文件和偏移量，用于下面的SQL命令  

在server3的MySQL上执行：

[sql] view plain copy

 

1. CHANGE MASTER TO   
2. MASTER_LOG_FILE='mysql-bin.000008',   
3. MASTER_LOG_POS=7375,  
4. MASTER_HOST='192.168.1.2',  
5. MASTER_USER='repl',  
6. MASTER_PASSWORD='123456',  
7. MASTER_PORT=6603;  
8.   
9. start slave;  
10. show slave status\G  

四、搭建server1到server2的主从复制

在server1的MySQL上执行：

[sql] view plain copy

 

1. CREATE USER 'repl'@'%' IDENTIFIED BY '123456';  
2. GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';  

在server2上执行：

[sql] view plain copy

 

1. mysqldump --single-transaction --databases db1 --master-data=1 --host=192.168.1.1 --user user1 -p123456 --port 6603 > server1.sql  
2. mysql -u wxy -p < server1.sql  
3. more server1.sql | grep CHANGE # 找到复制的起始文件和偏移量，用于下面的SQL命令  

在server2的MySQL上执行：

[sql] view plain copy

 

1. CHANGE MASTER TO   
2. MASTER_LOG_FILE='mysql-bin.000013',   
3. MASTER_LOG_POS=3499,  
4. MASTER_HOST='192.168.1.1',  
5. MASTER_USER='repl',  
6. MASTER_PASSWORD='123456',  
7. MASTER_PORT=6603;  
8.   
9. start slave;  
10. show slave status\G  
11.   
12. -- 这时在server3上已经有了server1的数据，现在要把server2上的db1中的表改为blackhole引擎，并且这步修改不要写入binlog  
13. select concat('alter table ',table_schema,'.',table_name,' engine=blackhole;') from information_schema.tables where table_schema='db1';  
14. SET sql_log_bin=0;  
15. alter table db1.t1 engine=blackhole;   
16. SET sql_log_bin=1;  

五、测试

在server1的MySQL上执行：

[sql] view plain copy

 

1. use db1;  
2. insert into t1 values (111),(121),(131);  
3. delete from t1 where a = 11;  
4. update t1 set a=102 where a=12;  
5. update t1 set a=103 where a=13;  
6. commit;  

在server2的MySQL上执行：

[sql] view plain copy

 

1. use db2;  
2. insert into t1 values (211),(221),(231);  
3. delete from t1 where a = 21;  
4. update t1 set a=202 where a=22;  
5. update t1 set a=203 where a=23;  
6. commit;  

在server3的MySQL上执行：

[sql] view plain copy

 

1. use db1;  
2. select * from t1;  
3.   
4. use db2;  
5. select * from t1;  

结果如下图：