金沙棋牌app手机下载

当前位置:金沙棋牌 > 金沙棋牌app手机下载 > MySql开启GTID和多线程复制功能,7二进制多实例安

MySql开启GTID和多线程复制功能,7二进制多实例安

来源:http://www.logblo.com 作者:金沙棋牌 时间:2019-11-22 11:09

1.修改参数

复制中的重要参数

与MySQL传统复制相比,GTID有哪些独特的复制姿势?

percona-mysql-5.7二进制多实例安装

规划:
端口号     配置文件     备注
3306 /data/mysql/mysql_3306/my_3306.cnf
3307 /data/mysql/mysql_3307/my_3307.cnf
3308 /data/mysql/mysql_3308/my_3308.cnf
3309 /data/mysql/mysql_3309/my_3309.cnf
3310 /data/mysql/mysql_3310/my_3310.cnf

# percona-server二进制多实例安装

一、系统配置-物理机-可能会有差异
#手工的执行如下的动作,使之立刻生效:
echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag
echo deadline > /sys/block/sda/queue/scheduler
echo "16" > /sys/block/sda/queue/read_ahead_kb
echo "512" > /sys/block/sda/queue/nr_requests


#减少预读:/sys/block/sda/queue/read_ahead_kb,默认128,调整为16
#增大队列:/sys/block/sda/queue/nr_requests,默认128,调整为512

echo "16" > /sys/block/sda/queue/read_ahead_kb
echo "512" > /sys/block/sda/queue/nr_requests

#如果是使用普通SAS盘的话,使用elevator=deadline
#如果是使用SSD/FLASH卡的话,使用elevator=noop
echo noop > /sys/block/sda/queue/scheduler
echo deadline > /sys/block/sda/queue/scheduler

#对于关闭透明大页的问题,也执行如下的操作:编辑 /etc/rc.local,添加如下内容

cat >> /etc/rc.local <<EOF
#echo noop > /sys/block/sda/queue/scheduler
echo deadline > /sys/block/sda/queue/scheduler
echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag
echo "16" > /sys/block/sda/queue/read_ahead_kb
echo "512" > /sys/block/sda/queue/nr_requests

if test -f /sys/kernel/mm/transparent_hugepage/enabled; then
   echo never > /sys/kernel/mm/transparent_hugepage/enabled
fi
if test -f /sys/kernel/mm/transparent_hugepage/defrag; then
   echo never > /sys/kernel/mm/transparent_hugepage/defrag
fi
EOF

cat >> /etc/sysctl.conf <<EOF
fs.file-max=655360
fs.aio-max-nr = 1048576
kernel.sem = 5050 646400 5050 128
kernel.shmmax = 137438953472
kernel.shmall = 4294967296
kernel.shmmni = 4096
net.ipv4.ip_local_port_range = 9000 65500
net.ipv4.tcp_mem = 94500000 915000000 927000000
net.core.wmem_default = 8388608
net.core.rmem_default = 8388608
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216
net.ipv4.tcp_rmem = 4096 87380 16777216
net.ipv4.tcp_wmem = 4096 65536 16777216
net.core.netdev_max_backlog = 32768
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_fin_timeout = 10
net.ipv4.tcp_keepalive_time = 300
net.ipv4.tcp_max_syn_backlog = 32768
net.ipv4.tcp_syncookies = 1
net.ipv4.tcp_timestamps = 0
net.ipv4.conf.default.accept_source_route = 0
vm.swappiness=1
EOF

 

# 使其生效

sysctl -p

# 安装依赖包

yum -y install gcc gcc-c++ make autoconf automake ncurses-devel bison ncurses cmake libaio libaio-devel boost

 

# 基本初始化 #
mkdir -p /opt/mysql
cd /opt/mysql/
wget
tar -zxf Percona-Server-5.7.20-18-Linux.x86_64.ssl101.tar.gz
cd /usr/local/
ln -s /opt/mysql/Percona-Server-5.7.20-18-Linux.x86_64.ssl101 mysql

# 创建所需要的目录:
mkdir -p /data/mysql/mysql_3306/{data,logs,tmp}
mkdir -p /data/mysql/mysql_3307/{data,logs,tmp}
mkdir -p /data/mysql/mysql_3308/{data,logs,tmp}
mkdir -p /data/mysql/mysql_3309/{data,logs,tmp}
mkdir -p /data/mysql/mysql_3310/{data,logs,tmp}

# 更改权限
groupadd mysql
useradd -g mysql mysql -d /home/mysql -s /sbin/nologin

# 配置文件: vim /data/mysql/mysql_3306/my_3306.cnf 详见附录

# 附录:
cat >/data/mysql/mysql_3306/my_3306.cnf <<EOF
#my.cnf
[client]
port = 3306
socket = /data/mysql/mysql_3306/tmp/mysql_3306.sock

[mysql]
prompt="\u@\h:p \R:\m:\s [\d]>"
#tee=/data/mysql/mysql_3306/data/query.log
no-auto-rehash

[mysqld]
#misc
user = mysql
basedir = /usr/local/mysql
datadir = /data/mysql/mysql_3306/data
port = 3306
socket = /data/mysql/mysql_3306/tmp/mysql_3306.sock
event_scheduler = 0
#tmp
tmpdir=/data/mysql/mysql_3306/tmp

#timeout
interactive_timeout = 300
wait_timeout = 300

#character set
character-set-server = utf8

open_files_limit = 65535
max_connections = 100
max_connect_errors = 100000

skip-name-resolve = 1
#logs
log-output=file
slow_query_log = 1
slow_query_log_file = /data/mysql/mysql_3306/logs/slow.log
log-error = /data/mysql/mysql_3306/logs/mysql_3306_error.log
log_error_verbosity = 3
pid-file = mysql_3306.pid
long_query_time = 1
#log-slow-admin-statements = 1
#log-queries-not-using-indexes = 1
log-slow-slave-statements = 1

#binlog
binlog_format = row
server-id = 623306
log-bin = /data/mysql/mysql_3306/logs/mysql-bin
log-bin-index = /data/mysql/mysql_3306/logs/mysql-bin.index
binlog_cache_size = 4M
max_binlog_size = 1G
max_binlog_cache_size = 2G
sync_binlog = 1
expire_logs_days = 90

#relay log
skip_slave_start = 1
max_relay_log_size = 1G
relay_log_purge = 1
relay_log_recovery = 1
log_slave_updates
#slave-skip-errors=1032,1053,1062

explicit_defaults_for_timestamp=1
#buffers & cache
table_open_cache = 2048
table_definition_cache = 2048
table_open_cache = 2048
max_heap_table_size = 96M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 256
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 256K
query_cache_min_res_unit = 512
thread_stack = 192K
tmp_table_size = 96M
key_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 32M

