MySQL传统复制与GTID复制原理及操作详解

mysql复制在业界里有叫：mysql同步，ab复制等。专业名称就是叫：复制

复制是单向的，只能从master复制到slave上，延时基本上是毫秒级别的。

一组复制结构中可以有多个slave，对于master一般场景推荐只有一个。

master用户写入数据，生成event记到binary log中

slave接收master上传来的binlog，然后按顺序应用，重现master上的用户操作。

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

MySQL5.6增加了GTID复制

 

classic主从配置

核心配置

[mysqld]

log-bin

server-id

gtid_mode=off #禁掉gtid

 

grantreplication slave on *.* to 'repl'@'%' identified by 'repl4slave';

flushprivileges;

 

级别	优点	缺点
statement	binlog文件较小 日志是包含用户执行的原始SQL,方便统计和审计 出现最早，兼容较好	存在安全隐患，可能导致主从不一致 对一些系统函数不能准确复制或是不能复制
row	相比statement更加安全的复制格式 在某些情况下复制速度更快（SQL复杂，表有主键） 系统的特殊函数也可以复制 更少的锁 更新和删除语句检查是否有主键，如果有则直接执行，如果没有，看是否有二级索引，如再没有，则全表扫描	binlog比较大（myql5.6支持binlog_row_image） 单语句更新（删除）表的行数过多，会形成大量binlog 无法从binlog看见用户执行SQL（5.6中增加binlog_row_query_log_events记录用户的query）
mixed	混合使用row和statement格式，对于DDL记录statument,对于table里的行操作记录为row格式。 如果使用innodb表，事务级别使用了READ_COMMITTED or READ_UMCOMMITTED日志级别只能使用row格式。 但是使用ROW格式中DDL语句还是会记录成statement格式。	mixed模式中，那么在以下几种情况下自动将binlog模式由SBR模式改成RBR模式。 当DML语句更新一个NDB表 当函数中包含UUID时 2个及以上auto_increment字段的表被更新时 行任何insert delayed语句时 用UDF时 视图中必须要求使用RBR时，例如创建视图使用了UUID()函数

 

 

添加一个新的从库

获取主库上一个带binlog及pos偏移量的备份

在从库上恢复后

>changemaster to

master_host='192.168.199.117',

master_user='slave',

master_port=7000,

master_password='slavepass',

master_log_file='mysql-bin.000008',

master_log_pos=896;

 

>startslave;

>showslave status\G;

 

错误跳过：

stopslave;

set globalsql_slave_skip_counter=1;

startslave;

show slavestatus\G;

 

GTID复制配置

一个事务对应一个唯一ID

一个GTID在一个服务器上只会执行一次

GTID是用来替代以前classic的复制方法

mysql5.62支持，mysql5.6.10后完善

 

优点：

相对于行复制来讲数据安全性更高

故障切换更简单

GTID

[mysqld]

#GTID:

gtid_mode=on

enforce-gtid-consistency=on

#binlog

log-bin=mysql-bin

log-slave-updates=1

 

GTID添加从库有两种方法：

1.如果master所有的binlog还在，安装slave后，直接changemaster 到master

原理是直接获取master所有的gtid并执行

优点是简单

缺点是如果binlog太多，数据完全同步需要的时间较长，并且需要master一开始就启用了GTID

总结：适用于master也是新建不久的情况

2.通过master或者其它slave的备份搭建新的slave.

原理：获取master的数据和这些数据对应的GTID范围，然后通过在slave设置@@GLOBAL.GTID_PURGED从而跳过备份包含的GTID

优点是可以避免第一种方法中的不足

缺点操作相对复杂

总结：适用于拥有较大数据集的情况

 

GTID添加从库：

mysqldump

在备份的时候需要指定--master-data

导出的语句中包括：set @@GLOBAL.GTID_PURGED='c8d960f1-83ca-11e5-a8eb-000c29ea831c:1-745497';恢复时，需要先在slave上执行一个

reset master;再执行

changemaster to

 

perconaxtrabackup

xtrabackup_binlog_info包含了GTID在信息

做从库恢复后，需要手工设置：

set@@GLOBAL.GTID_PURGED='c8d960f1-83ca-11e5-a8eb-000c29ea831c:1-745497';

恢复后，执行change master to

 

>changemaster to

master_host='192.168.199.117',

master_user='slave',

master_port=7000,

master_password='slavepass',

master_auto_position=1;

 

错误跳过

stopslave;

setgtid_next='xxxxxxxx:N';

begin;

commit;

setgtid_next='automatic';

startslave;

 

 

 

rpel	change master	error
classic	change master to master_host='192.168.199.117', master_user='slave', master_port=7000, master_password='slavepass', master_log_file='mysql-bin.000008', master_log_pos=896;	stop slave; set global sql_slave_skip_counter=1; start slave; show slave status\G;
GTID	change master to master_host='192.168.199.117', master_user='slave', master_port=7000, master_password='slavepass', master_auto_position=1;	stop slave; set gtid_next='xxxxxxxx:N'; begin; commit; set gtid_next='automatic'; start slave;

 

