232_mysql_复制补充
1 主从复制中使用不同的表定义
主从之间具有不同字段数的表的复制的限制条件
- 源表和目标表都存在的字段, 定义的顺序必须相同
- 源表和目标表有差异的字段, 差异字段的定义顺序必须在相同的字段后面
- 源表和目标表有差异字段 需要设置默认值
A(a1 a2 a3) B(a1 a2)
1.1 源表字段多于目标表字段数
Binlog_format=statement 如果SQL语句中只涉及主从相同的字段正常复制insert into b (1,2)，如果SQL中字段在主从上存在差异会报错 insert into b(1,2,3)会报错
Binlog_formt=row 语句正常复制, 多出的字段 mysql自动忽略 @1 @2代表字段顺序 # 前提 binlog_row_image=full
binlog_row_image=full/minimal/noblob
（1）前镜像(before image)：记录修改前的内容
（2）后镜像(after image)：记录修改后的内容
full会记录所有列
noblob会记录除blob和text外的所有列
minimal只会记录需要的列
1、对于insert来说，只有后镜像没有前镜像；对于update来说，有前镜像和后镜像；对于delete来说，只有前镜像没有后镜像。
2、对于binlog_row_image=full，在有主键、有唯一索引、只有普通二级索引等场景下，insert记录所有列的后镜像，update记录所有列的前镜像和后镜像，delete记录所有列的前镜像。
3、对于binlog_row_image=minimal，在不同场景下，记录的信息是不一样的：
（1）在有主键的场景下，insert记录所有指定列的后镜像，update记录主键列的前镜像和指定更新列的后镜像，delete记录主键列的前镜像。
（2）在有唯一索引的场景下，insert记录所有指定列的后镜像，update记录唯一索引列的前镜像和指定更新列的后镜像，delete记录唯一索引列的前镜像。
（3）在只有普通二级索引的场景下，insert记录所有指定列的后镜像，update记录所有列的前镜像和指定更新列的后镜像，delete记录所有列的前镜像。
1.2 目标表字段多于源表字段数B(a1 a2) A(a1 a2 a3)
Binlog_format=statement 如果SQL语句中只涉及主从相同的字段正常复制insert into b (1,2)，如果SQL中字段在主从上存在差异会报错 insert into b(1,2,3)会报错
Binlog_formt=row 语句正常复制, 多出的字段内容会被赋予默认值
1.3不同类型字段的复制
某些字段属性类型定义本身就不相同
- 1.3.1 属性提升 char(10) à char(25) TINYINT à BIGINT, DATETIME à VARCHAR(50)
- 1.3.2 有损转换&无损转换 目标字段无法完整写入要插入的源数据值- 转换方式 – slave_type_converions
| 有效值 |
行为 |
| ALL_LOSSY |
允许有损转换,不允许无损转换, 仅允许有损转换 或不需要转换的情况,如遇到无损转换会报错中止 例 不允许 TINYINT 字段转换为INT字段 |
| ALL_NON_LOSSY |
此模式不需要截断或者特殊处理，不允许数据截断，如果从库类型大于主库类型，是可以复制的，反过了，就不行了，从库报复制错误，复制终止 |
| ALL_LOSSY,ALL_NON_LOSSY |
所有允许的转换都会执行，而不管是不是数据丢失（有损和无损转换） |
| All_SIGNED |
将提升后的整数类型视为有符号值（默认） |
| ALL_UNSIGNED |
将提升后的整数类型视为无符号值 |
| All_SIGNED ALL_UNSIGNED |
如有可能将提升后的整数类型视为有符号值，否则视为无符号数 |
| [empty] |
默认值, 空串. 意味主从字段类型必须一致, 否则报错 |
2 复制过程中的存储过程函数事件触发器的功能调用
| RC隔离级别下 |
|
| statement复制场景 |
CREATE PROCEDURE, FUNCTION, EVENT, TRIGGER DROP PROCEDURE FUNCTION, EVENT, TRIGGER 语句以Query_log_event事件记录 调存储过程，触发器报错 ERROR1665 如果是event 即使是statement模式下, 所产生的影响也是以row格式记录的 |
| ROW&Mixed复制场景 |
CREATE PROCEDURE, FUNCTION, EVENT, TRIGGER 语句以Query_log_event事件记录 而且记录 “DEFINER=root@localhost” 调用存储过程所产生的操作会按row格式进行记录 SET @1=1, @2=2 DROP PROCEDURE FUNCTION, EVENT, TRIGGER 以query_log_event形式记录 |
| 备注 |
Create event & alter event 语句,事件在从库上的状态都被设置为 slaveside_disabled，表示即使配置了事件也不会被调用，该状态可以在information_schema.events表的status查到 |
3 复制中有limit的场景
Update xxx set aa=’xxx’ limit 3;
Insert into xxx select aa, bb from a_table limit 3
| Read-uncommited和read-commited模式下 |
Binlog_format为statement时, DML无法执行，报错1665 |
| REPETABLE-READ 和 SERIALIZABLE 模式下 |
|
| Statement |
DML 带limit mysql会有告警提示，无法保证查询结果正确性 但正常执行 Update ….limit ; delete ….limit; insert into … select …limit (GTID & QUERY_LOG_EVENT) binlog中记录原始语句 |
| Row & Mixed |
Update ….limit ; delete ….limit; insert into … select …limit (GTID & QUERY_LOG_EVENT) Binlog中使用row模式记录 where @1=xx @2=xxx SET @1=xx @2=xx ; where @1=xxx 基于row 模式记录，能保证LIMIT 子句复制的正确性 |
4 LOAD DATA语句
数据库系统变量设置
Gtid_mode = on
Enfore-gtid-consistency = true
Binlog_row_image = full
Secure_file_priv = “/tmp”
Sql_mode = ‘only_full_group_by, strict_trans_tables, no_zero_in_date, no_zero_date, error_for_division_by_zero, no_auto_create_user, no_engine_substitution’
导入导出SQL
Select * from t_name into outfile ‘/tmp/aaa.txt’
Load data infile ‘/tmp/aaa.txt’ into table table_name
主库执行完成后，将 /server_tmp/t.csv 文件的内容直接写到 binlog 文件中。
往 binlog 文件中写入语句 load data local infile ‘/tmp/SQL_LOAD_MB-1-0’ INTO TABLE `db2`.`t`,把这个 binlog 日志传到备库。
备库的 apply 线程在执行这个事务日志时：
a.先将 binlog 中 t.csv 文件的内容读出来，写入到本地临时目录 /tmp/SQL_LOAD_MB-1-0 中;
b.再执行 load data 语句，往备库的 db2.t 表中插入跟主库相同的数据
| Statement |
# Begin_load_query: file_id: 1 block_len:19 # timestamp server id xxx end_log_pos xxx Execute_load_query thread_id =2 exec_time=0 error_code = 0 Load DATA LOCAL INFILE ‘/tmp/SQL_LOAD_MB-1-0’ INTO TABLE ‘t_name’ FIELDS TERMINATED BY ‘/t’ ENCLOSED BY ‘’ ESCAPED BY ‘\\’ LINES TERMINATED BY ‘\n’ (`字段`，`字段2`) 1 load data语句在binlog中的关键字是begin_load_query 和 executed_load_query 2 从库解析binlog begin_load_query_log_event事件时,在 tmpdir查询sql_load_MB前缀的文件 3 当发现execute_load_query类型事件时, 会将中继日志relay_log 数据提取出来存到临时文件夹中 4 load data数据实际存在binlog中，但mysqlbinlog statement格式 并未展示 5 mysqlbinlog 解析statement格式记录的load data时，会在tmpdir中创建一个本地临时文件, sql_load_MB_1_0文件，这个文件就是load data 导入的数据 |
| row |
### insert into xxx set @1=xx @2=xxx 就是row格式记录binlog 日志 |
4 Max_allowed_packet
控制一个数据包或由mysql_stmt_send_long_data()函数发送的所有参数的最大尺寸,默认是4MB
- 包消息缓冲区初始化 net_buffer_length大小，需要时可以自动增加到 max_allowed_packet 定义的值
- 使用大BLOB列或长字符串,则必须增加此值，最大1G 设置1024的倍数
Slave_max_allowed_packet
设置从库SQL线程和I/O线程的最大数据包尺寸，方便基于row复制中，减少因为大事物而产生的数据包大小超过 max_allowed_packet设置的值
Net_buffer_length
每个客户端会话线程相关的连续缓冲区和结果集缓冲区的初始大小，这两个缓冲区的初始值都是 net_buffer_length, 后面根据需要可以动态增加到 max_allowed_packet
5 复制临时表
Create temporary table test(xxx)
Insert into test
Drop table test
临时表是会话独享的,会话断开，则自动删除临时表，并在binlog中写入 drop temporary table
| Statement |
在主库上的所有操作都会被记录在binlog中，statement格式 Drop table 会被改成 drop temporary table 语句 保证主库中不在需要的表在从库上也能被删除 |
| Row |
Drop temporary table if exists tests /*generated by server*/ 除了drop操作外 都不会被记录在binlog中 # 如果使用临时表，如何安全关闭slave 等到变量 slave_open_temp_tables = 0 时 stop slave sql_thread |
6 复制中事务不一致
某些情况主从库数据不一致是由于主从事务不一致导致的
场景
| 事务半提交 (half-applied-transaction) |
事务更新了不支持事务特性的表，从库重放主库事务时无法保证事务的原子性，导致事务部分提交成功，部分失败 |
| 间隙Gap |
出现在多线程复制会出现,一组队列的事务在从库同时回放，队列中某个事务尚未完成,后面的事务已经完成 避免gap Slave_parallel_type = logical_clock Log_bin = xxx; log_slave_update = on Slave_preserve_commit_order = 1 （该参数无法保证非事务表的DML操作顺序） |
| 低水位情况 Master log position lag |
只会在多线程复制时出现,即使没有出现gap, 仍可能出现 exec_master_log_pos = N 但是实际事务已经应用到N后面的情况. 单线程情况N 代表之前事务已经应用完成 多线程情况 每个worker都有自己应用事务的位置, 通过一定机制来计算所有worker线程已经应用完事务的位置（完成checkpoint事务的位置）在计算出exec_master_log_pos |
原因
| 在从库复制线程运行过程中，可能存在间隙和事务半提交的情况 |
| Mysqld 进程关闭，可能会中断应用中的事务，留下间隙或出现事务半提交的情况 |
| Kill 复制线程(单线程kill 复制SQL线程，多线程复制时 kill 协调器线程) 会中断应用中的事务，留下间隙或出现事务半提交的情况 |
| Applier线程(worker线程)出错时,可能会出现间隙， 如果发生在混合事务中,则该事务处于半提交状态, 如果多线程从库 未发生错误的worker会继续执行事务，因此发生错误时候，停止所有worker线程 会需要一些时间 |
| 多线程复制 stop slave后 从库会等待填充GAP,然后执行 exec_master_log_pos值，确保不会留下gap或者低水位情况 |
| 如果relaylog最后一个事务只接收了一半,而且多线程从库的协调器线程已开始将事务调度给worker线程，则执行stop slave语句后，等待60S 后还未收到剩余事务的事件，则会发生超时(stop slave后只有60s 给事务提交或者回滚) 超时后，协调器线程会放弃并终止正在执行的事务，如果是混合事务，会出现半提交事务 |
| 单线程复制中，stop slave 如果正在执行的事务仅更新新事务表，则该事务立即被回滚 如果执行混合事务，则最多等待60s以完成事务，超过60s 未完成则会被终止，可能会造成半提交事务 |
事务不一致的结果和影响
- Reset slave 会清理relay_log并重置复制的位置，如果主从复制存在gap ， 执行reset slave 语句后，关于gap情况的信息丢失，就无法发现间隙问题
- 使用GTID 复制，mysql会自动判断，如果GTID 的事务已经被应用则跳过,利用GTID 特性可以恢复主从复制
| 主从数据不一致，从库中缺少了某些数据 |
| Show slave status 中字段 exec_master_log_pos 是一个低水位 |
| 如果 applier正在运行，执行 change master to 语句将失败并显示错误 ERROR 3021(HY000): This operation cannot be performed with a running slave io thread；run stop slave IO_THREAD FOR CHANNEL first |
| 如果使用 –relay-log-recover 选项启动mysqld，则不会对该通道进行恢复，因为relay_log直接被清理，不会使用relay_log进行恢复，并打印告警信息 |
| 如果mysqldump 与 –dump-slave一起使用，则不会记录gap， 因为备份文件产生的change master to 语句中的relay_log_pos 的位置是通过 show slave status语句中获取的 exec_master_log_pos低水位位置值。 在另一台server上应用备份文件&启动复制线程后，可能出现已经被应用过的事务通过复制被重复应用，如果启用GTID ，则可以自动跳过重复事务，如未启用，则不建议使用 –dump-slave选项 |
| 关于主库位置点信心会放在 mysql.slave_worker_info表中, start slave 语句执行时 会从该表中查询信息，以应用正确的事务。 Start slave until sql after MTS GAPS 语句也能通过该表的信息填补复制间隙中缺失的事务 |
- 点赞
- 收藏
- 关注作者
评论（0）