#myisam
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1

#innodb
innodb_buffer_pool_size = 10G
innodb_buffer_pool_instances = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 64M
innodb_log_file_size = 500M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 50
innodb_file_per_table = 1
innodb_rollback_on_timeout
innodb_status_file = 1
innodb_io_capacity = 2000
transaction_isolation = READ-COMMITTED
innodb_flush_method = O_DIRECT
gtid_mode = ON
enforce_gtid_consistency = ON
master_info_repository = TABLE
relay-log-info-repository = TABLE
binlog_checksum = NONE
log_slave_updates = ON

# Two-Master configure
#server-1
#auto-increment-offset = 1
#auto-increment-increment = 2

#server-2
#auto-increment-offset = 2
#auto-increment-increment = 2

slave_preserve_commit_order = 1
slave_transaction_retries = 128
log_timestamps = system
show_compatibility_56 = on
slave_parallel_workers = 16
slave_parallel_type = LOGICAL_CLOCK

# group replication
##log-bin = mysql
##server-id = 613306
##gtid_mode = ON
##enforce_gtid_consistency = ON
##master_info_repository = TABLE
##relay-log-info-repository = TABLE
##binlog_checksum = NONE
##log_slave_updates = ON
##binlog_format = row
##transaction_write_set_extraction=XXHASH64
##loose-group_replication_group_name = '3db33b36-0e51-409f-a61d-c99756e90154'
##loose-group_replication_start_on_boot = off
##loose-group_replication_local_address= "10.0.0.62:23306" # 不能超过5位数字
##loose-group_replication_group_seeds= "10.0.0.62:23306,10.0.0.62:23307,10.0.0.62:23308" # 不能超过5位数字
##loose-group_replication_bootstrap_group= off
# loose-group_replication_single_primary_mode=FALSE ###本次搭建的是mutil_mode
# loose-group_replication_enforce_update_everywhere_checks= TRUE

[mysqld_safe]
#malloc-lib=/usr/local/mysql/lib/jmalloc.so
nice=-19
open-files-limit=65535
EOF

cp /data/mysql/mysql_3306/my_3306.cnf /data/mysql/mysql_3307/my_3307.cnf
cp /data/mysql/mysql_3306/my_3306.cnf /data/mysql/mysql_3308/my_3308.cnf
cp /data/mysql/mysql_3306/my_3306.cnf /data/mysql/mysql_3309/my_3309.cnf
cp /data/mysql/mysql_3306/my_3306.cnf /data/mysql/mysql_3310/my_3310.cnf

chown -R mysql.mysql /data/mysql/

sed -i 's/3306/3307/g' /data/mysql/mysql_3307/my_3307.cnf
sed -i 's/3306/3308/g' /data/mysql/mysql_3308/my_3308.cnf
sed -i 's/3306/3309/g' /data/mysql/mysql_3309/my_3309.cnf
sed -i 's/3306/3310/g' /data/mysql/mysql_3310/my_3310.cnf

#1、修改server-id
sed -i 's/623306/1413306/g' /data/mysql/mysql_3306/my_3306.cnf
sed -i 's/623307/1413307/g' /data/mysql/mysql_3307/my_3307.cnf
sed -i 's/623308/1413308/g' /data/mysql/mysql_3308/my_3308.cnf
sed -i 's/623309/1413309/g' /data/mysql/mysql_3309/my_3309.cnf
sed -i 's/623310/1413310/g' /data/mysql/mysql_3310/my_3310.cnf

#2、修改server-id
sed -i 's/623306/1423306/g' /data/mysql/mysql_3306/my_3306.cnf
sed -i 's/623307/1423307/g' /data/mysql/mysql_3307/my_3307.cnf
sed -i 's/623308/1423308/g' /data/mysql/mysql_3308/my_3308.cnf
金沙棋牌app手机下载,sed -i 's/623309/1423309/g' /data/mysql/mysql_3309/my_3309.cnf
sed -i 's/623310/1423310/g' /data/mysql/mysql_3310/my_3310.cnf

# 初始化
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3306/my_3306.cnf --initialize-insecure &
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3307/my_3307.cnf --initialize-insecure &
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3308/my_3308.cnf --initialize-insecure &
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3309/my_3309.cnf --initialize-insecure &
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3310/my_3310.cnf --initialize-insecure &

# 启动
# /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3306/my_3306.cnf &
# /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3307/my_3307.cnf &
# /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3308/my_3308.cnf &
# /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3309/my_3309.cnf &
# /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3310/my_3310.cnf &

# 启动推荐做法
/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3306/my_3306.cnf &
/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3307/my_3307.cnf &
/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3308/my_3308.cnf &
/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3309/my_3309.cnf &
/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3310/my_3310.cnf &

# 进入mysql
/usr/local/mysql/bin/mysql -uroot -p -P3306 -S /data/mysql/mysql_3306/tmp/mysql_3306.sock
/usr/local/mysql/bin/mysql -uroot -p -P3307 -S /data/mysql/mysql_3307/tmp/mysql_3307.sock
/usr/local/mysql/bin/mysql -uroot -p -P3308 -S /data/mysql/mysql_3308/tmp/mysql_3308.sock
/usr/local/mysql/bin/mysql -uroot -p -P3309 -S /data/mysql/mysql_3309/tmp/mysql_3309.sock
/usr/local/mysql/bin/mysql -uroot -p -P3310 -S /data/mysql/mysql_3310/tmp/mysql_3310.sock

set sql_log_bin = 0;
create user 'rpl_user'@'%';
grant replication slave on *.* to 'rpl_user'@'10.%' identified by 'BR8KLwXCKHgN';
update mysql.user set authentication_string=password('rootpwd2017') where user='root';
flush privileges;
set sql_log_bin = 1;
reset master ; reset slave all;

CHANGE MASTER TO MASTER_HOST='10.0.0.138',MASTER_USER='rpl_user',
MASTER_PASSWORD='BR8KLwXCKHgN',MASTER_PORT=3306,
MASTER_CONNECT_RETRY=10,MASTER_AUTO_POSITION =1;

start slave;
show slave statusG;

 

# 设置便捷使用方式
/usr/local/mysql/bin/mysql_config_editor set --host=localhost --login-path=3306_localhost_login
--user=root --port=3306 --password --socket=/data/mysql/mysql_3306/tmp/mysql_3306.sock

/usr/local/mysql/bin/mysql --defaults-file=/data/mysql/mysql_3306/my_3306.cnf --login-path=3306_localhost_login

/usr/local/mysql/bin/mysql_config_editor set --host=localhost --login-path=3307_localhost_login
--user=root --port=3307 --password --socket=/data/mysql/mysql_3307/tmp/mysql_3307.sock
/usr/local/mysql/bin/mysql --defaults-file=/data/mysql/mysql_3307/my_3307.cnf --login-path=3307_localhost_login

