026：简单复制

026：复制

一. 复制

常见数据库复制模式对比

线上必须设置为 binlog_format = row ，如果希望通过 binlog 实现 flashback 的功能（网易的 mysqlbinlog -B ），则必须设置 binlog_row_image=FULL （默认），保证所有的列都出现在binlog中。（FULL对性能影响不大，仅仅对空间占用较多）

1.1 基于binlog刷新和恢复

1.1.1 binlog格式

Table Map ：记录了一些元数据，比如列的类型等等
- 如果没有这个记录，就不知道第一列(@1)是哪个列，是什么类型等等信息
Rotate ：binlog日志分割
Query：查询
Update/Write/Delete Rows：对行的操作

命令 flush binary logs; 可以强制刷新binlog到磁盘，并且产生一个新的日志（重启MySQL 也会产生新的日志）,
参数 max_binlog_size 可以设置一个binlog日志的最大的 大小。

[root@node1 mysqldata]# mysqlbinlog binlog.000013 -vv
---------------省略部分-------------------------------------------------------------
create database mytest
/*!*/;
# at 359
#180214 15:05:44 server id 8888  end_log_pos 424 CRC32 0xc8484ebb 	GTID	last_committed=1	sequence_number=2
SET @@SESSION.GTID_NEXT= '9dc847d8-bf72-11e7-9ec4-000c2998e4f1:31'/*!*/;
# at 424
#180214 15:05:44 server id 8888  end_log_pos 530 CRC32 0xf7f59f56 	Query	thread_id=5	exec_time=1	error_code=0
use `mytest`/*!*/;
SET TIMESTAMP=1518591944/*!*/;
create table t1(a int,b int)
/*!*/;
# at 530
#180214 15:06:25 server id 8888  end_log_pos 595 CRC32 0xc2698315 	GTID	last_committed=2	sequence_number=3
SET @@SESSION.GTID_NEXT= '9dc847d8-bf72-11e7-9ec4-000c2998e4f1:32'/*!*/;
# at 595
#180214 15:06:25 server id 8888  end_log_pos 669 CRC32 0x9d13c3d8 	Query	thread_id=5	exec_time=1	error_code=0
SET TIMESTAMP=1518591985/*!*/;
BEGIN
/*!*/;
# at 669
#180214 15:06:25 server id 8888  end_log_pos 717 CRC32 0x05aa5af7 	Table_map: `mytest`.`t1` mapped to number 225
# at 717
-- at后面的数字表示的是文件的 偏移量，也就是常用的 start-position
#180214 15:06:25 server id 8888  end_log_pos 761 CRC32 0x6b56aca9 	Write_rows: table id 225 flags: STMT_END_F

-- 180214 15:06:25 表示该event开始的时间，YYMMDD HH:MM:SS（如果是备机，就是传递到备机上的时间）
-- server id 表示 MySQL服务器的ID
-- end_log_pos 表示下一个event的position
-- Query 表示事件的类型
-- thread_id 表示执行的线程ID
-- exec_time 表示执行的时间
-- error_code 表示执行的code，0表示没有错误

BINLOG '
8d+DWhO4IgAAMAAAAM0CAAAAAOEAAAAAAAEABm15dGVzdAACdDEAAgMDAAP3WqoF
8d+DWh64IgAALAAAAPkCAAAAAOEAAAAAAAEAAgAC//wBAAAACgAAAKmsVms=
'/*!*/;
### INSERT INTO `mytest`.`t1`
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2=10 /* INT meta=0 nullable=1 is_null=0 */
# at 761
#180214 15:06:25 server id 8888  end_log_pos 792 CRC32 0x658b9951 	Xid = 27
COMMIT/*!*/;
# at 792
#180214 15:06:29 server id 8888  end_log_pos 857 CRC32 0xcbef0b52 	GTID	last_committed=3	sequence_number=4
SET @@SESSION.GTID_NEXT= '9dc847d8-bf72-11e7-9ec4-000c2998e4f1:33'/*!*/;
# at 857
#180214 15:06:29 server id 8888  end_log_pos 931 CRC32 0x33da22fc 	Query	thread_id=5	exec_time=0	error_code=0
SET TIMESTAMP=1518591989/*!*/;
BEGIN
/*!*/;
# at 931
#180214 15:06:29 server id 8888  end_log_pos 979 CRC32 0x2e7be109 	Table_map: `mytest`.`t1` mapped to number 225
# at 979
#180214 15:06:29 server id 8888  end_log_pos 1023 CRC32 0xdeda3369 	Write_rows: table id 225 flags: STMT_END_F

BINLOG '
9d+DWhO4IgAAMAAAANMDAAAAAOEAAAAAAAEABm15dGVzdAACdDEAAgMDAAMJ4Xsu
9d+DWh64IgAALAAAAP8DAAAAAOEAAAAAAAEAAgAC//wCAAAAFAAAAGkz2t4=
'/*!*/;
### INSERT INTO `mytest`.`t1`
### SET
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
###   @2=20 /* INT meta=0 nullable=1 is_null=0 */
# at 1023
#180214 15:06:29 server id 8888  end_log_pos 1054 CRC32 0x8bbeb6e3 	Xid = 28
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@node1 mysqldata]#

1.1.2 binlog恢复

注意，如果你有 多个binlog文件想要恢复，不要一个一个顺序恢复

shell> mysqlbinlog binlog.000001 | mysql -u root -p ## DANGER!!

shell> mysqlbinlog binlog.000002 | mysql -u root -p ## DANGER!!

上面这种恢复方式是错误的，如果 binlog.000001 中创建了一个临时表（CREATE TEMPORARY TABLE），而 binlog.000002 中要使用这个临时表，但是第一个线程（binlog.000001）在 释放 的时候会 删除临时表 ，此时第二个线程(binlog.000002) 就无法使用这个临时表了

