MySQL的复制机制

　　　　　　　　　　　　　　　　　MySQL的复制机制

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　作者：尹正杰

一.MySQL复制介绍

1>.MySQL复制允许将主实例（master）上的数据同步到一个或多个从实例（slave）上，默认情况下复制是异步进行的，从库也不需要一直连接主库来同步数据；

2>.MySQL复制的数据粒度可以是主实例上所有的数据库，也可以是指定的一个或多个数据库，也可以是一个数据库里的指定的表；

3>.MySQL复制带来的优势在于：

扩展能力：
    通过复制可以将MySQL的性分到一个或多个slave上。这要求所有的写操作和修改操作都必须在Master上完成，而读操作可以被分配到一个或多个salve上。将读写分离到不同服务执行之后，MySQL的读写性能得到提升。

数据库备份：
　　由于从实例时同步主实例的数据，所以可以将备份作业部署到从库。

数据分析和报表：
　　同样，一些数据分析和报表的实现可以在从实例执行，以减少对主库的性能影响。

容灾能力：
　　可以在物理距离较远的另一个数据建立slave,保证在主实例所在地区遭遇灾难时，在另一个数据中心能快速恢复。

二.MySQL复制有两种方法

1>.传统方式

　　基于主库的bin-log将日志事件和事件位置复制到从库，从库再加以应用来达到主从同步的目的。

2>.Gtid方式

　　global transaction identitifiers 是基于事物来复制数据，因此也就不依赖日志文件，同时又能更好的保证主从库数据一致性。

三.MySQL复制有多种类型

1>.异步复制

　　一个主库，一个或多个从库，数据异步同步到从库。

2>.同步复制

　　在MySQL cluster中特有的复制方式。

3>.半同步复制

　　在异步复制的基础上，确保任何一个主库上的事物在提交之前至少有一个从库已经收到该事物并日志记录下来。

4>.延迟复制

　　在异步复制的基础上，人为设定主库和从库的数据同步延迟时间，即保证数据延迟至少是这个参数。

四.MySQL复制原理

1>.MySQL的复制原理

　　如上图所示，MySQL复制的原理大致总结如下：

1>.在Slave 服务器上执行sart slave命令开启主从复制开关，开始进行主从复制。

2>.此时，Slave服务器的IO线程会通过在master上已经授权的复制用户权限请求连接master服务器，并请求从执行binlog日志文件的指定位置（日志文件名和位置就是在配置主从复制服务时执行change master命令指定的）之后开始发送binlog日志内容

3>.Master服务器接收到来自Slave服务器的IO线程的请求后，二进制转储IO线程会根据Slave服务器的IO线程请求的信息分批读取指定binlog日志文件指定位置之后的binlog日志信息，然后返回给Slave端的IO线程。返回的信息中除了binlog日志内容外，还有在master服务器端记录的新的binlog文件名称，以及在新的binlog中的下一个指定更新位置。

4>.当Slave服务器的IO线程获取到Master服务器上IO线程发送的日志内容、日志文件及位置点后，会将binlog日志内容依次写到Slave端自身的Relay Log（即中继日志）文件（MySQL-relay-bin.xxx）的最末端，并将新的binlog文件名和位置记录到master-info文件中，以便下一次读取master端新binlog日志时能告诉Master服务器从新binlog日志的指定文件及位置开始读取新的binlog日志内容

5>.Slave服务器端的SQL线程会实时检测本地Relay Log 中IO线程新增的日志内容，然后及时把Relay LOG 文件中的内容解析成sql语句，并在自身Slave服务器上按解析SQL语句的位置顺序执行应用这样sql语句，并在relay-log.info中记录当前应用中继日志的文件名和位置点

2>.MySQL复制有三种核心格式

　　复制的工作原理是数据库修改记录到bin log日志并传递到slave，然后slave在本地还原的过程。而时间记录到bin log的格式会有所不同。

基于语句的复制（statement based replication）：
　　基于主库将SQL语句写入到bin log中完成复制。

基于行数据的复制（row based replication）：
　　基于主库将每一行数据变化的信息作为时间写入到bin log中完成日志。默认就是基于行级别的复制，因为它相对语句复制逻辑更为严谨。

混合复制（mixed based replication）：
　　上述两者的结合。默认情况下优先使用基于语句的复制，只有当部分语句如果基于语句复制不完全的情况下才会自动切换为基于行数据的复制。

