mysql主从复制

数据库的读写分离--->最终体现是主从复制

  为什么要读写分离？

对于一个小型网站，可能单台数据库服务器就能满足需求，但是在一些大型的网站或者应用中，单台的数据库服务器可能难以支撑大的访问压力，升级服务器性能，成本又太高，必须要横向扩展。还有就是，单库的话，读、写都是操作一个数据库，数据多了之后，对数据库的读、写性能就会有很大影响。同时对于数据安全性，和系统的稳定性，也是挑战。

  数据库的读写分离的好处？

1. 将读操作和写操作分离到不同的数据库上，避免主服务器出现性能瓶颈；

2. 主服务器进行写操作时，不影响查询应用服务器的查询性能，降低阻塞，提高并发；

3. 数据拥有多个容灾副本，提高数据安全性，同时当主服务器故障时，可立即切换到其他服务器，提高系统可用性；

MySQL复制描述

  主从复制是指一台服务器充当主数据库服务器，另一台或多台服务器充当从数据库服务器，主服务器中的数据自动复制到从服务器之中。对于多级复制，数据库服务器即可充当主机，也可充当从机。MySQL主从复制的基础是主服务器对数据库修改记录二进制日志，从服务器通过主服务器的二进制日志自动执行更新。

mysql复制原理

原理：

（1）master服务器将数据的改变记录二进制binlog日志，当master上的数据发生改变时，则将其改变写入二进制日志中；

（2）slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/OThread请求master二进制事件

（3）同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后I/OThread和SQLThread将进入睡眠状态，等待下一次被唤醒。

也就是说：

• 从库会生成两个线程,一个I/O线程,一个SQL线程;
• I/O线程会去请求主库的binlog,并将得到的binlog写到本地的relay-log(中继日志)文件中;
• 主库会生成一个log dump线程,用来给从库I/O线程传binlog;
• SQL线程,会读取relay log文件中的日志,并解析成sql语句逐一执行;

下图就描述了一个多个数据库间主从复制与读写分离的模型(来源网络)：

基于主从复制，1.数据库版本号一致，2.已初始化表 3.已启动
这里就不讲解linux安装mysql数据库过程，在我之前的博文中已有
https://blog.csdn.net/qq_39291929/article/details/78838095
本次只搭建一主一从服务器配置，不做一主多从，多主多从等配置策略，仅供参考
准备工作:

两台数据库服务器，分别是数据库1

1.修改主数据库master
vi   /etc/my.cnf
[mysqld]
log-bin=mysql-bin   //[必须]启用二进制日志
server-id=222      //[必须]服务器唯一ID，默认是1，一般取IP最后一段 

2.修改从数据库slave
[mysqld]
log-bin=mysql-bin   //[不是必须]启用二进制日志
server-id=222      //[必须]服务器唯一ID，默认是1，一般取IP最后一段 
3.重启两台服务器
service mysqld start
4.在主服务器上创建账号并授权从库slave
账号：mysql_test   密码：123456      创建用户并授权(关于创建用户和授权请看mysql安装博文)，对于主从复制账号一般不采用root账号
在主数据库目录下(/usr/local/mysql/bin) 敲打如下命令： ./mysql -umysql_test -p， 回车后提示输入密码就可以进入mysql命令行中
 mysql>GRANT REPLICATION SLAVE ON *.* to 'mysql_test'@'%' identified by '123456';   //“%”表示所有客户端都可能连，只要帐号，密码正确，此处可用具体客户端IP代替，如192.168.145.226，加强安全。 
5. 查看主服务器状态
mysql>show master status;
6.配置从数据库
mysql>change master to master_host='192.168.145.222',master_user='mysql_test',master_password='123456',
         master_log_file='mysql-bin.000004',master_log_pos=308;   //注意不要断开，308数字前后无单引号。
   Mysql>start slave;    //启动从服务器复制功能
7.查看从服务器复制状态
mysql> show slave status\G
8.主从服务器测试

 

-----------------------------------------------------------------------mysql主从同步---------------------------------------------------------------------------------------------------

快，开篇大伙先思考一个问题，MySQL 是怎么保证数据不丢失的呢？

其实要保证数据不丢失，说白了要具有下面两种能力：
（1）能恢复到任何时间点的状态；
（2）能保证 MySQL 在任何时间段突然宕机重启，已提交的数据不会丢失，未提交完整的数据也会自动回滚；

这不就引出来今天要聊的主题了么，实现第一点需要用 bin log，实现第二点需要用 redo log 和 undo log。

了解三大log之前，我们先看一下Mysql数据更新的流程：

上面这张图包含了 redo log、bin log、undo log 三种日志之间的大致关系，下面进入正题。

一、redo log 重做日志（MySQL 存储引擎 InnoDB 的事务日志）