正确的做法如下：

shell> mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p

---------------OR----------------

shell> mysqlbinlog binlog.000001 > /tmp/statements.sql shell> mysqlbinlog binlog.000002 >> /tmp/statements.sql

---------------OR----------------

shell> mysqlbinlog binlog.00000[1-2] > /tmp/statements.sql shell> mysql -u root -p -e "source /tmp/statements.sql"

注意：mysqlbinlog的参数 start/stop-position 不能是中间位置 ，必须是在 binlog 文件中 at 后面跟着的一个数字（必须是一个边界值）。参数 start/stop-datatime 可以通过时间戳来进行恢复

基于position

shell> mysqlbinlog bin.000017 --start-position=1959 --stop-position=2057 -vv > /tmp/a.sql

基于datetime

shell> mysqlbinlog bin.000017 --start-datetime="2016-03-02 21:03:58" --stop-datetime="2016-03-02 23:14:06" -vv > /tmp/a.sql 
shell> mysql -u root -p < a.sql

start和stop的范围是 [start, stop)

-- 在mysql中查看events信息 （from pos limit N,[M]）
(root@localhost) 15:06:35 [mytest]>  show binlog events;
+---------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                                               |
+---------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| binlog.000011 |   4 | Format_desc    |      8888 |         123 | Server ver: 5.7.18-log, Binlog ver: 4                              |
| binlog.000011 | 123 | Previous_gtids |      8888 |         194 | 9dc847d8-bf72-11e7-9ec4-000c2998e4f1:1-28                          |
| binlog.000011 | 194 | Gtid           |      8888 |         259 | SET @@SESSION.GTID_NEXT= '9dc847d8-bf72-11e7-9ec4-000c2998e4f1:29' |
| binlog.000011 | 259 | Query          |      8888 |         384 | use `employees`; DROP TABLE `t1` /* generated by server */         |
| binlog.000011 | 384 | Stop           |      8888 |         407 |                                                                    |
+---------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
5 rows in set (0.00 sec)

1.2 MySQL主从复制架构

主服务器
- 在 MySQL 5.7中， prepare log 部分的日志也是 组提交 的
- prepare log 和 commit log 写 redo file（iblogfile1、iblogfile2）
- binlog 写 binlig.00000X 文件
- MySQL Dump Thread 把 binlog 推送到远程的Slave服务器
  - 每一个Slave，就会对应有一个dump线程
- 同时，在MySQL主机上还有一个 Master Thread 在每隔1秒从redo log buffer中写入redo file
从服务器
- IO Thread 负责 接收 Dump线程发送过来的 binlog ，并且记录到本地的 relay log
- 接受的 单位 是 event
- SQL Thread/Coordinator Thread 负责将relay log中的日志 回放 到从机
  - 回放的 单位 也是 event
- 有了多线程以后，coordinator线程负责任务指派，work thread负责回放
- 在 MySQL5.6 中的多线程回放是 基于库 的，单个库还是单线程
- 在 MySQL5.7 中的多线程是在 主上如何并行执行的 ， 从机上也是如何并行回放的
- master-info.log 存放了 接收 到的binlog的 位置 （ event的位置）
- relay-info.log 存放了 回放 到的relay log的 位置 （ event的位置）

二. 可传输表空间

简单的说，就是将一个表空间文件（ibd文件），拷贝到远程另外一台数据库进行恢复，进行物理复制。

2.1. innodb 独立表空间导入和导出

操作步骤：
- 目的服务器：ALTER TABLE t DISCARD TABLESPACE;
- 源服务器：FLUSH TABLES t FOR EXPORT;
- 源服务器：拷贝t.ibd,t.cfg文件到目的服务器
- 源服务器：UNLOCK TABLES;
- 目的服务器：ALTER TABLE t IMPORT TABLESPACE;

2.2. 演示

hostname	逻辑库	表
node1.gczheng.com	mytest	t1
node2.gczheng.com	mytest	空

将node1中mytest库下面的t1表，传输node2中mytest库中

1、源服务器查看迁移表状态
源服务器

(root@localhost) 10:44:22 [mysql]> create database tablespace;
Query OK, 1 row affected (0.01 sec)

(root@localhost) 10:44:31 [mysql]> use tablespace;
Database changed

(root@localhost) 10:45:09 [tablespace]> create table qqq(a int);
Query OK, 0 rows affected (0.02 sec)

(root@localhost) 10:45:37 [tablespace]> insert into qqq values(1);
Query OK, 1 row affected (0.01 sec)

(root@localhost) 10:45:50 [tablespace]> select * from qqq;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

2、在目标服务器上创建表空间
目标服务器

(root@localhost) 10:46:59 [(none)]> create database tablespace;
Query OK, 1 row affected (0.00 sec)

(root@localhost) 10:47:18 [(none)]> use tablespace;
Database changed
(root@localhost) 10:47:24 [tablespace]> create table qqq(a int);
Query OK, 0 rows affected (0.01 sec)

创建完成后进行检查

[root@node2 tablespace]# ll
total 112
-rw-r----- 1 mysql mysql    61 Feb 15 10:47 db.opt
-rw-r----- 1 mysql mysql  8554 Feb 15 10:48 qqq.frm   --表结构
-rw-r----- 1 mysql mysql 98304 Feb 15 10:48 qqq.ibd   --表空间，需要通过 DISCARD 将表空间文件删除

ALTER TABLE qqq DISCARD TABLESPACE; 的含义是 保留qqq.frm文件，删除qqq.ibd。

通过discard 删除ibd文件

(root@localhost) 10:48:07 [tablespace]> ALTER TABLE qqq DISCARD TABLESPACE;
Query OK, 0 rows affected (0.01 sec)

