mysql 转发_mysql复制转发 blackhole

------------------------------------------------------|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 |

+------+