MySQL复制

 

MySQL复制的了解

MySQL-3.22版本开始支持复制（statement)

利用复制，添加从库，通过从库提升读的能力及主库故障可以在从库里提升出来一个新的主库，从而达到高可用的目的。

目前MySQL为了支持多引擎的主从复制，引入了一个Binary Log，所有的复制都是基于Binary Log实现

现在标准的选择的：Row + GTID复制

 

 

各个版本的复制变化

5.5 半同步（io_thread)

5.6 基于库的并行复制 （GTID）(并行复制，5.6和之前不要使用，可能会出现bug，5.7之后开始使用并行）

5.7 基于binlog group commit 并行复制，增强半同步(多源复制)

8.0 writeset（MGR)

 

 

复制原理

从库有两个工作线程，sql thread 和 IO thread， 从库请求主机的binlog中的内容， 主库通过自己的dump thread 把binlog的内容传递给从库，从库通过自己的IOthread将主库传递的内容写入到从库的中继日志（relay log），从库通过sql thread将relay log的内容重做到从库。

master: 

　　dump_thread (5.7开始从master thread独立出来)

slave:

　　IO_thread (写relay_log)

　　SQL_thread (读取relay_log)

 

从库写binlog， 开启： log_slave_updates = on

 

 

理解binlog

根据日志格式分：statement ， row ， mixed

记录最小的单位是一个Event，日志前4个字节是一个magic number，接下19个字节记录Format desc event : FDE

一个事务由多个event组成

binlog相关的包含：mysql-bin.000001 和 mysql-bin.index文件

5.6开始增加了GTID复制

statement格式

基于语句级的复制 :binlog_format=statement

优点：

binlog 文件较小

日志包含用户执行的原始sql ，方便统计和审计

出现最早可binlog兼容较好

binlog方便阅读，方便故障修复

缺点：

存在安全隐患，可能导致主从不一致

对一些系统函数不能准复制或是不能复制

load_file()

uuid()

user()

found_rows()

sysdate()

row格式

优点：

相比statement更加安全的复制格式 binlog_format=row

在某些情况下复制速度更快（sql复杂，表有主键）

系统的特殊函数也可以复制

更少的锁

缺点：

binlog 比较大（支持binlog_log_image)

但语句更新（删除）表的行数过多，会形成大量binlog

无法从binlog看见用户执行sql（event：binlog_row_query_log_events,记录用户的query）

mysqlbinlog -v --base64-output=decode-rows mysql-bin.000001

row格式主从

不管主库sql是什么，从库都会根据主键去执行，所以表必须创建主键，或索引，否则全表扫。(8.0开始默认启用hash_scan,slave_rows_search_algorithms参数控制)

mix格式

ddl dcl记录statement，dml记录row

 

建议：

binlog_format = row

binlog_row_image = FULL 

 

 

MySQL5.7复制增强

查看同步信息：

5.7之前： show slave status\G

5.7之后： performance_schema.repli*

在线支持传统复制到DTID复制切换

在线复制过滤规则变更

可以利用SQL从PS库获取复制信息

多线程复制

半同步复制线程改进

多源复制

 

 

 MySQL8.0改进

行级(write-set)并行复制

binlog_transaction_dependency_tracking

　　COMMIT_ORDER

　　WRITESET

　　WRITESET_SESSION

COMMIT_ORDER

　　基于锁的并发策略

WRITESET

　　基于主键的并发策略，可以并发的执行同一个session内的事务，具有最好的性能

　　（同一个事务操作不同的表，可以并行操作）

WRITESET_SESSION

　　基于主键的并发策略，不可以并发执行同一个session内的事务。

 如果使用WRITESET功能需要开启(8.0默认开启，5.7默认OFF)

transaction_write_set_extraction = XXHASH64

 

 

 延迟计算

 A -> B -> C -> D

怎么计算A和D之间的延迟

8.0之前：

1）create table t_delay(c1 datetime(6), c2 datetime(6));

2) set binlog_format=statement; insert into t_delay(c1) values(now());

3) select  c1 from t_delay limit 1;

4) update t_delay set c2=c1.values;

 8.0之后：

 select * from performance_schema.replication_applier_staus_by_worker\G;

日志记录：

original_commit_timestamp: 这个值是主库执行DML产生的时间戳

immediate_commit_timestamp：当前执行该DML时候的时间戳

 

 

复制环境搭建

基于binlog+file position方式的复制

1、创建同步用户：

grant replication slave on *.* to 'repl'@'%' identified by 'repl'; 

flush  privileges;

2、在主库上做备份
mysqldump --master-data=2 --single-transaction -A > db3306_`date+%Y%m%d`.sql
3、拷贝备份集到从库，并恢复数据

