mysql copy row_Mysql基础——复制

内容

复制方式有哪些？

复制基本过程是什么？

binlog

binlog格式有那几种？他们分别有什么问题？

和binlog文件密切相关的参数有哪些？

binlog event有哪些？它们分别代表什么？

如果在程序模拟一个slave接管binlog?

程序模拟slave，解析binlog要注意哪些问题？

GTID

gtid什么？

gtid复制原理什么？

gtid复制要着重关注哪些系统参数？

mariadb gtid

一 复制的方式有哪些

异步

1master在commit一个事务后，就返回成功信息给客户端，不关心binlog是否成功发送给slave节点；

半异步

1 以插件的形式实现的，需要 [加载/开启semi-sync插件] https://www.orczhou.com/index.php/2011/06/mysql-5-5-semi-sync-replication-setup-config/；

2 开启半异步方式后，master在提交一个事务后，会等待一个slave成功接收binlog，并写入自己relay lgo后，返回一个ack确认包给master，master收到这个确认包才会把事务提交成功信息告诉给客户端；

3 mysql5.7改进了半同步复制，引入了无数据丢失的半同步方案:Loss-Less半同步复制，引入了一个参数：rpl_semi_sync_master_wait_point, 这个参数有两种取值：1) AFTER_SYNC , 这个是新的半同步方案，主库在收到slave的ack信号之后，才完成事务binlog和redo 二阶段提交的commit操作。2) AFTER_COMMIT, 这个是老的半同步方案，主库在完成事务binlog和redo 二阶段提交的commit操作后，才等待slave的ack信号;mysql半同步复制

同步

1 master会等所有slave 都写入成功事务后，才会返回事务成功的消息给客户端；

group replication

1 mysql group replication解析：Mysql MGR解析

二 异步复制基本过程

2.1主从同步涉及的几个主要关键点：

binlog: 数据变更存储的二进制文件；

三个线程：

主库dump线程，就要将binlog event发送给从库；

从库io线程，接收主库的binlog event，然后将之写入到reylog中；

从库sql线程，从reylog中读取binlog event，解析event，将主库数据变更更新到从库；

两种binlog消费点记录方式：

1 binlog文件+pos: 通过binlog文件和消费的位置点记录从库读取了那些event

2 gtid：从mysql 5.6之后支持gtid的方式进行同步，gtid是全局事务id，通过gtid可以代替传送binlog name和pos的方法完成同步；

2.2 复制过程：

1.从库change to master，连接上master，并发送相关账号 密码 binlog 同步位置点等给master;

2.master收到从库来的binlog接收消息，开启dump线程，dump线程将binlog event从1中的位置点源源不断的发送给从库；

3.从库io线程将接收到binlog event写入到relay-log;

4.从库sql线程从relay-log消费binlog event，解析event，将变更同步到从数据库，完成同步；

具体实现步骤：

1. 主库锁库: FLUSH TABLES WITH READ LOCK，防止在dumper过程中主库有数据写入；

2. dump主库全量数据到sql；

3. 查看主库现在的binlog文件和位置(找出File和Position) ，并记录好这个信息；

show master status;

4. 主库解锁-》UNLOCK TABLES;

5. 从库全量同步主库的数据；

6. 从库全量同步主库数据完毕，进行检查，show slave status \G；

7. 如果数据一致，开始增量同步,change master，连接上master，并发3中记录的binlog位置信息发送给master;

CHANGE MASTER TO

MASTER_HOST='....',

MASTER_USER='....',

MASTER_PASSWORD='..',

MASTER_LOG_FILE='mysql-bin.0000001',

MASTER_LOG_POS=1;

8.start slave

注：更详细步骤可以看：[https://www.jianshu.com/p/7bfacf149d55](https://www.jianshu.com/p/7bfacf149d55)

三 binlog

3.1 binlog查看基本语句

show master status :查看主当前使用的binlog文件、写入的位置点、gtid等

show binary logs： 查看binlog 列表；

show binlog events in 'binlog.000003'： 查看某个binlog 文件的详细event;

flush logs: 从新开始写一个新的binlog;

mysqlbinlog --base64-output=DECODE-ROWS -v binlog.000001 ：查看binlog详细内容

3.2 binlog 有哪些类型？

binlog 有三种格式, 由binlog_format参数指定，binlog_format=ROW/STATEMENT/MIXED

STATEMENT：

记录修改数据的sql语句到binlog中。

优点：并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。

缺点：在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)；

ROW模式(RBR)

不记录具体的sql语句，仅记录哪条数据被修改了，修改成什么样了；

优点：不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题；

缺点：是会产生大量的日志，尤其是alter table的时候会让日志暴涨。

MIXED模式(MBR)

以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

3.3 和binlog相关的主要参数有哪些？

sync_binlog：控制了事务提交时，binlog刷盘策略

sync_binlog=0 的时候，表示每次提交事务，都只写到文件系统的 page cache，不 fsync，并没有把数据持久化到磁盘

