Mysql半同步复制、数据一致性检查

1：配置异步复制
scripts/mysql_install_db --user=mysql --datadir=/mysql/data
bin/mysqld_safe --user=mysql &

在master上创建复制用户：
mysql> GRANT replication slave ON *.* TO 'repl'@'%' identified by 'oracle';
mysql> flush privileges;

在slave上：
change master to master_host='192.168.1.23',
 master_port=3306, master_user='repl', 
master_password='oracle', master_log_file='mysql3306-bin.000003',master_log_pos=120;

start slave;

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.23
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql3306-bin.000003
          Read_Master_Log_Pos: 397
               Relay_Log_File: mysql3306-relay-bin.000002
                Relay_Log_Pos: 564
        Relay_Master_Log_File: mysql3306-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
看到两个yes，完成主从配置


2：半同步复制的安装部署
要想使用半同步复制，必须满足以下几个条件：
MySQL 5.5及以上版本，变量have_dynamic_loading为YES，异步复制已经存在

首先加载插件：
因用户需执行INSTALL PLUGIN, SET GLOBAL, STOP SLAVE和START SLAVE操作，所以用户需有SUPER权限。

主：
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

从：
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

查看插件是否加载成功
有两种方式：
a： 
mysql> show plugins;
......
| rpl_semi_sync_master       | ACTIVE   | REPLICATION        | semisync_master.so | GPL     |
+----------------------------+----------+--------------------+--------------------+---------+

b：在master
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS  WHERE PLUGIN_NAME LIKE '%semi%';
+----------------------+---------------+
| PLUGIN_NAME          | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
+----------------------+---------------+
 
在slave :
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS  WHERE PLUGIN_NAME LIKE '%semi%';
+---------------------+---------------+
| PLUGIN_NAME         | PLUGIN_STATUS |
+---------------------+---------------+
| rpl_semi_sync_slave | ACTIVE        |
+---------------------+---------------+

启动半同步复制：
在安装完插件后，半同步复制默认是关闭的，这时需设置参数来开启半同步
master：
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;

slave：
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;

重启slave的IO线程：
mysql> STOP SLAVE IO_THREAD;
mysql> START SLAVE IO_THREAD;

如果没有重启，则默认还是异步复制，重启后，slave会在master上注册为半同步复制的slave角色。

这时候，master的error.log中会打印如下信息：
2016-08-26 07:35:25 6939 [Note] Semi-sync replication initialized for transactions.
2016-08-26 07:35:25 6939 [Note] Semi-sync replication enabled on the master.
2016-08-26 07:36:41 6939 [Note] Stop asynchronous binlog_dump to slave (server_id: 243306)
2016-08-26 07:36:41 6939 [Note] Start semi-sync binlog_dump to slave (server_id: 243306), pos(mysql3306-bin.000003, 397) 

查看半同步是否在运行：
主：
mysql> show status like 'Rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON    |
+-----------------------------+-------+

从：
mysql> show status like 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
这两个变量常用来监控主从是否运行在半同步复制模式下。

至此，MySQL半同步复制搭建完毕~

#########################################

一、简介
pt-table-checksum是percona-toolkit系列工具中的一个， 可以用来检测主、 从数据库中数据的一致性。
其原理是在主库上运行， 对同步的表进行checksum， 记录下来。 然后对比主从中各个表的checksum是否一致， 
从而判断数据是否一致。检测过程中以块为单位， 对于大的表可以区分为多个块， 从而避免锁表（ 根据唯一
索引将表切分为块）检测时会自动判断复制延迟、 master的负载， 超过阀值后会自动将检测暂停。

pt-table-sync，顾名思义，用来修复多个实例之间数据的不一致。它可以让主从的数据修复到最终一致，
也可以使通过应用双写或多写的多个不相关的数据库实例修复到一致。同时它还内部集成了pt-table-checksum的校验功能，
可以一边校验一边修复，也可以基于pt-table-checksum的计算结果来进行修复。

二、工作原理
1. 单行数据checksum值的计算
计算逻辑与pt-table-checksum一样，也是先检查表结构，并获取每一列的数据类型，把所有数据类型都转化为字符串，
然后用concat_ws()函数进行连接，由此计算出该行的checksum值。checksum默认采用crc32计算。

2. 数据块checksum值的计算
同pt-table-checksum工具一样，pt-table-sync会智能分析表上的索引，然后把表的数据split成若干个chunk，
计算的时候以chunk为单位。可以理解为把chunk内所有行的数据拼接起来，再计算crc32的值，即得到该chunk的checksum值。

3. 坏块检测和修复
前面两步，pt-table-sync与pt-table-checksum的算法和原理一样。再往下，就开始有所不同：
pt-table-checksum只是校验，所以它把checksum结果存储到统计表，然后把执行过的sql语句记录到binlog中，任务就算完成。语句级的复制把计算逻辑传递到从库，并在从库执行相同的计算。pt-table-checksum的算法本身并不在意从库的延迟，延迟多少都一样计算(有同事对此不理解，可以参考我的前一篇文章)，不会影响计算结果的正确性(但是我们还是会检测延迟，因为延迟太多会影响业务，所以总是要加上—max-lag来限流)。 

pt-table-sync则不同。它首先要完成chunk的checksum值的计算，一旦发现主从上同样的chunk的checksum值不同，
就深入到该chunk内部，逐行比较并修复有问题的行。其计算逻辑描述如下(以修复主从结构的数据不一致为例，
业务双写的情况修复起来更复杂—因为涉及到冲突解决和基准选择的问题，限于篇幅，这里不介绍)：

对每一个从库，每一个表，循环进行如下校验和修复过程。对每一个chunk，在校验时加上for update锁。
一旦获得锁，就记录下当前主库的show master status值。

在从库上执行select master_pos_wait()函数，等待从库sql线程执行到show master status得到的位置。
以此保证，主从上关于这个chunk的内容均不再改变。

对这个chunk执行checksum，然后与主库的checksum进行比较。
如果checksum相同，说明主从数据一致，就继续下一个chunk。
如果checksum不同，说明该chunk有不一致。深入chunk内部，逐行计算checksum并比较(单行的checksum的比较过程
与chunk的比较过程一样，单行实际是chunk的size为1的特例)。

如果发现某行不一致，则标记下来。继续检测剩余行，直到这个chunk结束。

对找到的主从不一致的行，采用replace into语句，在主库执行一遍以生成该行全量的binlog，并同步到从库，
这会以主库数据为基准来修复从库；对于主库有的行而从库没有的行，采用replace在主库上插入(必须不能是insert)；
对于从库有而主库没有的行，通过在主库执行delete来删除(pt-table-sync强烈建议所有的数据修复都只在主库进行，
而不建议直接修改从库数据；但是也有特例，后面会讲到)。

直到修复该chunk所有不一致的行。继续检查和修复下一个chunk。
直到这个从库上所有的表修复结束。开始修复下一个从库。

三、校验测试
1：测试环境
Mysql Version ： 5.6.28
OS Version ： CentOS release 6.5 
Master IP : 192.168.1.23
Slave  IP : 192.168.1.24

2.Master服务器安装Yum依赖包：
yum install perl perl-devel perl-Time-HiRes perl-DBI perl-DBD-MySQL

3.安装percona-toolkit工具包
wget http://www.percona.com/get/percona-toolkit.tar.gz
tar zxf percona-toolkit-2.2.13.tar.gz
cd percona-toolkit-2.2.13
perl Makefile.PL
make && make install

4.Master / Slave 数据库创建用户及授权
create database pt CHARACTER SET utf8;
GRANT UPDATE,INSERT,DELETE,SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO ‘checksums‘@'%' identified by 'oracle';
GRANT ALL ON pt.* TO 'checksums'@'%' IDENTIFIED BY 'oracle';

use pt;
CREATE TABLE IF NOT EXISTS checksums (
db char(64) NOT NULL,
tbl char(64) NOT NULL,
chunk int NOT NULL,
chunk_time float NULL,
chunk_index varchar(200) NULL,
lower_boundary text NULL,
upper_boundary text NULL,
this_crc char(40) NOT NULL,
this_cnt int NOT NULL,
master_crc char(40) NULL,
master_cnt int NULL,
ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (db, tbl, chunk),
INDEX ts_db_tbl (ts, db, tbl)
) ENGINE=InnoDB;

5.校验（Master服务器运行）
pt-table-checksum --nocheck-binlog-format --nocheck-plan --nocheck-replication-filters --replicate=pt.checksums --set-vars innodb_lock_wait_timeout=120 --databases test -u'checksums' -p'oracle' -h192.168.1.23

#-h -u -p -P -S -d 连接信息
#--nocheck-replication-filters 检测中忽略mysql 配置参数binlog_ignore_db等。
#--nocheck-binlog-format 不检测日志格式
#--replicate 指定checksum 存储的db和表， 如test.checksum
# --chunk-size， --chunk-size-limit 用于指定检测块的大小。 可控性更强
# --ignore-databases/tables/column 跳出指定元素的过滤
# --lock-wait-timeout innodb 锁的超时设定， 默认为1
# --max-load 设置最大并发连接数
# --replicate-check-only 只输出数据不一致的信息。
# --help 有这个就行了， 以及其他的详见文档。
备注：--no-check-binlog-format 忽略检查binlog格式，否则会报错，默认会去检查statement模式，一般我们都用row模式


结果说明：
TS ：完成检查的时间。
ERRORS ：检查时候发生错误和警告的数量。
DIFFS ：0表示一致，1表示不一致。当指定--no-replicate-check时，会一直为0，当指定--replicate-check-only会显示不同的信息。
ROWS ：表的行数。
CHUNKS ：被划分到表中的块的数目。
SKIPPED ：由于错误或警告或过大，则跳过块的数目。
TIME ：执行的时间。
TABLE ：被检查的表名。
备注：主要观察DIFFS列，标识差异数。

6.查看差异（Slave库运行）
select db, tbl, sum(this_cnt) as total_rows, count(*) as chunks  
 from checksums 
where ( master_cnt <> this_cnt OR master_crc <> this_crc OR isnull(master_crc) <> isnull(this_crc) )
 group by db, tbl; 

7.制造master、slave两张不一样的表
master：
use test;
create table test (id int);
insert into test values(1),(2),(3),(4),(5);
ALTER TABLE test ADD CONSTRAINT pk_name PRIMARY KEY(id);
mysql> select * from test.test;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)

slave：
mysql> select * from test.test;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)

mysql> delete from test.test where id=3;
Query OK, 1 row affected (0.02 sec)

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

mysql> select * from test.test;
+------+
| id   |
+------+
|    1 |
|    2 |
|    4 |
|    5 |
+------+
4 rows in set (0.00 sec)

先检查test库中表的差异：
[root@mysql3 ~]# pt-table-checksum --nocheck-binlog-format --nocheck-plan --nocheck-replication-filters --replicate=pt.checksums --set-vars innodb_lock_wait_timeout=120 --databases test -u'checksums' -p'oracle' -h192.168.1.23
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
08-26T11:35:08      0      1        5       1       0   0.043 test.test

使主、从数据一致：
[root@mysql3 ~]# pt-table-sync --print --execute --sync-to-master h=192.168.1.24,P=3306,u=checksums,p='oracle' --databases=test --tables=test
REPLACE INTO `test`.`test`(`id`) VALUES ('3') /*percona-toolkit src_db:test src_tbl:test src_dsn:P=3306,h=192.168.1.23,
p=...,u=checksums dst_db:test dst_tbl:test dst_dsn:P=3306,h=192.168.1.24,p=...,u=checksums lock:1 transaction:1 
changing_src:1 replicate:0 bidirectional:0 pid:8195 user:root host:mysql3*/;

或者打印出sql语句，人工干预到Slave库执行（推荐）
pt-table-sync --print --sync-to-master h=192.168.1.24,P=3306,u=checksums,p='oracle' --databases=test --tables=test
pt-table-sync --print --sync-to-master h=192.168.1.24,P=3306,u=checksums,p='oracle' --replicate pt.checksums

#--sync-to-master ：指定一个DSN，即从的IP，他会通过show processlist或show slave status 去自动的找主。
#--replicate ：指定通过pt-table-checksum得到的表，这2个工具差不多都会一直用。
#--print ：打印，但不执行命令。
#--execute  ：执行命令。
备注：Slave需要授权主库Drop 和Create Temporary Tables权限

8.检验
slave：
mysql> select * from test.test;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
5 rows in set (0.00 sec)

重新执行一次pt-table-checksum，查看是否还存在差异：
[root@mysql3 ~]# pt-table-checksum --nocheck-binlog-format --nocheck-plan --nocheck-replication-filters --replicate=pt.checksums --set-vars innodb_lock_wait_timeout=120 --databases test -u'checksums' -p'oracle' -h192.168.1.23
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
08-26T11:48:26      0      0        5       1       0   0.028 test.test

四、注意事项
1.采用replace into来修复主从不一致，必须保证被replace的表上有主键或唯一键，否则replace into退化成insert into，起不到修复的效果。这种情况下pt-table-sync会采用其他校验和修复算法，但是效率非常低，例如对所有列的group by然后求count(*)(表一定要有主键！)。
2.主从数据不一致需要通过replace into来修复，该sql语句必须是语句级。pt-table-sync会把它发起的所有sql语句都设置为statement格式，而不管全局的binlog_format值。这在级联A-B-C结构中，也会遇到pt-table-checksum曾经遇到的问题，引起行格式的中继库的从库卡库是必然。不过pt-table-sync默认会无限递归的对从库的binlog格式进行检查并警告。
3.由于pt-table-sync每次只能修复一个表，所以如果修复的是父表，则可能导致子表数据连带被修复，这可能会修复一个不一致而引入另一个不一致；如果表上有触发器，也可能遇到同样问题。所以在有触发器和主外键约束的情况下要慎用。pt-table-sync工具同样也不欢迎主从异构的结构。pt-table-sync工具默认会进行先决条件的检查。
4.pt-table-sync在修复过程中不能容忍从库延迟，这正好与pt-table-checksum相反。如果从库延迟太多，pt-table-sync会长期持有对chunk的for update锁，然后等待从库的master_pos_wait执行完毕或超时。从库延迟越大，等待过程就越长，主库加锁的时间就越长，对线上影响就越大。因此要严格设置max-lag。
5.对从库数据的修复通常是在主库执行sql来同步到从库。因此，在有多个从库时，修复某个从库的数据实际会把修复语句同步到所有从库。数据修复的代价取决于从库与主库不一致的程度，如果某从库数据与主库非常不一致，举例说，这个从库只有表结构，那么需要把主库的所有数据重新灌一遍，然后通过binlog同步，同时会传递到所有从库。这会给线上带来很大压力，甚至拖垮集群。正确的做法是，先用pt-table-checksum校验一遍，确定不一致的程度：如果不同步的很少，用pt-table-sync直接修复；否则，用备份先替换它，然后用pt-table-sync修复。 说明： 这实际提供了一种对myisam备份的思路：如果仅有一个myisam的主库，要为其增加从库，则可以：先mysqldump出表结构到从库上，然后启动同步，然后用pt-table-sync来修复数据。

########################################

如何保证线上数据库，主从一致，或者尽量少出问题?
1：使用innodb存储引擎，每张表都要有主键，或者唯一性索引
2：设置innodb_flush_log_at_trx_commit = 1
3：使用binlog server
4：定期使用pt-table-checksum检查数据的一致性

######################################

复制过滤：

1.M上把事件从二进制日志中过滤
参数：binlog-do-db
      binlog-ignore-db

2.S上事件从中继日志中过滤
参数：replicate_do_db
      replicte_do_table
      repicate_ingore_db
      repliacate_ignore_table
      replicate_write_db
      replicate_wild_do_table
      replicate_wild_ignore_table

##############################################################

服务器硬件优化：
1：BIOS优化
开启最大性能模式；
开启最大PCIE ExpressSpeed
开始超线程
使用所有cpu核

2：底层磁盘
机械硬盘做成 RAID 1+0
有条件使用 PCIE SSD

3：OS优化
修改/etc/security/limits.conf，将mysql的nofile限制（soft、hard都需要修改）修改为65536
mysql的数据放在XFS文件系统的磁盘
IPMI开启，方便远程管理

-------------------------------------
5.6半同步的BUG，5.7的修复方案：
5.6半同步流程：
1：master commit，写binlog
2：传送到slave的relay log
3：master不等slave返回ack，就在存储引擎commit
所以，slave有可能丢失数据


5.7半同步流程：
1：master commit，写binlog
2：传送到slave的relay log
3：master等slave返回ack，才会在存储引擎commit;
当master、slave之间网络超时，在超过时间限定之后，会由半同步转变成异步复制，
从而不能保证master、slave之间数据不一致。
而当master在等slave返回ack，还没在存储引擎commit时候宕机，就会出现slave比master多一条记录。

-----------------------------------------------
relay-log-info：记录SQL线程读取Master binlog的位置，用于Slave宕机之后根据文件中记录的POS点恢复SQL线程
master-info：记录I/O线程已经读取到的Master binlog位置，用于Slave宕机后I/O线程根据文件中记录的POS点重新拉取binlog日志。
sync_relay_log：
默认为10000，即每10000次sync_relay_log事件会刷新到磁盘。为0则表示不刷新，交由OS的cache控制。
sync_master_info:
若master-info-repository为FILE，当设置为0，则每次sync_master_info事件都会刷新到磁盘，默认为10000次刷新到磁盘；
若master-info-repository为TABLE，当设置为0，则表不做任何更新，设置为1，则每次事件会更新表 #默认为10000
sync_relay_log_info：
若relay_log_info_repository为FILE，当设置为0，交由OS刷新磁盘，默认为10000次刷新到磁盘；
若relay_log_info_repository为TABLE，且为INNODB存储，则无论为任何值，则都每次evnet都会更新表。


问题：relay-log-info、master-info不是实时更新的，
如果sync_relay_log_info设置为1000，在999个事务的时候，slave宕机，则relay-log-info文件未能更新，
则在slave重启后，会根据上次记录的POS点开始SQL线程，则slave重复执行这999个事务，会报主键冲突。
如果master-info设置为1000，在999个事务的时候，slave宕机，则master-info文件未能更新，
则在slave重启后，会根据上次记录的POS点开始IO线程，slave则会从master重新拉取这999个事务，
然后重复执行这999个事务，会报主键冲突。
SQL线程和 SYNC不是在一个事务里，所以sync_relay_log_info、sync_master_info无论设置多少都不能解决问题。


解决方案：
Mysql提供了两个参数
relay-log-info-repository：将relay-log-info记录到slave_relay_log_info表中;
master-info-repository：将master-info记录到slave_master_info表中;
relay_log_recovery：当slave重启后会根据slave_relay_log_info重新创建一个文件，SQL线程会根据这个文件进行恢复复制，
IO线程会读取SQL线程的POS点，根据这个POS点向主库申请拉去数据。
所以只要将 relay-log-info-repository = tabl，relay_log_recovery=ON 设置好即可解决问题。