| 失效链接处理 |
|
MySQL多源复制与Mycat实现读写分离最佳实践 PDF 下载
本站整理下载：
相关截图：
主要内容：
多源复制
实验环境：
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)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
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)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
1/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)
|
| Java1234官方群25： | 
|
| Java1234官方群25： | 838462530 |
苏公网安备 32061202001004号
