Mysql主从复制数据架构全面解读_mysql主从复制原理以及架构

        大家好，我是山子，今天给大家分析Mysql 实现主从复制的方方面面，主从复制当然也是我们做读写分离的前提，以下内容是从各网络平台摘录整理总结归纳在一起的；内容已经从主从复制的各方面的维度进行了阐述；非常的全面；可以帮助大家节省时间，再次去从各网络平台进行查找；当然由于内容比较多，有小部分内容存在重复；就当是再次巩固阅读了把；全篇内容教比较长，请大家耐心的读完，相信大家一定会有收获的，对主从复制这块的知识一定会有很大认知的提升，用来做面试准备也是全面无死角覆盖！加油，看完哦！

      

1 主从复制原理

• ①当Master节点进行insert、update、delete操作时，会按顺序写入到binlog中。
• ②salve从库连接master主库，Master有多少个slave就会创建多少个binlog dump线程。
• ③当Master节点的binlog发生变化时，binlog dump 线程会通知所有的salve节点，并将相应的binlog内容推送给slave节点。
• ④I/O线程接收到 binlog 内容后，将内容写入到本地的 relay-log。
• ⑤SQL线程读取I/O线程写入的relay-log，并且根据 relay-log 的内容对从数据库做对应的操作。
• 

2 主从延迟是怎样定义?

• 主库执行完一个事务，写入binlog，我们把这个时刻记为 T1;
• 主库同步数据给从库，从库接受完这个binlog的时刻，记录为 T2；
• 从库执行完这个事务，这个时刻记录为 T3。

所谓主从延迟，其实就是指同一个事务，在从库执行完的时间和在主库执行完的时间差值，即 T3-T1。

1. 导致主从延迟的原因

MySql数据库从库同步的延迟问题(从根上理解同步延迟的原因)

        1)、MySQL数据库主从同步延迟原理mysql主从同步原理：主库针对写操作，顺序写binlog，从库单线程去主库顺序读”写操作的binlog”，从库取到binlog在本地原样执行（随机写），来保证主从数据逻辑上一致。mysql的主从复制都是单线程的操作，主库对所有DDL和DML产生binlog，binlog是顺序写，所以效率很高，slave的Slave_IO_Running线程到主库取日志，效率比较高，下一步，问题来了，slave的Slave_SQL_Running线程将主库的DDL和DML操作在slave实施。DML和DDL的IO操作是随即的，不是顺序的，成本高很多，还可能可slave上的其他查询产生lock争用，由于Slave_SQL_Running也是单线程的，所以一个DDL卡主了，需要执行10分钟，那么所有之后的DDL会等待这个DDL执行完才会继续执行，这就导致了延时。有朋友会问：“主库上那个相同的DDL也需要执行10分，为什么slave会延时？”，答案是master可以并发，Slave_SQL_Running线程却不可以。

        2)、MySQL数据库主从同步延迟是怎么产生的？当主库的TPS并发较高时，产生的DDL数量超过slave一个sql线程所能承受的范围，那么延时就产生了，当然还有就是可能与slave的大型query语句产生了锁等待。首要原因：数据库在业务上读写压力太大，CPU计算负荷大，网卡负荷大，硬盘随机IO太高次要原因：读写binlog带来的性能影响，网络传输延迟。

• 如果从库所在的机器比主库的机器性能差，会导致主从延迟，这种情况比较好解决，只需选择主从库一样规格的机器就好。
• 如果从库的压力大，也会导致主从延迟。比如主库直接影响业务，大家可能使用会比较克制，因此一般查询都打到从库了，结果导致从库查询消耗大量CPU，影响同步速度，最后导致主从延迟。这种情况的话，可以搞一主多从架构，即多接几个从库分摊读的压力。另外，还可以把binlog接入到Hadoop这类系统，让它们提供查询的能力。
• 大事务也会导致主从延迟。如果一个事务执行就要10分钟，那么主库执行完后，给到从库执行，最后这个事务可能就会导致从库延迟10分钟啦。日常开发中，我们为什么特别强调，不要一次性delete太多SQL，需要分批进行，其实也是为了避免大事务。另外，大表的DDL语句，也会导致大事务，大家日常开发关注一下哈。
• 网络延迟也会导致主从延迟，这种情况你只能优化你的网络啦，比如带宽20M升级到100M类似意思等。
• 如果从数据库过多也会导致主从延迟，因此要避免复制的从节点数量过多。从库数据一般以3-5个为宜。
• 低版本的MySQL只支持单线程复制，如果主库并发高，来不及传送到从库，就会导致延迟。可以换用更高版本的Mysql，可以支持多线程复制。

4.同步失败解决方案

        #忽略当前的错误执行下一步的同步

        先 stop slave;

        ① slave数据库中：SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

START SLAVE;

问题：I/O线程 一直处于connecting

        第一件事，看laster error,报错项内容是为支点找不到

        先 stop slave;

        ② slave数据库中：

        CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000001’,MASTER_LOG_POS=0;

        首先遇到这个是因为binlog位置索引处的问题，生产环境下不要直接reset slave（删除change master 操作的）；

        reset slave会将主从同步的文件以及位置恢复到初始状态，一开始没有数据还好，有数据的话，相当于重新开始同步，可能会出现一些问题；

        一般做主从同步，都是要求以后的数据实现主从同步，而对于旧的数据完全可以使用数据库同步工具先将数据库同步，完了再进行主从同步；

好了遇到上面的问题，正确做法是：

        1.打开主（master)服务器，进入mysql

        2.执行flush logs；//这时主服务器会重新创建一个binlog文件；

        3.在主服务上执行show master status\G;显示如下：

        4.来到从服务器的mysql；

        5.stop slave;

        6.change master to master_log_file='mysql-bin.000012',master_log_pos=154;//这里的file和pos都是上面主服务器master显示的。

        7.start slave; //这时候就应可以了

参考资料

        https://baijiahao.baidu.com/s?id=1743211585730328123&wfr=spider&for=pc

5 主从复制的方式

        在实际运用中，时常会出现这样的情况，主库的数据已经有了，可从库还是读取不到，可能要过几十毫秒，甚至几百毫秒才能读取到。

        一个是半同步复制，用来解决主库数据丢失问题；一个是并行复制，用来解决从库复制延迟的问题。半同步复制，也叫 semi-sync 复制，指的就是主库写入 binlog 日志之后，就会将强制此时立即将数据同步到从库，从库将日志写入自己本地的 relay log 之后，接着会返回一个 ack 给主库，主库接收到至少一个从库的 ack 之后才会认为写操作完成了。

        并行复制，指的是从库开启多个线程，并行读取 relay log 中不同库的日志，然后并行重放不同库的日志，这是库级别的并行。

        MySQL 支持的复制类型

        基于语句的复制（STATEMENT）：在主服务器上执行的 SQL 语句，在从服务器上执行同样的语句。MySQL 默认采用基于语句的复制，效率比较高。

        基于行的复制（ROW）：把改变的内容复制过去，而不是把命令在从服务器上执行一遍。

混合类型的复制（MIXED）：默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制。

异步复制

        异步复制：经典的主从复制，Primary-Secondary Replication，2000年MySQL 3.23.15版本引入 Replication。

        传统的MySQL复制提供了一种简单的主从复制方案。有一个主（source）并且有一或多个从（replicas）。主数据库execute事务，将其commit，然后将它们稍后（异步）发送给从数据库，以re-executed（在基于语句的复制中）或apply（在基于行的复制中）。它是一个无共享系统，默认情况下所有服务器都具有数据的完整副本。

• MySQL Asynchronous Replicatio

• MySQL Asynchronous Replicatio

优点

        简单

缺点

        SQL的每个增删改的会改变数据的操作，除了更新数据外，对这个增删改操作还会写入一个日志文件，记录这个操作的日志，即binlog。

        mysql 5.7新版本的并行复制，多个SQL线程，每个线程从relay日志里读一个库的 日志，重放。 从库同步主库数据的过程是串行化的，即主库上并行的操作，在从库上会串行执行。 由于从库从主库拷贝日志以及串行执行SQL的特点，在高并发下就有延时，从库数据一定比主库慢，所以经常出现，刚写入主库的数据读不到，要过几十甚至几百ms才能读到。 从库串行化过程：

1. 读取binlog日志
2. 写relay日志、应用日志变更到自己本地数据

        从库的I/O线程，读取主库的binlog日志时，老版本是单线程，5.6.x之后的新版本改为多线程读取。

        若主库宕机时，恰好数据还没同步到从库，则有些数据可能在从库上没有，可能就这么丢失了。

所以MySQL实际上在这有两个机制

        半同步复制，它向协议添加了一个同步步骤。这意味着主数据库在提交时等待从数据库确认已接收到事务。只有这样，主数据库才会恢复提交操作。

