mysql复制转发 blackhole

2019独角兽企业重金招聘Python工程师标准>>>

------------------------------------------------------|54.226(innodb)

54.220(master)---54.222(blackhole)----|54.227(innodb)

-------------------------------------------------------|54.228(innodb)

(1)规划主从主机
master:10.10.54.85
blackhole:10.10.54.87
innodb:10.10.54.86

(2)更改master配置文件
在主机：10.10.54.85上
[root@nan85 ~]# vim /etc/my.cnf 
log-bin=master-bin
binlog_format=mixed
server-id       = 10

(3)更改blackhole配置文件
在主机：10.10.54.87上
[root@nan87 ~]# vim /etc/my.cnf 
default-storage-engine=blackhole
log-slave-updates=1
log-bin=slave-bin
binlog_format=mixed
server-id       = 11

(4)更改从机配置文件
在主机：10.10.54.86
[root@nan88 ~]# vim /etc/my.cnf 
log-bin=slave-bin
binlog_format=mixed
server-id       = 12

(5)在master上创建复制用户，并授予权限
在主机：10.10.54.85上
mysql> grant replication slave on *.* to 'nan87'@'10.10.54.87' identified by 'nan87';
mysql> flush privileges;

(6)查看master上二进制日志和position位置
在主机：10.10.54.85上
mysql> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000002 |      334 |              |                  |
+-------------------+----------+--------------+------------------+

(7)在blackhole上创建复制用户，并授予权限
在主机：10.10.54.87上
mysql> grant replication slave on *.* to 'nan86'@'10.10.54.86' identified by 'nan86';
mysql> flush privileges;

(8)查看blackhole上二进制日志和position位置
在主机：10.10.54.87上
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| slave-bin.000024 |      339 |              |                  |
+------------------+----------+--------------+------------------+

(7)在blackhole上面change master操作
在主机：10.10.54.87上
mysql> change master to master_host='10.10.54.85',master_user='nan87', master_password='nan87', master_log_file='master-bin.000002',master_log_pos=334;
mysql> slave start;
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.54.85
                  Master_User: nan87
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000002
          Read_Master_Log_Pos: 334
               Relay_Log_File: nan87-relay-bin.000002
                Relay_Log_Pos: 259
        Relay_Master_Log_File: master-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes


(8)在slave上面change master操作
在主机：10.10.54.86上：
mysql> change master to master_host='10.10.54.87',master_user='nan86', master_password='nan86', master_log_file='slave-bin.000024',master_log_pos=339;
mysql> slave start;
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.54.87
                  Master_User: nan86
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: slave-bin.000029
          Read_Master_Log_Pos: 112
               Relay_Log_File: nan86-relay-bin.000012
                Relay_Log_Pos: 258
        Relay_Master_Log_File: slave-bin.000029
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

//测试转发：

(1)查看各个主机上的数据库：
master:10.10.54.85
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| employees          |
| mysql              |
| performance_schema |
| slave              |
| test               |
+--------------------+

blackhole:10.10.54.87
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| employees          |
| mysql              |
| performance_schema |
| test               |
+--------------------+

innodb:10.10.54.86
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| employees          |
| mysql              |
| performance_schema |
| slave              |
| test               |
+--------------------+

(2)在master上即10.10.54.85上
创建数据库sn,在里面创建表nan,插入数据
mysql> create database sn;
mysql> create table nan(age int) engine=innodb default charset=utf8;
mysql> insert into nan values(23);

(3)在blackhole上查看
即在主机：10.10.54.87上
mysql> use sn;
mysql> select * from nan;
+------+
| age  |
+------+
|   23 |
|   23 |
|   23 |
|   23 |
|   23 |
+------+

同样在slave上查看
即在主机上10。10.54.86上
mysql> use sn;
mysql> select * from nan;
+------+
| age  |
+------+
|   23 |
|   23 |
|   23 |
|   23 |
|   23 |
+------+

转载于:https://my.oschina.net/u/1454868/blog/203565