[root@node2 tablespace]# ll
total 16
-rw-r----- 1 mysql mysql   61 Feb 15 10:47 db.opt
-rw-r----- 1 mysql mysql 8554 Feb 15 10:48 qqq.frm

--已经删除表空间qqq.ibd

3、源服务器导出表空间

在源服务器上，通过export 命令导出表空间（同时加读锁）

源服务器

(root@localhost) 10:45:56 [tablespace]> FLUSH TABLES qqq FOR EXPORT;  --其实是对这个表加一个读锁
Query OK, 0 rows affected (0.01 sec)

将导出的cfg文件和ibd文件，拷贝到目标服务器的mytest库下

[root@node1 mytest]# ll
total 24
-rw-r----- 1 mysql mysql   61 Feb 14 15:05 db.opt
-rw-r----- 1 mysql mysql  416 Feb 14 18:12 t1.cfg    --export后，多出来的文件，里面保存了一些元数据信息	
-rw-r----- 1 mysql mysql 8578 Feb 14 16:35 t1.frm
-rw-r----- 1 mysql mysql  416 Feb 14 17:09 t1.ibd

[root@node1 mysqldata]# scp tablespace/qqq.cfg tablespace/qqq.ibd  node2:/r2/mysqldata/tablespace/
qqq.cfg                                                                                                                                   100%  373   360.4KB/s   00:00
qqq.ibd                                                                                                                                   100%   96KB  14.0MB/s   00:00

导出表空间后，尽快解锁

(root@localhost) 10:49:00 [tablespace]> unlock tables;
Query OK, 0 rows affected (0.01 sec)

注意：一定要先拷贝cfg和ibd文件，然后才能unlock，因为 unlock** ****的时候，**cfg文件会被删除

源服务器上的日志

2018-02-14T02:38:16.530256Z 4 [Note] Start binlog_dump to master_thread_id(4) slave_server(8899), pos(, 4)
2018-02-14T09:05:44.342882Z 5 [Note] InnoDB: Sync to disk of `mytest`.`t1` started.
2018-02-14T09:05:44.343641Z 5 [Note] InnoDB: Stopping purge                                     --其实stop purge，找个测试的表 for export 即可
2018-02-14T09:05:44.344836Z 5 [Note] InnoDB: Writing table metadata to './mytest/t1.cfg'
2018-02-14T09:05:44.345158Z 5 [Note] InnoDB: Table `mytest`.`t1` flushed to disk
2018-02-14T09:13:23.812898Z 5 [Note] InnoDB: Deleting the meta-data file './mytest/t1.cfg'      --unlock table后，该文件自动被删除
2018-02-14T09:13:23.812950Z 5 [Note] InnoDB: Resuming purge                                     --unlock后，恢复purge线程

4、在目标服务器上修改 cfg文件和ibd文件的权限
目标服务器

[root@node2 tablespace]# ll
total 116
-rw-r----- 1 mysql mysql    61 Feb 15 10:47 db.opt
-rw-r----- 1 root  root    373 Feb 15 10:49 qqq.cfg
-rw-r----- 1 mysql mysql  8554 Feb 15 10:48 qqq.frm
-rw-r----- 1 root  root  98304 Feb 15 10:49 qqq.ibd

[root@node2 tablespace]# chown -R mysql.mysql ./*

[root@node2 tablespace]# ll
total 116
-rw-r----- 1 mysql mysql    61 Feb 15 10:47 db.opt
-rw-r----- 1 mysql mysql   373 Feb 15 10:49 qqq.cfg
-rw-r----- 1 mysql mysql  8554 Feb 15 10:48 qqq.frm
-rw-r----- 1 mysql mysql 98304 Feb 15 10:49 qqq.ibd

在目标服务器上通过import 命令导入表空间

(root@localhost) 10:48:28 [tablespace]> ALTER TABLE qqq IMPORT TABLESPACE;  --导入表空间
Query OK, 0 rows affected (0.02 sec)

(root@localhost) 10:50:49 [tablespace]> select * from qqq;                  -- 可以读取到从源服务器拷贝过来的数据
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

error.log中出现的信息

2018-02-15T02:50:49.725304Z 3 [Note] InnoDB: Importing tablespace for table 'tablespace/qqq' that was exported from host 'node1.gczheng.com'
2018-02-15T02:50:49.725433Z 3 [Note] InnoDB: Phase I - Update all pages
2018-02-15T02:50:49.725705Z 3 [Note] InnoDB: Sync to disk
2018-02-15T02:50:49.728143Z 3 [Note] InnoDB: Sync to disk - done!
2018-02-15T02:50:49.729426Z 3 [Note] InnoDB: Phase III - Flush changes to disk
2018-02-15T02:50:49.739010Z 3 [Note] InnoDB: Phase IV - Flush complete
2018-02-15T02:50:49.739496Z 3 [Note] InnoDB: `tablespace`.`qqq` autoinc value set to 0

注意：

表的名称必须相同，经过上述测试，库名可以不同该方法也可以用于分区表的备份和恢复

三复制环境搭建

配置信息	主库（master）	从库(slave)
主机	node1.gczheng.com	node2.gczheng.com
IP	192.168.88.88	192.168.88.99
Port	3306	3306
MySQL版本	MySQL5.7.18	MySQL5.7.18
Server_ID	8888	8899

注意:server-id在主从的配置中必须不同（在一个复制关系中，server-id必须唯一）

3.1. 创建一个复制用户

在Master节点上创建一个用于复制的用户，供Slave节点使用

master服务器

