MySQL多源复制与Mycat实现读写分离最佳实践

suger

145

12页

0次

2022-10-16

100墨值下载

1/12

多源复制

实验环境：

1.关闭防火墙

2.关闭selinux

3.设置和解析主机名

192.168.0.13 master1.com

192.168.0.14 master2.com

192.168.0.11 slave.com

分别安装mysql5.7(略)

分别在master1 master2上建立不同的数据库已做测试

master1

mysql> create database test;

mysql> use test

mysql> create table test(name char(10));

mysql> insert into test values('robin');

mysql> insert into test values('zorro');

master2

mysql> create database db;

mysql> use db

mysql> create table t1(id int);

mysql> insert into t1 values(1);

mysql> insert into t1 values(2);

分别在master1 master2导出需要同步的数据库

master1

mysqldump -u root -pRobin_123 --databases test > /tmp/test.sql

master2

mysqldump -u root -pRobin_123 --databases db > /tmp/db.sql

分别在master1 master2上创建复制账号和密码

master1

grant replication slave on *.* to slave1@192.168.0.11 identified by "Slave_123";

master2

grant replication slave on *.* to slave2@192.168.0.11 identified by "Slave_123";

在slave节点测试

[root@web1 conf]# mysql -u slave1 -pSlave_123 -h 192.168.0.13

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 33

Server version: 5.7.18-log MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

[root@web1 conf]# mysql -u slave2 -pSlave_123 -h 192.168.0.14

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 25

Server version: 5.7.18 MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

2/12

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

修改

master_info_repository=TABLE

relay_log_info_repository=TABLE

master_info_repository

开启MTS功能后，务必将参数master_info_repostitory设置为TABL，这样性能可以有50%~80%的提升。这是因为并行复制开启后对于元

master.info这个文件的更新将会大幅提升，资源的竞争也会变大。在之前InnoSQL的版本中，添加了参数来控制刷新master.info这个文件的频

率，甚至可以不刷新这个文件。因为刷新这个文件是没有必要的，即根据master-info.log这个文件恢复本身就是不可靠的。在

MySQL 5.7中，推荐master_info_repository设置为TABLE，来减少这部分的开销。

relay_log_info_repository 同理

vim /etc/my.cnf

master_info_repository

=TABLE

relay_log_info_repository=TABLE

也可以在线修改

mysql> stop slave;

mysql> SET GLOBAL master_info_repository = 'TABLE';

mysql> SET GLOBAL relay_log_info_repository = 'TABLE';

将master1 master2备份的sql语句拷贝并导入slave数据库

拷贝

[root@web1 conf]# scp 192.168.0.13:/tmp/test.sql /tmp/

test.sql 100% 1937 1.9KB/s 00:00

[root@web1 conf]# scp 192.168.0.14:/tmp/db.sql /tmp/

db.sql 100% 1898 1.9KB/s 00:00

恢复

[root@web1 conf]# mysql -u root -pRobin_123 < /tmp/test.sql

mysql: [Warning] Using a password on the command line interface can be insecure.

[root@web1 conf]# mysql -u root -pRobin_123 < /tmp/db.sql

mysql: [Warning] Using a password on the command line interface can be insecure

分别找出master1 和master2的pos位置

master1

mysql> show master status\G

*************************** 1. row ***************************

File: master1.000001

Position: 154

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

master2

mysql> show master status\G

*************************** 1. row ***************************

File: master2.000001

Position: 154

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

slave

登录Slave进行同步操作，分别change master到两台Master服务器，后面以

FOR CHANNEL 'CHANNEL_NAME'区分

mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.13',MASTER_USER='slave1',

MASTER_PASSWORD='Slave_123',MASTER_LOG_FILE='master1.000001',MASTER_LOG_POS=154 FOR CHANNEL 'master1';

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

of 12

100墨值下载

mysql

关注

评论