mysql 5.6#1275_提高mysql的Replication 性能

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

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

一：导出数据：

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

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来恢复，默认是开启的！

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

五：验证！

在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使用！