/usr/local/mysql/bin/mysql_config_editor set --host=localhost --login-path=3308_localhost_login
--user=root --port=3308 --password --socket=/data/mysql/mysql_3308/tmp/mysql_3308.sock
/usr/local/mysql/bin/mysql --defaults-file=/data/mysql/mysql_3308/my_3308.cnf --login-path=3308_localhost_login

/usr/local/mysql/bin/mysql_config_editor set --host=localhost --login-path=3309_localhost_login
--user=root --port=3309 --password --socket=/data/mysql/mysql_3309/tmp/mysql_3309.sock
/usr/local/mysql/bin/mysql --defaults-file=/data/mysql/mysql_3309/my_3309.cnf --login-path=3309_localhost_login

/usr/local/mysql/bin/mysql_config_editor set --host=localhost --login-path=3310_localhost_login
--user=root --port=3310 --password --socket=/data/mysql/mysql_3310/tmp/mysql_3310.sock
/usr/local/mysql/bin/mysql --defaults-file=/data/mysql/mysql_3310/my_3310.cnf --login-path=3310_localhost_login

cat >>/root/.bashrc <<EOF
alias mysql.3306.start='/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3306/my_3306.cnf &'
alias mysql.3307.start='/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3307/my_3307.cnf &'
alias mysql.3308.start='/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3308/my_3308.cnf &'
alias mysql.3309.start='/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3309/my_3309.cnf &'
alias mysql.3310.start='/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3310/my_3310.cnf &'

alias mysql.3306.login='/usr/local/mysql/bin/mysql --defaults-file=/data/mysql/mysql_3306/my_3306.cnf --login-path=3306_localhost_login'
alias mysql.3307.login='/usr/local/mysql/bin/mysql --defaults-file=/data/mysql/mysql_3307/my_3307.cnf --login-path=3307_localhost_login'
alias mysql.3308.login='/usr/local/mysql/bin/mysql --defaults-file=/data/mysql/mysql_3308/my_3308.cnf --login-path=3308_localhost_login'
alias mysql.3309.login='/usr/local/mysql/bin/mysql --defaults-file=/data/mysql/mysql_3309/my_3309.cnf --login-path=3309_localhost_login'
alias mysql.3310.login='/usr/local/mysql/bin/mysql --defaults-file=/data/mysql/mysql_3310/my_3310.cnf --login-path=3310_localhost_login'
EOF

source /root/.bash_profile

#################################### gtid故障处理开始 ####################################
模拟在从库删除库,然后再主库删除该库,报如下错误
Last_SQL_Error: Error 'Can't drop database 'db1'; database doesn't exist' on query. Default database: 'db1'. Query: 'drop database db1'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 623306
Master_UUID: 11526eb0-fcbc-11e6-af7d-005056b937e2
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 170227 15:44:06
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 11526eb0-fcbc-11e6-af7d-005056b937e2:1-2
Executed_Gtid_Set: 11526eb0-fcbc-11e6-af7d-005056b937e2:1,
1760a7a5-fcbc-11e6-8f14-005056b90358:1
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:

处理方法:
stop slave;
set gtid_next='11526eb0-fcbc-11e6-af7d-005056b937e2:2';
begin;
commit;
set gtid_next='automatic';
start slave;
show slave statusG;

#################################### gtid故障处理开始 ####################################

 

 

 

master:

master上的参数

  • log-bin
  • server-id #必须 建议端口+ip
  • server-uuid #在$datadir/auto.cnf中,5.6后才有的
  • log-bin-index #mysql-bin.index
  • binlog_format
  • binlog_cache_size
  • max_binlog_size
  • sync_binlog
  • expire_logs_days
  • log_bin_trust_function_creators

gtid_mode = ON                        --开启gtid这个必须打开

参数解析

  • server_id
    • 是唯一区别ID,同一个集群内不可重复,可动态修改,从5.6开始
    • 可以动态修改
  • log-bin-index
    • 索引文件前缀名,和log-bin一样,也可以是fullpath
    • 不可动态修改
  • binlog_format
    • 是binlog日志格式,有statement,row,mixed三种
    • 可动态修改
  • binlog_cache_size
    • 是binlog写入buffer,1-2M
  • max_binlog_size
    • 限制单个binlog大小
    • 默认情况下是1G,binlog控制最小生成时间间隔要大于2分钟,不能太小,默认1G有点大不好解析,推荐128M,或者256M
    • 可动态修改
  • sync_binlog=N
    • 交易系统为了安全:设置成1
    • 意思是多少个SQL之后,调用fdatasync()函数刷新binlog到disk
    • 可动态修改
    • fsync()是完全刷新到磁盘,fdatasync()指刷新数据,不刷新metadata
  • expire_logs_days=n
    • 推荐保存7天
    • n天后自动删除binlog
    • 可动态修改
  • log_bin_trust_function_creators=1
    • 设置成1就可以了
    • 开启binlog时,是否允许创建存储程序(除非有super群贤,或者指定DETERMINISTIC,READS SQL DATA,NO SQL)
    • 可动态修改
  • 功能性参数
    • binlog_row_image :5.6.2后出现
      • 默认是full,binlog写入全部列
      • minimal #binlog写入改变的列,不改变的列不写入
      • noblob #写入全部的列,除了没更新的BLOB和TEXT这种大字段的列
    • Binlog_rows_query_log_events :5.6.2后出现
      • 开启后会将用户原始输入的SQL记录到binlog中,方便审计,按需开启
    • 早期参数名Binlog_impossiable_mode,5.7以后为binlog_error_action
      • 当不能写binlog时,当值为ABORT_SERVER可以报错
      • 默认没开启:IGNORE_ERROR,可以改成ABORT_SERVER

陈华军,苏宁云商IT总部资深技术经理,从事数据库服务相关的开发和维护工作,之前曾长期从事富士通关系数据库的开发,PostgreSQL中国用户会核心成员,熟悉PostgreSQL和MySQL。

enforce-gtid-consistency = ON    --开启gtid这个必须打开

特殊说明

  • log_warnings
    • 如果这个值设置的大于1,会在错误日志里看到异常断开的连(Aborted connection)或是被拒绝的请求
  • Crash Recovery
    • 基于MySQL5.6GTID的Crash Recovery是MySQL5.6.21支持,5.6.23开始稳定
      • 传统复制也可以支持,需要修改两个参数
        • master_info_repository改成table
        • relay_log_info_repository改成table
      • 这个特性的原理是当从库crash后,重新启动起来不在将自己的relay-log执行完再去主库拉取日志,是查看relay-master-log-file和exec-master-log-pos这两个值,直接去主库拉取这个值后面的日志载执行复制
  • MySQL5.6.5和MySQL5.7.5以后binlog变成了v2格式,因为引进了GTID和binlog_checksum
    • 如果有需求从5.5升级到5.6或者5.7是可以升级的,但要回滚版本就会出问题了,需要将两个参数设置
      • binlog_checksum = none
      • log_bin_use_v1_row_events = on #binlog用v1版本的
      • 如果之后升到5.6之后,最好还是改回去

 