mysql root@localhost:(none)> create user 'repl'@'192.168.88.99' identified by '123456';
Query OK, 0 rows affected
Time: 0.005s
mysql root@localhost:(none)> grant replication slave on *.* to 'repl'@'192.168.88.99';  --需要replication和slave的权限，线上建议`限制成内网的网段`
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:(none)> flush privileges;
Query OK, 0 rows affected
Time: 0.006s
mysql root@localhost:(none)> select User,Host from mysql.user where user='repl';
+------+---------------+
| User | Host          |
+------+---------------+
| repl | 192.168.88.99 |
+------+---------------+
1 row in set
Time: 0.008s
mysql root@localhost:(none)> show grants for 'repl'@'192.168.88.99';
+----------------------------------------------------------+
| Grants for repl@192.168.88.99                            |
+----------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.88.99' |
+----------------------------------------------------------+
1 row in set
Time: 0.007s
mysql root@localhost:(none)>

测试slave节点是否可以通过 rpl@'%'** **连接成功

slave服务器

[root@node2 tablespace]# mysql -h192.168.88.88 -urepl -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 17
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.

(repl@192.168.88.88) 11:41:06 [(none)]> select current_user;
+--------------------+
| current_user       |
+--------------------+
| repl@192.168.88.99 |
+--------------------+
1 row in set (0.00 sec)

(repl@192.168.88.88) 11:41:09 [(none)]>

说明此时Slave节点可以连接到Master节点了

3.2. 备份数据

3.2.1. 准备测试数据

master服务器

mysql root@localhost:(none)> show databases
+--------------------+
| Database           |
+--------------------+
| information_schema |
| employees          |
| mysql              |
| performance_schema |
| sys                |
| tablespace         |
+--------------------+
6 rows in set
Time: 0.007s
mysql root@localhost:(none)>

3.2.2. 导出数据

[root@node1 bakdata]# mydumper  -u root -p root --regex "employees.*|tablespace.*" -o /bakdata/alldb

[root@node1 bakdata]# cd alldb/
[root@node1 alldb]# ls
employees.departments-schema.sql  employees.dept_manager-schema.sql  employees.salaries-schema.sql  employees.titles.sql       tablespace-schema-create.sql
employees.departments.sql         employees.dept_manager.sql         employees.salaries.sql         metadata
employees.dept_emp-schema.sql     employees.employees-schema.sql     employees-schema-create.sql    tablespace.qqq-schema.sql
employees.dept_emp.sql            employees.employees.sql            employees.titles-schema.sql    tablespace.qqq.sql

将备份目录复制到 Slave 节点

[root@node1 alldb]# scp -r /bakdata/alldb  192.168.88.99:/bakdata/
employees-schema-create.sql                                                                                                               100%   68    22.2KB/s   00:00
tablespace-schema-create.sql                                                                                                              100%   69    49.8KB/s   00:00
employees.departments.sql                                                                                                                 100%  351   367.0KB/s   00:00
employees.dept_emp.sql                                                                                                                    100%   14MB  38.7MB/s   00:00
employees.dept_manager.sql                                                                                                                100% 1168   708.4KB/s   00:00
employees.employees.sql                                                                                                                   100%   17MB  48.4MB/s   00:00
employees.salaries.sql                                                                                                                    100%  113MB  56.6MB/s   00:02
employees.titles.sql                                                                                                                      100%   21MB  47.7MB/s   00:00
tablespace.qqq.sql                                                                                                                        100%  132    86.7KB/s   00:00
employees.departments-schema.sql                                                                                                          100%  266   225.5KB/s   00:00
employees.dept_emp-schema.sql                                                                                                             100%  555   127.8KB/s   00:00
employees.dept_manager-schema.sql                                                                                                         100%  567   264.6KB/s   00:00
employees.employees-schema.sql                                                                                                            100%  353   216.5KB/s   00:00
employees.salaries-schema.sql                                                                                                             100%  416   331.3KB/s   00:00
employees.titles-schema.sql                                                                                                               100%  427   468.3KB/s   00:00
tablespace.qqq-schema.sql                                                                                                                 100%  153    54.0KB/s   00:00
metadata                                                                                                                                  100%  175    87.7KB/s   00:00
[root@node1 alldb]#

3.3. 还原数据

[root@node2 bakdata]# myloader -u root -p root -o -d /bakdata/alldb -v -3
** Message: 4 threads created
** Message: Dropping table or view (if exists) `employees`.`departments`
** Message: Creating table `employees`.`departments`
** Message: Dropping table or view (if exists) `employees`.`dept_emp`
** Message: Creating table `employees`.`dept_emp`
** Message: Dropping table or view (if exists) `employees`.`dept_manager`
** Message: Creating table `employees`.`dept_manager`
** Message: Dropping table or view (if exists) `employees`.`employees`
** Message: Creating table `employees`.`employees`
** Message: Dropping table or view (if exists) `employees`.`salaries`
** Message: Creating table `employees`.`salaries`
** Message: Dropping table or view (if exists) `employees`.`titles`
** Message: Creating table `employees`.`titles`
** Message: Dropping table or view (if exists) `tablespace`.`qqq`
** Message: Creating table `tablespace`.`qqq`
** Message: Thread 1 restoring `employees`.`departments` part 0
** Message: Thread 2 restoring `employees`.`dept_emp` part 0
** Message: Thread 3 restoring `employees`.`dept_manager` part 0
** Message: Thread 4 restoring `employees`.`employees` part 0
** Message: Thread 1 restoring `employees`.`salaries` part 0
** Message: Thread 3 restoring `employees`.`titles` part 0
** Message: Thread 4 restoring `tablespace`.`qqq` part 0
** Message: Thread 4 shutting down
** Message: Thread 2 shutting down
** Message: Thread 3 shutting down
** Message: Thread 1 shutting down
[root@node2 bakdata]#

检查数据是否还原到备库

mysql root@localhost:(none)> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| employees          |
| mysql              |
| performance_schema |
| sys                |
| tablespace         |
+--------------------+
6 rows in set
Time: 0.063s

mysql root@localhost:(none)> use employees;
You are now connected to database "employees" as user "root"
Time: 0.045s

