MySQL复制

MySQL复制格式

1.基于语句复制的优势:
属于比较成熟的技术，得到广泛使用当SQL语句会修改很多数据时，使用语句复制会比较节省空间由于二进制文件中包含了所有的修改语句，所以可以用来做审计功能
2.基于语句复制的劣势: 某些特定的修改语句在基于语句复制的环境中复制会有问题，比如: 语句中包含自定义函数或者不确定性的存储过程 update/delete语句中包含Limit语句但不包含order by语句属于不确定性语句一些函数比如rand(), sysdate()，version()等由于不确定性也会导致复制异常每个导致复制异常的语句都会产生一个告警信息
[Warning] Statement is not safe to log in statement format.
3.基于行复制的优势:
所有的数据库修改都可以被复制，是一种安全的方式由于是行复制，所以某些语句在主库和从库上执行需要较少的lockd,当DML语句涉及到多行的修改时，则由于行复制会把每行的修改信息都记录下来，所以 bin log会很大，有可能会导致复制的延迟相比较语句复制要大不能直接查看在备库中执行的SQL语句建议仅使用InnoDB表做行复制，对MyISAM表的行复制有可能会导致复制异常;

基于语句复制的二进制文件内容:

log-bin=mysql-bin 
server_id=1 
binlog-format=statement

基于行复制的二进制文件内容:

log-bin=mysql-bin 
server_id=1 
binlog-format=row

执行命令:bin/mysqlbinlog -v data/mysql-bin.000003

假如主库的temp表数据展示如下:

mysql> select * from temp; 
+------+------+
| id | name |
+------+------+ 
| 2|eee|
| 3|eee|
| 4|eee|
| 5|eee|

备库的temp数据展示如下:

mysql> select * from temp; 
+------+------+
| id | name | 
+------+------+
| 3|eee|
| 2|eee|
| 4|eee|
| 5|eee|
+------+------+

主库执行delete from temp limit 1;

则主库删掉的数据是id=2的行，而备库删掉的是id=3的行。导致主备库数据不一致

在statement环境下实验主从一个表数据不一致的情况下复制是否还能继续:

 mysql> show variables like '%format%';
 +---------------------------+-------------------+
 | Variable_name | Value |
 +---------------------------+-------------------+
 | binlog_format | STATEMENT |

从库执行:

 mysql> delete from temp where id<10000;

主库执行:

 mysql> delete from temp where id=1;

查看从库的同步状态依然正常:

mysql> show slave statusG
*************************** 1. row ***************************
Slave_IO_Running: Yes Slave_SQL_Running: Yes
Last_Errno: 0
Last_Error: 
Seconds_Behind_Master: 0

在行复制环境下实验主从一个表数据不一致的情况下复制是否还能继续:

 mysql> show variables like '%format%';
 +---------------------------+-------------------+
 | Variable_name | Value |
  +---------------------------+-------------------+
 | binlog_format | ROW|

主库执行:

mysql> delete from temp where id=2;

查看从库的同步状态不正常:

mysql> show slave statusG
*************************** 1. row *************************** 
Slave_IO_Running: Yes
Slave_SQL_Running: No 
Replicate_Do_DB: test
Last_Errno: 1032
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The
most recent failure being: Worker 4 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000002, end_log_pos 386. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

如果手工在备库增加这条数据，则同步再次正常:

Insert into temp values(2,'mike');
mysql> show slave statusG
*************************** 1. row ***************************
Slave_IO_Running: Yes Slave_SQL_Running: Yes
Last_Errno: 0
Last_Error: Skip_Counter: 0
Seconds_Behind_Master: 0

MySQL复制涉及三个线程，其中一个在主库，另两个在从库binlog dump thread:在主库创建，用来在从库链接过来时发送bin log的内容slave io thread:在备库创建，用来连接主库并请求发送新的bin log内容。
该线程读取主库的bin log dump线程发送的更新内容并将此内容复制到本地的relay log中Slave sql thread:在备库创建，读取slave io线程在本地relay log中的内容并在本地执行内容中的事件