我们知道 MySQL 数据存在磁盘中，每次读写数据需做磁盘 IO，并发场景下性能差。为此 MySQL 引入缓存 Buffer Pool 做优化。其包含磁盘中部分数据页（page）的映射，来缓解数据库的磁盘压力。

当从数据库读数据时，首先从缓存中读，缓存中没有，则从磁盘读后放入缓存；当向数据库写数据时，先向缓存中写，此时缓存中的数据页数据会变更，该数据页叫脏页，Buffer Pool 中修改完数据后会按照设定的策略再定期刷到磁盘中去，这个过程叫刷脏页。

那么问题来了，如果 Buffer Pool 中修改的数据还没有及时的刷到磁盘，MySQL 宕机重启，就会导致数据丢失，无法保证事务的持久性，怎么办？

redo log 解决了这个问题。就是说数据库在修改数据时，会把更新记录先写到 redo log 中，再去修改 Buffer Pool 中的数据，当提交事务时，调用 fsync 把 redo log 刷入磁盘。至于缓存中更新的数据文件何时刷入磁盘，则由后台线程异步处理。

注意：此时 redo log 的事务状态是 prepare，还未真正提交成功，要等 bin log 日志写入磁盘完成后才会变为 commit，事务才算真正提交成功。

redo log 的写入方式？

redo log 采用大小固定，循环写入的方式，当写满后，会重新从头开始循环写，类似一个环状。这样设计原因是 redo log 记录的是数据页上的修改，如果 Buffer Pool 中数据页已经刷到磁盘，这些记录就失效了，新日志会将这些失效的记录覆盖擦除。

注意：redo log 满了，在擦除之前，要确保这些要被擦除记录都已经刷到磁盘中了。在擦除旧记录释放新空间期间，不能再接收新的更新请求，此时 MySQL 性能会下降。因此高并发情况下，合理调整 redo log 大小很重要。

crash-safe 能力是什么？

Innodb 引擎有 crash-safe 能力，即事务提交过程中任何阶段，MySQL 宕机重启后都能保证事务的完整性，已提交的数据不会丢失。这种能力是通过redo log保证的，MySQL 宕机重启，系统将自动检查 redo log，将修改还未写入磁盘的数据从 redo log 恢复到 MySQL 中。

二、undo log 回滚日志（MySQL 存储引擎 InnoDB 的事务日志）

undo log 记录的是数据修改之前的状态，属于逻辑日志，起到回滚的作用，是保证事务原子性的关键。
举个栗子：假如更新 ID=1 记录的 name 字段，name 原始数据为小王，现改 name 为小张，事务执行 update X set name = 小张 where id =1 语句时，先在 undo log 中记录一条相反逻辑的 update X set name = 小王 where id =1 记录，这样当某些原因导致事务失败，就可借助 undo log 将数据回滚到事务执行前的状态。

那么问题来了：同一个事务的一条记录被多次修改，难道每次都要把数据修改前的状态写 undo log 吗？

不会，因为 undo log 只记录事务开始前数据的原始版本，当再次对这行数据修改时，产生的修改记录会写到 redo log。undo log 负责回滚，redo log负责前滚。

啥是回滚和前滚？

（1）回滚

未提交的事务，即事务未执行 commit。但事务内修改的脏页中，有一部分已刷盘。此时数据库宕机重启，需要回滚来将先前那部分已经刷盘的脏块从磁盘上撤销。

（2）前滚

未完全提交的事务，即事务已经执行 commit，但该事务内修改的脏页中只有一部分数据被刷盘，另一部分还在 buffer pool，此时数据库宕机重启，就要用前滚来将未来得及刷盘的数据从 redo log 中恢复出来并刷盘。 

三、bin log 归档日志（数据库 Server 层二进制逻辑日志、和什么引擎无关）

bin log 记录了用户对数据库所有 sql 操作（不包含查询语句，因为这类操作对数据本身没有修改）。之所以可以称为归档日志，是因为它不会像 redo log 那样循环擦除之前的记录，而是会一直记录日志。一个 bin log 文件默认最大容量1G（可通过 max_binlog_size 参数修改），单个日志超过最大值则会新创建一个文件继续写。
注意：日志可能是基于事务来记录的，而事务不应该跨文件记录，如果 binlog 日志文件达到了最大值但刚好事务还没有提交，此时则不会创建新文件记录，而是继续增大日志。因此 max_binlog_size 的值和实际的 binlog 文件大小不一定相等。

经过上述介绍，binlog 主要用就是主从同步和数据库基于时间点的还原。

那么问题来了，可以没有 binlog 吗（有了 redo log 为啥还需要 bin log）？

需要分场景来看：

主从模式下，binlog 是必须的，因为从库的数据同步需要依赖 binlog；