mysql root@localhost:employees> show tables;
+---------------------+
| Tables_in_employees |
+---------------------+
| departments         |
| dept_emp            |
| dept_manager        |
| employees           |
| salaries            |
| titles              |
+---------------------+
6 rows in set
Time: 0.009s

mysql root@localhost:employees> select * from titles  limit 1;
+--------+-----------------+------------+------------+
| emp_no | title           | from_date  | to_date    |
+--------+-----------------+------------+------------+
| 10001  | Senior Engineer | 1986-06-26 | 9999-01-01 |
+--------+-----------------+------------+------------+
1 row in set
Time: 0.013s
mysql root@localhost:employees>

-- 已经还原到 Slave 节点上了

可以使用mysqldump加master-data参数，将master信息保存在备份中目前而言，主从数据已经是一致的了

3.4. CHANGE MASTER

由于使用 mydumper备份，没有将 Change Master信息写入SQL，而是写入到metadata中。

3.4.1. 查看master status

[root@node2 bakdata]# cat alldb/metadata
Started dump at: 2018-02-15 11:55:16
SHOW MASTER STATUS:
	Log: binlog.000013
	Pos: 3581
	GTID:9dc847d8-bf72-11e7-9ec4-000c2998e4f1:1-46

Finished dump at: 2018-02-15 11:55:20
[root@node2 bakdata]#

Log: binlog.000013 和 Pos: 3581 表明该备份开始时的 filename 和 postition

3.4.2. change master

slave服务器

mysql root@localhost:employees> change master to master_host='192.168.88.88', master_user='repl', master_password='123456', master_port=3306, master_log_file='binlog.000013', master_log_pos=3581;
Query OK, 0 rows affected
Time: 0.068s

mysql root@localhost:employees> show slave status G;
***************************[ 1. row ]***************************
Slave_IO_State                |
Master_Host                   | 192.168.88.88
Master_User                   | repl
Master_Port                   | 3306
Connect_Retry                 | 60
Master_Log_File               | binlog.000013            --change master中的filename	
Read_Master_Log_Pos           | 3581                     --metadata中指定的pos
Relay_Log_File                | node2-relay-bin.000001
Relay_Log_Pos                 | 4
Relay_Master_Log_File         | binlog.000013
Slave_IO_Running              | No                      --未启动slave同步，显示No
Slave_SQL_Running             | No                      --同上
Replicate_Do_DB               |
Replicate_Ignore_DB           |
Replicate_Do_Table            |
Replicate_Ignore_Table        |
Replicate_Wild_Do_Table       |
Replicate_Wild_Ignore_Table   |
Last_Errno                    | 0
Last_Error                    |
Skip_Counter                  | 0
Exec_Master_Log_Pos           | 3581
Relay_Log_Space               | 194
Until_Condition               | None
Until_Log_File                |
Until_Log_Pos                 | 0
Master_SSL_Allowed            | No
Master_SSL_CA_File            |
Master_SSL_CA_Path            |
Master_SSL_Cert               |
Master_SSL_Cipher             |
Master_SSL_Key                |
Seconds_Behind_Master         | <null>
Master_SSL_Verify_Server_Cert | No
Last_IO_Errno                 | 0
Last_IO_Error                 |
1 row in set
Time: 0.027s

mysql root@localhost:employees> start slave;            --开启slave
Query OK, 0 rows affected
Time: 0.053s

mysql root@localhost:employees> show slave status G;
***************************[ 1. row ]***************************
Slave_IO_State                | Waiting for master to send event    --IO 线程的状态
Master_Host                   | 192.168.88.88
Master_User                   | repl
Master_Port                   | 3306
Connect_Retry                 | 60
Master_Log_File               | binlog.000013                       --IO线程读取到的文件
Read_Master_Log_Pos           | 3581                                --IO线程读取文件中的位置
Relay_Log_File                | node2-relay-bin.000002    
Relay_Log_Pos                 | 317
Relay_Master_Log_File         | binlog.000013                       --SQL线程执行到的文件	
Slave_IO_Running              | Yes                                 --IO线程启动成功
Slave_SQL_Running             | Yes                                 --SQL线程启动成功	
Replicate_Do_DB               |
Replicate_Ignore_DB           |
Replicate_Do_Table            |
Replicate_Ignore_Table        |
Replicate_Wild_Do_Table       |
Replicate_Wild_Ignore_Table   |
Last_Errno                    | 0
Last_Error                    |
Skip_Counter                  | 0
Exec_Master_Log_Pos           | 3581                                --SQL线程执行到文件的位置
Relay_Log_Space               | 564
Until_Condition               | None
Until_Log_File                |
Until_Log_Pos                 | 0
Master_SSL_Allowed            | No
Master_SSL_CA_File            |
Master_SSL_CA_Path            |
Master_SSL_Cert               |
Master_SSL_Cipher             |
Master_SSL_Key                |
Seconds_Behind_Master         | 0                                   --Slave落后Master执行的秒数,这个值不准确
Master_SSL_Verify_Server_Cert | No
Last_IO_Errno                 | 0                                   --（IO）如果这里有信息的话，就是错误提示信息，可以用来排错		
Last_IO_Error                 |                                     --（SQL）如果这里有信息的话，就是错误提示信息，可以用来排错
1 row in set
Time: 0.020s
mysql root@localhost:employees>

Slave_IO_Running和 Slave_SQL_Running 这两个指标都为YES，表示目前的复制的状态是正常的

