今日目标

  • 能够理解MySQL主从复制原理

  • 能够掌握实现MySQL一主一从

  • 能够掌握MySQL一主多从

  • 能够掌握框架中的读写分离

一、MySQL复制简述

mysql复制是指从一个mysql服务器(MASTER)将数据通过日志的方式经过网络传送到另一台或多台mysql服务器(SLAVE)，然后在slave上重放(replay或redo)传送过来的日志，以达到和master数据同步的目的。

二、MySQL复制原理

1. 它的工作原理很简单。首先确保master数据库上开启了二进制日志，这是复制的前提。
  • 在slave准备开始复制时，首先要执行==change master to==语句设置连接到master服务器的连接参数，在执行该语句的时候要提供一些信息，包括如何连接和要从哪复制binlog，这些信息在连接的时候会记录到slave的datadir下的master.info文件中，以后再连接master的时候将不用再提供这新信息而是直接读取该文件进行连接。

mysql中data文件夹

MySQL中.frm文件：保存了每个表的元数据，包括表结构的定义等，该文件与数据库引擎无关。

MySQL中.ibd文件：InnoDB引擎开启了独立表空间(my.ini中配置innodb_file_per_table = 1)产生的存放该表的数据和索引的文件。

  • 在slave上有两种线程，分别是IO线程和SQL线程。

    IO线程用于连接master，监控和接受master的binlog。当启动IO线程成功连接master时，master会同时启动一个dump线程，该线程将slave请求要复制的binlog给dump出来，之后IO线程负责监控并接收master上dump出来的二进制日志，当master上binlog有变化的时候，IO线程就将其复制过来并写入到自己的中继日志(relay log)文件中。

    slave上的另一个线程SQL线程用于监控、读取并重放relay log中的日志，将数据写入到自己的数据库中。如下图所示。

站在slave的角度上看，过程如下:

MySQL主从复制 - 图1

站在master的角度上看，过程如下(默认的异步复制模式，前提是设置了sync_binlog=1，否则binlog刷新时间由操作系统决定)：

MySQL主从复制 - 图2

所以，可以认为复制大致有三个步骤：

  1. 数据修改写入master数据库的binlog中。
  2. slave的IO线程复制这些变动的binlog到自己的relay log中。
  3. slave的SQL线程读取并重新应用relay log到自己的数据库上，让其和master数据库保持一致。

从复制的机制上可以知道，在复制进行前，slave上必须具有master上部分完整内容作为复制基准数据。例如，master上有数据库A，二进制日志已经写到了pos1位置，那么在复制进行前，slave上必须要有数据库A，且如果要从pos1位置开始复制的话，还必须有和master上pos1之前完全一致的数据。如果不满足这样的一致性条件，那么在replay中继日志的时候将不知道如何进行应用而导致数据混乱。也就是说，复制是基于binlog的position进行的，复制之前必须保证position一致。(注：这是传统的复制方式所要求的)

可以选择对哪些数据库甚至数据库中的哪些表进行复制。默认情况下，MySQL的复制是异步的。slave可以不用一直连着master，即使中间断开了也能从断开的position处继续进行复制。

三、MySQL主从复制的好处

围绕下面的拓扑图来分析：

MySQL主从复制 - 图3 ==主要有以下几点好处：==

1.提供了读写分离的能力。

replication（复制）让所有的slave都和master保持数据一致，因此外界客户端可以从各个slave中读取数据，而写数据则从master上操作。也就是实现了读写分离。

需要注意的是，为了保证数据一致性，写操作必须在master上进行。

通常说到读写分离这个词，立刻就能意识到它会分散压力、提高性能。

2.为MySQL服务器提供了良好的伸缩(scale-out)能力。

由于各个slave服务器上只提供数据检索而没有写操作，因此”随意地”增加slave服务器数量来提升整个MySQL群的性能，而不会对当前业务产生任何影响。

之所以”随意地”要加上双引号，是因为每个slave都要和master建立连接，传输数据。如果slave数量巨多，master的压力就会增大，网络带宽的压力也会增大。

3.数据库备份时，对业务影响降到最低。

由于MySQL服务器群中所有数据都是一致的(至少几乎是一致的)，所以在需要备份数据库的时候可以任意停止某一台slave的复制功能(甚至停止整个mysql服务)，然后从这台主机上进行备份，这样几乎不会影响整个业务(除非只有一台slave，但既然只有一台slave，说明业务压力并不大，短期内将这个压力分配给master也不会有什么影响)。

4.能提升数据的安全性。

这是显然的，任意一台mysql服务器断开，都不会丢失数据。即使是master宕机，也只是丢失了那部分还没有传送的数据(异步复制时才会丢失这部分数据)。

5.数据分析不再影响业务。

需要进行数据分析的时候，直接划分一台或多台slave出来专门用于数据分析。这样OLTP和OLAP可以共存，且几乎不会影响业务处理性能。

四、Mysql复制涉及的各类文件

不管是哪个数据库产品，一定会有日志文件。在MariaDB/MySQL中，主要有5种日志文件：

1.错误日志(error log)：记录mysql服务的启停时正确和错误的信息，还记录启动、停止、运行过程中的错误信息。