time mysql -uroot -p -S /tmp/my3306.sock < /tmp/db.sql  (time 可以看执行时间）
4、从库配置主从信息（master_log_file，master_log_pos 的值在备份文件中可以查看）

change master to master_host='192.168.1.12',master_port=3306,master_user='repl',master_password='repl',master_log_file='mysql-bin.000005',master_log_pos=333; 

建议生产从库配置log_slave_updates

基于GTID环境搭建

1、创建同步用户：

grant replication slave on *.* to 'repl'@'%' identified by 'repl'; 

flush  privileges;

2、在主库上做备份
mysqldump --master-data=2 --single-transaction -A > db3306_`date+%Y%m%d`.sql
3、拷贝备份集到从库，并恢复数据

time mysql -uroot -p -S /tmp/my3306.sock < /tmp/db.sql  (time 可以看执行时间）
4、从库配置主从信息

change master to master_host='192.168.1.11',master_port=3306,master_user='repl',master_password='repl',master_auto_position=1;

通过 checksum table tbname; 查看主从表的checksum值是否相同。 

 

 从库IO_thread线程做了什么

 主库开启general log

从库连接主库

1) repl@host on using TCP/IP

2）SELECT UNIX_TIMESTAMP()

3) SELECT @@GLOBAL.SERVER_ID

4) SET @master_heartbeat_period = xxxxxxxxx

5) SET @master_binlog_checksum = @@global.binlog_checksum

6) SELECT @master_binlog_checksum

7) SELECT @@GLOBAL.GTID_MODE

8) SELECT @@GLOBAL.SERVER_UUID

9) SET @slave_uuid = 'xxxxxxxxxxxxxxxxxx'    （告诉主库，从库的GTID） 

10） GTID Log: '' Pos:4 GTIDs: 'xxxxxxxxxxxxxx,xxxxxxxxxxxxxx' （告诉主库，从库执行了那些GTID）

 

 

 主从模式非GTID环境切换成GTID环境

分别在从库，主库执行下面命令

SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN;

SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;

SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;

SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;

SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT';

SET @@GLOBAL.GTID_MODE = ON;

STOP SLAVE [FOR CHANNEL 'channel'];

CHANGE MASTER TO MASTER_AUTO_POSITION = 1 [FOR CHANNEL 'channel'];

START SLAVE [FOR CHANNEL 'channel'];

 

 

从库同步报错，跳过报错事务

GTID模式：

show slave status\G;

找到Executed_Gtid_set: xxxxxxxxxxxxxxxxxxxx:1-1000

stop slave;

SET @@SESSION.GTID_NEXT= ' xxxxxxxxxxxxxxxxxxxx:1-1001';

begin; commit;

set gtid_next='automatic';

start slave;

非GTID模式：

stop slave;

set global sql_slave_skip_counter=1;

start slave;

show slave status\G;

 

  

复制延迟判断

命令： show slave status\G

不延迟

Master_Log_FIle == Relay_Master_Log_File

Read_Master_Log_Pos == Exec_Master_Log_Pos

GTID：

Retrieved_Gtid_Set == Execute_Gtid_Set