sync_binlog=1 的时候，表示每次提交事务都会执行 fsync，都会持久化到磁盘；

sync_binlog=N(N>1) 的时候，表示每次提交事务都 写到文件系统的 page cache，但累积 N 个事务后才 fsync。

binlog_format： 控制binlog格式；

binlog_row_image: 控制binlog在row格式下，每行数据的在binlog中记录的信息类型；

full：记录更的行的所有的列值信息，默认值。

minimal：仅仅记录被更改的以及能够唯一识别数据行的列值；

noblob：记录所有的列值，但是BLOB 与 TEXT列除外(如未更改)。

3.3 binlog event

FORMAT_DESCRIPTION_EVENT

是binlog version 4中为了取代之前版本中的START_EVENT_V3事件而引入的。它是binlog文件中的第一个事件，而且，该事件只会在binlog中出现一次。MySQL根据FORMAT_DESCRIPTION_EVENT的定义来解析其它事件。它通常指定了MySQL Server的版本，binlog的版本，该binlog文件的创建时间。

PREVIOUS_GTIDS_LOG_EVENT

开启GTID模式后，每个binlog开头都会有一个PREVIOUS_GTIDS_LOG_EVENT事件，它的值是上一个binlog的PREVIOUS_GTIDS_LOG_EVENT+GTID_LOG_EVENT，在数据库重启的时候，需要重新填充gtid_executed的值，该值即是最新一个binlog的PREVIOUS_GTIDS_LOG_EVENT+GTID_LOG_EVENT。

例如：如果上一个binlog文件的PREVIOUS_GTIDS_LOG_EVENT是d4d6b3e6-235c-11eb-b3e4-e8bdd106a08d：1-10，最后一个GTID_LOG_EVENT是d4d6b3e6-235c-11eb-b3e4-e8bdd106a08d：20， 那么下一个binlog 文件的PREVIOUS_GTIDS_LOG_EVENT是：d4d6b3e6-235c-11eb-b3e4-e8bdd106a08d：1-20

GTID_LOG_EVENT

在启用GTID模式后，MySQL实际上为每个事务都分配一个GTID，在每个事务开始前会先插入这个event，指定下一个事务的gtid值；

image.png

QUERY_EVENT

QUERY_EVENT以文本的形式来记录事务的操作。QUERY_EVENT类型的事件通常在以下几种情况下使用：

事务开始时，执行的BEGIN操作。

STATEMENT格式中的DML操作(STATEMENT 增删改)

ROW格式中的DDL操作

用于row格式，在记录DML语句的数据时，总会先写入一个table_map_event，这种类型的event用于记录表结构相关元数据信息，比如数据库名称，表名称，表的字段类型，表的字段元数据等等

ROWS_EVENT

对于ROW格式的binlog，所有的DML语句都是记录在ROWS_EVENT中。

ROWS_EVENT分为三种：WRITE_ROWS_EVENT，UPDATE_ROWS_EVENT，DELETE_ROWS_EVENT，分别对应insert，update和delete操作。

对于insert操作，WRITE_ROWS_EVENT包含了要插入的数据;

对于update操作，UPDATE_ROWS_EVENT不仅包含了修改后的数据，还包含了修改前的值。

对于delete操作，仅仅需要指定删除的主键(在没有主键的情况下，会给定所有列)

对于QUERY_EVENT事件，是以文本形式记录DML操作的。而对于ROWS_EVENT事件，并不是文本形式，所以在通过mysqlbinlog查看基于ROW格式的binlog时，需要指定-vv --base64-output=decode-rows。

通过binlog_row_image参数控制是否在binlog event中显示所有的字段值，包括有没有变动过的；binlog_row_image有两个取值；

- full: binlog中显示所有的字段值，如下所示，delete时，第二字段没有指定限制条件，也显示出来；

- minimal: 只显示有变动的列；

image.png

XID_EVENT

在事务提交时，不管是STATEMENT还是ROW格式的binlog，都会在末尾添加一个XID_EVENT事件代表事务的结束。该事件记录了该事务的ID，在MySQL进行崩溃恢复时，根据事务在binlog中的提交情况来决定是否提交存储引擎中状态为prepared的事务。

ROTATE_EVENT

当binlog文件的大小达到max_binlog_size的值或者执行flush logs命令时，binlog会发生切换，这个时候会在当前的binlog日志添加一个ROTATE_EVENT事件，用于指定下一个日志的名称和位置。

STOP_EVENT

当MySQL数据库停止时，会在当前的binlog末尾添加一个STOP_EVENT事件表示数据库停止。

四 gtid

4.1 gtid是什么

gtid全称是Global Transaction Identifier，mysql5.6之后可以使用gtid代替以前的file_name和file_position的方式进行主从复制；

gtid长啥样？

gtid由server_uuid和transaction_id组成，长这个样子：dcb84b95-eb6c-11ea-9ab3-6c92bf64c50c:1

dcb84b95-eb6c-11ea-9aa3-6c92bf64c50c: server_uuid，在auto.cnf中配置，可以通过 show variables like '%uuid%'查看；

