MYSQL同步中参数

MySQL 5.6.22的参数,总共432个

change maseter to 

show global variables;

以下所有参数都是mysqld里的

复制中的一些重点参数(最基础的)

log-bin(binlog路径)
server-id
(服务标识)
log-bin-index
binlog_format(binlog格式)
binlog_cache_size
max_binlog_size
sync_binlog
expire_logs_days
log_bin_trust_function_creators

server-uuid  以后有可能代替server-id(标识)

gtid从库没有配置service id也是也是可以的 但是主库一定要配置

Master的参数:

============================================

server-id
  唯一区别ID,同一个集群内不可重复
  可动态修改

binlog_format
  binlog日志格式:statement、row、mixed三种
  可动态修改 建议修改session的不修改全局的 推荐用row

binlog_cache_size
  binlog写入buffer
  可动态修改

max_binlog_size
  限制单个binlog大小
  可动态修改 通常配置1M

max_binlog_size
  限制单个binlog大小
  可动态修改 一般建议设置成200M/500M 根据日志产生量 默认是1G

  5min左右刷一个或者10min左右刷一个

  binlog刷新过程数据是提交不了的 所以让binlog刷新慢一点 写数据不会被卡住

sync_binlog = n
  多少个SQL之后,调用fdatasync()函数刷新binlog到disk

  默认是0,在调优中很重要

  多少个sql刷一次binlog的cache,同步到磁盘

  比如说现在写成0就是由系统来刷新binlog cache

  系统觉得binlog大了就去刷新

  对一些交易型的,账单或者扣钱的东西 建议设置成1

  让没个sql都刷新一下binlog 让binlog更安全一点

  当然设置成1了 性能也是最差的,设置成0的时候性能是最好的

  这个是对性能影响很严重的参数

  tokodb不开binlog写入可以达到5,6w

  开启之后写入只能达到2W的写入

expire_logs_days =n
  n天后自动删除binlog
  可动态修改 默认指定为7

  如果不开启就会造成binlog把磁盘占满了

  应用卡住了 也不知道怎么回事

log_bin_trust_function_creators

  默认是0 要用的话改成1就行了

  允许在存储过程调用的时候用到,如果没有涉及存储过程是用不到

log_warnings

  参数级别 默认是2  默认是0

  error log看到Abort connection拒绝连接请求

  如果不想看到这些就把值设置成1

binlog_error_action 

binlog_error_action=ABORT_SERVER

当空间满了写不进去了 会在应用层直接报错 不会直接卡在那里

5.6之前只能再error log里看到

binlog_row_image=[full|minimal|noblog]

也是5.6的

日志格式为row的时候日志里会记录修改之前的和修改之后的记录所以日志很大

利用这个参数之后只保留写后的记录

update tb set c2=xxx where id=xxx;

[minimal]

update tb 
set 
@2=xxx,
where 
@1=xxx,
@2=xxx;

没有变更到的就不记录了

Binlog_rows_query_log_events=[1|0]

默认关闭,(日志格式是row 的时候)开启参数之后会生成query event

会记录用户原始sql

gtid_mode=on

开启的时候必须设置这些参数

log-bin ,

log-slave-updates(从库上也会记录binlog 可以用来备份或者机连备份) ,  

enforce-gtid-consistency(一致性,控制哪些语句能安全的记录到binlog里)

Executed_Gtid_set 执行过的gtid集合

set gtid_next="uuid:NO."; 指定下一个事物是哪个事物

set global gtid_purged='uuid:NO.';

进行同步的时候忽略这个事件之前的,告诉主库包括NO.之前的事物都不要给我了

reset master;

才能清空,清空之后才能设置

Gtid_executed

从库上到底执行过哪些gtid操作

uuid:1-100:300-10000:20001-NNNNN

发现有空洞

有可能做了互为主从造成了空洞

如果是传统的A->B的应该是不会有空洞的

uuid:1-NNNNN

或者是set global gtid_purged开始的 到 NNNNN

show master status\G  

show slave status\G

一样

这个参数也需要reset master

slave的参数

============================================

server-id 

推荐配置service-id可能从库以后要挂从库或者转为主库

relay-log

从本地拿到的binlog会存到relay-log里

relay-log-index

read-only 对非super 用户起作用

set global read-only = 0|1

slave其他参数

log-slow-slave-statements
log_slave_updates
max_relay_log_size
relay-log-info-file
relay_log_purge
relay_log_recovery 
replicate-same-server-id 
skip-slave-start
slave_load_tmpdir
slave_transaction_retries

slave_parallel_workers

log-slow-slave-statements

sql_thread 执行sql超过long_query_time 会记到慢日志中

默认没开启

max_relay_log_size

设置一下relay-log的大小

slave和master不会形成长连接 每次链接就会生成一个relay log

relay-log-info-file =relay.info

relay crash recover

[root@node21 data]# cat relay-log.info 
7
./mysql-relay-bin.000008
4
mybinlog.000005
191
0
0
1
1

执行到005 位置是191

从库忽然挂掉 又起来了同步接不上

要把这个参数打开之后会从binlog005 191这个位置向主库重新请求

在gtid里做了一个变相实现

5.6.21叫 simplified_binlog_gtid_recovery 

5.6.23改名了叫binlog_gtid_recovery_simpliefied

可以从gtid里拿到已经同步到哪个gtid了只要这个gtid之后的数据就行了

5.6.21只前gtid重启之后没有这个重新请求的过程中没有crash recover处理机制

是在5.6.21之后引入进来的

而且这个参数默认是没有开启的

这个功能很好 之前从库挂掉都要去修复

replicate-same-server-id

默认没开启 自己复制自己的server id基本上不用 