mysql root@localhost:employees> show processlistG;
***************************[ 1. row ]***************************
Id      | 18
User    | root
Host    | localhost
db      | employees
Command | Query
Time    | 0
State   | starting
Info    | show processlist
***************************[ 2. row ]***************************
Id      | 20
User    | system user
Host    |
db      | <null>
Command | Connect
Time    | 1269
State   | Waiting for master to send event                      -- IO线程
Info    | <null>
***************************[ 3. row ]***************************
Id      | 21
User    | system user
Host    |
db      | <null>
Command | Connect
Time    | 1269
State   | Slave has read all relay log; waiting for more updates  -- SQL线程
Info    | <null>
3 rows in set
Time: 0.010s
mysql root@localhost:employees>

3.4.3. 添加并行复制

slave服务器

在/etc/my.cnf文件中配置

slave-parallel-type=LOGICAL_CLOCK 
slave-parallel-workers=4

如果开启了并行复制（multi-threaded slave）, show processlist 中可以看到 Coordinator 线程

mysql root@localhost:employees> show processlist;
+----+-------------+-----------+-----------+---------+------+--------------------------------------------------------+------------------+
| Id | User        | Host      | db        | Command | Time | State                                                  | Info             |
+----+-------------+-----------+-----------+---------+------+--------------------------------------------------------+------------------+
| 1  | system user |           | <null>    | Connect | 17   | Waiting for master to send event                       | <null>           |
| 2  | system user |           | <null>    | Connect | 17   | Slave has read all relay log; waiting for more updates | <null>           |
| 3  | system user |           | <null>    | Connect | 17   | Waiting for an event from Coordinator                  | <null>           |
| 4  | system user |           | <null>    | Connect | 17   | Waiting for an event from Coordinator                  | <null>           |
| 5  | system user |           | <null>    | Connect | 17   | Waiting for an event from Coordinator                  | <null>           |
| 6  | system user |           | <null>    | Connect | 17   | Waiting for an event from Coordinator                  | <null>           |
| 9  | root        | localhost | employees | Query   | 0    | starting                                               | show processlist |
+----+-------------+-----------+-----------+---------+------+--------------------------------------------------------+------------------+
7 rows in set
Time: 0.009s
mysql root@localhost:employees>

主要的监控参数

Relay_Log_File 和 Relay_Log_Pos 是中继日志（Relay_Log）信息。

由于 IO线程 拉取数据的速度快于 SQL线程 回放数据的速度，所以 Relay_Log 可在两者之间起到一个缓冲的作用

Relay_Log 的格式和 binlog 的格式是一样的，但是两者的内容是不一样的（不是和binlog一一对应的）

Relay_Log 在 SQL线程回放完成后，（默认）就会被删除，而 binlog 不会（由 expire_logs_days控制）

Relay_Log 可以通过设置 relay_log_purge=0 ，使得 Relay_Log 不被删除（MHA中不希望被Purge），需要通过外部的脚本进行删除

验证复制
master节点

mysql root@localhost:(none)> insert into tablespace.qqq values(2);
Query OK, 1 row affected
Time: 0.019s

slave节点

mysql root@localhost:employees> select * from tablespace.qqq ;
+---+
| a |
+---+
| 1 |
| 2 |
+---+
2 rows in set
Time: 0.026s
mysql root@localhost:employees>

当前演示时的relay-log文件是 binlog.000029

[root@node2 bakdata]# mysqlbinlog /r2/mysqldata/binlog.000029 -vv
------------------省略其他输出-----------------

# at 1134
#180215 12:39:57 server id 8888  end_log_pos 1174 CRC32 0xe52db744 	Write_rows: table id 233 flags: STMT_END_F

BINLOG '
HQ+FWhO4IgAANAAAAG4EAAAAAOkAAAAAAAEACnRhYmxlc3BhY2UAA3FxcQABAwABY9f0lA==
HQ+FWh64IgAAKAAAAJYEAAAAAOkAAAAAAAEAAgAB//4CAAAARLct5Q==
'/*!*/;
### INSERT INTO `tablespace`.`qqq`                              --- 这个注释的信息就是传过来的插入数据的信息
### SET
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
# at 1174
#180215 12:39:57 server id 8888  end_log_pos 1205 CRC32 0x8dabbbc4 	Xid = 556
COMMIT/*!*/;
# at 1205
#180215 12:43:53 server id 8899  end_log_pos 1228 CRC32 0x720c1617 	Stop
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

3.5. 复制搭建总结

1.Master 和 Slave 上配置 不同 的 server-id ，且binlog_format 设置为 ROW 格式

2.在Master上创建一个rpl@%的用户（ %替换为内网网段）

3.将Master的备份数据恢复到Slave 上，注意记录master status信息（ binlog_file 和position）

4.在Slave上进行‘change master’操作，注意 master_log_file和master_log_pos 要和备份中的master status一致

5.在Slave上进行start slave 操作

6.在Slave上进行show slave statusG; 操作，确保Slave_IO_Running 和Slave_SQL_Running均为YES

3.6. 搭建真正的高可靠复制环境

3.6.1. 重要的参数

Master
- binlog-do-db = #需要复制的库
- binlog-ignore-db = #需要被忽略的库
- max_binlog_size = 2048M #默认为1024M
- binlog_format = ROW #必须为ROW
- transaction-isolation = READ-COMMITTED
- expire_logs_days = 7 # binlog保留多少天，看公司计划安排
- server-id = 8888 #必须和所有从机不一样，且从机之间也不一样
- binlog_cache_size = # binlog缓存的大小，设置时要当心
- sync_binlog = 1 #必须设置为1，默认为0
- innodb_flush_log_at_trx_commit = 1 #提交事物的时候刷新日志
- innodb_support_xa = 1 #确保事务日志写入bin-log 的顺序与是事务的time-line是一致的
Slave
- log_slave_updates #将SQL线程回放的数据写入到从机的binlog中去（用于级联复制）
- replicate-do-db = #需要复制的库
- replicate-ignore-db = #需要忽略的库
- replicate-do-table = #需要复制的表
- replicate-ignore-table =需要忽略的表
- server-id = 8899 #必须在一个复制集群环境中全局唯一
- relay-log-recovery = 1 #I/O thread crash safe – IO线程安全
- relay-log-info-repository = TABLE # SQL thread crash safe – SQL线程安全
- master_info_repository = TABLE
- read_only = 1