1：事务ID号全局递增

gtid何时产生？

在一个事务提交时，会产生一个特殊的binlog event, 这个event类型是GTID_LOG_EVENT，这个event指定了下一个事务的gtid， 即：可以这样理解，每个事务开始前会对应有一个GTID_LOG_EVENT类型的event

4.2 使用gtid进行主从复制

gtid配置

MySQL 5.6

gtid_mode=ON#(必选) ，开启gtid模式

log_bin=ON #(必选)

log-slave-updates=ON

enforce-gtid-consistency=ON #强制gtid一致性(必选)， 保证gtid安全，因为开启grid_mode以后，许多MySQL的SQL和GTID是不兼容的。比如开启ROW 格式时，CREATE TABLE ... SELECT，在binlog中会形成2个不同的事务，GTID无法唯一

MySQL5.7.13 or higher

gtid_mode=ON(必选)

enforce-gtid-consistency=ON(必选)

log_bin=ON(可选)--高可用切换，最好设置ON

log-slave-updates=ON(可选)--高可用切换，最好设置ON

开启gtid模式后，就可以使用gtid来进行主从复制，在从库执行以下语句就可以自动的进行复制：

mysql> CHANGE MASTER TO \

-> MASTER_HOST = '', \

-> MASTER_PORT = 3306, \

-> MASTER_USER = 'test', \

-> MASTER_PASSWORD = '', \

-> MASTER_AUTO_POSITION = 1;

4.3 gtid原理

使用gtid复制，主库如何找点？

在change master后，从库会计算Retrieved_Gtid_Set和Executed_Gtid_Set的并集(通过SHOW SLAVE STATUS可以查看)，然后把这个GTID并集发送给主库。主库会使用从库请求的GTID集合和自己的gtid_executed比较，把从库GTID集合里缺失的事务全都发送给从库。如果从库缺失的GTID，已经被主库pruge了，从库报1236错误，IO线程中断。

4.4 mariadb gtid复制

mariadb的gtid有domainId serverId和tranctionId组成，长domainId-serverId-tranctionId这个样子，如下：0-2967541547-12869203，每个gtid event标识下一个事务event group所使用的全局事务id。mariadb可以通过如下语句一个slave加入某个master的备机中，进行主从复制：

CHANGE MASTER TO master_host="127.0.0.1", master_port=3306, master_user="root", master_use_gtid={ slave_pos | current_pos | no };

当master_use_gtid设置为current_pos时, slave加入主备复制时，主会把备机的gtid_current_pos作为开始复制binlog的起始点，一般情况下只有备机没有自己的binlog时，才考虑这种方式，因为如果备机自己产生了binlog，如实例A一开始是slave，并且是只读的实例，但是如果对实例A进行写的操作，并且实例A的产生了自己的gtid，这个时候在用current_pos模式加入复制时，主就找不到实例A自己产生的gtid点了

当master_use_gtid设置为slave_pos时，主会把备机的gtid_slave_pos作为开始复制binlog的起止点，gtid_slave_pos记录是实例A从其他master应用的event的最后一个gtid点，可以通过下面查看gtid各变量信息：

MariaDB [(none)]> show variables like '%gtid%';

+-------------------------+--------------------------------------------+

| Variable_name | Value |

+-------------------------+--------------------------------------------+

| gtid_binlog_pos | 0-2967541547-12869500 |

| gtid_binlog_state | 0-622864171-10710441,0-2967541547-12869500 |

| gtid_cleanup_batch_size | 64 |

| gtid_current_pos | 0-2967541547-12869500 |

| gtid_domain_id | 0 |

| gtid_ignore_duplicates | OFF |

| gtid_pos_auto_engines | |

| gtid_seq_no | 0 |

| gtid_slave_pos | 0-622864171-10710441 |

| gtid_strict_mode | OFF |

| last_gtid | |

| wsrep_gtid_domain_id | 0 |

| wsrep_gtid_mode | OFF |

+-------------------------+--------------------------------------------+

gtid_binlog_pos： gtid_binlog_pos是一个只读变量，变量是写入二进制日志的最后一个事件组的gtid; reset master是会重置gtid_binlog_pos的，但是不会重置gtid_slave_pos；reset master后，因为gtid_current_pos是根据gtid_binlog_pos和gtid_slave_pos计算得来的，所以gtid_current_pos是有可能高于gtid_binlog_pos的；

gtid_current_pos: gtid_current_pos是每个复制域应用到本实例的最新一个gtid，gtid_current_pos是根据gtid_binlog_pos和gtid_slave_pos计算得来的，当gtid_binlog_pos中的gtid的serverid和实例本身serverId相同并且序列号大于gtid_slave_pos时，gtid_current_pos取gtid_binlog_pos的值，反之则取gtid_slave_pos值，总之gtid_current_pos是在本实例执行的最新的gtid，不管实例是作为主还是从；

gtid_slave_pos: gtid_slave_pos记录是实例A从其他master应用的event的最后一个gtid点;

引用