2.查询日志(general log)：记录建立的客户端连接和执行的语句。

3.二进制日志(bin log)：记录所有更改数据的语句，可用于数据复制。

4.慢查询日志(slow log)：记录所有执行时间超过long_query_time的所有查询或不使用索引的查询。

5.中继日志(relay log)：主从复制时使用的日志。

五、安装多个mysql服务

这里以wamp集成环境为例

1.步骤一：复制bin目录下的mysql安装目录，并命名为mysqlmaster

MySQL主从复制 - 图4

MySQL主从复制 - 图5

2.步骤二：打开安装目录，修改my.ini文件

修改port 端口为3307（自己定义），并修改basedir和datadir的路径为MySQL2的安装目录

MySQL主从复制 - 图6 MySQL主从复制 - 图7

3.步骤三：打开cmd并进入到mysqld运行目录下，以管理员身份运行（注意：一定要以管理员身份运行，不然会报错）

MySQL主从复制 - 图8

运行mysqld --initialize-insecure --user=mysql用来生成刚才删除的data目录，并把用户名和密码进行初始化，用户名初始化为root密码为空（大约有20秒的执行时间）

MySQL主从复制 - 图9

运行 mysqld install mysqlmaster --defaults-file="D:\wamp64\bin\mysqlmaster\mysql5.7.31\my.ini"（注意:这里是你自己的mysqlmaster配置文件目录）

==mysqlmaster 这个是你自定义的名称==

MySQL主从复制 - 图10 说明创建服务成功

4.启动mysqlmaster服务

打开控制面板->管理工具->服务 找到mysqlmaster 启动就行了

MySQL主从复制 - 图11

六、实现一主一从

此处先配置默认的异步复制模式。由于复制和binlog息息相关，如果对binlog还不熟悉，请先了解binlog，见：详细分析二进制日志。

mysql支持一主一从和一主多从。但是每个slave必须只能是一个master的从，否则从多个master接受二进制日志后重放将会导致数据混乱的问题。

以下是一主一从的结构图：

MySQL主从复制 - 图12

1.必须要有两个数据库

MySQL主从复制 - 图13

2.开启big-log日志

在[mysqld]模块，添加 log-bin=mysql-bin并进行重启mysqlmaster服务 MySQL主从复制 - 图14 进行检验是否开启成功

进行连接数据库，并运行以下命名show variables like '%log_bin%';

MySQL主从复制 - 图15

3. 创建一个用于从库访问主库的账号：
  • （主）链接主库并执行下列语句：
  • 创建一个账号，拥有所有权限
  1. GRANT ALL PRIVILEGES ON *.* TO 'slave'@'%' IDENTIFIED BY 'slave' WITH GRANT OPTION;

MySQL主从复制 - 图16

  • 刷新权限
  1. flush privileges;

MySQL主从复制 - 图17 ==重点：在这里重启两台mysql服务器~==

4.主（查看主服务器的状态）

show master status

MySQL主从复制 - 图18

记录下以上两个值，并且保证主服务器不要有任何操作

5.（从）从库链接主库
  • 链接从库
  • (从)执行下列sql语句，链接主库
  1. CHANGE MASTER TO 
  2. master_host='127.0.0.1',
  3. master_port=3307,
  4. master_user='slave',
  5. master_password='slave',
  6. master_log_file='mysql-bin.000004',
  7. master_log_pos=154;

MySQL主从复制 - 图19 ==注意==

1、master_log_file 与 master_log_pos 均是我们上一步在主库所查询的数据

2、如果无法执行以上sql语句，可能是你之前配置过，在所有操作之前，一定要执行一下 stop slave 停止从库

6.（从）启动从库
  1. start slave;

MySQL主从复制 - 图20

7.(从)查看从库状态
  1. show slave status\G

MySQL主从复制 - 图21

结果之中，以上两个值必须为 YES，否则配置就是失败的

到目前为止，我们主从就配好了！ 接下来我们来测试一下！

8.测试一下是否主从同步了
  • （主）创建一个库
  1.  create database sync_test;

MySQL主从复制 - 图22

  • （从） 查看从库是否与主库同步

MySQL主从复制 - 图23

七、框架实现读写分离

ThinkPHP6.x分布式数据库配置连接：https://www.kancloud.cn/manual/thinkphp6_0/1037577 MySQL主从复制 - 图24

==移出mysql服务命令==

  1. mysqld -remove mysql3309

==mysql3309 这个是你自己定义的名称==

扩展：主从延迟的解决方法

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

  1. 产生主从延迟的原因
  2. 1、TPS较高时，产生的DDL数量超过了Slave一个SQL线程处理能力
  4. 2、Slave中与大型的Query产生了锁等待
  6. 如何判断产生了主从延迟
  7. show slave status -> Seconds_Behind_Master
  9. 如何尽量避免主从延迟
  10. 1、关闭Slave的sync_binlog
  12. 2、关闭Slave的innodb_flushlog
  14. 3、使用性能比Master更好的机器作为Slave
  16. 4、设置一台不提供查询服务的Slave
  18. 5、增加Slave的数量，降低单台Slave上的读压力