MySQL全面瓦解27：主从复制（原理 + 实践）
概念
主从复制，是指建立一个和主数据库完全一样的数据库环境（称为从数据库），并将主库的操作行为进行复制的过程：将主数据库的DDL和DML的操作日志同步到从数据库上，
然后在从数据库上对这些日志进行重新执行，来保证从数据库和主数据库的数据的一致性。
为什么要做主从复制
1、在复杂的业务操作中，经常会有操作导致锁行甚至锁表的情况，如果读写不解耦，会很影响运行中的业务，使用主从复制，让主库负责写，从库负责读。
即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运行。
2、保证数据的热备份，主库宕机后能够及时替换主库，保障业务可用性。
3、架构的演进：业务量扩大，I/O访问频率增高，单机无法满足，主从复制可以做多库方案，降低磁盘I/O访问的频率，提高单机的I/O性能。
4、本质上也是分治理念，主从复制、读写分离即是压力分拆的过程。
5、读写比也影响整个拆分方式，读写比越高，主从库比例应越高，才能保证读写的均衡，才能保证较好的运行性能。读写比下的主从分配方法下：
| 读写比 | 主库 | 从库 |
| 50：50 | 1 | 1 |
| 66.6：33.3 | 1 | 2 |
| 80：20 | 1 | 4 |
| -- -- | -- -- | -- -- |
主从复制的原理
当在从库上启动复制时，首先创建I/O线程连接主库，主库随后创建Binlog Dump线程读取数据库事件并发送给I/O线程，I/O线程获取到事件数据后更新到从库的中继日志Relay Log中去，之后从库上的SQL线程读取中继日志Relay Log中更新的数据库事件并应用，
如下图所示：
细化一下有如下几个步骤：
1、MySQL主库在事务提交时把数据变更（insert、delet、update）作为事件日志记录在二进制日志表（binlog）里面。
2、主库上有一个工作线程 binlog dump thread，把binlog的内容发送到从库的中继日志relay log中。
3、从库根据中继日志relay log重做数据变更操作，通过逻辑复制来达到主库和从库的数据一致性。
4、MySQL通过三个线程来完成主从库间的数据复制，其中binlog dump线程跑在主库上，I/O线程和SQL线程跑在从库上。拥有多个从库的主库会为每一个连接到主库的从库创建一个binlog dump线程。
搭建主从实例
我们这边在个人PC机上进行MySQL主从复制的搭建测试，所以使用Docker会更方便。有如下优势：
1、可以节省资源。
2、相对于Docker来说，虚拟机搭建对机器配置有要求，且安装MySQL步骤繁琐。
3、一台机器上可以运行多个Docker容器，所以我们可以部署多个MySQL服务。
4、Docker容器之间相互独立，有独立ip，互不冲突
5、Docker使用步骤简便，启动容器在秒级别。
Daocker安装
我这边是mac机，以此作为示范。可以用Homebrew 进行安装，也可以手动下载安装。
手动下载的话可以点击以下链接下载 Edge Docker for Mac。
如同Mac OS 其它软件一样，安装也非常简单，双击下载的 .dmg 文件，然后将鲸鱼图标拖拽到 Application 文件夹即可。
从应用中找到 Docker 图标并点击运行。去Docker Hub上去注册一个Docker ID，进行登录即可。
打开终端，可以查看Docker相关的版本及相关信息。可以输入如下信息：
1 # 查看docker版本 2 docker --version 3 # 查看docker基本信息 4 docker info
搭建主从服务器
1、拉取docker的MySQL镜像，这边以5.7的版本为准：
1 docker pull mysql:5.7
2、使用此镜像启动主库容器：
1 docker run -p 3307:3306 --name master -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
从这边可以看出，Master主库容器对外映射的端口是3307，账号默认root，密码是123456，执行完之后，docker中运行了一个名为master的MySQL实例。
docker容器是相互独立的，每个容器有其独立的ip，所以不同容器使用相同的端口并不会冲突。这里我们应该尽量使用mysql默认的3306端口，否则可能会出现无法通过ip连接docker容器内mysql的问题。
因为容器内的3306端口映射的对外端口是3307，所以外部主机可以通过 宿主机ip:3307 访问到MySQL的服务，而实际是访问容器内MySQL的3306端口，密码就是我们前面设置好的123456。
登录完之后可以看到一个空库，这个就是我们在docker里面拉的MySQL镜像。
3、同样的道理，我们继续建一个slave（从库）的MySQL实例。注意名称为slave，端口为3308
1 docker run -p 3308:3306 --name slave -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
4、主从都搭建完成之后，我们可以使用 docker ps 来查看当前运行的容器信息，如下：
注意下第一个参数为 container id，是我们后续进入服务进行配置的容器识别编号。
配置主服务器（Master）
首先，进入到 Master 服务器。
1 wengzhihua@B000000147796DS ~ % docker exec -it 777fe9ce7f9d /bin/bash 2 root@777fe9ce7f9d:/#
这边注意：777fe9ce7f9d 是 master 的 container id，然后查看MySQL的状态。
1 root@777fe9ce7f9d:/# service mysql status 2 [info] MySQL Community Server 5.7.35 is running.
然后到MySQL的目录下去修改配置，切换到/etc/mysql目录下，然后 vi my.cnf 对my.cnf进行编辑：
1 root@777fe9ce7f9d:/# cd /etc/mysql 2 root@777fe9ce7f9d:/etc/mysql# vi my.cnf 3 bash: vi: command not found
此时会报出 bash: vi: command not found ，需要我们在docker容器内部自行安装vim。我们使用 apt-get install vim 命令来安装vim
1 root@777fe9ce7f9d:/etc/mysql# apt-get install vim 2 Reading package lists... Done 3 Building dependency tree 4 Reading state information... Done 5 E: Unable to locate package vim
这边又提示 Unable to locate package vim。
执行 apt-get update ，然后再次执行 apt-get install vim 即可成功安装vim。
然后我们就可以使用vim编辑my.cnf，在my.cnf中添加如下配置：
1 [mysqld] 2 ## 设置server_id，一般设置为IP，同一局域网内使用唯一值即可，注意要保证唯一，这边我们暂且使用主库的映射端口，方便识别 3 server_id=3307 4 ## 复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步） 5 binlog-ignore-db=mysql 6 ## 开启二进制日志功能，可以随便取，最好有含义（关键就是这里了） 7 log-bin=test-mysql-bin 8 ## 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存 9 binlog_cache_size=1M 10 ## 主从复制的格式（mixed,statement,row，默认格式是statement） 11 binlog_format=mixed 12 ## 二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。 13 expire_logs_days=7 14 ## 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。 15 ## 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致 16 slave_skip_errors=1062
配置完成之后重启服务：
1 service mysql restart
这个命令会使得容器停止，重新启动就可以了。
接下来创建数据同步用户：
1 root@777fe9ce7f9d:/etc/mysql# mysql -u root -p 2 Enter password: 3 Welcome to the MySQL monitor. Commands end with ; or \g. 4 Your MySQL connection id is 2 5 Server version: 5.7.35-log MySQL Community Server (GPL) 6 7 Copyright (c) 2000, 2021, Oracle and/or its affiliates. 8 9 Oracle is a registered trademark of Oracle Corporation and/or its 10 affiliates. Other names may be trademarks of their respective 11 owners. 12 13 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 14 15 mysql> CREATE USER 'slave'@'%' IDENTIFIED BY '123456'; 16 Query OK, 0 rows affected (0.02 sec) 17 18 mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%'; 19 Query OK, 0 rows affected (0.00 sec)
这里主要是要授予用户 slave，REPLICATION SLAVE权限和REPLICATION CLIENT权限，用于同步数据。
配置从服务器（Slave）
同主服务器一样的操作方式，先进入MySQL配置文件
1 wengzhihua@B000000147796DS ~ % docker exec -it d4ba5e063deb /bin/bash 2 root@d4ba5e063deb:/# cd etc/mysql 3 root@d4ba5e063deb:/etc/mysql# vi my.cnf
然后配置从库的信息：
1 [mysqld] 2 ## 设置server_id，一般设置为IP,注意要唯一，这边我们使用从库的映射端口，方便识别 3 server_id=3308 4 ## 复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步） 5 binlog-ignore-db=mysql 6 ## 开启二进制日志功能，以备Slave作为其它Slave的Master时使用 7 log-bin=test-mysql-slave1-bin 8 ## 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存 9 binlog_cache_size=1M 10 ## 主从复制的格式（mixed,statement,row，默认格式是statement） 11 binlog_format=mixed 12 ## 二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。 13 expire_logs_days=7 14 ## 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。 15 ## 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致 16 slave_skip_errors=1062 17 ## relay_log配置中继日志 18 relay_log=edu-mysql-relay-bin 19 ## log_slave_updates表示slave将复制事件写进自己的二进制日志 20 log_slave_updates=1 21 ## 防止改变数据(除了特殊的线程) 22 read_only=1
配置完成之后重启服务 service mysql restart ：
1 root@d4ba5e063deb:/etc/mysql# service mysql restart 2 [info] Stopping MySQL Community Server 5.7.35. 3 ... 4 [info] MySQL Community Server 5.7.35 is stopped. 5 [info] Re-starting MySQL Community Server 5.7.35.
跟上面一样，这个命令会使得容器停止，重新启动就可以了。
完成Master和Slave的连接
注意，需要保证 Master 和 Slave 除了不同步的数据库，其他数据库的数据要一致。
1、在 Master 进入 MySQL， 然后执行命令：
1 mysql> show master status; 2 +----------------------+----------+--------------+------------------+-------------------+ 3 | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | 4 +----------------------+----------+--------------+------------------+-------------------+ 5 | test-mysql-bin.000001 | 617 | | mysql | | 6 +----------------------+----------+--------------+------------------+-------------------+ 7 1 row in set (0.00 sec)
记录下 File 和 Position 字段的值，后面会用到。
2、然后再查询一下主从两个容器所对应的IP，主库对应 172.17.0.2，从库对应172.17.0.3：
1 wengzhihua@B000000147796DS ~ % docker ps 2 CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 3 d4ba5e063deb mysql:5.7 "docker-entrypoint.s…" 5 hours ago Up 19 minutes 33060/tcp, 0.0.0.0:3308->3306/tcp, :::3308->3306/tcp slave 4 777fe9ce7f9d mysql:5.7 "docker-entrypoint.s…" 5 hours ago Up 51 minutes 33060/tcp, 0.0.0.0:3307->3306/tcp, :::3307->3306/tcp master 5 wengzhihua@B000000147796DS ~ % docker inspect --format='{{.NetworkSettings.IPAddress}}' 777fe9ce7f9d 6 172.17.0.2 7 wengzhihua@B000000147796DS ~ % docker inspect --format='{{.NetworkSettings.IPAddress}}' d4ba5e063deb 8 172.17.0.3
3、然后到 Slave 中进入 mysql，执行命令：
1 mysql> change master to master_host='172.17.0.2', master_user='slave', master_password='123456', master_port=3306, master_log_file='test-mysql-bin.000001', master_log_pos=617, master_connect_retry=30; 2 Query OK, 0 rows affected, 2 warnings (0.02 sec)
大意就是在从库使用什么账号（slave）进行同步，同步的主库的IP（master_host）、密码（master_password）、端口（master_port）、binlog日志文件（master_log_file）以及其实同步的位置（master_log_pos）等，
理解下这个命令的各个参数：
1 master_host: Master 的IP地址 2 master_user: 在 Master 中授权的用于数据同步的用户，就我们之前在主库容器里创建的哪个slave用户 3 master_password: 同步数据的用户的密码 4 master_port: Master 的数据库的端口号，注意，这边是3306，不是3307，3307是映射外部宿主主机的，写成3307，会造成 Slave_IO_Running 一直是 Connecting 状态，在这边踩坑了 5 master_log_file: 指定 Slave 从哪个日志文件开始复制数据，即上文中提到的 File 字段的值 6 master_log_pos: 从哪个 Position 开始读，即上文中提到的 Position 字段的值 7 master_connect_retry: 当重新建立主从连接时，如果连接失败，重试的时间间隔，单位是秒，默认是60秒。
在 Slave 的 MySQL 终端执行查看主从同步状态
1 mysql> show slave status \G; 2 *************************** 1. row *************************** 3 Slave_IO_State: 4 Master_Host: 172.17.0.2 5 Master_User: slave 6 Master_Port: 3307 7 Connect_Retry: 30 8 Master_Log_File: edu-mysql-bin.000001 9 Read_Master_Log_Pos: 617 10 Relay_Log_File: edu-mysql-relay-bin.000001 11 Relay_Log_Pos: 4 12 Relay_Master_Log_File: edu-mysql-bin.000001 13 Slave_IO_Running: No 14 Slave_SQL_Running: No 15 Replicate_Do_DB: 16 Replicate_Ignore_DB: 17 Replicate_Do_Table: 18 Replicate_Ignore_Table: 19 Replicate_Wild_Do_Table: 20 Replicate_Wild_Ignore_Table: 21 Last_Errno: 0 22 Last_Error: 23 Skip_Counter: 0 24 Exec_Master_Log_Pos: 617 25 Relay_Log_Space: 154 26 Until_Condition: None 27 Until_Log_File: 28 Until_Log_Pos: 0 29 Master_SSL_Allowed: No
1 start slave;
1 mysql> show slave status \G; 2 *************************** 1. row *************************** 3 Slave_IO_State: Waiting for master to send event 4 Master_Host: 172.17.0.2 5 Master_User: slave 6 Master_Port: 3306 7 Connect_Retry: 30 8 Master_Log_File: edu-mysql-bin.000001 9 Read_Master_Log_Pos: 2351 10 Relay_Log_File: edu-mysql-relay-bin.000006 11 Relay_Log_Pos: 1259 12 Relay_Master_Log_File: edu-mysql-bin.000001 13 Slave_IO_Running: Yes 14 Slave_SQL_Running: Yes 15 Replicate_Do_DB: 16 Replicate_Ignore_DB: 17 Replicate_Do_Table: 18 Replicate_Ignore_Table: 19 Replicate_Wild_Do_Table: 20 Replicate_Wild_Ignore_Table: 21 Last_Errno: 0 22 Last_Error: 23 Skip_Counter: 0 24 Exec_Master_Log_Pos: 2351 25 Relay_Log_Space: 1640 26 Until_Condition: None
验证同步是否成功：
1、我们在主服务这边创建一个test库，库下面创建了一个person表，并增加了一条数据。
2、转到从库这边，马上就查询到数据了
主从同步延迟解决方案
最近部门在招人，面试Java程序员的时候在数据库部分问的最频繁的问题就是这个，问题不难，不过很多候选人同学没办法比较完整的回答，跟今天的标题有点相关，我就放上来。
按照我们这边的要求，主从库同步应该是近实时的，极端情况下也不应该超过8s，如果超过，我们认为是有问题的。
- 保证数据库处在最有状态下：优化系统配置（链接层或者存储引擎层）：最大连接数、允许错误数、允许超时时间、pool_size、log_size，保证内存、CPU、存储空间的扩容（硬件部分）。
- 业务量不多的情况下，不做读写分离。既然主从延迟是由于从库同步写库不及时引起的，那我们也可以在有主从延迟的地方改变读库方式，由原来的读从库改为读主库。当然这也会增加代码的一些逻辑复杂性。（部分业务读主库）
- 假如你的业务时间允许，你可以在写入主库的时候，确保数据都同步到从库了之后才返回这条数据写入成功，当然如果有多个从库，你也必须确保每个从库都写入成功。显然，这个方案对性能和时间的消耗是极大的，不推荐。
- 可以引入redis或者其他nosql数据库来存储我们经常会产生主从延迟的业务数据。当我在写入数据库的同时，我再写入一份到redis中。我们可以先去查看redis中是否有这个数据，如果有我们就可以直接从redis中读取这个数据。当数据真正同步到数据库中的时候，再从redis中把数据删除。
- 任何的服务器都是有吞吐量的限制的，没有任何一个方案可以无限制的承载用户的大量流量。所以我们必须估算好我们的服务器能够承载的流量上限是多少。达到这个上限之后，就要采取缓存，限流，降级的方式来应对我们的流量。这也是应对主从延迟的根本处理办法。
- 如果系统流量确实庞大，单纯的读写分离已经无法解决问题了，那么就应该对数据库进一步治理，垂直分区和水平分区是不错的方式，下一章我们会详细说说。