MySQL复制使用场景

MySQL复制可以作为数据库备份的一种解决方案，由于主库的数据会复制到备库，所以可以在备库执行数据库备份作业而不用影响主库的性能在备库的备份通常有两种选择: 当数据库比较小时，可以采用mysqldump的方式。由于mysqldump出来的文件内容是SQL语句，所以可以很方便的将其中的一部分复制出来应用到其他数据库里。在执行mysqldump之前，为了保证数据的一致性，最好是把slave进程停掉。

mysqladmin stop-slave或者mysql -e 'STOP SLAVE SQL_THREAD;'
mysqldump --all-databases > fulldb.dump
mysqladmin start-slave

当数据库比较大时，采用mysqldump方式的效率不高，所以可以使用物理文件拷贝的方式。为了保证数据的一致性，物理备份需要将备库关闭

shell> mysqladmin shutdown
shell> tar cf /tmp/dbbackup.tar ./data
/etc/init.d/mysql.server start

MySQL复制可以用在主库和从库采用不同的存储引擎的情况下。这样做的目的通常是在主库和从库可以分别利用不同存储引擎的优势，比如在主库使用 InnoDB是为了事务功能，而从库使用MyISAM因为是只读操作而不需要事务功能
当使用mysqldump方式来创建备库时，改变备库的表存储引擎的方式就是在应用dump文件之前先修改文件里的所有关于表存储引擎的地方
如果是使用文件拷贝的方式来创建备库时，则唯一修改备库表存储引擎的方式就是在启动备库之后使用alter table命令修改

mysql> STOP SLAVE;
mysql> alter table temp engine='myisam';
mysql> START SLAVE;

MySQL复制可以用来做负载均衡功能的水平扩展，最主要是将数据库的读压力分担到多个MySQL slave实例上，这种情况适用在读多写少的环境中。比如一个基本的WEB架构:

MySQL复制可以用在当需要将主库上的不同数据库复制到不同的slave上，以便在不同的slave上执行不同的数据分析任务时。可以在每个slave上配置不同的参数来约束复制过来的数据，通过replicate-wild-do-table参数或者replicate-do-db参数
slave1上应该配置参数replicate-wild-do-table=databaseA.% slave2上应该配置参数replicate-wild-do-table=databaseB.% slave3上应该配置参数replicate-wild-do-table=databaseC.% 每个slave其实是接收到完整的bin log日志，但在应用环节中会进行过滤，仅应用符合参数配置的事件在配置完参数之后，通过mysqldump的方式将对应数据库在slave应用起来，再启动slave线程

MySQL延迟复制

延迟复制是指定从库对主库的延迟至少是指定的这个间隔时间，默认是0秒。可以通过change master to命令来指定

CHANGE MASTER TO MASTER_DELAY = N;

其原理是从库收到主库的bin log之后，不是立即执行，而是等待指定的秒数之后再执行
延迟复制的使用场景比如:
确保在主库上被错误修改的数据能及时找回
测试在从库IO集中在恢复bin log过程中对应用程序的访问影响保留一份若干天前的数据库状态，和当前状态可以做对比 show slave status中SQL_Delay值表明了设置的延迟时长

MySQL复制主从切换

如果是使用GTID的复制方式，可以使用mysqlfailover工具做主从复制状态的监控和自动切换;如果是使用非GTID模式，则需要使用其他的方式做监控和切换
当新的master产生之后，需要通过在其他slave上执行change master to语句来对应到新的master上。slave不会检查自己的数据库和新的master上是否一致，而是直接获取master上的二进制日志并继续自己的复制功能新当选master的实例需要运行在log_bin模式下新的master上开启

log-bin=mysql-bin

Master上查看bin log信息

mysql> show master status;

在slave上执行:

mysql> reset slave all;
Query OK, 0 rows affected (0.01 sec)
mysql> stop slave;
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.237.130', 
-> MASTER_PORT=3308,
-> MASTER_USER='repl',
-> MASTER_PASSWORD='mysql',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=154;
mysql> start slave;