skip-slave-start

建议在配置文件里要有

slave启动之后不要默认开启同步

slave_load_tmpdir

指向到tmpdir下 一般不用设置

slave_transaction_retries

sql语句在执行中上层mysql如果发生堵住的情况把某个数据区间锁住了

sql thread就会等待锁的释放 机会有一个重试的过程

当重试超过次数之后 就会报错

start slave sql_thread;

5.6引入  并行复制 库级别的复制

slave_parallel_workers 最大1024

默认是关闭的

如果sql_thread断了但是io_thread还是yes

同步断掉之后本地的relay_log 会记录很多 造成磁盘被写满

所以引入了relay_log_space_limit 限定relay log占用磁盘大小

超过了之后io_thread  会等待relay-log释放空间

relay log执行完会被删掉

SHOW SLAVE HOSTS

slave上需要配置这3个参数

report_password
report_port
report_user

实际生产中很少去配 库都是动态切换的 配置上意义不大

sync_master_info
sync_relay_info

5.5之后 可以吧master info 和 relay info存到数据库里

5.6.17之前这2个参数如果配置成1或者过小会有内存溢出错误

保持默认不变就行了

sync_relay_log   =  sync_binlog

========================================

复制过滤规则

1. 库级别的

2. 表级别

支持: 精确和通配符?

正向逻辑和反向逻辑

分binlog 和sql_thread两个部分

一部分可以控制记录binlog的时候记录什么

另一部分可以在sql thread里面控制执行什么

master 部分

只记录某个db的binlog

replicate-do-db = "leokim"

create database jl;

就不会记录

use jl;

insert into leokim.tb(col1) values(1);

语句格式下是记录不上去的

在行格式是能让记录的

replication-ignore-db="mysql"忽略mysql库

想忽略某个表

replicate-ignore-table=leokim.quota

复制中改库

a_db -> b_db

在slave上才能配置

replicate-rewrite-db='a_db->b_db';

把某个库映射到另一个库上

从库上的

binlog

replicate-wild-do-table
replicate-wild-ignore-table

replicate-wild-do-table="leokim.ds_%";(不是这个前缀的就不计)

mysql同步复制里有个坑

slave_net_timeout

从库和主库多少时间断开才会说连不上主库 — 默认3600S

忽然闪断2min 数据不同步 io_thread 和 sql_thread 都是ok

推荐配置成10

slave_net_timeout=10

slave_skip_errors

slive中复制,忽略那些错误

常见的:

1062 主建冲突

1032 找不到记录

一般建议不允许忽略任何错误

除非从库来不及修又要用

就先skip掉晚上用工具修复

sql_slave_skip_counter
忽略多少个复制事件,遇到个别错误(主键冲突、记录不存在等)时,可以忽略这些事件,继续复制进程
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=n;
START SLAVE;
SHOW SLAVE STATUS\G
一般一次只忽略一个事件,除非很肯定,否则不要设置大于1

简单总结:

推荐gtid 管理方便不用找偏移量位置

 

server-id 推荐ip最后一位

传统配置:

#replication 
log-bin=/path/mysql_xxxx/logs/mysql-bin
server-id=[ip]port
log-bin-index=mysql-bin.index
binlog_format=row
binlog_cache_size=1M
max_binlog_size=200M
sync_binlog=0
expire_logs_days=7
log_bin_trust_function_creators=1
#master binlog filter

#slave
relay-log=relay-bin
relay-log-recovery=1

log-slow-slave-statements
log_slave_updates
slave_net_timeout=10
master-retry-count= 86400

MYSQL GTID复制实验

blob.png

blob.png

blob.png

blob.png

我在主库清除了所有binlog然后在leo表里删除了几条记录又重新插入了一些

blob.png

从库操作

change master to master_host='192.168.61.131',master_user='repl',master_password='repl4slave', master_auto_position=1;

blob.png

blob.png

blob.png

可以看到1-3的都copy过来了

示例情况

我在从库插入了一条记录 

在主也插入同样记录的时候,从库同步就会报错

这个时候处理方式是 把从库上的该条重复记录删掉

然后执行

start slave sql_thread;

就继续同步过来了

同样现在从库删除一条记录

然后再去主库删除同样的记录

尽然没有报错 哈哈

update也不会报错 

主库上没有id为2的记录 从库上有id为2的记录

主库上执行delete id 2 从库上的会被干掉

binlog要换成row格式

对于gtid必须要用row格式要么会有很多错误都不爆

上面那些delete update应该出错的就会出现 slave就会起不起来了

跳过错误

set gtid_next=b9c8ef95-27ea-11e7-9c7b-000c29a8f010:10

stop slave;

begin;commit;

blob.png

跳过错误

同步就继续了数据就同步过来了

blob.png

MYSQL传统复制实验

blob.png

blob.png

blob.png

初始化master和slave数据

删除所有数据

blob.png

创建从库账号

blob.pngblob.png

前面的都是初始化的log可以不要

从库也是相同配置

change master to master_host='192.168.61.131',master_user='repl', master_password='repl4slave', master_log_file='mybinlog.000001', master_log_pos=120;

blob.png

blob.png

warning就是不提倡在命令行里直接写密码 哈哈

blob.png

blob.png

这里有个报错 说是因为server id要用不一样的 我去修改一下

blob.png

还是有error

妈蛋···绑定master的mybinerlog写成mybilog了·····

后来试了一下还是有报错

blob.png

最后发现是定义master的时候把密码写错了

blob.png

成功了。

主库上创建leokim的库

blob.png

从库上也生成了leokim这个库

blob.png

主库上创建表

blob.png

从库上也能看到表和数据

blob.png

blob.png

blob.png

blob.png

blob.png