复制延迟，复制优化和参数模板

复制延迟

搞明白当前的数据库在干什么

查看MySQL当前SQL的状态

利用perf top查看MySQL的调度情况

 

复制延迟解决方法

常规检查

所有的机器是不是一样

MySQL的版本是不是一样

相关配置是不是一样

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

IO thread延迟

master: show master status;

slave: show slave status\G read_master_log_file,read_master_log_pos < master_log_file,master_log_pos

SQL thread延迟

是否开启并行复制(主从：binlog group commit)

是否启用writeset

更改从库的事务提交配置

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

禁用表的自动收集统计信息

 

复制优化

master配置优化

binlog_format = row

binlog_row_image = full

gtid_mode = on

enforce_gtid_consistency = on

#binlog group commit

binlog_group_commit_sync_delay = 100

binlog_group_commit_sync_no_delay_count = 10

binlog_order_commits = off

#writeset

transaction_write_set_extraction = on

binlog_transaction_dependency_tracking = COMMIT_ORDER | WRITESET | WRITESET_SESSION

binlog_transaction_dependency_history_size = 25000

io_thread配置优化

slave_net_timeout = 20 | 30

io_thread更多的优化：change master to 相关参数

master_connect_retry = 60

master_connect_count = 24*3600

master_auto_position = 1

master_delay = 0

master_bind = ''

sql_thread配置优化

log_slave_updates

slave_parallel_type = LOGICAL_CLOCK

slave_parallel_workers = 4 | 8

slave_checkpoint_group = 512

slave_checkpoint_period = 300

slave_preserve_commit_order = on

slave_rows_search_algorithms = INDEX_SCAN,HASH_SCAN

其他优化

sync_binlog = 0

innodb_flush_log_trx_commit = 2

read_only = 1

super_read_only = 1 

#binlog group commit

binlog_group_commit_sync_delay = 100

binlog_group_commit_sync_no_delay_count = 10

 

描述出复制原理(异步，io_thread，sql_thread，半同步)

搭建新的从库，利用复制做数据迁移

理解增强半同步复制 & binlog写入增强

binlog group commit & writeset & 并行复制

可以处理复制故障 & 理解复制延迟

理解多源复制，延迟复制，复制过滤

 

复制参数配置终版

binlog相关优化配置(含binlog group commit)

binlog_format = row 

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

max_binlog_size = 1024M

binlog_rows_query_log_events = on

log_slave_updates = on

expire_logs_days = 7

binlog_cache_size = 65536                      # 65535 (64k)

binlog_checksum = none                        # CRC32

sync_binlog = 1

slave-preserve-commit-order = on

#GTID

gtid_mode = on

enforce_gtid_consistency = on

#binlog group commit

binlog_group_commit_sync_delay = 100

binlog_group_commit_sync_no_delay_count = 10

io_thread相关优化配置

slave_net_timeout = 20 | 30 | 60

change master to ... master_connect_retry masster_retry_count master_heartbeat_period

max_relay_log_size = 8M

relay_log = relay-bin

relay_log_recovery = on

sql_thread相关优化配置(含并行复制，writeset配置)

slave_parallel_type = logical_clock

slave_parallel_workers = 4

slave_preserve_commit_order = on

slave_rows_search_algorithms = INDEX_SCAN,HASH_SCAN

#writeset

binlog_transaction_dependency_tracking = writeset | write_session

transaction_write_set_extraction = XXHASH64

半同步中参数优化配置

rpl_semi_sync_master_enabled = 1

rpl_semi_sync_slave_method = 1

rpl_semi_sync_master_timeout = 1000

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

relay_log_recovery = 1

sync_relay_log_info = 1

master_info_repository = table

relay_log_info_repository = table

skip_slave_start = 1