slave_parallel_workers=4           --开启基于库的多线程复制默认0不开启  5.6只能支持一个数据库开启一个线程,5.7可以一个数据库开启多个线程

master其他参数

  • auto_increment_increment
    • 每次自增的步长
  • auto_increment_offset
    • 自增起始偏移量
  • binlog-do-db(特别声明:所有的过滤条件规则不建议在主库设置)
    • binlog记录db,除此外,其他db上的binlog都不记录,存在风险
    • binlog-do-db=hetan情况下
    • use mysql;update hetan.t1 set user='user2' where id=1;这个记录会记录到binlog中吗?
      • SBR模式下,这个SQL不会记录到binlog,这会导致主从不一致,而在RBR模式下则会被记录
  • binlog-ignore-db
    • 和上面的规则相反
    • binlog-ignore-db=hetan
    • use hetan;update mydb.t1 set user='user2' where id=1;
      • SBR:不记录,RBR记录
  • max_binlog_cache_size
    • binlog最大的cache size,有大SQL写入时需要用到,或者大数据load data时
  • binlog_stmt_cache_size
    • 用于cache那些事物中非事物表产生的SQL语句,如果经常在事物中涉及到非事物表,需要加大该参数
  • max_binlog_stmt_cache_size
    • 用于cache那些事物中非事物表产生的SQL语句,最大cache
  • binlog_direct_non_transaction_updates
    • 事物中既有事物表,也有非事物表,发生数据变更时,非事物表会直接写binlog,而不是也和事物表一样缓存在buffer中
    • 这个参数只影响SBR模式,RBR,MBR都不受影响
  • 针对gtid的参数
    • gtid_mode
      • 用于控制是否启用GTID
      • 如果设置成on,则必须是log-bin,log-slave-updates,enforce_gtid_consistency也要启动
    • enforce_gtid_consistency
      • 如果开启GTID必须要把这个选项打开
      • 用于控制只有事物安全的语句才能被记录到log中
    • gtid_next
      • 用来指定下一个要执行的事物,设置这个变量需要有super权限
    • gtid_purged
      • 用于指定那个事物从binlog忽略了,reset master这个值会被清空
    • gtid_executed
      • 执行到的GTID号及所有执行过得gtid号,和Executed_Gtid_set输出的这列意思是一样的
      • Reset master这个值会被清空

 

binlog_cache_size = 8M 

slave上的参数

  • server-id
    • 同上
  • server-uuid
    • 同上
  • relay-log
    • io thread读取过来存储到本地的日志
  • relay-log-index
    • relaylog索引文件前缀名,和relay-log一样,也可以是fullpath
    • 不可动态修改
  • read-only
    • 设置从库为只读,但对super权限的用户不起作用
    • 可动态修改
  • log_slave_updates
    • 将master传输过来的变更操作,再次记录成本地的binlog,用于做二次复制,当做中继分发点

[MySQL 5.6] GTID实现、运维变化及存在的bug

max_binlog_size = 50M 

slave其他参数

  • log-slow-slave-statements
    • slave复制产生的slow log,是否要记录的慢日志
  • max_relay_log_size
    • 限制relay log的大小,一般128M就可以
  • master-info-file/relay-log-info-file
    • 记录relay log的最新信息,master.info
  • sync_master_info=n
    • 多少个事物/事件后调用fdatasync()刷新master.info文件
  • relay_log_purge
    • relay应用完后,是否自动立即删除
  • relay_log_recovery
    • slave崩溃或正常重启时,未应用完的relay log会被删掉,重新从master请求binlog,再次生成relay log
  • replicate-same-server-id
    • slave复制是否应用和自己相同server-id的binlog,和log-slave-updates选项互斥
  • skip-slave-start
    • mysqld实例启动时,不跟着启动slave线程
  • slave_load_tmpdir
    • slave复制中遇到load data file 时使用的tmpdir
  • slave_transaction_retries
    • slave复制中,如果因为innodb死锁或者锁超时导致复制线程执行非事物失败重试次数,一般保持默认就可以
  • slave_parallel_workers
    • 库并发控制
    • 默认是关闭的
  • relay_log_space_limit
    • 防止relay_log过多将磁盘占满,可以设置这个参数,一般没有问题就不需要设置
  • 以下三个参数和早期版本的show slave hosts有关,5.7版本不设置也可以查看,建议不设置
    • report_password #建议不配置
    • report_port #建议不配置
    • report_user #建议不配置

max_binlog_cache_size = 100M 

slave过滤参数

  • 可以在线修改
    • change replication filter语句
  • replicate-do-table
    • 只复制指定的表
  • replicate-ignore-table
    • 忽略指定的表
  • replicate-rewrite-db
    • 如果master和slave有个库同名了,那么可以采用rewrite规则,将这个库上的复制重写到另一个新的库名上
  • replicate-wild-do-table
    • 以通配符形式指定复制哪些table
  • replicate-wild-ignore-table
    • 以通配符的形式指定不复制哪些table
  • replicate-do-db
    • 只复制指定的库
  • replicate-ignore-db
    • 忽略指定的库
  • slave_net_timeout
    • slave连接master超时时长
    • 建议调成20-30秒
  • slave_skip_errors
    • slave复制中,忽略某些错误
    • 常见的有:
      • 1032
      • 1053
      • 1062
      • 1050
      • 1051
      • 1054
      • 1146
      • all #全部错误
  • sql_slave_skip_counters
    • 忽略多少个复制事件,遇到个别错误(主键冲突,记录不存在等)时,可以忽略这些事件,继续复制进程

 

sync_binlog = 1 

为了使用crash-safe replication功能,必须使用:

  • relay_log_info_repository=table
  • relay-log-recovery=1
  • 使用支持事物的引擎
  • 在slave故障挂掉起来后,在设置了relay-log-recovery=1时,会清理掉已经存在的relay-log,从relay_log_info_repository里读到的同步位置,重新请求一份relay-log,实际上是做了一次:把Read_Master_Log_Pos设置为Executed_Master_Log_Pos,Master_Log_file设置成Relay_Master_log_file

适当减小binlog文件的大小
如果开启GTID,理论上最好调小每个binlog文件的最大值,以缩小扫描文件的时间。

expire_logs_days = 1 

 

log-slave-updates=true              --开启gtid这个必须打开

GTID(Global Transaction ID)是MySQL5.6引入的功能,可以在集群全局范围标识事务,用于取代过去通过binlog文件偏移量定位复制位置的传统方式。借助GTID,在发生主备切换的情况下,MySQL的其它Slave可以自动在新主上找到正确的复制位置,这大大简化了复杂复制拓扑下集群的维护,也减少了人为设置复制位置发生误操作的风险。另外,基于GTID的复制可以忽略已经执行过的事务,减少了数据发生不一致的风险。

 

GTID虽好,要想运用自如还需充分了解其原理与特性,特别要注意与传统的基于binlog文件偏移量复制方式不一样的地方。本文概述了关于GTID的几个常见问题,希望能对理解和使用基于GTID的复制有所帮助。

slave:

GTID长什么样

binlog_format = row

根据官方文档定义,GTID由source_id加transaction_id构成。
GTID = source_id:transaction_id

gtid_mode = ON

上面的source_id指示发起事务的MySQL实例,值为该实例的server_uuidserver_uuid由MySQL在第一次启动时自动生成并被持久化到auto.cnf文件里,transaction_id是MySQL实例上执行的事务序号,从1开始递增。 例如:
e6954592-8dba-11e6-af0e-fa163e1cf111:1

enforce-gtid-consistency = ON

一组连续的事务可以用'-'连接的事务序号范围表示。例如
e6954592-8dba-11e6-af0e-fa163e1cf111:1-5

binlog_cache_size = 8M

更一般的情况是GTID的集合。GTID集合可以包含来自多个source_id的事务,它们之间用逗号分隔;如果来自同一source_id的事务序号有多个范围区间,各组范围之间用冒号分隔,例如:
e6954592-8dba-11e6-af0e-fa163e1cf111:1-5:11-18,
e6954592-8dba-11e6-af0e-fa163e1cf3f2:1-27

max_binlog_size = 50M

即,GTID集合拥有如下的形式定义:
gtid_set:
    uuid_set [, uuid_set] ...
    | ''

max_binlog_cache_size = 100M

uuid_set:
    uuid:interval[:interval]...

sync_binlog = 1

uuid:
    hhhhhhhh-hhhh-hhhh-hhhh-hhhhhhhhhhhh

expire_logs_days = 1

h:
    [0-9|A-F]

slave_parallel_workers=4

interval:
    n[-n]

max_relay_log_size = 50M

    (n >= 1)

relay_log_purge = 1

如何查看GTID

relay_log_recovery = 1  

可以通过MySQL的几个变量查看相关的GTID信息。

master_verify_checksum = 1        --主事件校验

gtid_executed
在当前实例上执行过的GTID集合; 实际上包含了所有记录到binlog中的事务。所以,设置set sql_log_bin=0后执行的事务不会生成binlog 事件,也不会被记录到gtid_executed中。执行RESET MASTER可以将该变量置空(搭主从时候,导入数据到从库不能导入就执行reset master)。
gtid_purged
binlog不可能永远驻留在服务器上,需要定期进行清理(通过expire_logs_days可以控制定期清理间隔),否则迟早它会把磁盘用尽。gtid_purged用于记录已经被清除了的binlog事务集合,它是gtid_executed的子集。只有gtid_executed为空时才能手动设置该变量,此时会同时更新gtid_executed为和gtid_purged相同的值。gtid_executed为空意味着要么之前没有启动过基于GTID的复制,要么执行过RESET MASTER。执行RESET MASTER时同样也会把gtid_purged置空,即始终保持gtid_purged是gtid_executed的子集。

slave_sql_verify_checksum = 1     --从事件校验

gtid_next
会话级变量,指示如何产生下一个GTID。可能的取值如下:
1)AUTOMATIC:
自动生成下一个GTID,实现上是分配一个当前实例上尚未执行过的序号最小的GTID
2)ANONYMOUS:
设置后执行事务不会产生GTID。
3)显式指定的GTID:
可以指定任意形式合法的GTID值,但不能是当前gtid_executed中的已经包含的GTID,否则,下次执行事务时会报错。

slave_allow_batching = 1

这些变量可以通过show命令查看,比如:

log-slave-updates=true

show variables like '%gtid%';
+---------------------------------+-----------+
| Variable_name | Value |
+---------------------------------+-----------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed | |
| gtid_mode | ON |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
| simplified_binlog_gtid_recovery | ON |
+---------------------------------+-----------+

 

如何产生GTID

2.重启数据库

GTID的生成受gtid_next控制。 在Master上,gtid_next是默认的AUTOMATIC,即在每次事务提交时自动生成新的GTID。它从当前已执行的GTID集合(即gtid_executed)中,找一个大于0的未使用的最小值作为下个事务GTID。同时在binlog的实际的更新事务事件前面插入一条set gtid_next事件。

mysqladmin -uroot -hloalhost -p shutdown

以下是一条insert语句生成的binlog记录:
金沙棋牌app手机下载 1

/usr/local/mysql/bin/mysqld_safe&

在Slave上回放主库的binlog时,先执行set gtid_next ...,然后再执行真正的insert语句,确保在主和备上这条insert对应于相同的GTID。

 

一般情况下,GTID集合是连续的,但使用多线程复制(MTS)以及通过gtid_next进行人工干预时会导致gtid空洞。比如下面这样:
金沙棋牌app手机下载 2

3.在slave端change

继续执行事务,MySQL会分配一个最小的未使用GTID,也就是从出现空洞的地方分配GTID,最终会把空洞填上。

change master to master_host='192.168.10.100',

金沙棋牌app手机下载 3

master_port=3306,

这意味着严格来说我们即不能假设GTID集合是连续的,也不能假定GTID序号大的事务在GTID序号小的事务之后执行,事务的顺序应由事务记录在binlog中的先后顺序决定。

master_user='repl',

GTID的持久化

master_password='123456',

GTID相关的信息存储在binlog文件中,为此MySQL5.6新增了下面2个binlog事件。

master_auto_position=1;

Previous_gtids_log_event 在每个binlog文件的开头部分,记录在该binlog文件之前已执行的GTID集合。
Gtid_log_event 即前面看到的set gtid_next ...,它出现在每个事务的前面,表明下一个事务的gtid。

4.查看

示例如下:
金沙棋牌app手机下载 4

show slave status G

MySQL服务器启动时,通过读binlog文件,初始化gtid_executed和gtid_purged,使它们的值能和上次MySQL运行时一致。

 

gtid_executed被设置为最新的binlog文件中Previous_gtids_log_event和所有Gtid_log_event的并集。
gtid_purged为最老的binlog文件中Previous_gtids_log_event。

           Retrieved_Gtid_Set: 43ea2a1b-5fcc-11e8-8e35-000c29d02a8e:4