MySQL半同步复制

默认创建的MySQL复制是异步的，意味着主库将数据库修改事件写入到自己的bin log，而并不知道从库是否获取了这些事件并应用在自己身上。所以当主库崩溃导致要主从切换时，有可能从库上的数据不是最新的
从5.7版本开始MySQL通过扩展的方式支持了半同步复制
当主库执行一个更新操作事务时，提交操作会被阻止直到至少有一个半同步的复制slave确认已经接收到本次更新操作，主库的提交操作才会继续半同步复制的slave发送确认消息只会在本次更新操作记录已经记录到本地的 relay log之后如果没有任何slave发送确认消息而导致超时时，半同步复制会转换成异步复制
半同步复制会对MySQL性能产生影响，因为主库的提交动作只有在收到至少一个从库的确认消息之后才能执行。但这个功能是性能和数据可靠性方面的权衡

MySQL半同步复制
rpl_semi_sync_master_wait_point参数用来控制半同步复制的行为: AFTER_SYNC:默认值
AFTER_COMMIT
需要配置的系统参数包括:
rpl_semi_sync_master_enabled:在主库配置，确保主库的半同步复制功能开启
rpl_semi_sync_master_timeout:配置主库等待多少毫秒时间来保证接收备库的确认消息，当超过这个时间时，半同步变成异步方式 rpl_semi_sync_slave_enabled:在从库配置，确保从库的半同步复制功能开启

半同步复制是通过插件的方式建立，要分别在主库和从库安装一个插件前提条件:
5.5版本及以上 have_dynamic_loading参数必须是YES代表可以安装插件并动态加载事先建立好异步复制关系
相关的插件安装文件会在plugin_dir文件夹下，并以semisync_master和semisync_slave名字打头
主库上安装插件:

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; 在每个从库上安装插件:
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; 查看插件的安装情况:
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME LIKE '%semi%';
+----------------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE |
+———————————+---------------+

在主库上开启半同步复制:

SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_master_timeout = N; ##N是毫秒，默认是10000，代表10秒 在备库上开启半同步复制:
SET GLOBAL rpl_semi_sync_slave_enabled =1;

在备库上重启slave进程:

STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

MySQL半同步复制
半同步复制监控参数:
Rpl_semi_sync_master_clients:检查半同步的slave个数
Rpl_semi_sync_master_status:1表示主库的半同步功能开启并且运行正常，0表示主库的半同步功能关闭或者半同步复制已经变成了异步复制
Rpl_semi_sync_master_no_tx:表示有多少提交没有收到slave的确认消息
Rpl_semi_sync_master_yes_tx:表示有多少个提交收到了slave的确认消息
Rpl_semi_sync_slave_status:1表示备库上slave功能开启并且运行正常，0 表示功能为开启或者运行异常

通过命令mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';查看各个参数的状态

MySQL半同步复制
从库关闭IO线程

mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)

主库执行update数据操作，需要等待10秒才能返回

mysql> update temp2 set name='ddd' where id=12;

超时返回之后，从库的半同步状态变成OFF状态

 mysql> show status like '%Rpl_semi%'; 
| Rpl_semi_sync_slave_status | OFF |

当从库同步正常后，半同步状态显示正常

mysql> START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
 mysql> show status like '%Rpl_semi%'; 
| Rpl_semi_sync_slave_status | ON |

当有两个从库都开启半同步复制时，停止其中一个的slave IO线程，再在主库上执行插入，操作很快返回

mysql> insert into temp2 values(131,'a');
Query OK, 1 row affected (0.00 sec)

当把第二个从库的slave IO线程关闭时，则主库插入数据需要等待10秒才能返回

mysql> insert into temp2 values(132,‘a’); ##等待10秒

MySQL基于GTID的复制

GTID(global transaction identifiers)复制是完全基于事务的复制,即每个在主库上执行的事务都会被分配一个唯一的全局ID并记录和应用在从库上这种复制方式简化了建立slave和master/slave之间切换的工作，因为其完全不需要找当前执行的bin log和log中的位置完成切换一个GTID是master上执行的任何commit事务所分配的全局唯一ID标示，
其由两部分组成
GTID = source_id:transaction_id Source_id代表主库的server_uuid，transaction_id代表事务按顺序提交的ID，比如第一个提交则是1，第十个提交的事务就是10

GTID集合代表一组GTID

1.当一个事务在主库提交时，该事务就被赋予了一个GTID，并记录在主库的binary log
2.主库的binary log会被传输到从库的relay log中，从库读取此GTID并生成gtid_next系统参数
3.从库验证此GTID并没有在自己的binary log中使用，则应用此事务在从库上MySQL5.6的GTID复制模式，slave必须开启bin-log和log_slave_updates参数，否则启动就报错，因为需要在binlog找到同步复制的信息(UUID:事务号) (注:开启log_slave_updates参数，是把relay-log里的日志内容再记录到slave本地的 binlog里。)
但在MySQL5.7里，官方做了调整，用一张gtid_executed系统表记录同步复制的信息 (UUID:事务号)，这样就可以不用开启log_slave_updates参数，减少了从库的压力

从MySQL5.7.4版本开始，GTID会存放在mysql系统库的gtid_executed表中

CREATE TABLE gtid_executed ( source_uuid CHAR(36) NOT NULL, 
interval_start BIGINT(20) NOT NULL, 
interval_end BIGINT(20) NOT NULL, 
PRIMARY KEY (source_uuid, interval_start) )
 
show master status; 
select * from mysql.gtid_executed;

MySQL基于GTID的复制
创建复制流程假定两个数据库实例间的主从关系已经通过传统模式创建好了
将主库和从库都设置为read only，确保两者之间的数据都完全同步

mysql> SET @@global.read_only = ON;

关闭主库和从库

mysqladmin -uusername -p shutdown

设置主从库GTID后启动并暂时关闭slave进程

[mysqld]
gtid-mode=on
enforce-gtid-consistency=on
skip-slave-start=1 
#Enforce-gtid-consistency参数是确保只有对gtid复制机制安全的语句才会被log

重新设置主从库的复制关系

mysql> CHANGE MASTER TO
MASTER_HOST = host, 
MASTER_PORT = port,
MASTER_USER = user, 
MASTER_PASSWORD = password, 
MASTER_AUTO_POSITION = 1;

启动slave进程

mysql> START SLAVE;

关闭主库的read only模式

mysql> SET @@global.read_only = OFF;

使用GTID复制的限制条件:

由于GTID复制是依赖于事务的，所以MySQL的一些属性不支持当一个事务中既包含对InnoDB表的操作，也包含对非事务型存储引擎表 (MyISAM)的操作时，就会导致一个事务中可能会产生多个GTID的情况;或者是当master和slave的表使用的存储引擎不一样时，都会导致GTID复制功能不正常
create table...select语句在基于语句复制的环境中是不安全的，在基于行复制的环境中，此语句会被拆分成两个事件，一是创建表，二是insert数据，在某些情况下这两个事件会被分配相同的GTID，而导致insert数据的操作被忽略，所以GTID复制不支持create table ... select语句
create/drop temporary table语句在GTID复制环境中不能放在事务中执行,只能单独执行，并且autocommit要开启 sql_slave_skip_counter语句是不支持的，如果想要跳过事务，可以使用 gtid_executed变量

mysql> create table temp2 select * from temp;
ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.

mysql> create table temp2(id int,name varchar(10)) engine=myisam; 
Query OK, 0 rows affected (0.02 sec)

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

mysql> insert into temp2 select * from temp; 
Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0

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

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

mysql> update temp set name='abc'; 
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3 Changed: 3 Warnings: 0

mysql> insert into temp2 select * from temp;
ERROR 1785 (HY000): Statement violates GTID consistency: Updates to non- transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables.