本文详细介绍了MySQL的级联复制和多源复制的配置步骤。首先通过mysql-utilities工具设置基于filename和pos的级联复制，然后演示了解决在数据一致性前提下搭建主从复制的问题。接着讲解了级联复制的实现，包括创建复制账户、设置复制日志点位置等。最后，文章阐述了多源复制的配置，强调了在多主架构与多源架构的区别，并给出了具体的配置示例。
级联复制的结构如图
我们来设置基于filename和pos的级联复制，并且接受mysql-utilities工具中mysqlreplicate的用法！
首先在MySQL官网下载mysql-utilities工具
[root@test2 src]# tar zxvf mysql-utilities-1.6.5.tar.gz[root@test2 src]# cd mysql-utilities-1.6.5
[root@test2 mysql-utilities-1.6.5]# python setup.py install
#工具已经安装完毕
[root@test3 ~]# mysqlreplicate --version
MySQL Utilities mysqlreplicate version 1.6.5
License type: GPLv
master：10.0.102.214 test3
slave ：10.0.102.204 test2
slave-2: 10.0.102.179 test1
第一步：首先要保证要备份的主从服务器数据的一致,先搭建主从(master---slave)
[root@test3 ~]# mysqldump -uroot -p123456 --single-transaction --all-databases > all.sql #这里的提示，因为是测试环境因此没有加--set-gtid-purged=OFF参数
mysqldump: [Warning] Using a password onthe command line interface can be insecure.
Warning: A partialdump 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 do not want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
[root@test2 ~]# mysql -uroot -p123456 < all.sql #在从库上导入数据，这里报错了
mysql:[Warning] Using a password onthe command line interface can be insecure.
ERROR1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED isempty.[root@test2 ~]# mysql -uroot -p123456 #解决办法
mysql:[Warning] Using a password onthe command line interface can be insecure.
Welcometo the MySQL monitor. Commands end with ; or\g.
Your MySQL connection idis 5Server version:5.7.22-logMySQL Community Server (GPL)
Copyright (c)2000, 2018, Oracle and/or its affiliates. Allrights reserved.
Oracleis a registered trademark of Oracle Corporation and/orits
affiliates. Other names may be trademarksoftheir respective
owners.
Type'help;' or '\h' for help. Type '\c' to clear the currentinput statement.
mysql> select @@GLOBAL.GTID_EXECUTED;+------------------------------------------+
| @@GLOBAL.GTID_EXECUTED |
+------------------------------------------+
| fc5f303f-f6c1-11e8-98bc-fa1dae125200:1-3 |
+------------------------------------------+
1 row in set (0.00sec)
mysql>reset master;
Query OK,0 rows affected (0.01sec)
mysql> select @@GLOBAL.GTID_EXECUTED;+------------------------+
| @@GLOBAL.GTID_EXECUTED |
+------------------------+
| |
+------------------------+
1 row in set (0.00sec)
mysql> exitBye[root@test2 ~]# mysql -uroot -p123456 < all.sql
数据已经保持一致：
在主上查看filename和pos：
mysql>show master status;+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| test3-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
在从或主服务器上执行如下命令：【需要注意的是这个--rpl-user指定的用户名和密码需要在master上创建】
[root@test2 ~]# mysqlreplicate --master=root:123456@10.0.102.214 --slave=root:123456@10.0.102.204 --rpl-user=repl:123456 --master-log-file=test3-bin.000001 --master-log-pos=154 -vv
WARNING: Using a password on the command line interface can be insecure.
# master on10.0.102.214: ... connected.
# slave on10.0.102.204: ... connected.
# masterid = 5# slaveid = 3# master uuid= 4687e05d-f37f-11e8-8fc7-fa336351fc00
# slave uuid= f1983579-f6c4-11e8-8651-fa1dae125200
# Checking InnoDB statisticsfortype and version conflicts.
# Checking storage engines...
# Checkingforbinary logging on master...
# Setting up replication...
# Connecting slave to master...
# CHANGE MASTER TO MASTER_HOST= '10.0.102.214', MASTER_USER = 'repl', MASTER_PASSWORD = '123456', MASTER_PORT = 3306, MASTER_AUTO_POSITION=1# Starting slave from master logfile 'test3-bin.000001' using position 154...
# IO status: Waitingformaster to send event
# IO thread running: Yes
# IO error: None
# SQL thread running: Yes
# SQL error: None
# ...done.
mysqlreplicate命令的用法如下：
[root@test2 log]# mysqlreplicate --help
MySQL Utilities mysqlreplicate version1.6.5License type: GPLv2
Usage: mysqlreplicate--master=root@localhost:3306 --slave=root@localhost:3310 --rpl-user=rpl:passwdmysqlreplicate-establish replication with a master
Options:--version show program's version number and exit
--help display a help message and exit--license display program's license and exit
--master=MASTER connection information for master server inthe form:[:]@[:][:] or
[:][:] or
path>[].--slave=SLAVE connection information for slave server inthe form:[:]@[:][:] or
[:][:] or
path>[].--rpl-user=RPL_USER the user and password forthe replication user
requirement,in the form: [:] or. E.g. rpl:passwd
-p, --pedantic fail ifstorage engines differ among master and slave.--test-db=TEST_DB database name to use intesting replication setup
(optional)--master-log-file=MASTER_LOG_FILE
use this master logfileto initiate the slave.--master-log-pos=MASTER_LOG_POS
use this positionin the master log fileto initiate
the slave.-b, --start-from-beginning
start replication from the first event recordedinthe
binary logging of the master. Not valid with--master-log-file or --master-log-pos.-v, --verbose control how much information is displayed. e.g., -v =verbose,-vv = more verbose, -vvv =debug-q, --quiet turn off all messages for quiet execution.
#这个--rpl-user指定的用户名和密码，不能识别在master上创建的使用“%”的标识，也就是master需要指定创建"repl"@"10.0.102.204"的用户名，
"repl"@"%"好像就不可以。
从服务器上一定要开启二进制日志和log_slave_updates参数。因为若是不设置log_slave_updates，从服务器只是开启了二进制日志，但是却没有向二进制日志里面写入数据。
测试如下：从没有开启log_slave_updates！
#从服务器二进制日志行数
[root@test2 mysql]# mysqlbinlog test2-bin.000001 | wc -l20#主上插入数据
mysql> insert into tb2 select null;
Query OK,1 row affected (0.02sec)
Records:1 Duplicates: 0 Warnings: 0mysql> insert into tb2 select null;
Query OK,1 row affected (0.00sec)
Records:1 Duplicates: 0 Warnings: 0mysql> insert into tb2 select null;
Query OK,1 row affected (0.00sec)
Records:1 Duplicates: 0 Warnings: 0mysql> insert into tb2 select null;
Query OK,1 row affected (0.00sec)
Records:1 Duplicates: 0 Warnings: 0mysql> insert into tb2 select null;
Query OK,1 row affected (0.01sec)
Records:1 Duplicates: 0 Warnings: 0mysql> insert into tb2 select null;
Query OK,1 row affected (0.01sec)
Records:1 Duplicates: 0 Warnings: 0mysql> insert into tb2 select null;
Query OK,1 row affected (0.01sec)
Records:1 Duplicates: 0 Warnings: 0#从服务器数据同步过来
mysql> select *from tb2;+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+----+
7 rows in set (0.00sec)
#但是二进制行数没有变，也就是二进制日志没有增加
[root@test2 mysql]# mysqlbinlog test2-bin.000001 | wc -l20
log_slave_updates参数测试
slave服务器log_slave_updates，以当前的slave为主，做salve~slave-2的主从！
把当前slave的数据备份，在slave-2上恢复！
【过程略，参考上面的】
从服务器上的server-id要设置为唯一的！【在集群中是唯一的】
mysqlreplicate --master=root:123456@10.0.102.204 --slave=root:123456@10.0.102.179 --rpl-user=repl:123456 --master-log-file=test2-bin.000002 --master-log-pos=1597 -vv
#repl-user:指定的用户需要提前在对应的master上创建！
使用show slave status检查是否成功！
mysql>desc test1;+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a | int(11) | NO | PRI | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00sec)
mysql> create table test2(host varchar(30));
Query OK,0 rows affected (0.02sec)
mysql> insert into test2 select@@hostname;
Query OK,1 row affected (0.02sec)
Records:1 Duplicates: 0 Warnings: 0mysql> select *from test2;+-------+
| host |
+-------+
| test3 |
+-------+
1 row in set (0.00sec)
mysql>slave上查看
mysql> select@@hostname;+------------+
| @@hostname |
+------------+
| test2 |
+------------+
1 row in set (0.00sec)
mysql> select *from test2;+-------+
| host |
+-------+
| test3 |
+-------+
1 row in set (0.00sec)
slave-2上查看数据
mysql> select@@hostname;+------------+
| @@hostname |
+------------+
| test1 |
+------------+
1 row in set (0.00sec)
mysql> select *from test2;+-------+
| host |
+-------+
| test3 |
+-------+
1 row in set (0.00 sec)
级联复制测试
多源复制
一个slave连接多个master并接收所有来自master的变更，这种架构称为多源架构。
注意与多主架构的区分：在多源架构中，变更来自多个master;而在多主拓补架构中，将每个master上的变更复制到其他所有的master，整个服务器扮演单个master的角色。
若数据库的状态不一致，需要先备份，恢复数据使其达到一致的状态！
master1 ---test1------10.0.102.179
master2 ---test2------10.0.102.204
slave ---test3------10.0.102.214
多源复制过程与传统异步复制过程是一样的！
创建复制账户，查看复制日志点位置！
#在两个从上执行
mysql> grant all privileges on *.* to "repl"@"%" identified by "123456";
Query OK,0 rows affected, 1 warning (0.01sec)
mysql> show master status; #查看日志点的位置
然后再从上开启复制：
mysql> change master to master_host="10.0.102.204", master_user="root",master_password="123456",master_log_file="test2-bin.000003",master_log_pos=514 for channel "ch1";
ERROR3077(HY000): To have multiple channels, repository cannot be of type FILE; Please check the repository configuration and convert them to TABLE.
mysql>
#这里报错需要设置relay_log_info_repository 为table，把relay-log的信息写进表中！
master_info_repository = TABLE #建议设置为table
relay_log_recovery = 1 #I/O thread crash safe
relay_log_info_repository = TABLE # SQL thread crash safe
read_only = 1
super_read_only = on #mysql5.7 加入的
设置之后重启服务器：
语句的后面加上了for channel！
mysql> change master to master_host="10.0.102.204", master_user="root",master_password="123456",master_log_file="test2-bin.000003",master_log_pos=514 for channel "ch1";
Query OK, 0 rows affected, 2 warnings (0.05 sec)
mysql> change master to master_host="10.0.102.204", master_user="root",master_password="123456",master_log_file="test2-bin.000001",master_log_pos=154 for channel "ch2";
Query OK, 0 rows affected, 2 warnings (0.03 sec)
#分别启动
mysql> start slave for channel "ch1";
Query OK, 0 rows affected (0.04 sec)
mysql> start slave for channel "ch2";
Query OK, 0 rows affected (0.00 sec)
然后可以使用show slave status for channel 命令查看对应的状态！
扫一扫
4844
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