由于这两个重要的变量值记录在binlog中,所以开启gtid_mode时必须同时在主库上开启log_bin在备库上开启log_slave_updates。

            Executed_Gtid_Set: 43ea2a1b-5fcc-11e8-8e35-000c29d02a8e:1-4

但是,在MySQL5.7中没有这个限制。MySQL5.7中,新增加一个系统表mysql.gtid_executed用于持久化已执行的GTID集合。当主库上没有开启log_bin或在备库上没有开启log_slave_updates时,mysql.gtid_executed会跟用户事务一起每次更新。否则只在binlog日志发生rotation时更新mysql.gtid_executed。

如何配置基于GTID的复制

MySQL服务器的my.cnf配置文件中增加GTID相关的参数
log_bin                                 = /mysql/binlog/mysql_bin
log_slave_updates                = true
gtid_mode                            = ON
enforce_gtid_consistency      = true
relay_log_info_repository       = TABLE
relay_log_recovery                = ON

然后在Slave上指定MASTER_AUTO_POSITION = 1执行CHANGE MASTER TO即可。比如:
CHANGE MASTER TO MASTER_HOST='node1',MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_AUTO_POSITION=1;

基于GTID的复制如何工作

在MASTER_AUTO_POSITION = 1的情况下 ,MySQL会使用 COM_BINLOG_DUMP_GTID 协议进行复制。过程如下:

备库发起复制连接时,将自己的已接受和已执行的gtids的并集(后面称为slave_gtid_executed)发送给主库。即下面的集合:
UNION(@@global.gtid_executed, Retrieved_gtid_set - last_received_GTID)

主库将自己的gtid_executed与slave_gtid_executed的差集的binlog发送给Slave。主库的binlog dump过程如下:

1、检查slave_gtid_executed是否是主库gtid_executed的子集,如否那么主备数据可能不一致,报错。
2、检查主库的purged_executed是否是slave_gtid_executed的子集,如否代表缺失备库需要的binlog,报错
3、从最后一个Binlog开始扫描,获取文件头部的PREVIOUS_GTIDS_LOG_EVENT,如果它是slave_gtid_executed的子集,则这是需要发送给Slave的第一个binlog文件,否则继续向前扫描。
4、从第3步找到的binlog文件的开头读取binlog记录,判断binlog记录是否已被包含在slave_gtid_executed中,如果已包含跳过不发送。

从上面的过程可知,在指定MASTER_AUTO_POSITION = 1时,Master发送哪些binlog记录给Slave,取决于Slave的gtid_executed和Retrieved_Gtid_Set以及Master的gtid_executed,但是和relay_log_info以及master_log_info中保存的复制位点没有关系。

如何修复复制错误

在基于GTID的复制拓扑中,要想修复Slave的SQL线程错误,过去的SQL_SLAVE_SKIP_COUNTER方式不再适用。需要通过设置gtid_next或gtid_purged完成,当然前提是已经确保主从数据一致,仅仅需要跳过复制错误让复制继续下去。比如下面的场景:

在从库上创建表tb1
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> create table tb1(id int primary key,c1 int);
Query OK, 0 rows affected (1.06 sec)

mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

在主库上创建表tb1:
mysql> create table tb1(id int primary key,c1 int);
Query OK, 0 rows affected (1.06 sec)

由于从库上这个表已经存在,从库的复制SQL线程出错停止。

上面的输出可以知道,从库已经执行过的事务是'e10c75be-5c1b-11e6-ab7c-000c296078ae:1-5',执行出错的事务是'e10c75be-5c1b-11e6-ab7c-000c296078ae:6',当前主备的数据其实是一致的,可以通过设置gtid_next跳过这个出错的事务。

在从库上执行以下SQL:
mysql> set gtid_next='e10c75be-5c1b-11e6-ab7c-000c296078ae:6';
Query OK, 0 rows affected (0.00 sec)

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

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

mysql> set gtid_next='AUTOMATIC';
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

设置gtid_next的方法一次只能跳过一个事务,要批量的跳过事务可以通过设置gtid_purged完成。假设下面的场景:

主库上已执行的事务
金沙棋牌app手机下载 5

从库上已执行的事务
金沙棋牌app手机下载 6

假设经过修复从库已经和主库的数据一致了,但由于复制错误Slave的SQL线程依然处于停止状态。现在可以通过把从库的gtid_purged设置为和主库的gtid_executed一样跳过不一致的GTID使复制继续下去,步骤如下。

在从库上执行

金沙棋牌app手机下载 7

此时从库的Executed_Gtid_Set已经包含了主库上'1-10'的事务,再开启复制会从后面的事务开始执行,就不会出错了。
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

使用gtid_next和gtid_purged修复复制错误的前提是,跳过那些事务后仍可以确保主备数据一致。如果做不到,就要考虑pt-table-sync或者拉备份的方式了。

GTID与备份恢复

在做备份恢复的时候,有时需要恢复出来的MySQL实例可以作为Slave连上原来的主库继续复制,这就要求从备份恢复出来的MySQL实例拥有和数据一致的gtid_executed值。这也是通过设置gtid_purged实现的,下面看下mysqldump做备份的例子。

1、通过mysqldump进行备份

通过mysqldump做一个全量备份:
[root@node1 ~]# mysqldump --all-databases --single-transaction --routines --events --host=127.0.0.1 --port=3306 --user=root > dump.sql

生成的dump.sql文件里包含了设置gtid_purged的语句
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;
...
SET @@GLOBAL.GTID_PURGED='e10c75be-5c1b-11e6-ab7c-000c296078ae:1-10';
...
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;

恢复数据前需要先通过reset master清空gtid_executed变量
[root@node2 ~]# mysql -h127.1 -e 'reset master'
[root@node2 ~]# mysql -h127.1 <dump.sql

否则执行设置GTID_PURGED的SQL时会报下面的错误
ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

此时恢复出的MySQL实例的GTID_EXECUTED和备份时点一致:

show master status看一下从库的GTID_EXECUTED
金沙棋牌app手机下载 8

由于恢复出的MySQL实例已经被设置了正确的GTID_EXECUTED,以master_auto_postion = 1的方式CHANGE MASTER到原来的主节点即可开始复制。
CHANGE MASTER TO MASTER_HOST='node1', MASTER_USER='repl', MASTER_PASSWORD='repl', MASTER_AUTO_POSITION = 1

如果不希望备份文件中生成设置GTID_PURGED的SQL,可以给mysqldump传入--set-gtid-purged=OFF关闭。

2、通过Xtrabackup进行备份

相比mysqldump,Xtrabackup是效率更高并且被广泛使用的备份方式。使用Xtrabackup进行备份的举例如下。

