mysql5.6.10 Replication 主从从提高性能

时间:2022-09-22 12:30:38
在实际的应用过程中,如果所有slave都直接连到master 上,所有的slave都会从master上接受binglog的内容,而当你业务扩张的时候,slave 节点的不断增加,这必然会对master增加负担,容易达到瓶颈。

      有一种提高Replication性能的方法,就是增加mysql Replication结构的深度,就是一个master 只Replication给一个slave,在由这个slave 复制给其他的slave。

      结构如下:

    mysql5.6.10 Replication 主从从提高性能

这张图中 master2 就是master1 的slave,而slave1,slave2,slave3 是master2 的slave

以下是大致的配置过程:

环境如下:

Maste1 :

      Hostname:media

      Ip:172.72.15.41

Master2:

      Hostname:rac1

      Ip:172.72.15.151

Slave1:

      Hostname:rac2

          Ip:172.72.15.152

Mysql的版本都是5.6.10

 

先配置master1端的/etc/my.cnf文件,加入下述配置,然后重启master1的MySQL服务:
binlog-format=ROW
log-bin=master-bin.log
log-bin-index=master-bin.index
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log-events=1
server-id=1
sync_binlog=1
report-port=3306
port=3306
report-host=hostname
innodb_flush_log_at_trx_commit=1


再修改master2、slave1、slave2、slave3端的my.cnf文件,加入下述配置,然后重启slave的MySQL服务:
binlog-format=ROW
log-bin=slave-bin.log
log-bin-index=slave-bin.index
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log-events=1
server-id=2
sync_binlog=1
report-port=3306
port=3306
report-host=hostname
innodb_flush_log_at_trx_commit=1
skip_parallel_works=4


注意:在master2端启用binlog,就是为了salve1 slave2 slave3 同步master2数据,所以采用和master1主服务器类似的配置。

 

环境中只复制new1这个数据库

一:导出数据:

在导出数据之前把数据库的表都锁上,并置于read only状态

1
2
3
4
5
6
7
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
  
mysql> set global read_only=on;
Query OK, 0 rows affected (0.00 sec)
  
mysql>

再开始maste1上面把数据导出来,由于5.6版本增加了gtid的功能,所以在用mysqldump导出的时候,可以通过 --set-gtid-purged 这个选项控制是否要用gtids来恢复,默认是开启的!

1
2
3
4
5
6
7
8
[root@media ~] # mysqldump -u root -psbcenter -B new1 > /tmp/new1.sql
Warning: Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass -- set -gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 
[root@media ~] #
[root@media ~] # scp /tmp/new1.sql 172.72.15.151:/root/
root@172.72.15.151's password: 
new1.sql                                                                                                                             100% 8346     8.2KB /s   00:00    
[root@media ~] #

二:到maste2,slave1上导入数据

 如果mysqldump 导出数据的时候 --set-gtid-purged 没有设置成off,那么master2启动mysqld 的时候需要启用gtid_mode=on

Master2上导入数据

[root@rac1 ~]# mysql < new1.sql

Slave1 上导入数据

[root@rac2 ~]# mysql < /root/new1.sql 

三:创建复制用户

在master 1 上创建master2的复制用户

mysql> GRANT REPLICATION SLAVE ON *.* TO 'rep1'@'172.72.15.151' IDENTIFIED BY  '123456';

mysql> flush privileges ;

Query OK, 0 rows affected (0.00 sec)

在master2上创建slave1 的复制用户

mysql> GRANT REPLICATION SLAVE ON *.* TO 'rep1'@'172.72.15.152' IDENTIFIED BY  '123456';

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

 

四:在master2和slave1 上配置master属性

在master2上配置master属性

mysql> change master to

    -> master_host='172.72.15.41',

    -> master_user='rep1',

    -> master_password='123456',

    -> master_auto_position=1;

Query OK, 0 rows affected, 2 warnings (0.17 sec)

 

mysql>start slave;

 

在slave1 上配置master属性

mysql> change master to

    -> master_host='172.72.15.152',

    -> master_user='rep1',

    -> master_password='123456',

    -> master_auto_position=1;

Query OK, 0 rows affected, 2 warnings (0.17 sec)

 

mysql>start slave;

 

---------------------------------------------

注意:主从不同步,请查阅http://blog.csdn.net/magerguo/article/details/9145329

-----------------------------------------------------

五:验证!

在master1 的new1上创建一张表

mysql> use new1

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

 

Database changed

mysql>

mysql>

mysql> show tables;

+----------------+

| Tables_in_new1 |

+----------------+

| a              |

+----------------+

1 row in set (0.00 sec)

 

mysql> create table b like a;

Query OK, 0 rows affected (0.24 sec)

 

mysql> insert into b select * from a;

Query OK, 1031 rows affected (0.13 sec)

Records: 1031  Duplicates: 0  Warnings: 0

 

mysql>

 

 

到master2查看是否已经复制成功

mysql> use new1;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

 

Database changed

mysql> show tables;

+----------------+

| Tables_in_new1 |

+----------------+

| a              |

| b              |

+----------------+

2 rows in set (0.00 sec)

 

mysql> select count(*) from b;

+----------+

| count(*) |

+----------+

|     1031 |

+----------+

1 row in set (0.00 sec)

 

mysql>

 

再到slave1 上查看

mysql> use new1

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

 

Database changed

mysql> show tables;

+----------------+

| Tables_in_new1 |

+----------------+

| a              |

| b              |

+----------------+

2 rows in set (0.00 sec)

 

mysql> select count(*) from bl

    -> ;

ERROR 1146 (42S02): Table 'new1.bl' doesn't exist

mysql> select count(*) from b;

+----------+

| count(*) |

+----------+

|     1031 |

+----------+

1 row in set (0.00 sec)

 

mysql>

 

 

 整个过程就是这样,另外要提及的一点是如果mysql 版本是5.6以下的或者是没开启gtids

的,需要在master2上配置log-slave-updates 参数,这个参数的作用是master2接受到master1的binlog到relay日志中去时,也会更新到自己的binlog中去,以便给它的slave使用!