如果出现延迟，查看此时正在执行的SQL事务。(主库执行）

mysqlbinlog -v -v --base64-output=decode-rows --start-position=Read_Master_Log_Pos  Master_Log_FIle

 

 

复制延迟解决办法

常规检查

　　所有机器配置是不是一样（Mem，CPU，Disk。。。）

　　MySQL版本是不是一样

　　网路是不是存在较大的延迟

IO thread延迟

　　Master: show master status;

　　Slave: show slave status;  read_master_log_file, read_master_log_pos < master_log_file, Master_log_pos

SQL thread延迟

　　是否是开启并行复制（主从: binlog group commit）

　　是否启用 writeset

　　更改从库的事务提交配置

　　检查是不是存在没有主键的表

　　禁用表的自动手机统计信息 

　　是否有大事务卡住：exec_master_log_pos 不动

　　从库：innodb_flush_log_at_trx_commit = 2 / sync_binlog = 0  (容易丢数据，如果切换成主，需要双1)

　　减少relay log大小 ,max_relay_log_size = 8M　　

　　禁用从库binlog（不推荐） （log-bin, log_slave_updates）

 

 

提高主从同步性能

MASTER：

开启 binlog-group-commit;  binglog并行写入。 ( 针对主库写Binlog做的优化）

默认是不启用的。

如果启用：

设置：  sync_binlog =1

            binlog_order_commits=off     (根据时间等待，按顺序执行，建议关闭，提高性能)

            binlog_format=row

            binlog_row_image=full

            gtid_mode =on

            ecforce_gtid_consistency =on

            binlog_group_commit_sync_delay=100 (微秒）

            binlog_group_commit_sync_no_delay_count=10 （个事务）

 

 

SLAVE：

io_thread:

slave_net_timeout = 20|30    20s对主库进行一次检测

change master to 相关参数：

master_connect_retry=60  重试时间（60s一重试）

master_connect_count=24*3600   重试次数

master_auto_position=1

master_delay =0  

master_bind=''

sql_thread:

log_slave_updates   开启binlog

slave_parallel_type=LOGICAL_CLOCK  开启并行

slave_parallel_workers=4|8       并发线程

slave_checkpoint_group=512  默认

slave_checkpoint_period=300  默认

slave_preserve_commit_order=on    (按主库顺序提交）要求：log-bin,slave-log-updates,slave_parallel_type=LOGICAL_CLOCK 

slave_rows_search_algorithms=TABLE_SCAN,INDEX_SCAN　　　　（没有主键，可以设置该参数）

开启writeset功能：　　（针对SQL_Thread，前提是主库开启binlog group commit）

transaction_write_set_extraction = on

 binlog_transaction_dependency_history_size=25000     

 binlog_transaction_dependency_tracking = COMMIT_ORDER | 

 

Replication Crash Safe

从库配置

relay_log_recovery = 1

relay_log_info_repository = table

sync_relay_log = 1

 

 

复制相关问题

master binlog 能否拷贝到slave上来替换relay log？

可以

writeset 是优化sql thread的，它是将多个行的lastcommited变成一个值，是在master写binlog时改变的，还是在写入relay log时猜统一改成一个lastcommited的？

sql_thread做

删除relay log物理文件，会被标记为deleted，但是内存态有relay log缓存吗？复制在这个时候还会继续正常跑吗？

不会正常跑，需要重新change master 。。。进行获取

线上发生一个drop table tb1； master-slave 怎么能快速恢复？

有延迟库：

从库：stop slave sql_thread;

主库: flush logs; 找倒数第二个binlog文件

mysqlbinlog -v --base64-output=decode-rows mysql-bin.last02;  find drop table 的gtid值

从库: change master to master_delay=0;

start slave sql_thread until gtid_before_gtid='主库找到的gtid值'

表空间传输，将从库该表导入到主库

没有延迟库：

当天备份重建一个从库

主库: flush logs; 找倒数第二个binlog文件

mysqlbinlog -v --base64-output=decode-rows mysql-bin.last02;  find drop table 的gtid值

change master to auto_position=1;

start slave io_thread;

change replication filter replicate_do_table=(dbname.dtopable);

start slave sql_thread until sql_before_gtid='主库找到的gtid值'';

dump 表，import到主库

 

 

主从切换方案

非GTID: MHA

GTID: replication-manager, github:orchestrator

 

复制参数配置

binlog相关优化配置（含binlog group commit）MySQL5.7之后

binlog_format = row

log_bin = /data/mysql/mysql3306/logs/mysql-bin

max_binlog_size = 1024M

binlog_rows_query_log_events = on     (开启之后binlog记录原始sql)

log_slave_updates = on

expire_logs_days = 7

binlog_cache_size = 65536

sync_binlog = 1

slave_preserve-commit-order = on　　( )

#GTID

gtid_mode = on

enforce_gtid_consistency = on

#binlog group commit (100ms或10个事务一提交)

binlog_group_commit_sync_delay = 100

binlog_group_commit_sync_no_delay_count = 10 

 

IO_Thread相关优化配置

slave_net_timeout = 60   (超过60s主从没有交互，主从断开连接)

change master to ....　　(不用修改)

　　master_connect_retry

　　master_retry_count

　　master_heartbeat_period

max_relay_log_size = 128M

relay_log = relay-bin

relay_log_recovery = on

 

SQL_Thread相关优化配置（含并行复制，writeset配置）

slave_parallel_type = logical_clock

slave_parallel_workers = 4 (cpu核数的1-1.5倍)

slave_preaerve_commit_order = on

slave_rows_search_algorithms = ’INDEX_SCAN,HASH_SCAN‘

#writeset

binlog_transaction_dependency_tracking = writeset | write_session (writeset_session)

transaction_write_set_extraction = XXHASH64

 

半同步中参数优化设置

rpl_semi_sync_master_enable =  1    (主）

rpl_semi_sync_slave_enable =  1　　（从）

repl_semi_sync_master_timeout = 1000  (1s,退化异步)

 

其他复制相关的特性及配置

master_info_repository = TABLE
relay_log_info_repository = TABLE

skip_slave_start = 1

 

转载于:https://www.cnblogs.com/yujiaershao/p/11239721.html