1.主从复制的原理?

复制(replication)是 MySQL 数据库提供的一种高可用高性能的解决方案，一般用来建立大型的应用。总体来说，replication 的工作原理分为以下 3 个步骤:

1. 主服务器(master)把数据更改记录到二进制日志(binlog)中。
2. 从服务器(slave)把主服务器的二进制日志复制到自己的中继日志(relaylog)中。
3. 从服务器重做中继日志中的日志，把更改应用到自己的数据库上，以达到数据的最终一致性。

复制的工作原理并不复杂，其实就是一个完全备份加上二进制日志备份的还原。不同的是这个二进制日志的还原操作基本上实时在进行中。这里特别需要注意的是，复制不是完全实时地进行同步，而是异步实时。这中间存在主从服务器之间的执行延时，如果主服务器的压力很大，则可能导致主从服务器延时较大。

2.什么是 SBR 和 RBR?

主从复制 binlog 日志有三种记录方式

Replication 之所以能够工作，主要还是归结于binlog（binary log），所以在 Replication 模式下必须开启 binlog 功能；slave 从 masters 上增量获取 binlog 信息，并在本地应用日志中的变更操作（即重放）。变更操作将根据选定的格式类型写入 binlog 文件，目前支持三种 format：

• statement-based Replication（SBR）：master 将 SQL statements 语句写入 binlog，slave 也将 statements 复制到本地执行；简单而言，就是在 master 上执行的 SQL 变更语句，也同样在 slaves 上执行。SBR 模式是 MySQL 最早支持的类型，也是 Replication 默认类型。

• row-based Replication（RBR）：master 将每行数据的变更信息写入 binlog，每条 binlog 信息表示一行（row）数据的变更内容，对于 slaves 而言将会复制 binlog 信息，然后单条或者批量执行变更操作；

• mix-format Replication：混合模式，在这种模式下，master 将根据存储引擎、变更操作类型等，从 SBR、RBR 中来选择更合适的日志格式，默认为 SBR；具体选择那种格式，这取决于变更操作发生的存储引擎、statement 的类型以及特征，优先选择“数据一致性”最好的方式（RBR），然后才兼顾性能，比如 statement 中含有“不确定性”方法或者批量变更，那么将选择 RBR 方式，其他的将选择 SBR 以减少 binlog 的大小。我们建议使用 mix 方式。

SBR 和 RBR 都有各自的优缺点，对于大部分用而言，mix 方式在兼顾数据完整性和性能方面是最佳的选择。

3.SBR 和 RBR 的优缺点?

SBR 的优点

• 因为 binlog 中只写入了变更操作的 statements，所以日志量将会很小；
• 当使用 SQL 语句批量更新、删除数据时，只需要在 binlog 中记录 statement 即可，可以大大减少 log 文件对磁盘的使用
• 当然这也意味着 slave 复制信息量也更少，以及通过 binlog 恢复数据更加快速；

SBR 的缺点

有些变更操作使用 SBR 方式会带来数据不一致的问题，一些结果具有不确定性的操作使用 SBR 将会引入数据不一致的问题。

• statement 中如果使用了 UDF(User Defination Fuction)，UDF 的计算结果可能依赖于 SQL 执行的时机和系统变量，这可能在 slave 上执行的结果与 master 不同，此外如果使用了 trigger，也会带来同样的问题；

• statement 中如果使用了如下函数的（举例）：UUID()，SYSDATE()，RAND()等，不过 NOW()函数可以正确的被 Replication（但在 UDF 或者触发器中则不行）；这些函数的特点就是它们的值依赖于本地系统，RAND()本身就是随机所以值是不确定的。如果 statement 中使用了上述函数，那么将会在日志中输出 warning 信息；

• 对于“INSERT...SELECT”语句，SBR 将比 RBR 需要更多的行锁。（主要是为了保障数据一致性，需要同时锁定受影响的所有的行，而 RBR 则不必要）；

• 对于 InnoDB，使用“AUTO_INCREMENT”的 insert 语句，将会阻塞其他“非冲突”的 INSERT。（因为 AUTO_INCREMENT，为了避免并发导致的数据一致性问题，只能串行，但 RBR 则不需要）；

• 对于复杂的 SQL 语句，在 slaves 上仍然需要评估（解析）然后才能执行，而对于 RBR，SQL 语句只需要直接更新相应的行数据即可；在 slave 上评估、执行 SQL 时可能会发生错误，这种错误会随着时间的推移而不断累加，数据一致性的问题或许会不断增加。

RBR 的优点：

• 所有的变更操作，都可以被正确的 Replication，这是最安全的方式；
• 对于“INSERT…SELECT”、包含“AUTO_INCREMENT”的 inserts、没有使用索引的 UPDATE/DELETE，相对于 SBR 将需要更少的行锁。（意味着并发能力更强）；

RBR 的缺点：

• 最大的缺点：就是 RBR 需要更多的日志量。任何数据变更操作都将被写入 log，受影响的每行都要写入日志，日志包含此行所有列的值（即使没有值变更的列）；
• 因此 RBR 的日志条数和尺寸都将会远大于 SBR，特别是在批量的 UPDATE/DELETE 时，可能会产生巨大的 log 量，反而对性能带来影响，尽管这确实保障了数据一致性，确导致 Replication 的效率较低；

4.主从复制有几种方式?

• 同步复制：MySQL 主库提交事务的线程要等待所有从库的复制成功响应，才返回客户端结果。这种方式在实际项目中，基本上没法用，原因有两个：一是性能很差，因为要复制到所有节点才返回响应；二是可用性也很差，主库和所有从库任何一个数据库出问题，都会影响业务。
• 异步复制（默认模型）：MySQL 主库提交事务的线程并不会等待 binlog 同步到各从库，就返回客户端结果。这种模式一旦主库宕机，数据就会发生丢失。
• 半同步复制：MySQL 5.7 版本之后增加的一种复制方式，介于两者之间，事务线程不用等待所有的从库复制成功响应，只要一部分复制成功响应回来就行，比如一主二从的集群，只要数据成功复制到任意一个从库上，主库的事务线程就可以返回给客户端。这种半同步复制的方式，兼顾了异步复制和同步复制的优点，即使出现主库宕机，至少还有一个从库有最新的数据，不存在数据丢失的风险。

Master 处理事务过程中，提交完事务后，必须等至少一个 Slave 将收到的 binlog 写入 relay log 返回 ack 才能继续执行处理用户的事务。

相关配置

#【这里MySQL5.5并没有这个配置，MySQL5.7为了解决半同步的问题而设置的】
rpl_semi_sync_master_wait_point=AFTER_COMMIT

#（最低必须收到多少个slave的ack）
rpl_semi_sync_master_wait_for_slave_count=1

#（等待ack的超时时间）
rpl_semi_sync_master_timeout=100

增强半同步和半同步不同是，等待 ACK 时间不同

rpl_semi_sync_master_wait_point=AFTER_SYNC（唯一区别）

半同步的问题是因为等待 ACK 的点是 Commit 之后，此时 Master 已经完成数据变更，用户已经可以看到最新数据，当 Binlog 还未同步到 Slave 时，发生主从切换，那么此时从库是没有这个最新数据的，用户又看到老数据。

增强半同步将等待 ACK 的点放在提交 Commit 之前，此时数据还未被提交，外界看不到数据变更，此时如果发送主从切换，新库依然还是老数据，不存在数据不一致的问题。

5.主从复制有什么好处?

• 在从服务器可以执行查询工作， 降低主服务器压力； （主库写， 从库读，降压） 读写分离
• 对主服务器进行数据备份， 避免备份期间影响主服务器服务； 容灾
• 当主服务器出现问题时， 可以切换到从服务器 。提高可用性

6.主节点自动切换

要让 MySQL 集群自动更换主节点而不被程序感知，可以按照以下步骤进行设置：

1. 配置 MySQL 集群：确保您的 MySQL 集群已正确设置和配置，并且已经正常运行。确保您有一个可用的备用节点。

2. 使用虚拟 IP（VIP）：为 MySQL 集群配置一个虚拟 IP（VIP），它将用作主节点的标识。这个 VIP 将会漂移到新的主节点上。

3. 监控主节点状态：设置一个监控机制来检测主节点的状态。您可以使用一个脚本或者专门的监控工具来监视主节点是否可用。

4. 监控节点之间的心跳：使用一个心跳监控机制，确保集群中的所有节点都能够相互通信。常用的方法是使用软件或硬件心跳。

5. 监测主节点的宕机：当监控机制检测到主节点不可用时，它应该触发一系列动作来进行故障转移。

6. 故障转移操作：在主节点宕机后，您需要执行一系列操作来进行故障转移，将备用节点升级为新的主节点。

   a. 在备用节点上启动 MySQL 服务，并确保它已经成为新的主节点。

   b. 将 VIP 从原来的主节点迁移到新的主节点上，这样客户端程序就会将请求发送到新的主节点。

   c. 更新集群配置信息，确保所有节点都知道新的主节点位置。

7. 更新程序连接信息：在故障转移完成后，您需要更新客户端程序的连接信息，使其连接到新的主节点。这可以通过动态获取主节点位置并更新程序配置来实现，或者使用服务发现机制来自动更新连接信息。

通过以上步骤，您可以实现 MySQL 集群的自动主节点切换，而不需要手动干预并且程序可以无感知地连接到新的主节点。请注意，这是一个高级配置，需要一定的专业知识和经验，确保在进行任何更改之前备份您的数据库以防止数据丢失。

7.主从同步延迟原因

MySQL的主从复制都是单线程的操作，主库对所有DDL和DML产生的日志写进binlog，由于binlog是顺序写，所以效率很高。 Slave的SQL Thread线程将主库的DDL和DML操作事件在slave中重放。DML和DDL的IO操作是随即的，不是顺序的，成本高很多。 另一方面，由于SQL Thread也是单线程的，当主库的并发较高时，产生的 DML 数量超过slave的SQL Thread所能处理的速度，或者当slave中有大型query语句产生了锁等待那么延时就产生了。

常见原因：

• Master 负载过高
• Slave 负载过高
• 网络延迟
• 机器性能太低
• MySQL 配置不合理

8.主从延时排查方法

通过监控 show slave status 命令输出的Seconds_Behind_Master参数的值来判断：

• NULL，表示 io_thread 或是 sql_thread 有任何一个发生故障；
• 0，该值为零，表示主从复制良好；
• 正值，表示主从已经出现延时，数字越大表示从库延迟越严重

9.解决数据丢失问题

解决数据丢失的问题：

• 半同步复制:从 MySQL5.5 开始，MySQL 已经支持半同步复制了，半同步复制介于异步复制和同步复制之间，主库在执行完事务后不立刻返回结果给客户端，需要等待至少一个从库接收到并写到 relay log 中才返回结果给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一个TCP/IP往返耗时的延迟。
• 主库配置sync_binlog=1，innodb_flush_log_at_trx_commit=1 sync_binlog的默认值是 0，MySQL 不会将binlog同步到磁盘，其值表示每写多少binlog同步一次磁盘。innodb_flush_log_at_trx_commit为 1 表示每一次事务提交或事务外的指令都需要把日志 flush 到磁盘。

注意:将以上两个值同时设置为 1 时，写入性能会受到一定限制，只有对数据安全性要求很高的场景才建议使用，比如涉及到钱的订单支付业务，而且系统 I/O 能力必须可以支撑！

10.解决从库复制延迟的问题

• 架构方面

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

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

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

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

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

• 硬件方面

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

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

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

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

• mysql 主从同步加速

  1. sync_binlog在 slave 端设置为 0

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

  3. 直接禁用slave端的 binlog

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

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

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

11.mysql 的热备和冷备?

• 冷备份发生在数据库已经正常关闭的情况下，将关键性文件拷贝到另外位置的一种说法
• 热备份是在数据库运行的情况下，采用归档方式备份数据的方法

热备 Hot Backup 是指数据库运行中直接备份，对正在运行的数据库操做没有任何的影响。这种方式在 MySQL 官方手册中称为 Online Backup(在线备份)。

冷备 Cold Backup 是指备份操作是在数据库停止的情况下，这种备份最为简单，一般只需要复制相关的数据库物理文件即可。这种方式在 MySQL 官方手册中称为 Offline Backup(离线备份)。对于 InnoDB 存储引擎的冷备非常简单，只需要备份 MySQL 数据库的 frm 文件，共享表空间文件，独立表空间文件(*.ibd)，重做日志文件。另外建议定期备份 MySQL 数据库的配置文件 my.cnf，这样有利于恢复的操作。

温备 Warm Backup 备份同样是在数据库运行中进行的，但是会对当前数据库的操作有所影响，如加一个全局读锁以保证备份数据的一致性.

12.冷备优缺点?

冷备优点

• 是非常快速的备份方法（只需拷贝文件）
• 容易归档（简单拷贝即可）
• 容易恢复到某个时间点上（只需将文件再拷贝回去）
• 能与归档方法相结合，作数据库“最新状态”的恢复。
• 低度维护，高度安全。

冷备缺点

• 单独使用时，只能提供到“某一时间点上”的恢复。
• 在实施备份的全过程中，数据库必须要作备份而不能作其它工作。也就是说，在冷备份过程中，数据库必须是关闭状态。
• 若磁盘空间有限，只能拷贝到磁带等其它外部存储设备上，速度会很慢。
• 不能按表或按用户恢复。

13.mysql 备份工具?

常用备份工具

• mysql 复制

• 逻辑备份(mysqldump，mydumper)

• 物理备份(copy，xtrabackup)

备份工具差异对比

• 1.mysql 复制相对于其他的备份来说，得到的备份数据比较实时。
• 2.逻辑备份：分表比较容易。
  • mysqldump 备份数据时是将所有 sql 语句整合在同一个文件中；
  • mydumper 备份数据时是将 SQL 语句按照表拆分成单个的 sql 文件，
  • 每个 sql 文件对应一个完整的表。
• 3.物理备份：拷贝即可用，速度快。
  • copy:直接拷贝文件到数据目录下，可能引起表损坏或者数据不一致。
  • xtrabackup 对于 innodb 表是不需要锁表的，对于 myisam 表仍然需要锁表。

14.Canal 原理

主从复制步骤：

将 Master 的 binary-log 日志文件打开，mysql 会把所有的 DDL，DML，TCL 写入 BinaryLog 日志文件中 Master 会生成一个 log dump 线程，用来给从库的 i/o 线程传 binlog 从库的 i/o 线程去请求主库的 binlog，并将得到的 binlog 日志写到中继日志（relaylog）中从库的 sql 线程，会读取 relaylog 文件中的日志，并解析成具体操作，通过主从的操作一致，而达到最终数据一致，而 Canal 的原理就是伪装成 Slave 从 Binlog 中复制 SQL 语句或者数据。

Canal 作为 MySQL binlog 增量获取和解析工具，可将变更记录投递到 MQ 系统中，比如 Kafka/RocketMQ，可以借助于 MQ 的多语言能力，因此我们可以使用下面这种方案来同步数据。

15.Mysql 三种集群模式

MySQL 集群主要有三种模式：

• 主从复制
• 多主复制
• Galera Cluster

主从复制:

主从复制是 MySQL 集群的最基本形式，主要思想是将一台 MySQL 服务器定义为主服务器，另外一台或多台 MySQL 服务器定义为从服务器。主服务器上的数据修改会同步到从服务器上，从服务器只能读取数据，不具备写入的能力。主从复制的主要原理是通过 binlog 来实现，binlog 是 MySQL 数据库的二进制日志，记录了所有的 SQL 语句以及数据变化的情况，从服务器通过读取主服务器上的 binlog 进行数据同步。

主从复制的使用场景主要是读写分离或者备份。当主服务器承担写入操作时，从服务器可以负责读取操作，从而实现负载均衡；同时，从服务器可以作为备份数据库，主服务器出现故障时可以通过从服务器来恢复数据。

多主复制:

多主复制和主从复制不同的是，多主复制中每个节点都可以进行读写操作，同时节点之间的数据同步也是相互的。当一个节点修改数据后，相应的变化会自动同步到其它节点中。多主复制的主要原理是通过对每个节点的 binlog 进行多点复制，实现数据同步。

多主复制主要的使用场景是业务分区。将不同的业务模块分配给不同的节点，每个节点可以独立地进行数据修改，同时数据也会自动同步到其他节点中。

Galera Cluster:

Galera Cluster 是一种基于 MySQL InnoDB 存储引擎的集群解决方案，是一种完全同步的多主复制技术。它与多主复制最大的不同是采用了同步复制的方式，保证了数据修改的可靠性，同时支持自动分片。

Galera Cluster 的主要实现原理是采用了一种称为”Wating Commit”的机制，当一个数据变化时，该数据不仅要向主节点同步，还要向集群中其它节点同时进行同步，直到所有节点的数据完全一致，提交操作才算成功。

由于 Galera Cluster 中没有单点故障，因此该模式被广泛应用于高可用性的业务场景。同时，全局序列号机制和自动分片功能也使得 Galera Cluster 在处理大规模数据时表现出色。

MySQL 集群的三种模式各具特点，可以根据不同的业务需求进行选择。如果需要进行读写分离或备份操作，主从复制是不错的选择；如果需要进行业务分区，多主复制可以选用；如果需要高可用性和自动分片功能，那么 Galera Cluster 是不二之选。

16.数据迁移方案

数据迁移执行流程，大概有 10 个步骤：

1. 环境准备：线上库配置完成
2. 全量同步：数据迁移工具上新建 2 张表（积分表、明细表）的全量任务
3. 增量同步：全量迁移完成后开启增量(自动回溯全量开始时间，消息多次消费会进行幂等)
4. 数据校验：全量数据校验，查看数据是否一致
5. 切流测试：改造代码预发测试（采集线上流量进行回放，多种 case 跑一下，切流开关等校验），没问题发布上线
6. 二次校验：再次全量进行校验&订正（数据追平）
7. 开启双写：打开双写（保证数据实时性）既写老库，又写新库
8. 开启读灰度：低峰时段，进行灰度切流userId%x，进行验证，逐步流量打开，持续观察
9. 只写新库：写流量切到新库，只写新库，不写老库。完成数据迁移方案
10. 迁移完成：系统稳定运行一段时间，迁移&双写代码下线，老库进行资源释放