GTID的限制：

不支持非事务引擎（从库报错，stopslave; start slave; 忽略）

不支持create table … select 语句复制（主库直接报错）

不允许在一个SQL同时更新一个事务引擎和非事务引擎的表

在一个复制组中，必须要求统一开启CTID或是关闭GTID

开启DTID需要重启（5.7中可能不需要）

开启DTID后，就不在使用原来的传统的复制方式

对于createtemporary table 和drop temporary table语句不支持

不支持sql_slave_skip_counter

 

MySQL复制默认是异步复制，Master将事件写入binlog，但并不知道Slave是否或何时已经接收且已处理。在异步复制的机制的情况下，如果Master宕机，事务在Master上已提交，但很可能这些事务没有传到任何的Slave上。假设有Master->Salve故障转移的机制，此时Slave也可能会丢失事务。

 

官方半同步复制的概念：

1.当Slave主机连接到Master时，能够查看其是否处于半同步复制的机制。

2.当Master上开启半同步复制的功能时，至少应该有一个Slave开启其功能。此时，一个线程在Master上提交事务将受到阻塞，直到得知一个已开启半同步复制功能的Slave已收到此事务的所有事件，或等待超时。

3.当一个事务的事件都已写入其relay-log中且已刷新到磁盘上，Slave才会告知已收到。

4.如果等待超时，也就是Master没被告知已收到，此时Master会自动转换为异步复制的机制。当至少一个半同步的Slave赶上了，Master与其Slave自动转换为半同步复制的机制。

5.半同步复制的功能要在Master，Slave都开启，半同步复制才会起作用；否则，只开启一边，它依然为异步复制。

 

同步（社区增强半同步），异步，半同步复制的比较：

同步复制：Master提交事务，直到事务在所有的Slave都已提交，此时才会返回客户端，事务执行完毕。缺点：完成一个事务可能会有很大的延迟。

异步复制：当Slave准备好才会向Master请求binlog。缺点：不能保证一些事件都能够被所有的Slave所接收。

半同步复制：半同步复制工作的机制处于同步和异步之间，Master的事务提交阻塞，只要一个Slave已收到该事务的事件且已记录。它不会等待所有的Slave都告知已收到，且它只是接收，并不用等其完全执行且提交。

 

半同步,开启后严重影响性能

解决主库不关心日志是否被从库读到

半同步配置，在master和slave上都配置

master

[mysqld]

rpl_semi_sync_master_enabled=1

rp;_semi_sync_master_timeout=1000#1s

 

slave

[mysqld]

rpl_semi_sync_slave_enabled=1

 

 

复制参数

master

slave

log-bin=/data/mysql/mysql3316/logs/mysql-bin server-id=[ip]port log-bin-index=mysql-bin.index binlog_format=row binlog_cache_size=1M max_binlog_size=1G sync_binlog=0 expire_logs_days=7 log_bin_trust_function_creators=1 #使用存储过程或函数打开   enforce-gtid-consistency=1 gtid-mode=on gtid-next gtid-purged gtid-execued 执行过的gtid                                     复制过滤 建议能不用就不用，如果必须用，在从库上操作，不要在主库上设置   ==================== binlog-do-db binlog-ignore-db

server-id log_slave_updates #会把复制的binlog也写到binlog中 relay_log_recover=1 #在slave崩溃或正常启动时，未应用完的relay_log会被删掉，重新从master请求binlog再次生成relay_log skip-slave-start slave_transaction_retries #slave复制中，如果因为innodb死锁或者锁超时导致复制线程执行的事务失败重试次数，一般为5 slave_parallel_workers #5.6引入基于库计并发复制的功能，默认是关闭的 read-only #打开只读，但对有super权限的用户无效 slave_net_timeout = 10#默认3600s slave_skip_errors   log-slow-slave-statements #默认复制慢语句不会记录到慢日志中，启动此功能，会把复制中超过long_query_time的语句记录到慢日志中 max_relay_log_size 设置relay_log的最大大小，建议不设置 relay-log-info-file #relay_log的索引文件 relay_log_purge 默认开启，在relay_log使用完毕后，尽可能的清理掉 replicate-same-server-id 对于相同的server-id的binlog event默认不执行 slave_load_tmpdir 对于复制load data in file语句，slave指定创建临时文件的目录，默认是tmpdir relay_log_space_limit 限制relay_log战胜磁盘的总大小   =========================== #slave to filter replicate-do-db replicate-ignore-db repicate-rewrite-db 如果master和slave有个库同名了，可使用rewrite规则，将这个库上的复制重写到另一个新的库上 replicate-do-table replicate-ignore-table replicate-wild-do-table replicate-wild-ignore-table