通过Xtrabackup创一个全量备份(可以在Slave上创建备份,以避免对主库的性能冲击
innobackupex --defaults-file=/etc/my.cnf --host=127.1 --user=root --password=mysql --no-timestamp --safe-slave-backup --slave-info /mysql/bak

应用日志
innobackupex --apply-log /mysql/bak

查看备份目录中的xtrabackup_binlog_info文件可以找到备份时已经执行过的gtids
[root@node2 ~]# cat /mysql/bak/xtrabackup_binlog_info
mysql_bin.000001    191    e10c75be-5c1b-11e6-ab7c-000c296078ae:1-10

由于备份时添加了”--slave-info”选项并且从Slave节点拉取的备份,所以会生成xtrabackup_slave_info文件,也可以从这个文件里查找建立复制的SQL语句。
[root@node2 ~]# cat /mysql/bak/xtrabackup_slave_info
SET GLOBAL gtid_purged='e10c75be-5c1b-11e6-ab7c-000c296078ae:1-10';
CHANGE MASTER TO MASTER_AUTO_POSITION=1

将备份文件传送到新的节点node3的/mysql/bak目录并恢复(如果直接把备份传输到数据目录了,这一步可以省略  备份文件可以直接mv)
[root@node3 ~]# innobackupex --defaults-file=/etc/my.cnf --copy-back /mysql/bak

启动MySQL。
[root@node3 ~]# mysqld --defaults-file=/home/mysql/etc/my.cnf --skip-slave-start &

如果是从Slave拉的备份,一定不能直接开启Slave复制,这时的gtid_executed是错误的。需要手动设置gtid_purged后再start slave
MASTER_HOST='node1',MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_AUTO_POSITION=1;
start slave;

GTID与MHA

MHA是被广泛使用MySQL HA组件,MHA 0.56以后支持基于GTID的复制。 MHA在failover时会自动判断是否是GTID based failover,需要满足下面3个条件即为GTID based failover

所有节点gtid_mode=1
所有节点Executed_Gtid_Set不为空
至少一个节点Auto_Position=1

和之前的基于binlog文件位置的复制相比,基于GTID复制下,MHA在故障切换时的变化主要如下:

基于binlog文件位置的复制
在Master宕机后会尝试从Master上拷贝binlog日志进行补偿   
如果候选Master不拥有最新的relay log,会从拥有最新relay log的Slave上生成差异的binlog传送到候选Master并实施补偿  
新Master的日志补偿完成后,同样采用应用差异binlog的方式将其它Slave和新Master同步后再change master到新Master 

基于GTID的复制  
如果候选Master不拥有最新的relay log,让候选Master连上拥有最新relay log的Salve进行补偿。  
尝试从binlog server上拉取缺失的binlog并应用
新Master的数据同步到最新后,让其它的Slave连上新Master并等待数据完成同步。并且可以给masterha_master_switch传入--wait_until_gtid_in_sync=1参数使其不等其它Slave完成数据同步,以加快切换速度。

GTID模式下MHA不会尝试从旧Master上拷贝binlog日志进行补偿,所以在MySQL进程crash而OS仍然健康的情况下,应尽量不要做主备切换而是原地重启MySQL,除非有其它能确保切换后不丢数据的措施。

在GTID模式下MHA支持在复制拓扑中增加一个或多个binlog server起到日志补偿的作用,非GTID模式下即使配置了binlog server也会被MHA忽略。

日志补偿可以说是MHA中最复杂也最精华的部分,有了GTID后故障切换变得更简单了,不再需要原本复杂的binlog日志解析和补偿。所以Oracle官方推出了只支持GTID复制的切换工具mysqlfailover,在GTID的帮助下,我们有更多靠谱的HA工具可以选择。

GTID与crash safe salve

crash safe slave是MySQL 5.6提供的功能,意思是说在slave crash后,把slave重新拉起来可以继续从Master进行复制,不会出现复制错误也不会出现数据不一致。

1、基于binlog文件位置的复制
在基于binlog文件位置的复制下,要保证crash safe slave,配置下面的参数即可。
relay_log_info_repository      = TABLE
relay_log_recovery               = ON

这样可行的原因是,**relay_log_info_repository = TABLE时,apply

event和更新relay_log_info表的操作被包含在同一个事务里(如果用relay_log_info文件就不能包含在同一个事务里),innodb要么让它们同时生效,要么同时不生效,保证位点信息和已经应用的事务精确匹配。同时relay_log_recovery

ON时,会抛弃master_log_info中记录的复制位点,根据relay_log_info的执行位置重新从Master获取binlog,这就回避了由于relaylog文件未同步刷盘导致的binlog文件接受位置和实际relaylog文件不一致以及relay log文件被截断删除的问题。**

在同时使用MTS(multi-threaded slave)时,为保证crash safe slave基于binlog文件位置的复制还需要设置sync_relay_log=1,因为MySQL在Crash恢复时必须先通过读取relay log补齐MTS导致的事务空洞。

2、基于GTID的复制
上面的设置并不适用于基于GTID的复制。在基于GTID的复制下,crash的Slave重启后,从binlog中解析的gtid_executed决定了要apply哪些binlog记录,所以binlog必须和innodb存储引擎的数据保持一致。要做到这一点,需要把sync_binlog和innodb_flush_log_at_trx_commit都设置为1,即所谓的"双1"。

slave_relay_log_info表没有Executed_Gtid_Set,只有pos点

select * from slave_relay_log_infoG;
*************************** 1. row ***************************
Number_of_lines: 7
Relay_log_name: /data/mysql/mysql3306/logs/mysql-relay.000001
Relay_log_pos: 4
Master_log_name:
Master_log_pos: 0
Sql_delay: 0
Number_of_workers: 0
Id: 1

 

另外MySQL启动时,会从relay log文件中获取已接收的GTIDs并更新Retrieved_Gtid_Set。由于relay log文件可能不完整,所以需要抛弃已接收的relay log文件。因此relay_log_recovery = ON也是必须的。

这样,对于基于GTID的复制,保证crash safe slave的设置就是下面这样。

sync_binlog                               = 1
innodb_flush_log_at_trx_commit  = 1
relay_log_recovery                     = ON

关于如何设置以确保crash safe slave,官方文档有明确记载,见 17.3.2 Handling an Unexpected Halt of a Replication Slave。

但是其中关于GTID的记载中存在笔误,将relay_log_recovery=1写成了relay_log_recovery=0 (#83711)。同时也没有提到必须设置"双1",但是"双1"是必要的,否则crash的Slave重启后,可能会重复应用binlog event也可能会遗漏应用binlog event(#70659)。其中遗漏应用binlog event的情况更可怕,因为Slave在不触发SQL错误的情况下就默默的和Master不一致了。

3、设置"双1"对性能的影响

出于安全考虑,强烈推荐设置"双1"。"双1"会增大每个事务的RT,但得益于MySQL的组提交机制,高并发下"双1"对系统整体tps的影响在可接受范围内。

sysbench oltp.lua 10张表每张表100w记录(qps/并发数)

对更新同一行这样无法有效并行的场景,"双1"对性能的影响非常大。

sysbench update_non_index.lua 1张表1条记录(qps/并发数)

对不能有效并行的Slave replay,存在同样的问题。

通过指定tx-rate执行sysbench的update_non_index.lua脚本压测30秒,完成后检查主备延迟。

可以发现在Slave被配置为"双1"的情况下,延迟非常严重,1000以上的QPS就会出现延迟,非"双1"下QPS到5000以上才会出现延迟(主库配置为"双1")。

sysbench update_non_index.lua 1张表100w条记录 128并发(延迟/qps)

以上测试环境是Percona Server 5.6运行在配置HDD的8 core虚机,由于测试结果和系统IO能力有很大关系,仅供参考。

4、如何在非"双1"下保证crash safe slave

如果是MySQL 5.7可以关闭log_slave_updates,这样MySQL会将已执行的GTIDs实时记录到系统表mysql.gtid_executed中,mysql.gtid_executed是和用户事务一起提交的,因此可以保证和实际的数据一致。
log_slave_updates              = OFF
relay_log_recovery             = ON

如果是MySQL 5.6可以采用如下变通的方式。

按照基于binlog文件复制时crash safe slave的要求设置relay_log_info_repository = TABLE
relay_log_info_repository      = TABLE
relay_log_recovery               = ON

在Slave crash后,根据relay_log_info_repository设置相应的gitd_purged再开启复制,步骤如下。

1.启动MySQL,但不开启复制
 mysqld --skip-slave-start

2.在Slave上修改为基于binlog文件位置的复制
change master to MASTER_AUTO_POSITION = 0

3.启动slave IO线程
start slave io_thread
这里不能启动SQL线程,如果接受到的GTID已经在Slave的gtid_executed里了,会被Slave skip掉。

4.检查binlog传输的开始位置(即Retrieved_Gtid_Set的值)
show slave statusG
假设输出的Retrieved_Gtid_Set值为e10c75be-5c1b-11e6-ab7c-000c296078ae:7-10

5.在Master上检查gtid_executed
show master status
假设输出的Executed_Gtid_Set值为e10c75be-5c1b-11e6-ab7c-000c296078ae:1-10

6.在Slave上设置gitd_purged为binlog传输位置的前面的GTID的集合
reset master;
set global gitd_purged='e10c75be-5c1b-11e6-ab7c-000c296078ae:1-6';  

7.修改回auto position的复制
change master to MASTER_AUTO_POSITION = 1

8.启动slave SQL线程
 start slave sql_thread

但是,这种变通的方法不适合多线程复制。因为多线程复制可能产生gtid gap和Gap-free low-watermark position,这会导致Salve上重复apply已经apply过的event。后果就是数据不一致或者复制中断,除非设置binlog格式为row模式并且slave_exec_mode=IDEMPOTENT,slave_exec_mode=IDEMPOTENT(幂等(idempotent、idempotence))允许Slave回放binlog时忽略重复键和找不到键的错误,使得binlog回放具有幂等性,但这也意味着如果真的出现了主备数据不一致也会被它忽略。

5、MTS下特有的问题

在同时使用MTS(slave_parallel_workers > 1)时,即使按上面crash safe slave的要求设置了基于GTID的复制,Slave crash后再重启还是会导致复制中断。

通过强制杀掉MySQL所在虚机的方式模拟Slave宕机,然后再启动MySQL,MySQL日志中有如下错误消息:

启动slave时也会报错
mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

出现这种现象的原因在于,relay_log_recovery=1 且 slave_parallel_workers>1的情况下,mysql启动时会进入MTS Group恢复流程,即读取relay log,尝试填补由于多线程复制导致的gap。然后relay log文件由于不是实时刷新的,在relay log文件中找不到gap对应的relay log记录(覆盖了gap的relay log起始和结束位置分别被称为低水位和高水位,低水位点即slave_relay_log_info.Relay_log_pos的值)就会报这个错。

实际上,在GTID模式下,slave在apply event的时候可以跳过重复事件,所以可以安全的从低水位点应用日志,没必要解析relay log文件。 这看上去是一个bug,于是提交了一个bug报告#83713,目前还没有收到回复。

作为回避方法,可以通过清除relay log文件,跳过这个错误。执行步骤如下:
reset slave;
change master to MASTER_AUTO_POSITION = 1
start slave;

在这里,单纯的调reset slave不能把状态清理干净,内部的Relay_log_info.inited标志位仍然处于未被初始化状态,此时调用start slave仍然会失败。因此需要补一刀change master。

6、Master的crash safe

前面一直在讲crash safe slave,Master的crash safe同样重要。 要想Master保持crash safe需要按下面的参数进行设置,否则不仅会丢失事务,gtid_executed还可能和实际的innodb存储引擎中的数据不一致。
sync_binlog                              = 1
innodb_flush_log_at_trx_commit = 1

在Master配置为"双1"的情况下,Master crash后,如果没有发生failover,可以继续作为Master。 如果发生了failover,可以检查旧Master和新Master上由旧Master执行的事务集合是否一致。
show master status

如果一致,可以按MASTER_AUTO_POSITION = 1的方式将旧Master作为Slave和新Master建立复制关系。否则,考虑做事务补偿或从新Master上拉取备份进行恢复。

在Master配置不是"双1"的情况下,在Master crash后由于难以准确知道旧Master上究竟执行了哪些事务,安全的做法是实施主备切换,并从新Master上拉取备份,把旧Master作为新Master的Slave进行恢复。


mysql5.7并行复制(MTS:enhanced Multi-threaded slave)

5.7.2 支持单库增强型多线程slave(多个sql work线程),mariadb 10.0.5支持

原理
slave利用事务组提交的特性(To provide parallel execution of transactions in the same schema, MariaDB 10.0 and MySQL 5.7 take advantage of the binary log group commit optimization),在slave 多个sql worker线程进行并行回放
master依据group commit的并行性,在binary log进行标记,slave使用master提供的信息并行执行事务

 

注意事项
级联复制场景,其他slave将会出现并行性更小(并行度不一样),使用binlog server做替代方案
配置

mysql 5.7
slave_parallel_type=logical_clock 默认为database,使用db并行方式,logical_clock使用逻辑时钟的并行模式
slave_parallel_workers=16 设置worker线程数
binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 在master延时事务提交,增加group commit事务数

查看状态
show processlist 检查worker线程的状态


从这篇文章里知道
从库的gtid_executed从binlog里获取
从库的Retrieved_Gtid_Set从relaylog里获取

 

本文由金沙棋牌发布于金沙棋牌app手机下载,转载请注明出处:MySql开启GTID和多线程复制功能,7二进制多实例安

关键词: