mysql的级联复制和多源复制
MySQL的复制： https://www.cnblogs.com/wxzhe/p/10051114.html
级联复制的结构如图
我们来设置基于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 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 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 on the command line interface can be insecure. ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty. [root@test2 ~]# mysql -uroot -p123456 #解决办法 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 5 Server version: 5.7.22-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, 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> select @@GLOBAL.GTID_EXECUTED; +------------------------------------------+ | @@GLOBAL.GTID_EXECUTED | +------------------------------------------+ | fc5f303f-f6c1-11e8-98bc-fa1dae125200:1-3 | +------------------------------------------+ 1 row in set (0.00 sec) mysql> reset master; Query OK, 0 rows affected (0.01 sec) mysql> select @@GLOBAL.GTID_EXECUTED; +------------------------+ | @@GLOBAL.GTID_EXECUTED | +------------------------+ | | +------------------------+ 1 row in set (0.00 sec) mysql> exit Bye [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 on 10.0.102.214: ... connected. # slave on 10.0.102.204: ... connected. # master id = 5 # slave id = 3 # master uuid = 4687e05d-f37f-11e8-8fc7-fa336351fc00 # slave uuid = f1983579-f6c4-11e8-8651-fa1dae125200 # Checking InnoDB statistics for type and version conflicts. # Checking storage engines... # Checking for binary 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 log file 'test3-bin.000001' using position 154... # IO status: Waiting for master 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 version 1.6.5 License type: GPLv2 Usage: mysqlreplicate --master=root@localhost:3306 --slave=root@localhost:3310 --rpl-user=rpl:passwd mysqlreplicate - 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 in the form: <user>[:<password>]@<host>[:<port>][:<socket>] or <login-path>[:<port>][:<socket>] or <config- path>[<[group]>]. --slave=SLAVE connection information for slave server in the form: <user>[:<password>]@<host>[:<port>][:<socket>] or <login-path>[:<port>][:<socket>] or <config- path>[<[group]>]. --rpl-user=RPL_USER the user and password for the replication user requirement, in the form: <user>[:<password>] or <login-path>. E.g. rpl:passwd -p, --pedantic fail if storage engines differ among master and slave. --test-db=TEST_DB database name to use in testing replication setup (optional) --master-log-file=MASTER_LOG_FILE use this master log file to initiate the slave. --master-log-pos=MASTER_LOG_POS use this position in the master log file to initiate the slave. -b, --start-from-beginning start replication from the first event recorded in the 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 -l 20 #主上插入数据 mysql> insert into tb2 select null; Query OK, 1 row affected (0.02 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into tb2 select null; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into tb2 select null; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into tb2 select null; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into tb2 select null; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into tb2 select null; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into tb2 select null; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 #从服务器数据同步过来 mysql> select * from tb2; +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | +----+ 7 rows in set (0.00 sec) #但是二进制行数没有变，也就是二进制日志没有增加 [root@test2 mysql]# mysqlbinlog test2-bin.000001 | wc -l 20
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.00 sec) mysql> create table test2(host varchar(30)); Query OK, 0 rows affected (0.02 sec) mysql> insert into test2 select @@hostname; Query OK, 1 row affected (0.02 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select * from test2; +-------+ | host | +-------+ | test3 | +-------+ 1 row in set (0.00 sec) mysql> slave上查看 mysql> select @@hostname; +------------+ | @@hostname | +------------+ | test2 | +------------+ 1 row in set (0.00 sec) mysql> select * from test2; +-------+ | host | +-------+ | test3 | +-------+ 1 row in set (0.00 sec) slave-2上查看数据 mysql> select @@hostname; +------------+ | @@hostname | +------------+ | test1 | +------------+ 1 row in set (0.00 sec) 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.01 sec) 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"; ERROR 3077 (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 命令查看对应的状态！