半同步复制

        2010 年引入Semisynchronous Replication，5.5 可用，解决主库数据丢失问题，保证 Source 和 Replica 的最终一致性。 需要启用插件。

1. 主库写入binlog日志后，会强制立即将数据同步到从库
2. 从库将日志写入自己的relay log后，会返回ack给主库
3. 主库接收到至少一个从库的ack后才会认为写操作完成

上面的图片可看到经典的异步MySQL复制协议（及其半同步变量）的示意图。不同实例之间的箭头表示服务器之间交换的消息或服务器与客户端应用程序之间交换的消息。

组复制

2016年引入，5.7 开始，启用插件。

基于 Paxos 协议实现的组复制，保证数据一致性。

        组复制是一种可用于实施容错系统的技术。复制组是一组服务器，每个服务器都有自己的完整数据副本（无共享复制方案），并通过消息传递相互交互。通信层提供了一组保证，例如原子消息和总订单消息传递。这些功能非常强大，可以转化为非常有用的抽象，可以用来构建更高级的数据库复制解决方案。

        MySQL组复制建立在这些属性和抽象之上，并在所有复制协议中实现多源更新。一个复制组由多个服务器组成，该组中的每个服务器可以随时独立执行事务。但是，所有读写事务只有在组批准后才提交。换句话说，对于任何读写事务，组都需要决定是否提交，因此提交操作不是来自原始服务器的单方面决定。只读事务无需组内的任何协调即可立即提交。

        当读写事务准备好在原始服务器上提交时，服务器自动广播写值（已更改的行）和相应的写集（已更新的行的唯一标识符）。由于事务是通过原子广播发送的，因此该组中的所有服务器都将接收该事务，否则将不会。如果他们收到了，那么相对于之前发送的其他事务，他们都将以相同的顺序收到它。因此，所有服务器都以相同的顺序接收相同的交易集，并且为交易建立了全局总订单。

        但是，在不同服务器上同时执行的事务之间可能存在冲突。通过在称为认证的过程中检查并比较两个不同并发事务的写集，可以检测到此类冲突。在认证过程中，冲突检测是在行级别执行的：如果在不同服务器上执行的两个并发事务更新同一行，则存在冲突。冲突解决过程指出，已首先订购的事务在所有服务器上提交，而已订购第二的事务中止，因此在原始服务器上回滚并由组中的其他服务器丢弃。例如，如果t1和t2在不同的站点上同时执行，都更改了同一行，并且t2在t1之前排序，则t2赢得了冲突，并且t1被回滚。这实际上是一个分布式的首次提交胜出规则。请注意，如果两个事务之间的冲突经常发生，那么在同一个服务器上启动它们是一个好习惯，在那里，它们有机会在本地锁管理器上进行同步，而不必由于认证而回滚。

        对于应用和外部化已认证的交易，如果不破坏一致性和有效性，组复制允许服务器偏离交易的约定顺序。组复制是最终的一致性系统，这意味着一旦传入流量减慢或停止，所有组成员将具有相同的数据内容。当流量在流动时，可以按略有不同的顺序对事务进行外部化，或者对某些成员先进行外部化。例如，在多主要模式下，尽管尚未应用全局顺序中较早的远程事务，但是本地事务可能会在认证后立即被外部化。当证明过程确定交易之间没有冲突时，这是允许的。在单主模式下，在主服务器上，并发，无冲突的本地事务以与组复制所同意的全局顺序不同的顺序进行提交和外部化的可能性很小。在不接受来自客户端的写操作的辅助服务器上，事务始终按照约定的顺序进行提交和外部化。

        下图描述了MySQL组复制协议，通过将其与MySQL复制（甚至MySQL半同步复制）进行比较，您可以看到一些区别。请注意，为清楚起见，此图中缺少一些基本的共识和Paxos相关的消息。

6 主从延时排查方法

        譬如用户升级条件达到了，主库也成功更新了用户状态，可在生产环境高峰期，这个时候，主从复制延时了，从库在高峰期时候却没更新。导致用户在手机app界面上显示的等级还是原来的。

主从延时排查方法

        MySQL 有主从同步的状态信息，可以通过MySQL命令 show slave status获取，除了获知当前是否主从同步正常工作，另外一个重要指标就是 Seconds_Behind_Master，根据输出的Seconds_Behind_Master参数的值来判断：NULL，表示io_thread或是sql_thread有任何一个发生故障；0，该值为零，表示主从复制良好；正值，表示主从已经出现延时，数字越大表示从库延迟越严重。

mysql数据库从库同步的延迟问题:

1）相关参数：

首先在服务器上执行show slave satus;可以看到很多同步的参数：　

Master_Log_File：                      SLAVE中的I/O线程当前正在读取的主服务器二进制日志文件的名称

Read_Master_Log_Pos：        在当前的主服务器二进制日志中，SLAVE中的I/O线程已经读取的位置

Relay_Log_File：                        SQL线程当前正在读取和执行的中继日志文件的名称

Relay_Log_Pos：                        在当前的中继日志中，SQL线程已读取和执行的位置

Relay_Master_Log_File：      由SQL线程执行的包含多数近期事件的主服务器二进制日志文件的名称

Slave_IO_Running：                 I/O线程是否被启动并成功地连接到主服务器上

Slave_SQL_Running：              SQL线程是否被启动

Seconds_Behind_Master：     从属服务器SQL线程和从属服务器I/O线程之间的时间差距，单位以秒计。

        从库同步延迟情况出现的● show slave status显示参数Seconds_Behind_Master不为0，这个数值可能会很大

        ● show slave status显示参数Relay_Master_Log_File和Master_Log_File显示bin-log的编号相差很大，说明bin-log在从库上没有及时同步，所以近期执行的bin-log和当前IO线程所读的bin-log相差很大

        ● mysql的从库数据目录下存在大量mysql-relay-log日志，该日志同步完成之后就会被系统自动删除，存在大量日志，说明主从同步延迟很厉害

判断主从延时，通常有两个方法：

        1. Seconds_Behind_Master  vs  2. mk-heartbeat，下面具体说下两者在实现功能的差别。

可以通过监控show slave status\G命令输出的Seconds_Behind_Master参数的值来判断，是否有发生主从延时。
其值有这么几种：
        NULL - 表示io_thread或是sql_thread有任何一个发生故障，也就是该线程的Running状态是No,而非Yes.
        0 - 该值为零，是我们极为渴望看到的情况，表示主从复制良好，可以认为lag不存在。
        正值 - 表示主从已经出现延时，数字越大表示从库落后主库越多。
        负值 - 几乎很少见，只是听一些资深的DBA说见过，其实，这是一个BUG值，该参数是不支持负值的，也就是不应该出现。

        Seconds_Behind_Master是通过比较sql_thread执行的event的timestamp和io_thread复制好的 event的timestamp(简写为ts)进行比较，而得到的这么一个差值。我们都知道的relay-log和主库的bin-log里面的内容完全一 样，在记录sql语句的同时会被记录上当时的ts，所以比较参考的值来自于binlog，其实主从没有必要与NTP进行同步，也就是说无需保证主从时钟的 一致。你也会发现，其实比较真正是发生在io_thread与sql_thread之间，而io_thread才真正与主库有关联，于是，问题就出来了， 当主库I/O负载很大或是网络阻塞，io_thread不能及时复制binlog（没有中断，也在复制），而sql_thread一直都能跟上 io_thread的脚本，这时Seconds_Behind_Master的值是0，也就是我们认为的无延时，但是，实际上不是，你懂得。这也就是为什 么大家要批判用这个参数来监控数据库是否发生延时不准的原因，但是这个值并不是总是不准，如果当io_thread与master网络很好的情况下，那么 该值也是很有价值的。（就好比：妈–儿子–媳妇的关系，妈与儿子亲人，媳妇和儿子也亲人，不见得媳妇与妈就很亲。开个玩笑:-）之前，提到 Seconds_Behind_Master这个参数会有负值出现，我们已经知道该值是io_thread的最近跟新的ts与sql_thread执行到 的ts差值，前者始终是大于后者的，唯一的肯能就是某个event的ts发生了错误，比之前的小了，那么当这种情况发生时，负值出现就成为可能。

方法2. mk-heartbeat，Maatkit万能工具包中的一个工具，被认为可以准确判断复制延时的方法。

        mk-heartbeat的实现也是借助timestmp的比较实现的，它首先需要保证主从服务器必须要保持一致，通过与相同的一个NTP server同步时钟。它需要在主库上创建一个heartbeat的表，里面至少有id与ts两个字段，id为server_id，ts就是当前的时间戳 now()，该结构也会被复制到从库上，表建好以后，会在主库上以后台进程的模式去执行一行更新操作的命令，定期去向表中的插入数据，这个周期默认为1 秒，同时从库也会在后台执行一个监控命令，与主库保持一致的周期去比较，复制过来记录的ts值与主库上的同一条ts值，差值为0表示无延时，差值越大表示 延时的秒数越多。我们都知道复制是异步的ts不肯完全一致，所以该工具允许半秒的差距，在这之内的差异都可忽略认为无延时。这个工具就是通过实打实的复 制，巧妙的借用timestamp来检查延时，赞一个！

7 主从延迟解决方案

        凡是采用读写分离架构，就会有同步延迟问题，我们只能想办法去克服这个问题。

        分库，将一个主库拆分为多个主库，（可以是多主一从）这样每个主库的写并发会减少。单个库读写分离，一主多从，主写从读，分散压力。这样从库压力比主库高，保护主库。、MySQL 支持的并行复制，多个库并行复制。但要是单库写入并发太高，并行复制并没有意义。升级Slave硬件配置

• 解决主从赋值延迟有以下几种常用的方法：
• 1、写操作后的读操作指定发给数据库主服务器

例如，注册账号完成后，登录时读取账号的读操作也发给数据库主服务器。这种方式和业务强绑定，对业务的侵入和影响较大，如果哪个新来的程序员不知道这样写代码，就会导致一个 bug。

• 2、读取从机失败后再读一次主机

这就是通常所说的“二次读取”，二次读取和业务无绑定，只需要对底层数据库访问的 API 进行封装即可，实现代价较小，不足之处在于如果有很多二次读取，将大大增加主机的读操作压力。例如，黑客暴力破解账号，会导致大量的二次读取操作，主机可能顶不住读操作的压力从而崩溃。

• 3、关键业务读写操作全部指向主机，非关键业务采用读写分离

例如，对于一个用户管理系统来说，注册 + 登录的业务读写操作全部访问主机，用户的介绍、爱好、等级等业务，可以采用读写分离，因为即使用户改了自己的自我介绍，在查询时却看到了自我介绍还是旧的，业务影响与不能登录相比就小很多，还可以忍受

针对不同的场景如何解决主从延迟

        正常的延迟应保持在100ms以内，20ms左右是比较理想的。但是，程序员用到主从库，心里就要有个意识，延迟随时可能发生，甚至会非常严重，在程序设计与开发的时候，要考虑到这个因素。

        场景1：写后立即读。在一个请求中，写入数据后，立即数据库中读取数据。这种读的场景，写入和读取的时间间隔小于1ms，比正常的主从延迟时间更短，解决方案就是强制读主库。

        场景2：读后写。这种典型场景就是insertOrUpdate，不存在插入，存在就更新。如果两个请求的时间间隔小于主从延迟的时间间隔，第二次的请求因为读不到，就会执行insert。当然可以用强制读主库的方式解决问题，但是这样增加了主库的读压力。正确的解决方案是分布式锁+缓存。

step1：加分布式锁。防止同时对一条记录操作。

step2：缓存中读数据。如果读到数据，执行step4。如果读不到数据，执行step3。

step3：从库中读数据。

step4：写库。

step5：写缓存。设置缓存的有效期，缓存的有效期，即为允许的主从最大延迟时间。

 数据同步写入从库

        主从复制模式，一般都是异步写数据到从库，当然这个异步也可以设置为同步，只有当从库写完成，主库上的写请求才能返回。

这种方案是最佳单也是最有效的一种，但也是性能最差的一种，尤其是有大量从库的情况下，严重影响请求效率。

 缓存（中间件）路由法

        写请求时缓存记录一个key，这个key的失效时间设置为主从同步的延时，读请求的时候先去缓存中确认是否存在key，如果key存在说明发生了写请求，数据未同步到从库，这时走主库即可，若不存在这个key，直接走从库的查询即可。

中间件应该也是可以判断是否同步完成，与使用缓存记录类似。

        这种方案最大的弊端是引入了缓存，系统复杂度上升。

 选择性强制读主库

        对于一些特殊的业务场景，采用强制读主库。

弊端，需要把每一个这种情况都找出来，设置成强制走主库。

等GTID 方案

        MySQL 在执行完事务后，会将该事务的 GTID 会给客户端，然后客户端可以使用该命令去要执行读操作的从库中执行，等待该 GTID，等待成功后，再执行读操作；如果等待超时，则去主库执行读操作，或者再换一个从库执行上述流程。

        MariaDB 的 MaxScale 就是使用该方案，MaxScale 是 MariaDB 开发的一个数据库智能代理服务(也支持 MySQL)，允许根据数据库 SQL 语句将请求转向目标一个到多个服务器，可设定各种复杂程度的转向规则。

以不变应万变

        有延迟就有延迟，对数据强一致性要求不高的场景可以放任不管。

 主从延迟

• 只能数据分片，把数据量做小

主从同步适用场景

        推荐在读 >> 写，且读时对数据时效性要求不高时采用。所以可以考虑用MySQL的并行复制，但问题是那是库级别的并行，所以有时作用不是很大。

主从延迟严重解决方案

1. 分库 : 将一个主库拆分，每个主库的写并发就降低了，主从延迟即可忽略不计
2. 打开MySQL支持的并行复制，多个库并行复制，若某个库的写入并发特别高，写并发达到了2000/s，并行复制还是没意义。二八法则，很多时候比如说，就是少数的几个订单表，写入了2000/s，其他几十个表10/s。 从库开启多线程，并行读取relay log中不同库的日志，然后并行重放不同库的日志，这是库级别的并行。
3. 重构代码 : 重构代码，插入数据后，直接更新，不查询
4. 若确实存在必须先插入，立马要求查询，然后立马就反过来执行一些操作，对这个查询设置直连主库(不推荐，这会导致读写分离失去意义)

其他反面考虑数据库从库同步的延迟解决方案

1)、架构方面

        1.业务的持久化层的实现采用分库架构，mysql服务可平行扩展，分散压力。

        2.单个库读写分离，一主多从，主写从读，分散压力。这样从库压力比主库高，保护主库。

        3.服务的基础架构在业务和mysql之间加入memcache或者redis的cache层。降低mysql的读压力。

        4.不同业务的mysql物理上放在不同机器，分散压力。

        5.使用比主库更好的硬件设备作为slave总结，mysql压力小，延迟自然会变小。

2)、硬件方面

        1.采用好服务器，比如4u比2u性能明显好，2u比1u性能明显好。

        2.存储用ssd或者盘阵或者san，提升随机写的性能。

        3.主从间保证处在同一个交换机下面，并且是万兆环境。

总结，硬件强劲，延迟自然会变小。一句话，缩小延迟的解决方案就是花钱和花时间。

3)、mysql主从同步加速

        1、sync_binlog在slave端设置为0

        2、–logs-slave-updates 从服务器从主服务器接收到的更新不记入它的二进制日志。

        3、直接禁用slave端的binlog

        4、slave端，如果使用的存储引擎是innodb,innodb_flush_log_at_trx_commit =2

4)、从文件系统本身属性角度优化 

        master端修改linux、Unix文件系统中文件的etime属性， 由于每当读文件时OS都会将读取操作发生的时间回写到磁盘上，对于读操作频繁的数据库文件来说这是没必要的，只会增加磁盘系统的负担影响I/O性能。可以通过设置文件系统的mount属性，组织操作系统写atime信息，在linux上的操作为：打开/etc/fstab，加上noatime参数/dev/sdb1 /data reiserfs noatime 1 2然后重新mount文件系统#mount -oremount /data

5)、同步参数调整主库是写，对数据安全性较高，比如sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之类的设置是需要的而slave则不需要这么高的数据安全，完全可以讲sync_binlog设置为0或者关闭binlog，innodb_flushlog也可以设置为0来提高sql的执行效率

        1、sync_binlog=1 oMySQL提供一个sync_binlog参数来控制数据库的binlog刷到磁盘上去。默认，sync_binlog=0，表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新。这时候的性能是最好的，但是风险也是最大的。一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。

        如果sync_binlog>0，表示每sync_binlog次事务提交，MySQL调用文件系统的刷新操作将缓存刷下去。最安全的就是sync_binlog=1了，表示每次事务提交，MySQL都会把binlog刷下去，是最安全但是性能损耗最大的设置。这样的话，在数据库所在的主机操作系统损坏或者突然掉电的情况下，系统才有可能丢失1个事务的数据。但是binlog虽然是顺序IO，但是设置sync_binlog=1，多个事务同时提交，同样很大的影响MySQL和IO性能。虽然可以通过group commit的补丁缓解，但是刷新的频率过高对IO的影响也非常大。

        对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。所以很多MySQL DBA设置的sync_binlog并不是最安全的1，而是2或者是0。这样牺牲一定的一致性，可以获得更高的并发和性能。默认情况下，并不是每次写入时都将binlog与硬盘同步。因此如果操作系统或机器(不仅仅是MySQL服务器)崩溃，有可能binlog中最后的语句丢失了。要想防止这种情况，你可以使用sync_binlog全局变量(1是最安全的值，但也是最慢的)，使binlog在每N次binlog写入后与硬盘同步。即使sync_binlog设置为1,出现崩溃时，也有可能表内容和binlog内容之间存在不一致性。

        2、innodb_flush_log_at_trx_commit （这个很管用）抱怨Innodb比MyISAM慢 100倍？那么你大概是忘了调整这个值。默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的。特别是使用电池供电缓存（Battery backed up cache）时。设成2对于很多运用，特别是从MyISAM表转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬 盘，所以你一般不会丢失超过1-2秒的更新。设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统 挂了时才可能丢数据。

3、ls(1) 命令可用来列出文件的 atime、ctime 和 mtime。

        atime 文件的access time 在读取文件或者执行文件时更改的ctime 文件的create time 在写入文件，更改所有者，权限或链接设置时随inode的内容更改而更改mtime 文件的modified time 在写入文件时随文件内容的更改而更改ls -lc filename 列出文件的 ctimels -lu filename 列出文件的 atimels -l filename 列出文件的 mtimestat filename 列出atime，mtime，ctimeatime不一定在访问文件之后被修改因为：使用ext3文件系统的时候，如果在mount的时候使用了noatime参数那么就不会更新atime信息。这三个time stamp都放在 inode 中.如果mtime,atime 修改,inode 就一定会改, 既然 inode 改了,那ctime也就跟着改了.之所以在 mount option 中使用 noatime, 就是不想file system 做太多的修改, 而改善读取效能

 

MySql数据库从库同步其他问题及解决方案

        1)、mysql主从复制存在的问题：  ● 主库宕机后，数据可能丢失  ● 从库只有一个sql Thread，主库写压力大，复制很可能延时2)、解决方法：  ● 半同步复制---解决数据丢失的问题  ● 并行复制----解决从库复制延迟的问题

        3)、半同步复制mysql semi-sync（半同步复制）半同步复制：  ● 5.5集成到mysql，以插件的形式存在，需要单独安装  ● 确保事务提交后binlog至少传输到一个从库  ● 不保证从库应用完这个事务的binlog  ● 性能有一定的降低，响应时间会更长  ● 网络异常或从库宕机，卡主主库，直到超时或从库恢复4)、主从复制--异步复制原理、半同步复制和并行复制原理比较

a、异步复制原理：

b、半同步复制原理：

        事务在主库写完binlog后需要从库返回一个已接受，才放回给客户端；5.5集成到mysql，以插件的形式存在，需要单独安装确保事务提交后binlog至少传输到一个从库不保证从库应用完成这个事务的binlog性能有一定的降低网络异常或从库宕机，卡主库，直到超时或从库恢复

        c、并行复制mysql并行复制  ● 社区版5.6中新增  ● 并行是指从库多线程apply binlog  ● 库级别并行应用binlog，同一个库数据更改还是串行的(5.7版并行复制基于事务组)设置set global slave_parallel_workers=10;设置sql线程数为10

        原理：从库多线程apply binlog在社区5.6中新增库级别并行应用binlog，同一个库数据更改还是串行的5.7版本并行复制基于事务组

Mysql 中主库跑太快，从库追不上怎么整？

        随着日益增长的访问量，单台数据库的应接能力已经捉襟见肘。因此采用主库写数据，从库读数据这种将读写分离开的主从架构便随之衍生了出来。

在生产环境中，常见的主从架构有很多种，在这里给大家介绍几种比较常见的架构模式。

        对于主从来说，通常的操作是主库用来写入数据，从库用来读取数据。这样的好处是通过将读写压力分散开，避免了所有的请求都打在主库上。同时通过从库进行水平扩展使系统的伸缩性及负载能力也得到了很大的提升。

Mysql 中主从复制时有两个很重要的日志文件：

• binlog（二进制日志文件）
• relay log（中继日志文件）
• 

        在主从同步的过程中，主库会将所有的操作事件记录在 binlog 中，从库通过开启一个 I/O 线程保持与主库的通信，并在一定时间间隔内探测 binlog 日志文件是否发生改变。如果 binlog 日志发生了变化，主库生成一个 binlog dump 线程向从库 I/O 线程传送 binlog。从库上的 I/O 线程将 binlog 复制到自己的 relay log 中。最终由从库中的 SQL 线程读取 relay log 中的事件重放到从库上。

主从延迟原因

        上面的流程我们已经知道了主从复制的相关过程了，但是主库有更新就会同步从库，那为什么会出现主从延迟的情况呢？

随机重放

        Mysql 主库中写 binlog 的操作是顺序写的，之前我们提到过，磁盘的顺序读写速度是很快的。同样的，从库中的 I/O 线程操作日志的速度效率也是很高的。但是别忘了，还有一个 SQL 线程来进行数据重放，而重放的过程是随机写盘的。到这里你应该就明白了吧，某一时刻 relay log 里的数据来不及重放进从库，就会产生主从延迟的情况。

主库并发高

        知道了从库中 SQL 线程的重放情况，对于主库并发高导致主从延迟肯定就不难理解了。某一时刻，大量写请求打到主库上，意味着要不断对 binlog 进行写入，此时从库中的 SQL 线程就会应接不暇，自然会产生主从延迟。

锁等待

        对于 SQL 单线程来说，当遇到阻塞时就会一直等待，直到执行成功才会继续进行。如果某一时刻从库因为查询产生了锁等待的情况，此时只有当前的操作执行完成后才会进行下面的操作，同理也就产生了主从延迟的情况。

主从延迟处理

        知道了主从延迟的原因，接下来我们看看如何来进行处理。

并行复制

        既然 SQL 单线程进行重放时速度有限，那么能不能采用多线程的方式来进行重放呢？MySQL 5.6 版本后，提供了一种并行复制的方式，通过将 SQL 线程转换为多个 work 线程来进行重放，这样就解决了主从延迟的问题。

        你可能会说了，我现在用的低版本的数据库，也没法升版本啊，那我怎么整。对于主库并发高的情况，这种方式你只能通过控制并发来解决延迟了，多用用 Redis。

读主库

        这种情况你肯定不陌生，对于一些实时性要求比较高的数据，你总不能读从库去拿吧，万一延迟个大半天，你不得贡献自己的年终奖啊。

        每次请求打到这个系统后：

        读请求，直接读从库

        写请求，先写入主库，然后主库将数据同步到其他从库

        在高并发或者网络状况不理想时，写完数据后，主库还没来得及将数据同步到从库，其他读请求去读从库，发现从库中的数据仍然是旧数据。这就是读写分离数据库数据不一致的根本原因。

下面给出两种方案去解决这个问题：

缓存标记法

上图流程：

        1）A发起写请求，更新了主库，再在缓存中设置一个标记，代表此数据已经更新，标记格式（ 用户ID+业务ID+其他业务维度）

        2）设置此标记，要加上过期时间，可以为预估的主库和从库同步延迟的时间

        3）B发起读请求的时候，先判断此请求的业务在缓存中有没有更新标记

        4）如果存在标记，走主库；如果没有走从库。

这个方案就有效了解决了数据不一致的问题。

        但这个方案会有个严重的问题，也就是每次的读请求都要到缓存中去判断是否存在缓存标记，如果是单机部署用的是jvm缓存，对性能还好；但如果是集群部署缓存肯定用redis，每次读都要和redis进行交互，这样肯定会影响系统吞吐量。

技术代码层面解决办法

        那么解决这个问题可以用下面这个方案

        每次请求打到这个系统后：读请求，直接读从库；写请求，先写入主库，然后主库将数据同步到其他从库

        在高并发或者网络状况不理想时，写完数据后，主库还没来得及将数据同步到从库，其他读请求去读从库，发现从库中的数据仍然是旧数据。这就是读写分离数据库数据不一致的根本原因。

下面给出两种方案去解决这个问题：

缓存标记法

上图流程：

1）A发起写请求，更新了主库，再在缓存中设置一个标记，代表此数据已经更新，标记格式（ 用户ID+业务ID+其他业务维度）

2）设置此标记，要加上过期时间，可以为预估的主库和从库同步延迟的时间

3）B发起读请求的时候，先判断此请求的业务在缓存中有没有更新标记

4）如果存在标记，走主库；如果没有走从库。

这个方案就有效了解决了数据不一致的问题。

        但这个方案会有个严重的问题，也就是每次的读请求都要到缓存中去判断是否存在缓存标记，如果是单机部署用的是jvm缓存，对性能还好；但如果是集群部署缓存肯定用redis，每次读都要和redis进行交互，这样肯定会影响系统吞吐量。

那么解决这个问题可以用下面这个方案

本地缓存标记

        1）用户A发起写请求，更新了主库，并在客户端设置标记，过期时间（预估的主库和从库同步延迟的时间），可以使用cookie实现

        2）用户A再发起读请求时，带上这个cookie

        3）服务器处理请求时，获取请求传过来的数据，看有没有这个标记

        4）有这个业务标记，走主库；没有走从库。

        这个方案就保证了用户A的读请求肯定是数据一致的，而且没有性能问题，因为标记是本地客户端传过去的。

但是无法保证其他用户读数据是一致的，但是实际场景很少需要保持其他用户也保持强一致。延迟个几秒也没问题。

总结

主从复制原理

• 主从复制中有两个很重要的日志文件，binlog和relay log，分别位于主库与从库中。其中 binlog 是主从复制的基础，通过将操作事件写入 binlog 通过 I/O 线程传送至从库进行同步。

主从延迟原因

• 从库中 SQL 线程重放的过程是随机写盘的，并且 SQL 线程是单线程的，因此数据来不及重放的话就会导致主从延迟。
• 主库并发高会导致写操作不断写入 binlog，对于 SQL 线程说可能会应接不暇，也会产生主从延迟。
• 重放过程中如果遇到锁等待也是产生延迟的原因之一。

主从延迟处理

• MySQL 5.6版本以后通过并行复制的方式来解决 SQL 单线程产生的主从延迟问题。对于低版本来说，可以通过降低主库的并发来解决。如果对数据实时性要求比较严格的话，可以通过读主库来达到目的。

        数据库读写分离主要解决高并发时，提高系统的吞吐量。因为大部分场景下都是读多写少。下图是数据库读写分离模型。

8如何实现主从复制

        我这里用三台虚拟机(Linux)演示，IP分别是104(Master)，106(Slave)，107(Slave)。

预期的效果是一主二从，如下图所示：

 

mysql -u root -p

        接着输入root用户的密码(密码忘记的话就网上查一下重置密码吧~)，然后创建用户：

//192.168.0.106是slave从机的IP

        GRANT REPLICATION SLAVE ON *.* to 'root'@'192.168.0.106' identified by 'Java@1234';

//192.168.0.107是slave从机的IP

        GRANT REPLICATION SLAVE ON *.* to 'root'@'192.168.0.107' identified by 'Java@1234';

//刷新系统权限表的配置

        FLUSH PRIVILEGES;

创建的这两个用户在配置slave从机时要用到。

接下来在找到mysql的配置文件/etc/my.cnf，增加以下配置：

# 开启binlog

        log-bin=mysql-bin

        server-id=104

# 需要同步的数据库，如果不配置则同步全部数据库

        binlog-do-db=test_db

# binlog日志保留的天数，清除超过10天的日志

# 防止日志文件过大，导致磁盘空间不足

        expire-logs-days=10

配置完成后，重启mysql：

        service mysql restart

可以通过命令行show master status\G;查看当前binlog日志的信息(后面有用)：

Slave配置

        Slave配置相对简单一点。从机肯定也是一台MySQL服务器，所以和Master一样，找到/etc/my.cnf配置文件，增加以下配置：

# 不要和其他mysql服务id重复即可

server-id=106

接着使用命令行登录到mysql服务器：

mysql -u root -p

然后输入密码登录进去。

进入到mysql后，再输入以下命令：

CHANGE MASTER TO

MASTER_HOST='192.168.0.104',//主机IP

MASTER_USER='root',//之前创建的用户账号

MASTER_PASSWORD='Java@1234',//之前创建的用户密码

MASTER_LOG_FILE='mysql-bin.000001',//master主机的binlog日志名称

MASTER_LOG_POS=862,//binlog日志偏移量

master_port=3306;//端口

还没完，设置完之后需要启动：

# 启动slave服务

start slave;

启动完之后怎么校验是否启动成功呢？使用以下命令：

show slave status\G;

可以看到如下信息（摘取部分关键信息）：

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.0.104

                  Master_User: root

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000001

          Read_Master_Log_Pos: 619

               Relay_Log_File: mysqld-relay-bin.000001

                Relay_Log_Pos: 782

        Relay_Master_Log_File: mysql-bin.000001 //binlog日志文件名称

             Slave_IO_Running: Yes //Slave_IO线程、SQL线程都在运行

            Slave_SQL_Running: Yes

             Master_Server_Id: 104 //master主机的服务id

                  Master_UUID: 0ab6b3a6-e21d-11ea-aaa3-080027f8d623

             Master_Info_File: /var/lib/mysql/master.info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

           Master_Retry_Count: 86400

                Auto_Position: 0

另一台slave从机配置一样，不再赘述。

测试主从复制

在master主机执行sql：

CREATE TABLE `tb_commodity_info` (

  `id` varchar(32) NOT NULL,

  `commodity_name` varchar(512) DEFAULT NULL COMMENT '商品名称',

  `commodity_price` varchar(36) DEFAULT '0' COMMENT '商品价格',

  `number` int(10) DEFAULT '0' COMMENT '商品数量',

  `description` varchar(2048) DEFAULT '' COMMENT '商品描述',

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品信息表';

接着我们可以看到两台slave从机同步也创建了商品信息表：

主从复制就完成了

具体参数可参考:

MySql数据库主从复制的实现和原理解析_mysql主从复制-CSDN博客

https://zhuanlan.zhihu.com/p/335142300

** 两台服务器，mysql版本必须一致，不然会出现问题****

注意：在进行主从同步之前先确保要备份的数据库和表在从库存在，主从复制不会创建库和表，如果没有会出现不能同步的错误，可以自己通过mysqldump进行数据导出，然后再用source进行数据恢复到从库中去。

*注意事项：如果主库里面有初始化数据，那么在主从复制前需要把数据先导入到从库以保证初始一致性！*

【关于验证主从复制是否有效，方法是，往主服务器mysql里写数据，然后在从服务器查询数据】

上面的日志文件位置是需要手动指定的，实际生产环境中可能出现二进制文件非常多的情况，再用手动指定会很不方便下面介绍如自动指定文件位置

【一主一从-主从同步配置，自动指定二进制文件位置】

操作步骤跟上一致，只是参数有所变化，主从服务同时开启gtid模式

9 如何处理主从部署无法高可用

        9.1.1 为什么要高可用

                1、读写分离，提升读的处理能力 2、故障转移，提供 failover 能力

加上业务侧连接池的心跳重试，实现断线重连，业务不间断，降低RTO和RPO。

高可用意味着，更少的不可服务时间。一般用SLA/SLO衡量。

1年 = 365天 = 8760小时

99 = 8760 * 1% = 8760 * 0.01 = 87.6小时

99.9 = 8760 * 0.1% = 8760 * 0.001 = 8.76小时

99.99 = 8760 * 0.0001 = 0.876小时 = 0.876 * 60 = 52.6分钟

99.999 = 8760 * 0.00001 = 0.0876小时 = 0.0876 * 60 = 5.26分钟

9.1.2 failover，故障转移，灾难恢复

        容灾：热备与冷备 对于主从来说，就是主挂了，某一个从，变成主，整个集群来看，正常对外提供服务。 常见的一些策略：

• 多个实例不在一个主机/机架上
• 跨机房和可用区部署
• 两地三中心容灾高可用方案

9.1.3 高可用方案

 主从手动切换

如果主节点挂掉，将某个从改成主；重新配置其他从节点。修改应用数据源配置。 缺点：

1. 可能数据不一致
2. 需要人工干预
3. 代码和配置的侵入性

主从手动切换

用 LVS+Keepalived 实现多个节点的探活+请求路由。 配置 VIP 或 DNS 实现配置不变更。 缺点：

• 手工处理主从切换
• 大量的配置和脚本定义

只能算半自动。

MHA

MHA，Master High Availability，目前在 MySQL 高可用方面是一个相对成熟的解决方案，它由日本 DeNA 公司的 youshimaton（现就职于 Facebook）开发，是一套优秀的作为 MySQL 高可用性环境下故障切换和主从提升的高可用软件。

基于 Perl 语言开发，一般能在30s内实现主从切换。 切换时，直接通过 SSH 复制主节点的日志。

缺点：

• 需要配置 SSH 信息
• 至少3台

 MGR

不借助外部力量，只使用 MySQL 本身。如果主节点挂掉，将自动选择某个从改成主；无需人工干预，基于组复制，保证数据一致性。

缺点：

• 外部获得状态变更需要读取数据库
• 外部需要使用 LVS/VIP 配置

特点：

• 高一致性 基于分布式Paxos协议实现组复制，保证数据一致性
• 高容错性 自动检测机制，只要不是大多数节点都宕机就可继续工作，内置防脑裂保护机制
• 高扩展性 节点的增加与移除会自动更新组成员信息，新节点加入后，自动从其他节点同步增量数据，直到与其他节点数据一致
• 高灵活性 提供单主模式和多主模式，单主模式在主库宕机后能够自动选主，所有写入都在主节点进行，多主模式支持多节点写入

适用场景：

• 弹性复制 需要非常流畅的复制基础架构的环境，其中服务器的数量必须动态地增长或缩减，而最少尽可能的痛苦。
• 

• 高可用分片 Sharding is a popular approach to achieve write scale-out. Users can use MySQL Group Replication to implement highly available shards. Each shard can map into a Replication Group. 分片是实现写横向扩展的一种流行方法。用户可以使用MySQL组复制来实现高度可用的分片。每个分片可以映射到副本组。
• 

 MySQL Cluster

完整的数据库层高可用解决方案。 MySQL InnoDB Cluster是一个高可用的框架，构成组件：

• MySQL Group Replication 提供DB的扩展、自动故障转移
• MySQL Router 轻量级中间件，提供应用程序连接目标的故障转移。MySQL Router是一个轻量级的中间件，可以提供负载均衡和应用连接的故障转移。它是MySQL团队为MGR量身打造的，通过使用Router和Shell,用户可以利用MGR实现完整的数据库层的解决方案。如果您在使用MGR，请一定配合使用Router和Shell，可以理解为它们是为MGR而生的，会配合MySQl 的开发路线图发展的工具。
• MySQL Shell 新的MySQL客户端，多种接口模式。可以设置群组复制及Router。MySQL Shell是MySQL团队打造的一个统一的客户端， 它可以对MySQL执行数据操作和管理。它支持通过JavaScript，Python，SQL对关系型数据模式和文档型数据模式进行操作。使用它可以轻松配置管理InnoDB Cluster。
• 

如果主节点挂掉，将某个从改成主。 一款MySQL高可用和复制拓扑管理工具，支持复制拓扑结构的调整，自动故障转移和手动主从切换等。后端数据库用MySQL或SQLite存储元数据，并提供Web界面展示MySQl 复制的拓扑关系及状态，通过Web可更改MySQL实例的复制关系和部分配置信息，同时也提供命令行和API接口，方便运维管理。

特点:

1. 自动发现MySQL的复 制拓扑，并且在web.上展示;
2. 重构复制关系， 可以在web进行拖图来进行复制关系变更;
3. 检测主异常，并可以自动或手动恢复，通过Hooks进行自定义脚本;
4. 支持命令行和web界面管理复制。

基于 Go 语言开发，实现了中间件本身的高可用。

两种部署方式 orchestrator/raft：

1. 数据一致性由orchestrator的raft协议保证
2. 数据库之间不通信 orchestrator/[galera | xtradb cluster | innodb cluster]:
3. 数据一致性由数据库集群保证
4. 数据库结点之间通信

如果不部署client

1. 使用HTTP (/api/leader-check)查询并路由到主节点

优势： 能直接在 UI 界面 拖拽改变主从关系

10 主从同步的过程

MySQL主从同步

        MySQL主从同步是一种常用的数据库复制技术，用于将一个MySQL数据库（主数据库）的数据变更同步到另一个MySQL数据库（从数据库）上。

这种复制技术可以实现数据备份、读写分离、容灾等功能。

主从同步的过程

        1. 主库记录二进制日志（Binary Log）

        主数据库将所有数据更改操作（如插入、更新、删除等）都记录在二进制日志中。

二进制日志包含了数据库的逻辑修改，以便在从库上重现这些操作。

2. 从库连接主库并请求同步

        从数据库连接到主数据库，并请求开始同步过程。从库会询问主库，从哪个位置的二进制日志开始同步。

3. 主库发送二进制日志

        主数据库根据从库的请求，将二进制日志的内容发送给从库。

4. 从库应用二进制日志

        从数据库接收到主库发送的二进制日志后，从库会按照接收到的顺序逐条应用这些日志中的数据修改操作，使得从库的数据与主库保持同步。

5. 从库定期确认同步位置

        从库在同步过程中会记录自己已经成功同步的位置，以便在发生断线或重新连接时，能供从上次同步的位置继续。

全同步和半同步

        这两种同步方式指的是从库在复制数据时，主库和从库之间的数据一致性保障程序不同。

全同步（Fully Synchronous）

        在全同步模式下，当事务在主库上提交时，主库会等待所有的从库都将日志写入它们的中继日志（Relay Log）并应用完成后，主库才会认为事务提交成功。

这确保了主从数据的完全一致性，但也导致主库的写入性能降低，因为主库需要等待从库的确认。

半同步（Semi-Synchronous）

        半同步模式下，当事务在主库上提交时，主库只等待至少一个从库将日志写入它们的中继日志并应用完成后，主库就认为事务提交成功。这样可以提高主库的写入性能，但可能会导致主从数据稍有延迟，因为并非所有从库都已经同步完成。

注意

        需要注意的是，全同步模式相对于半同步模式来说，数据的一致性更高，但性能代价也更大。具体选择哪种模式取决于对数据一致性和性能的需求权衡。

在关键业务场景下，可能更倾向于全同步模式，而在某些读写分离的场景下，可以考虑半同步模式。

11 Mysql 主从复制主要用途

l 读写分离

        在开发工作中，有时候会遇见某个sql 语句需要锁表，导致暂时不能使用读的服务，这样就会影响现有业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。

l 数据实时备份，当系统中某个节点发生故障时，可以方便的故障切换

l 高可用HA

l 架构扩展

        随着系统中业务访问量的增大，如果是单机部署数据库，就会导致I/O访问频率过高。有了主从复制，增加多个数据存储节点，将负载分布在多个从节点上，降低单机磁盘I/O访问的频率，提高单个机器的I/O性能。

• MySQL 主从形式

一主一从

一主多从，提高系统的读性能

一主一从和一主多从是最常见的主从架构，实施起来简单并且有效，不仅可以实现HA，而且还能读写分离，进而提升集群的并发能力。

多主一从 （从5.7开始支持）

多主一从可以将多个mysql数据库备份到一台存储性能比较好的服务器上。

双主复制

级联复制

        级联复制模式下，部分slave的数据同步不连接主节点，而是连接从节点。因为如果主节点有太多的从节点，就会损耗一部分性能用于replication，那么我们可以让3~5个从节点连接主节点，其它从节点作为二级或者三级与从节点连接，这样不仅可以缓解主节点的压力，并且对数据一致性没有负面影响。

12新一代主从复制模式 - GTID 复制模式#

        在传统的复制里面，当发生故障，需要主从切换，需要找到 Binlog 和 位点信息，恢复完成数据之后将主节点指向新的主节点。在 MySQL 5.6 里面，提供了新的数据恢复思路，只需要知道主节点的 IP、端口以及账号密码就行，因为复制是自动的，MySQL 会通过内部机制 GTID 自动找点同步。基于 GTID 的复制是 MySQL 5.6.5 后新增的复制方式。GTID (global transaction identifier) 即全局事务 ID，一个事务对应一个 GTID，保证了在每个在主库上提交的事务在集群中有一个唯一的 ID。

GTID复制原理

        在原来基于日志的复制中，从库需要告知主库要从哪个偏移量进行增量同步， 如果指定错误会造成数据的遗漏，从而造成数据的不一致。

        而基于 GTID 的复制中，从库会告知主库已经执行的事务的 GTID 的值，然后主库会将所有未执行的事务的 GTID 的列表返回给从库，并且可以保证同一个事务只在指定的从库执行一次，通过全局的事务 ID 确定从库要执行的事务的方式代替了以前需要用 Binlog 和 位点确定从库要执行的事务的方式。

基于 GTID 的复制过程如下：

master 更新数据时，会在事务前产生 GTID，一同记录到 Binlog 日志中。

slave 端的 I/O 线程将变更的 Binlog，写入到本地的 relay log 中,读取值是根据gitd_next变量，告诉我们 slave 下一个执行哪个 GTID。

SQL 线程从 relay log 中获取 GTID，然后对比 slave 端的 Binlog 是否有记录。如果有记录，说明该 GTID 的事务已经执行，slave 会忽略。

如果没有记录，slave 就会从 relay log 中执行该 GTID 的事务，并记录到 Binlog。

在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有二级索引就用全部扫描。

GTID 组成

        ​ GTID = source_id:transaction_id source_id 正常即是 server_uuid，在第一次启动时生成(函数 generate_server_uuid)，并持久化到 DATADIR/auto.cnf 文件里。

transaction_id 是顺序化的序列号(sequence number)，在每台 MySQL 服务器上都是从 1 开始自增长的序列，是事务的唯一标识。

GTID 生成

        GTID 的生成受 gtid_next 控制。

        在 Master 上，gtid_next 是默认的 AUTOMATIC,即 GTID 在每次事务提交时自动生成。它从当前已执行的 GTID 集合(即 gtid_executed)中，找一个大于 0 的未使用的最小值作为下个事务 GTID。在实际的更新事务记录之前将 GTID 写入到 Binlog。

在 Slave 上，从 Binlog 先读取到主库的 GTID(即 set gtid_next 记录)，而后执行的事务采用该 GTID。

GTID 的好处

        GTID 使用 master_auto_position=1 代替了 Binlog 的主从复制方案，相比 Binlog 方式更容易搭建主从复制。

        GTID 方便实现主从之间的 failover（主从切换），不用一步一步的去定位 Binlog日志文件和查找 Binlog 的位点信息。

GTID 模式复制局限性

        在一个事务里面混合使用引擎，如 Innodb(支持事务)、MyISAM(不支持事务)， 造成多个 GTIDs 和同一个事务相关联出错。

        CREATE TABLE…..SELECT 不能使用，该语句产生的两个 Event。 在某一情况会使用同一个 GTID(同一个 GTID 在 slave 只能被使用一次)：

event one：创建表语句 create table

event two ：插入数据语句 insert

CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE 不能在事务内使用 (启用了 –enforce-gtid-consistency 参数)。

使用 GTID 复制从库跳过错误时，不支持 sql_slave_skip_counter 参数的语法。

GTID 主从复制实战

1.Master 主数据库上的操作

在 my.cnf 文件中配置 GTID 主从复制

[root@mysql-master ~]# cp /etc/my.cnf /etc/my.cnf.bak

[root@mysql-master ~]# >/etc/my.cnf

[root@mysql-master ~]# cat /etc/my.cnf

[mysqld]

datadir = /var/lib/mysql

socket = /var/lib/mysql/mysql.sock

symbolic-links = 0

  

log-error = /var/log/mysqld.log

pid-file = /var/run/mysqld/mysqld.pid

  

#GTID:

server_id = 1

gtid_mode = on

enforce_gtid_consistency = on

    

#binlog

log_bin = mysql-bin

log-slave-updates = 1

binlog_format = row

sync-master-info = 1

sync_binlog = 1

   

#relay log

skip_slave_start = 1

配置后，重启 MySQL 服务：

Copy

[root@mysql-master ~]# systemctl restart mysqld

登录 MySQL，并查看 Master 状态， 发现多了一项 Executed_Gtid_Set：

Copy

mysql> show master status;+-------------------+----------+--------------+------------------+-------------------+| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+-------------------+----------+--------------+------------------+-------------------+| mysql-bin.000001 |      154 |              |                  |                   |+-------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)

  