[root@node101 ~]# mysql -uroot -pyinzhengjie
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 2
Server version: 5.7.25-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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> 
mysql> SHOW VARIABLES LIKE '%BINLOG_FORMAT%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.01 sec)

mysql>

mysql> SHOW VARIABLES LIKE '%BINLOG_FORMAT%';　　　　　　　　　　　　　　　　#查看日志信息

[root@node101 ~]# mysql -uroot -pyinzhengjie
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 3
Server version: 5.7.25-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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> 
mysql> SHOW VARIABLES LIKE '%BINLOG_FORMAT%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.01 sec)

mysql> 
mysql> 
mysql> SET binlog_format='STATEMENT';
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> SHOW VARIABLES LIKE '%BINLOG_FORMAT%';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)

mysql> 
mysql>

mysql> SET binlog_format='STATEMENT';　　　　　　　　　　　　　　　　　　　　　#修改为基于语句即复制

五.MySQL复制使用场景

　　MySQL复制可以作为数据库备份的一种解决方案，由于主库的数据会复制到备库，所以可以在备库数据库备份作业而不影响主库的性能。

　　当数据库比较小时，可以采用mysqldump的方式。由于mysqlddump出来的文件内容是SQL语句，所以可以很方便的将其中的一部分复制出来应用到其他数据库里。在执行mysqldump之前，为了保证数据的一致性，最好是把salve进程停掉。

[root@node102 ~]# mysqladmin -uroot -p stop-slave 
Enter password: 
Slave stopped
[root@node102 ~]# 
[root@node102 ~]# mysql -uroot -p -e 'STOP SLAVE SQL_THREAD'
Enter password: 
[root@node102 ~]# 
[root@node102 ~]# 
[root@node102 ~]# mysqldump --all-databases -uroot -p > fulldb.dump
Enter password: 
[root@node102 ~]# 
[root@node102 ~]# ll -h fulldb.dump 
-rw-r--r--. 1 root root 782K Mar  5 08:24 fulldb.dump
[root@node102 ~]# 
[root@node102 ~]# mysqladmin -uroot -p start-slave 
Enter password: 
Slave started
[root@node102 ~]#

案例实操

　　当数据库比较大时，采用mysqldump方式的效率不高，所以可以使用物理文件拷贝的方式。为了保证数据的一致性，物理备份需要将备份关闭。

[root@node102 ~]# mysqladmin -uroot -p shutdown
Enter password: 
[root@node102 ~]# 
[root@node102 ~]# grep datadir /etc/my.cnf
datadir=/yinzhengjie/softwares/mysql/data/
[root@node102 ~]# 
[root@node102 ~]# cd /yinzhengjie/softwares/mysql/
[root@node102 mysql]# 
[root@node102 mysql]# tar zcf yinzhengjie-dbbackup.tar.gz ./data
[root@node102 mysql]# 
[root@node102 mysql]# ll yinzhengjie-dbbackup.tar.gz 
-rw-r--r--. 1 root root 1374758 Mar  5 08:32 yinzhengjie-dbbackup.tar.gz
[root@node102 mysql]# 
[root@node102 mysql]# /etc/init.d/mysql.server start
Starting MySQL. SUCCESS! 
[root@node102 mysql]# 
[root@node102 mysql]# 
[root@node102 mysql]#

案例实操

　　MySQL复制可以用在主库和从库采用不同的存储引擎的情况下。这样做的目的通常是在主库和从库分别利用不同存储殷勤的优势，比如在主库使用InnoDB是为了事务功能，而从库使用MyISAM因为是只读操作而不需要事务功能。

　　当使用mysqldump方式来创建备库时，改变备库的表存储引擎的方式就是在应用dump文件之前修改文件里的所有关于表存储引擎的地方。

　　如果是使用文件拷贝的方式来创建备库时，则唯一修改备库表存储引擎的方式就是启动备库之后使用ALTER TABLE命令修改。

[root@node102 ~]# mysql -uroot -pyinzhengjie
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 4
Server version: 5.7.25-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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> 
mysql> use course;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 
mysql> 
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| course     |
+------------+
1 row in set (0.00 sec)

mysql> 
mysql> SHOW TABLES;
+------------------+
| Tables_in_course |
+------------------+
| course           |
| dept             |
| score            |
| students         |
| teacher          |
+------------------+
5 rows in set (0.00 sec)

mysql> 
mysql> 
mysql> STOP SLAVE;
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> 
mysql> ALTER TABLE students ENGINE = 'InnoDB';
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql>