【mysql复制】02、mysql复制深入

zsyzhou

　　
　　一、mysql复制的常用架构
　　1、一主一从（或多从）
　　主服务器能读能写，从服务器只能读；可以利用从服务器来做备份
　　读写分离：
　　主从模型下，让前端分发器能识别读/写，并且按需调度至目标主机
　　两种实现方式：
　　程序内部实现：
　　程序和架构耦合度过高
　　使用前端分发器：
　　读写分离，并将读请求均衡的分配给从服务器
　　mysql-proxy（不稳定） --> atlas（360）
　　amoeba（没更新） --> cobar（没更新） --> mycat（比较火）
　　复制时默认为异步工作模式，会造成从服务器慢于主服务器
　　 因为主服务器可以使用多线程并发写，但是从服务器的只有一个SQL THREAD线程串行写，时间久了，从服务器必然会慢于主服务器；从服务器慢也并不一定就是坏事，当主服务器有误操作时，可以立刻关闭从服务器的同步线程，从服务器此时可能没有同步到误操作保存了数据。
　　解决办法：
　　1、percona toolkit
　　2、半同步 semi-synchronously
　　2、双主及多主master-master
　　互为主从

　　mysql从服务器复制主服务器的二进制日志时，会保留对方信息中的Server>　　需要解决的问题：
　　1）必须设定双方的的自动增长属性，以避免冲突
　　auto_increment_increment = N   # 设定起始值
　　auto_increment_offset = 2     # 设定步长
　　2）数据不一致
　　某时刻会有此场景：AB服务器都操作同一行数据，A锁定1字段，修改2字段；B锁定2字段对，修改1字段，同步后就会造成数据不一致。
　　双主模型数据不一致无法避免，只能使用工具不停的检测双方数据的一致性，不一致时，自动修复数据；或手动同步一次，风险很大。
　　功能：
　　均衡读请求，写请求没有分摊，要想分摊写操作，只能将数据库分片（非常复杂）
　　生产环境中双主或多主常使用的中间件：
　　MMM
　　mmm开源项目是由perl语言开发的脚本
　　mmm节点监控mysql的多节点完成主从同步，并保证数据一致性，实现多主复制集群
　　MHA
　　实现多主
　　Percona Galera Cluster
　　多主集群，和主从复制没有关系，在物理级别实现数据同步
　　
　　3、一从多主
　　用来汇总数据
　　MariaDB-10+（或mysql5.6+）：支持多主模型，即多源复制（multi-source replication）
　　
　　4、多级（级联）复制
　　中继服务器上二进制日志和中继日志都必须开启
　　多级复制可以降低主服务器binlog dump线程的压力，把压力分摊给下一级
　　可以把中间服务器的存储引擎改为blackhole，来降低本地IO压力，只生成二进制日志作为中继服务器（relay server），然后把二进制日志发送给下游服务器
　　
　　5、环型复制
　　每台服务器都是下一台服务器的主服务器同时也是上一台服务器的从服务器，使得每台服务器都是主从，形成传递环。
　　每台服务器的修改都会同步到环上任何一台服务器中。
　　server_id 不能相同。
　　二、mysql复制类型
　　默认的复制是异步的，即master commit时不等更新被slave接受就向客户端回话应答成功。slave会对master有一个更新延迟，当master宕机，slave被提升为新的master时，必然会发生数据丢失。
　　mysql5.5+支持插件式的半同步复制（谷歌贡献给mysql）
　　1、异步复制（Asynchronous replication）
　　MySQL默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理，这样就会有一个问题，主如果crash掉了，此时主上已经提交的事务可能并没有传到从上，如果此时，强行将从提升为主，可能导致新主上的数据不完整。
　　2、全同步复制（Fully synchronous replication）
　　指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。
　　3、半同步复制（Semisynchronous replication）
　　介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。所以，半同步复制最好在低延时的网络中使用。
　　下面来看看半同步复制的原理图：

　　半同步复制的潜在问题
　　客户端事务在存储引擎层提交后，在得到从库确认的过程中，主库宕机了，此时，可能的情况有两种
　　事务还没发送到从库上
　　此时，客户端会收到事务提交失败的信息，客户端会重新提交该事务到新的主上，当宕机的主库重新启动后，以从库的身份重新加入到该主从结构中，会发现，该事务在从库中被提交了两次，一次是之前作为主的时候，一次是被新主同步过来的。
　　事务已经发送到从库上
　　此时，从库已经收到并应用了该事务，但是客户端仍然会收到事务提交失败的信息，重新提交该事务到新的主上。
　　4、无数据丢失的半同步复制
　　针对上述潜在问题，MySQL 5.7引入了一种新的半同步方案：Loss-Less半同步复制。
　　针对上面这个图，“Waiting Slave dump”被调整到“Storage Commit”之前。
　　当然，之前的半同步方案同样支持，MySQL 5.7.2引入了一个新的参数 rpl_semi_sync_master_wait_point 来控制半同步模式下主库在返回给会话事务成功之前提交事务的方式。
　　AFTER_SYNC  
　　这个即新的半同步方案，Waiting Slave dump在Storage Commit之前。
　　master 将每个事务写入binlog ,传递到slave，并且刷新到磁盘（relay log），master等待slave 反馈接收到事务并刷新到磁盘。一旦接到slave反馈，master在主库提交事务并且返回结果给会话。 在AFTER_SYNC模式下，所有的客户端在同一时刻查看已经提交的数据。假如发生主库crash，所有在主库上已经提交的事务已经同步到slave并记录到relay log。此时切换到从库，可以保障最小的数据损失。
　　AFTER_COMMIT     默认
　　老的半同步方案，如图所示。
　　master 将每个事务写入binlog ,传递到slave 刷新到磁盘(relay log)，然后在主库提交事务。master在提交事务后等待slave 反馈接收到事务并刷新到磁盘。一旦接到slave反馈，master将结果反馈给客户端。
　　在AFTER_COMMIT模式下,如果slave 没有应用日志,此时master crash，系统failover到slave，app将发现数据出现不一致，在master提交而slave 没有应用。
　　三、配置无数据丢失的半同步复制
　　1、须满足的条件
　　MySQL5.5+版本
　　have_dynamic_loading 系统变量设置为YES
　　复制已经配置并且在运行
MariaDB [(none)]> select @@have_dynamic_loading;　　
+------------------------+
　　
| @@have_dynamic_loading |
　　
+------------------------+
　　
| YES                    |
　　
+------------------------+
　　
1 row in set (0.00 sec)
　　2、安装插件并启动
　　Syntax:
　　INSTALL PLUGIN plugin_name SONAME 'shared_library_name';
　　查看共享库名：
[root@Node5 ~]# ls /usr/local/mysql/lib/plugin/semi*　　
/usr/local/mysql/lib/plugin/semisync_master.so  /usr/local/mysql/lib/plugin/semisync_slave.so
　　主节点：
　　安装插件并启动半同步复制功能：
MariaDB [(none)]> show variables like '%semi%'; # 没有安装半同步插件时，没有相关的变量　　
Empty set (0.00 sec)
　　
MariaDB [(none)]> install plugin rpl_semi_sync_master soname 'semisync_master.so';
　　
Query OK, 0 rows affected (0.00 sec)
　　

　　
MariaDB [(none)]> show variables like '%semi%';
　　
+------------------------------------+--------------+
　　
| Variable_name                      | Value        |
　　
+------------------------------------+--------------+
　　
| rpl_semi_sync_master_enabled       | OFF          |          # 默认没有开启
　　
| rpl_semi_sync_master_timeout       | 10000        |#等待从服务器的超时时间单位为毫秒
　　
| rpl_semi_sync_master_trace_level   | 32           |          # 跟踪级别，不管
　　
| rpl_semi_sync_master_wait_no_slave | ON           |          # 是否等待从服务器上线
　　
| rpl_semi_sync_master_wait_point    | AFTER_COMMIT |
　　
+------------------------------------+--------------+
　　
5 rows in set (0.00 sec)
　　
MariaDB [(none)]> set global rpl_semi_sync_master_enabled=on;
　　
Query OK, 0 rows affected (0.00 sec)
　　

　　
MariaDB [(none)]> set global rpl_semi_sync_master_timeout=2000;
　　
Query OK, 0 rows affected (0.01 sec)
　　

　　
MariaDB [(none)]> set global rpl_semi_sync_master_wait_point=after_sync;
　　
Query OK, 0 rows affected (0.00 sec)
　　

　　
MariaDB [(none)]> show variables like '%semi%';
　　
+------------------------------------+------------+
　　
| Variable_name                      | Value      |
　　
+------------------------------------+------------+
　　
| rpl_semi_sync_master_enabled       | ON         |
　　
| rpl_semi_sync_master_timeout       | 2000       |
　　
| rpl_semi_sync_master_trace_level   | 32         |
　　
| rpl_semi_sync_master_wait_no_slave | ON         |
　　
| rpl_semi_sync_master_wait_point    | AFTER_SYNC |
　　
+------------------------------------+------------+
　　
5 rows in set (0.00 sec)
　　

　　
MariaDB [(none)]>
　　rpl_semi_sync_master_timeout：表示主库等待从库响应的超时时间，如果在这个时间内没有收到从库相应，复制模式就会自动降级到异步模式。
　　rpl_semi_sync_master_wait_no_slave：没有从服务器时，是否等待从服务器上线
　　从节点：
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';　　
Query OK, 0 rows affected (0.05 sec)
　　
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%';
　　
+---------------------------------+-------+
　　
| Variable_name                   | Value |
　　
+---------------------------------+-------+
　　
| rpl_semi_sync_slave_enabled     | OFF   |
　　
| rpl_semi_sync_slave_trace_level | 32    |
　　
+---------------------------------+-------+
　　
2 rows in set (0.00 sec)
　　
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_slave_enabled=1;
　　
Query OK, 0 rows affected (0.00 sec)
　　

　　
MariaDB [(none)]> show global status like '%semi%';
　　
+----------------------------+-------+
　　
| Variable_name              | Value |
　　
+----------------------------+-------+
　　
| Rpl_semi_sync_slave_status | OFF   |
　　
+----------------------------+-------+
　　
1 row in set (0.00 sec)
　　

　　
MariaDB [(none)]> stop slave io_thread;
　　
Query OK, 0 rows affected (0.00 sec)
　　

　　
MariaDB [(none)]> start slave io_thread;
　　
Query OK, 0 rows affected (0.01 sec)
　　

　　
MariaDB [(none)]> show global status like '%semi%';
　　
+----------------------------+-------+
　　
| Variable_name              | Value |
　　
+----------------------------+-------+
　　
| Rpl_semi_sync_slave_status | ON    |
　　
+----------------------------+-------+
　　
1 row in set (0.00 sec)
　　停止slave IO 线程，然后重新开启该线程。以使slave IO 线程从新连接到主库，并以半同步方式在主库注册。
　　在从库上执行以下命令重启slave IO线程：
　　STOP SLAVE IO_THREAD;
　　START SLAVE IO_THREAD;
　　查看是否工作在半同步模式下：
MariaDB [(none)]> show global status like '%semi%';　　
+--------------------------------------------+-------+
　　
| Variable_name                              | Value |
　　
+--------------------------------------------+-------+
　　
| Rpl_semi_sync_master_clients               | 1     |
　　
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
　　
| Rpl_semi_sync_master_net_wait_time         | 0     |
　　
| Rpl_semi_sync_master_net_waits             | 0     |
　　
| Rpl_semi_sync_master_no_times              | 0     |
　　
| Rpl_semi_sync_master_no_tx                 | 0     |
　　
| Rpl_semi_sync_master_status                | ON    |
　　
| Rpl_semi_sync_master_timefunc_failures     | 0     |
　　
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
　　
| Rpl_semi_sync_master_tx_wait_time          | 0     |
　　
| Rpl_semi_sync_master_tx_waits              | 0     |
　　
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
　　
| Rpl_semi_sync_master_wait_sessions         | 0     |
　　
| Rpl_semi_sync_master_yes_tx                | 0     |
　　
+--------------------------------------------+-------+
　　
14 rows in set (0.00 sec)
　　

　　
MariaDB [(none)]>
　　对上面变量的一些解释，下面的变量仅仅在主服务的半复制插件初始化后才有效：
　　Rpl_semi_sync_master_clients：半复制连接的客户端从服务器数量。
　　Rpl_semi_sync_master_net_avg_wait_time：主服务器等待从服务器的平均应答时间(单位：微秒)
　　Rpl_semi_sync_master_net_wait_time：主服务器等待从服务器的应答的总时间(单位：微秒)
　　Rpl_semi_sync_master_net_waits：The total number of times the master waited for
　　slave replies
　　Rpl_semi_sync_master_no_times：主服务器关闭半复制的次数即降级为异步同步的次数
　　Rpl_semi_sync_master_no_tx：从服务器提交事物后没有收到ack确认成功的次数
　　Rpl_semi_sync_master_status：是否工作在半同步模式下，设置为ON表示开启，OFF表示关闭，为异步模式
　　Rpl_semi_sync_master_timefunc_failures：主服务器调用时间函数失败的次数，例如gettimeofday()
　　Rpl_semi_sync_master_tx_avg_wait_time：主服务器等候每个事物的平均时间(毫秒)
　　Rpl_semi_sync_master_tx_waits：主服务器等候事物的总次数
　　Rpl_semi_sync_master_wait_pos_backtraverse：改变当前等待最小二进制日志坐标的次数（改变当前等待最小二进制日志的次数）
　　Rpl_semi_sync_master_wait_sessions：正在等候从服务器回复的会话数
　　Rpl_semi_sync_master_yes_tx：从服务器成功得到答应的提交事物次数在从上的当在从服务运行时调整支持半同步复制，必须重启I/O线程。
　　注意点：
　　在mysql启动前将全局变量写入到配置文件中。以避免忘记设置使半同步不生效。
　　主库配置文件：
　　[mysqld]
　　rpl_semi_sync_master_enabled = 1
　　rpl_semi_sync_master_timeout=2000
　　从库配置文件：
　　[mysqld]
　　rpl_semi_sync_slave_enabled = 1
　　卸载插件：
　　Syntax:
　　UNINSTALL PLUGIN plugin_name
　　总结：
　　1. 在一主多从的架构中，如果要开启半同步复制，并不要求所有的从都是半同步复制。
　　2. MySQL 5.7极大的提升了半同步复制的性能。
　　5.6版本的半同步复制，dump thread 承担了两份不同且又十分频繁的任务：传送binlog 给slave ，还需要等待slave反馈信息，而且这两个任务是串行的，dump thread 必须等待 slave 返回之后才会传送下一个 events 事务。dump thread 已然成为整个半同步提高性能的瓶颈。在高并发业务场景下，这样的机制会影响数据库整体的TPS 。
　　5.7版本的半同步复制中，独立出一个 ack collector thread ，专门用于接收slave 的反馈信息。这样master 上有两个线程独立工作，可以同时发送binlog 到slave ，和接收slave的反馈。
　　三、复制过滤器
　　让slave仅复制有限的几个数据库，而非所有
　　有两种实现思路： # 配置在配置文件中
　　1、主服务器仅向二进制日志中记录有特定数据库相关的写操作
　　问题：即时点还原将无法全面实现
　　binlog_do_db=db_name         # 数据库白名单，多个数据库使用逗号分隔？
　　binlog_ignore_db=db_name      # 数据库黑名单
　　2、从服务器的SQL_THREAD仅在中继日志中读取特定数据相关的语句并应用在本地
　　问题：会造成网络带宽和磁盘IO的浪费
　　Replicate_Do_DB=db_name
　　Replicate_Ignore_DB=db_name
　　Replicate_Do_Table=db_name.table_name
　　Replicate_Ignore_Table=db_name.table_name
　　Replicate_Wild_Do_Table=db_name.*          # 基于表的过滤还支持通配符
　　Replicate_Wild_Ignore_Table=db_name.*
　　在主服务器上过滤不支持基于表级别
　　四、基于SSL的复制
　　前提：支持SSL 编译时指定的
MariaDB [node5]> show global variables like '%ssl%';　　
+---------------------+---------------------------------+
　　
| Variable_name       | Value                           |
　　
+---------------------+---------------------------------+
　　
| have_openssl        | YES                             |  # YES表示编译时指定了支持SSL
　　
| have_ssl            | DISABLED                        |　# 设置为yes表示启用ssl
　　
| ssl_ca              |                                 |
　　
| ssl_capath          |                                 |
　　
| ssl_cert            |                                 |
　　
| ssl_cipher          |                                 |  #　支持的加密算法
　　
| ssl_crl             |                                 |
　　
| ssl_crlpath         |                                 |
　　
| ssl_key             |                                 |
　　
| version_ssl_library | OpenSSL 1.0.1e-fips 11 Feb 2013 |
　　
+---------------------+---------------------------------+
　　
10 rows in set (0.00 sec)
　　步骤：
　　(1) 主服务器端配置证书和私钥，并创建一个要求必须使用SSL连接的复制账号(授权REQUIRE SSL权限);
　　(2) SLAV 端连接master时，使用MASTER_SSL相关的选项来配置证书等信息；
　　五、mysql复制的监控和维护
　　1、清理日志
　　PURGE
　　2、复制监控
　　SHOW MASTER STATUS；
　　SHOW BINLOG EVENTS；
　　SHOW BINARY LOGS；
　　
　　SHOW SLAVE STATUS；
　　3、如何判断slave是否落后于master
　　Seconds_Behind_Master: 0
　　show slave status；可以看到到此项，表示slave落后于主服务器多少秒，0表示没落后
　　4、如何确定主从节点数据是否一致？
　　1）通过表自身的CHECKSUM检查
　　定义表的时候，启用CHECKSUM功能
　　使用show table status;查看表的CHECKSUM信息（一样表示一致）
　　2）使用percona-toolkit中pt-table-checksum命令
　　5、数据不一致的修复方法
　　1）手动重新复制
　　2）使用percona-toolkit中pt-table-sync命令