mysql> show global variables like '%uuid%';+---------------+--------------------------------------+| Variable_name | Value                                |+---------------+--------------------------------------+| server_uuid   | 317e2aad-1565-11e9-9c2e-005056ac6820 |+---------------+--------------------------------------+1 row in set (0.00 sec)

查看确认 GTID 功能

mysql> show global variables like '%gtid%';+----------------------------------+-------+| Variable_name                    | Value |+----------------------------------+-------+| binlog_gtid_simple_recovery      | ON    || enforce_gtid_consistency         | ON    || gtid_executed                    |       || gtid_executed_compression_period | 1000  || gtid_mode                        | ON    || gtid_owned                       |       || gtid_purged                      |       || session_track_gtids              | OFF   |+----------------------------------+-------+8 rows in set (0.00 sec)

查看确认 Binlog 日志功能打开:

Copy

mysql> show variables like 'log_bin';+---------------+-------+| Variable_name | Value |+---------------+-------+| log_bin       | ON    |+---------------+-------+1 row in set (0.00 sec)

授权 slave 复制用户，并刷新权限:

Copy

mysql> flush privileges;

Query OK, 0 rows affected (0.04 sec)

  

mysql> show grants for slave@'172.23.3.66';+-------------------------------------------------------------------------------+| Grants for slave@172.23.3.66                                                |+-------------------------------------------------------------------------------+| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'172.23.3.66' |+-------------------------------------------------------------------------------+1 row in set (0.00 sec)

  

再次查看 master 状态:

Copy

mysql> show master status;+-------------------+----------+--------------+------------------+------------------------------------------+| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |+-------------------+----------+--------------+------------------+------------------------------------------+| mysql-bin.000001 |      622 |              |                  | 317e2aad-1565-11e9-9c2e-005056ac6820:1-2 |+-------------------+----------+--------------+------------------+------------------------------------------+1 row in set (0.00 sec)

这里需要注意一下：
启动配置之前，同样需要对从服务器进行初始化。对从服务器初始化的方法基本和基于日志点是相同的，只不过在启动了 GTID 模式后，在备份中所记录的就不是备份时的二进制日志文件名和偏移量了，而是记录的是备份时最后的 GTID 值。
需要先在主数据库机器上把目标库备份一下，假设这里目标库是 slave_test：

Copy

mysql> CREATE DATABASE slave_test CHARACTER SET utf8 COLLATE utf8_general_ci;

Query OK, 1 row affected (0.02 sec)

  

mysql> use slave_test;

Database changed

mysql> create table user (id int(10) PRIMARY KEY AUTO_INCREMENT,name varchar(50) NOT NULL);

Query OK, 0 rows affected (0.27 sec)

  

mysql> insert into slave_test.user values(1,"xiaoming"),(2,"xiaohong"),(3,"xiaolv");   

Query OK, 3 rows affected (0.06 sec)

Records: 3  Duplicates: 0  Warnings: 0

  

mysql> select * from slave_test.user;+----+----------+| id | name     |+----+----------+|  1 | xiaoming ||  2 | xiaohong ||  3 | xiaolv   |+----+----------+3 rows in set (0.00 sec)

把 slave_test 库备份出来:

Copy

[root@mysql-master ~]# mysqldump --single-transaction --master-data=2 --triggers --routines --databases slave_test -uroot -p123456 > /root/user.sql

这里有个版本上的问题：

MySQL 5.6 使用 mysqldump 备份时，指定备份的具体库，使用 --database。

MySQL 5.7 使用 mysqldump 备份时，指定备份的具体库，使用--databases。

然后把备份的/root/user.sql 文件拷贝到 slave 从数据库服务器上。

Copy

[root@mysql-master ~]# rsync -e "ssh -p20" -avpgolr /root/user.sql

到这里主库的操作结束，包含 GTID 的备份数据已经 copy 到从库，下面来进行从库的操作。

2.从库操作

在 my.cnf 文件中配置 GTID 主从复制

与主服务器配置大概一致，除了 server_id 不一致外，从服务器还可以在配置文件里面添加read_only＝on ，使从服务器只能进行读取操作，此参数对超级用户无效，并且不会影响从服务器的复制。

Copy

[root@mysql-slave1 ~]# >/etc/my.cnf

[root@mysql-slave1 ~]# vim /etc/my.cnf

[mysqld]

datadir = /var/lib/mysql

socket = /var/lib/mysql/mysql.sock

  

symbolic-links = 0

  

log-error = /var/log/mysqld.log

pid-file = /var/run/mysqld/mysqld.pid

  

#GTID:

server_id = 2

gtid_mode = on

enforce_gtid_consistency = on

    

#binlog

log_bin = mysql-bin

log-slave-updates = 1

binlog_format = row

sync-master-info = 1

sync_binlog = 1

   

#relay log

skip_slave_start = 1

read_only = on

配置完成后，重启mysql服务。

Copy

[root@mysql-slave1 ~]# systemctl restart mysql

接着将主数据库目标库的备份数据 user.sql导入到从数据库里。

Copy

[root@mysql-slave1 ~]# ls /root/user.sql/root/user.sql

[root@mysql-slave1 ~]# mysql -p123456

.........

mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                |+--------------------+4 rows in set (0.00 sec)

  

mysql> source /root/user.sql;

  

mysql> select * from slave.test;+----+----------+| id | name     |+----+----------+|  1 | xiaoming ||  2 | xiaohong ||  3 | xiaolv   |+----+----------+3 rows in set (0.00 sec)

在从数据库里，使用 change master 配置主从复制:

Copy

mysql> stop slave;

Query OK, 0 rows affected, 1 warning (0.00 sec)

  

mysql> change master to master_host='172.23.3.66',master_user='slave1',master_password='123456',master_auto_position=1;

Query OK, 0 rows affected, 2 warnings (0.26 sec)

  