单机模式下，不考虑数据库基于时间点的还原，binlog 就不是必须的，因为有 redo log 就可以保证 crash-safe 能力了；

redo log 的记录修改落盘后，日志会被覆盖掉，无法用于数据恢复等操作，redo log 是 innodb 引擎层实现的，并不是所有引擎都有；

redo log 与 bin log 的区别？

 什么是 redo log 两阶段提交，为什么要这么做？
更新内存后引擎层写 redo log 将状态改成 prepare 为提交第一阶段，Server 层写 bin log，将状态改成 commit 为提交第二阶段。 两阶段提交目的是确保 binl og 和 redo log 数据一致性。

如果不是两阶段提交可能会出现什么情况？
1）假设先写 redo log 再写 bin log，即 redo log 没有 prepare 阶段，写完直接置为commit，然后再写 bin log。如果写完 redo log 后还没写完 bin log 数据库宕机了，重启后系统自动用 redo log 恢复，此时会造成磁盘上数据页数据比 bin log 上的记录数据多，数据不一致。
2）假设先写 bin log 再写 redo log，如果写完 bin log 没写完 redo log 数据库宕机了，那么 bin log 上的记录就会比磁盘上数据页的记录多一些，下次用 bin log 恢复数据，恢复后的数据和原来的数据不一致。

描述一下 redo log 容灾恢复过程？
如果 redo log 是完整（commit 状态）的，直接用 redo log 恢复；
如果 redo log 是预提交 prepare 但不是 commit 状态，此时要去判断 binlog 是否完整，如果完整（commit）那就提交 redo log，再用 redo log 恢复，不完整就回滚事务。

----------------------------------------------------------------------------主从复制延迟或者数据丢失解决方案--------------------------------------------------------------------------------------------

MySQL 有三种同步模式，分别是：

「异步复制」：MySQL 默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返给客户端，并不关心从库是否已经接收并处理。这样就会有一个问题，一旦主库宕机，此时主库上已经提交的事务可能因为网络原因并没有传到从库上，如果此时执行故障转移，强行将从提升为主，可能导致新主上的数据不完整。

「全同步复制」：指当主库执行完一个事务，并且所有的从库都执行了该事务，主库才提交事务并返回结果给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。

「半同步复制」：是介于全同步复制与全异步复制之间的一种，主库只需要等待至少一个从库接收到并写到 Relay Log 文件即可，主库不需要等待所有从库给主库返回 ACK。主库收到这个 ACK 以后，才能给客户端返回 “事务完成” 的确认。

MySQL 默认的复制是异步的，所以主库和从库的数据会有一定的延迟，更重要的是异步复制可能会引起数据的丢失。但是全同步复制又会使得完成一个事务的时间被拉长，带来性能的降低。因此我把目光转向半同步复制。从 MySQL 5.5 开始，MySQL 以插件的形式支持 semi-sync 半同步复制。

相对于异步复制，半同步复制提高了数据的安全性，减少了主从延迟，当然它也还是有一定程度的延迟，这个延迟最少是一个 TCP/IP 往返的时间。所以，半同步复制最好在低延时的网络中使用。
 

半同步复制是通过插件来实现的，因此，安装半同步复制环境，需先在主、从库安装半同步复制插件，插件安装后，便有了相关的系统变量、状态变量对半同步环境进行配置和监控。要使用半同步复制，必须满足如下要求：

• MySQL服务器支持动态加载插件，即变量have_dynamic_loading值为YES，默认支持
• 主从复制环境已经运行，即在已经运行的复制环境安装半同步复制插件并进行相应的配置
• 半同步复制只支持默认的复制通道，不能同时配置多个复制通道

安装半同步复制可参考如下地址：安全验证 - 知乎

重点：mysql主从复制延迟和数据丢失最佳方案：半同步复制（ACK机制）

其他方案：
 

1、主库DML请求频繁（tps较大）

即主库写请求较多，有大量insert、delete、update并发操作，短时间产生了大量的binlog。

【原因分析】

主库并发写入数据，而从库SQL Thread为单线程应用日志，很容易造成relaylog堆积，产生延迟。

【解决思路】

做分库分表

 

2、主库执行大事务

比如大量导入数据，INSERT INTO $tb1 SELECT * FROM $tb2、LOAD DATA INFILE等
比如UPDATE、DELETE了全表等
Exec_Master_Log_Pos一直未变，Slave_SQL_Running_State为Reading event from the relay log
分析主库binlog，看主库当前执行的事务也可知晓。

【原因分析】

假如主库花费200s更新了一张大表，在主从库配置相近的情况下，从库也需要花几乎同样的时间更新这张大表，此时从库延迟开始堆积，后续的events无法更新。

【解决思路】

拆分大事务，及时提交。

3、增加从服务器的硬件配置