3.6.2. SQL线程高可靠问题

如果将relay_log_info_repository 设置为FILE ，MySQL会把回放信息记录在一个 relay-info.log 的文件中，其中包含SQL线程 回放到的Relay_log_name和Relay_log_pos ，以及对应的Master的Master_log_name和Master_log_pos

SQL线程回放event
将回放到的binlog的文件名 和位置写到relay-info.log文件
参数sync_relay_log_info = 10000 （fsync）代表每回放10000个event,写一次relay-info.log
- 如果该参数设置为1，则表示每回放一个event就写一次relay-info.log，那写入代价很大，且性能很差
- 设置为1后，即使性能上可以接受，还是会丢最有一次的操作，恢复起来后还是有1062的错误（重复执行event）

SQL线程的数据回放是写数据库操作，relay-info是写文件操作，这两个操作很难保证一致性

当一个Slave节点在复制数据时，可能发生如下情况，数据2和数据3写入成功(且已经落盘)，但是relay-info.log 中的记录还是数据1的位置（因为sync_relay_log_info的关系，此时还没有fsync），如下图所示：

此时Slave宕机，然后重启，便会产生如下的状况：

Slave的库中 存在数据2和数据3
Slave读取relay-info.log中的 Relay_log_name和Relay_log_pos ，此时记录的是 回放到数据1的位置
Slave 从数据1开始回放 ，继续 插入数据2和数据3
但是，此时的数据库中 存在数据2和数据3 ，于是发生了 1062 的错误（重复记录）

mysql root@localhost:(none)> select * from mysql.slave_relay_log_info G;
***************************[ 1. row ]***************************
Number_of_lines   | 7
Relay_log_name    | ./node2-relay-bin.000013    -- relay日志的文件名
Relay_log_pos     | 317                         -- relay日志的位置
Master_log_name   | binlog.000014               -- 对应回放到的 binlog 文件名（Master节点）
Master_log_pos    | 706                         -- 对应回放到的位置
Sql_delay         | 0
Number_of_workers | 4
Id                | 1
Channel_name      |
1 row in set
Time: 0.006s
mysql root@localhost:(none)>

设置为 TABLE 的原理为：将 event的回放 和 relay-info的更新 放在同一个事物 里面，变成原子操作，从而保证一致性（要么都写入，要么都不写）。
每一次事物提交，都会写入 mysql.slave_relay_log_info 中，sync_relay_log_info=N 将被忽略。官方参数解释：

BEGIN;

apply log event; apply log event;

UPDATEmysql.slave_relay_log_info

SETMaster_log_pos = Exec_Master_Log_Pos,

Master_log_name = Relay_Master_Log_File,

Relay_log_name = Relay_Log_File,

Relay_log_pos = Relay_Log_Pos;

COMMIT;

3.6.3. I/O线程高可用

IO线程也是接收一个个的 event ，将接收到的event，通过设置参数 master_info_repository 可以将master-info 信息（IO线程接收到的位置，Master_log_name 和 Master_log_pos ）写入到文件（FILE ）或者数据库( TABLE )中。然后将接收到的event 写入relay log file

参数 sync_master_info=10000 表示每接收10000个event，写一次master-info

这里同样存在这个问题， master-info.log 和 relay-log 无法保证一致性。

假设存在下面这个情况，event2和event3已经写入到relay-log，但是master-info还没有同步到master-info.log

此时如果服务宕机后，MySQL重启，I/O线程会读取master-info.log的内容，读取到的位置为event1的位置，然后I/O线程会继续将event2和event3拉取过来，然后继续写入到relay-log 中。

如上图所示，event2 和event3 被重复写入到了relay-log文件中，当SQL线程回放时，就会产生 1062 的错误（重复记录）

看到的现象还是 IO线程正常，SQL线程报错

解决问题的方法:
- 设置参数 relay-log-recover = 1 ，该参数表示 当前接收到的relay-log全部删除 ，然后从SQL线程回放到的位置 重新拉取（SQL线程通过配置后是可靠的）
所以说，真正的MySQL复制的高可靠是从 5.6 版本开始的，通过设置
- relay-log-recover = 1
- relay_log_info_repository = TABLE
- master_info_repository = TABLE

这三个参数，可以确保整体复制的高可靠（换言之，之前的版本复制不可靠是正常的）。

注意：如果 Slave落后Master 的时间很多，超过了Master上binlog的保存时间，那Master上对应的binlog就会被删除，Slave的I/OThread就拉不到数据了，注意监控主从落后的时间

在已启用主从同步的实例中，设置set GLOBAL relay_log_info_repository='TABLE'; 需要先stop slave，再start slave。

mysql root@localhost:(none)> stop slave;
Query OK, 0 rows affected
Time: 0.002s
mysql root@localhost:(none)> set GLOBAL  relay_log_info_repository='TABLE';
Query OK, 0 rows affected
Time: 0.005s
mysql root@localhost:(none)> start slave;
Query OK, 0 rows affected
Time: 0.008s
mysql root@localhost:(none)> show variables like '%relay%';
+---------------------------+-------------------------------------+
| Variable_name             | Value                               |
+---------------------------+-------------------------------------+
| max_relay_log_size        | 0                                   |
| relay_log                 |                                     |
| relay_log_basename        | /r2/mysqldata/node2-relay-bin       |
| relay_log_index           | /r2/mysqldata/node2-relay-bin.index |
| relay_log_info_file       | relay-log.info                      |
| relay_log_info_repository | TABLE                               |
| relay_log_purge           | ON                                  |
| relay_log_recovery        | ON                                  |
| relay_log_space_limit     | 0                                   |
| sync_relay_log            | 10000                               |
| sync_relay_log_info       | 10000                               |
+---------------------------+-------------------------------------+
11 rows in set
Time: 0.014s
mysql root@localhost:(none)>  select * from mysql.slave_relay_log_info G;
***************************[ 1. row ]***************************
Number_of_lines   | 7
Relay_log_name    | ./node2-relay-bin.000013
Relay_log_pos     | 317
Master_log_name   | binlog.000014
Master_log_pos    | 706
Sql_delay         | 0
Number_of_workers | 4
Id                | 1
Channel_name      |
1 row in set
Time: 0.006s
mysql root@localhost:(none)>

3.6.4. master_info_repository设置

master_info_repository 设置为 TABLE 或者 FILE 对 复制的可靠性 是 没有帮助 的，因为设置 relay-log-recover = 1 后，会重新通过SQL线程回放到的位置进行拉取。
但是 master_info_repository 也一定要设置为 TABLE ，性能上比设置为FILE 有很高的提升（官方BUG）
设置为 TABLE 后， master-info 将信息保存到 mysql.slave_master_info 中

mysql root@localhost:(none)> select * from mysql.slave_master_infoG;
***************************[ 1. row ]***************************
Number_of_lines        | 25
Master_log_name        | binlog.000014
Master_log_pos         | 706
Host                   | 192.168.88.88
User_name              | repl
User_password          | 123456
Port                   | 3306
Connect_retry          | 60
Enabled_ssl            | 0
Ssl_ca                 |
Ssl_capath             |
Ssl_cert               |
Ssl_cipher             |
Ssl_key                |
Ssl_verify_server_cert | 0
Heartbeat              | 30.0
Bind                   |
Ignored_server_ids     | 0
Uuid                   | 9dc847d8-bf72-11e7-9ec4-000c2998e4f1
Retry_count            | 86400
Ssl_crl                |
Ssl_crlpath            |
Enabled_auto_position  | 0
Channel_name           |
Tls_version            |
1 row in set
Time: 0.010s
mysql root@localhost:(none)>

3.6.5. read_only与super_read_only

如果在Slave机器上对数据库进行修改或者删除，会导致主从的不一致，需要对Slave机器设置为 read_only = 1 ，让Slave提供 只读 操作。
注意： read_only 仅仅对 没有SUPER权限 的用户 有效 （即 mysql.user表的Super_priv字段为Y），一般给 App 的权限是 不需要SUPER权限 的。
参数 super_read_only 可以将有 SUPER权限 的用户也设置为 只读 ，且该参数设置为 ON 后， read_only 也跟着 自动 设置为 ON ；

mysql root@localhost:(none)> show variables like "read_only";  
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | OFF   |   
+---------------+-------+
1 row in set
Time: 0.009s
mysql root@localhost:(none)> set global super_read_only=1;   -- 开启super用户的read_only
Query OK, 0 rows affected
Time: 0.003s
mysql root@localhost:(none)> show variables like "read_only";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |      --配置自动启用
+---------------+-------+
1 row in set
Time: 0.007s
mysql root@localhost:(none)>

3.7. mysqlreplicate 搭建主从复制

MySQL Utilities官方文档

使用 mysqlreplicate 需要安装 mysql-utilities 包

[root@node1 software]# rpm -ivh https://cdn.mysql.com//Downloads/Connector-Python/mysql-connector-python-2.1.7-1.el7.x86_64.rpm
[root@node1 software]# rpm -ivh https://cdn.mysql.com//Downloads/MySQLGUITools/mysql-utilities-1.6.5-1.el7.noarch.rpm

3.7.1. 测试

搭建一个备机 node3，并初始化实例；
node3 上新建一个用户 'gcdb'@'%' (%可以换成内网网段)；
然后在 node3 或 node1 （或者其他任何可以连接到Master/Slave的机器上）执行如下命令

mysql root@localhost:performance_schema> create user 'repl'@'192.168.88.100' identified by '123456';

mysql root@localhost:performance_schema> select user,host from mysql.user;
+-----------+----------------+
| user      | host           |
+-----------+----------------+
| gcdb      | %              |
| monitor   | %              |
| repl      | 192.168.88.100 |
| repl      | 192.168.88.99  |
| dbbackup  | localhost      |
| mysql.sys | localhost      |
| operator  | localhost      |
| root      | localhost      |
+-----------+----------------+
8 rows in set
Time: 0.005s

mysql root@localhost:performance_schema> grant replication slave on *.* to 'repl'@'192.168.88.100';
Query OK, 0 rows affected
Time: 0.010s

mysql root@localhost:performance_schema> flush privileges;
Query OK, 0 rows affected
Time: 0.004s

[root@node1 software]#  mysqlreplicate --master=gcdb:iforgot@192.168.88.88:3306 --slave=gcdb:iforgot@192.168.88.100:3306 --rpl-user=repl:123456 -b
WARNING: Using a password on the command line interface can be insecure.
# master on 192.168.88.88: ... connected.
# slave on 192.168.88.100: ... connected.
# Checking for binary logging on master...
# Setting up replication...
# ...done.
[root@node1 software]#

然后在 node3 上执行 show slave status 操作，复制正常

相关阅读:
ajax专题
 luogu P1346 电车最短路
 luogu P1462 通往奥格瑞玛的道路最短路
 luogu P1328 生活大爆炸版石头剪刀布
 luogu P1315 联合权值枚举
 luogu P1156 垃圾陷阱背包问题
 luogu P1217 回文质数枚举
 luogu P3650 滑雪课程设计枚举
 luogu1209 修理牛棚贪心
 luogu P1223 排队接水贪心
原文地址：https://www.cnblogs.com/gczheng/p/8471782.html