mysql> start slave;

Query OK, 0 rows affected (0.02 sec)

  

mysql> show slave status \G;*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 172.23.3.66

                  Master_User: slave1

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000001

          Read_Master_Log_Pos: 1357

               Relay_Log_File: mysql-slave1-relay-bin.000002

                Relay_Log_Pos: 417

        Relay_Master_Log_File: mysql-bin.000001

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

................

................

            Executed_Gtid_Set: 317e2aad-1565-11e9-9c2e-005056ac6820:1-5

                Auto_Position: 1

  

由此，Master 和 Slave 节点已经配置了主从同步关系。接下来你可以自行在主库插入一条数据观察从库是否同步过来。

使用 GTID 添加从库有两种方式

直接同步主库所有GTID

        如果主库一开始就开启了 GTID，那么可以直接获取主库的所有GTID来同步至从库。但是如果主库 Binlog 日志太多，那么相应同步的时间也会变长。这种方式适用于小数据量的同步。

使用这种方式来同步相应的命令为：

Copy

mysql>change master to master_host='xxxxxxx',master_user='xxxxxx',master_password='xxxxx',MASTER_AUTO_POSITION=1;

mysql> start slave;

mysql> stop slave io_thread; #重启 io 线程,刷新状态

mysql> start slave io_thread;

        当使用 MASTER_AUTO_POSITION 参数的时候，MASTER_LOG_FILE，MASTER_LOG_POS 参数不能使用。
如果想要从 GTID 配置回 pos，再次执行这条语句，不过把 MASTER_AUTO_POSITION 置为 0。

通过设定范围进行同步

        通过指定 GTID 的范围，然后通过在 slave 设置 @@GLOBAL.GTID_PURGED 从而跳过备份包含的 GTID 。

                这种方案适用于数据量比较大一次同步需要耗费巨量时间的数据。但同时也有操作复杂的问题，需要你记住每次同步的范围。

用这种方式来同步相应的命令为：

Copy

mysql>change master to master_host='xxxxxxx',master_user='xxxxxx',master_password='xxxxx',MASTER_LOG_POS='xxxx';

mysql> start slave;

mysql> stop slave io_thread; #重启 io 线程,刷新状态

mysql> start slave io_thread;

这里注意到我们的参数换了：MASTER_LOG_POS，该参数表示当前需要同步的 GTID 事务的起点值。

总结#

        本篇介绍主从复制的两种形式：基于 Binlog 和 位点信息的传统复制方式；基于 Binlog 和 GTID 的新式复制方式。现在很多公司可能还在使用 MySQL 5.6 的版本，所以 GTID不一定可以使用。

13 多线程复制(主从复制的方式)

        多线程复制（基于库），在MySQL 5.6以前的版本，slave的复制是单线程的，而master是并发写入的，所以延时是避免不了的。唯一有效的方法是把多个库放在多台slave，这样又有点浪费服务器。在MySQL 5.6里面，我们可以把多个表放在多个库，这样就可以使用多线程复制。

但 5.6 中的每个线程只能处理一个数据库，所以如果只有一个数据库，或者绝大多数写操作都是集中在某一个数据库的，那么这个“多线程复制”就不能充分发挥作用了

Mysql 5.7 对 “多线程复制” 进行了改善，可以按照逻辑时钟的方式来分配线程，大大提高了复制性能

14 经验总结

        在日常学习及工作过程中，主从复制方面也积累了一些经验，下面简单分享几点，希望各位少踩坑。

• 主从两端数据库版本尽量保持一致。
• 主从库参数建议相同，比如字符集、sql_mode这类参数要设置一样。
• 从库服务器性能不能过于落后主库，以免因服务器性能产生主从延迟。
• 所有表强制拥有主键，因为无主键表同步到从库极易产生主从延迟。
• 建议从库设为read only，以防人为误操作从库数据。
• 监控主从延迟及状态，及时解决同步中断或延迟问题

这里推荐大家使用GTID模式来搭建主从复制

MySQL主从复制详解-阿里云开发者社区  (参考连接)

. ★踩坑案例(主从延迟问题解决方法)

        其实这块东西我们经常会碰到，就比如说用了mysql主从架构之后，可能会发现，刚写入库的数据结果没查到，结果就完蛋了。。。。

        所以实际上你要【考虑好应该在什么场景下来用这个mysql主从同步，建议是一般在读远远多于写，而且读的时候一般对数据时效性要求没那么高的时候，才用mysql主从同步】

        所以这个时候，我们可以考虑的一个事情就是，【你可以用mysql的并行复制，但问题是那是库级别的并行，所以有时候作用不是很大】

        所以通常来说，对于那种写了之后立马就要保证可以查到的场景，【采用强制读主库的方式】，这样就可以保证你肯定的可以读到数据。【其实用一些数据库中间件是没问题的。】

一般来说，如果主从延迟较为严重：

        1、架构层面：分库，将一个主库拆分为多个。比如将1个库拆为4个主库，每个主库的写并发就500/s，此时主从延迟可以忽略不计。

        2、开启mysql支持的并行复制，多个库并行复制。但如果说某个库的写入并发就是特别高，单库写并发达到了2000/s，并行复制还是没意义。28法则，很多时候比如说，就是少数的几个订单表，写入了2000/s，其他几十个表10/s。

        3、代码层面：重写代码。写代码的同学，要慎重。当时我们其实短期是让那个同学重写了一下代码，插入数据之后直接就更新，不要查询。

        4、直连主库。如果确实存在插入后立马要求就查询到，然后根据结果(比如某个状态)反过来执行一些操作，则可以

Mysql5.7中的并行复制

        而MySQL 5.7版本对并行复制进一步改进，已经支持“真正”的并行复制功能，是基于组提交的并行复制，官方称为enhanced multi-threaded slave（简称MTS），复制延迟问题已经得到了极大的改进。

        其核心思想：一个组提交的事务都是可以并行回放（配合binary log group commit）；slave机器的relay log中 last_committed相同的事务（sequence_num不同）可以并发执行。

        通过设置参数slave_parallel_workers>0并且global.slave_parallel_type＝‘LOGICAL_CLOCK’，即可支持一个schema下，slave_parallel_workers个的worker线程并发执行relay log中主库提交的事务。

        mysql的一主一/多从、双主都是一个复制源，而mysql5.7多源复制则允许多个mssql同时对一个从库进行复制。

主从复制架构中应注意的问题：

        从节点要设置某些限定使得它不能进行写操作,才能保证复制当中的数据一致。

1：限制从服务器为只读

        在从服务器上设置：

        read_only = ON,但是此限制对拥有SUPER权限 的用户均无效。

阻止所有用户：

        mysq>FLUSH TABLES WITH READ LOCK;

2:如何保证主从复制时的事物安全？

1：在主节点设置参数

        sync_binlog=1： Mysql开启bin-log日志使用bin-log时，默认情况下，并不是每次执行写入就与硬盘同步，这样在服务器崩溃时，就可能导致bin-log最后的语句丢失。可以通过这个参数来调节，sync_binlog=N，使执行N次写入后，与硬盘同步。1是最安全的，但是也是最慢的。

如果用到innode 存储引擎:

        innodb_flush_logs_at_trx_commit=ON（刷写日志：在事务提交时，要将内存中跟事务相关的数据立即刷写到事务日志中去。）

innodb_support_xa=ON (分布式事务：基于它来做两段式提交功能)

        sync_master_info=1：每次给从节点dump一些事件信息之后，主节点的master info 信息会立即同步到磁盘上。让从服务器中的 master_info 及时更新。

2：在每个slave节点

        skip_slave_start =ON (跳过自动启动，使用手动启动。)

        relay_log也会在内从中先缓存，然后在同步到relay_log中去，可以使用下面参数使其立即同步。

        sync_relay_log =1 ，默认为10000，即每10000次sync_relay_log事件会刷新到磁盘。为0则表示不刷新，交由OS的cache控制。

        sync_relay_log_info=1每间隔多少事务刷新relay-log.info，如果是table（innodb）设置无效，每个事务都会更新

注： 在从节点中 master.info是记录在主节点复制位置的文件。

        relylog_info: 本地将来至于主节点的哪一个二进制文件中position并且保存文本地哪一个中继日志中的哪一个postion. 从节点启动时也需要根据relay-log.info定位本地relay-log.

        注意点：- 主从复制是异步的逻辑的 SQL 语句级的复制；- 复制时，主库有一个 I/O 线程，从库有两个线程，及 I/O 和 SQL 线程；- 实现主从复制的必要条件是主库要开启记录 binlog 的功能；- 作为复制的所有 MySQL 节点的 server-id 都不能相同；- binlog 文件只记录对数据内容有更改的 SQL 语句，不记录任何查询语句。