MySQL主从复制之常规模式（异步模式）（一主一从/双主双从）

MySQL主从部署模式

MySQL主从复制有异步模式、半同步模式、GTID模式以及多源复制模式，MySQL默认模式是异步模式。所谓异步模式，是指MySQL主服务器上I/O thread 线程将二进制日志写入binlog文件之后就返回客户端结果，不会考虑二进制日志是否完整传输到从服务器以及是否完整存放到从服务器上的relay日志中，这种模式一旦主服务(器)宕机，数据就会发生丢失。

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–master将操作语句记录到binlog日志中，然后授予slave远程连接的权限（master一定要开启binlog二进制日志功能；通常为了数据安全考虑，slave也开启binlog功能）。

2–slave开启两个线程：IO线程和SQL线程。其中：IO线程负责读取master的binlog内容到中继日志relay log里；SQL线程负责从relay log日志里读出binlog内容，并更新到slave的数据库里，这样就能保证slave数据和master数据保持一致了。

3–Mysql复制至少需要两个Mysql的服务，当然Mysql服务可以分布在不同的服务器上，也可以在一台服务器上启动多个服务。

4–Mysql复制最好确保master和slave服务器上的Mysql版本相同（如果不能满足版本一致，那么要保证master主节点的版本低于slave从节点的版本）

5–master和slave两节点间时间需同步

mysql主从同步延时分析

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

解决方案：

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

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

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

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

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

6.使用更加强劲的硬件设备

一主一从

创建目录

先创建简单MySQL，复制配置文件：

创建MySQL网络

创建两个MySQL，一主一从

主（master）配置

重要参数解析：
server-id：设置server_id，一般设置为IP，同一局域网内注意要唯一。（必须）
log-bin：开启二进制日志功能，可以随便取，最好有含义（关键就是这里了）。（必须）
binlog_ignore_db：复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步）。
其他参数：
binlog_cache_size=1M：为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存。
binlog_format=mixed：主从复制的格式（mixed,statement,row，默认格式是statement）。
expire_logs_days=7：二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。
slave_skip_errors=1062：跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致。
auto-increment-increment=2：表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1，取值范围是1 ~ 65535。
auto-increment-offset=2：表示自增长字段从哪个数开始，指字段一次递增多少，他的取值范围是1 ~ 65535。
binlog-do-db=数据库名字：设置需要复制的数据库。

从（slave）配置

参数解析：
server-id：设置server_id，一般设置为IP，同一局域网内注意要唯一。（必须）
relay-log：启用中继日志。（必须）
其他参数：
log_slave_updates=1：log_slave_updates表示slave将复制事件写进自己的二进制日志。
read_only=1：防止改变数据(除了特殊的线程)。

在主（master）MySQL上建立帐户并授权 slave

这里主要是要授予用户 slave REPLICATION SLAVE权限和REPLICATION CLIENT权限。

记录主（master）MySQL信息

分别记录下File和Position的值。执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化。

在从（slave）MySQL上配置需要复制的主机

命令解释：
master_host：Master 的IP地址
master_user： 在 Master 中授权的用于数据同步的用户
master_password： 同步数据的用户的密码
master_port： Master 的数据库的端口号
master_log_file： 指定 Slave 从哪个日志文件开始复制数据，即上文中提到的 File 字段的值
master_log_pos： 从哪个 Position 开始读，即上文中提到的 Position 字段的值
master_connect_retry：当重新建立主从连接时，如果连接失败，重试的时间间隔，单位是秒，默认是60秒。

启动从服务器复制功能：

在 Slave 的 MySQL 终端执行查看主从同步状态：

下面两个参数都是Yes，则说明主从配置成功！
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

双主双从

创建目录

先创建简单MySQL，复制配置文件：

创建MySQL网络

创建四个MySQL，双主双从

主（master）配置

主1：

主2：

重要参数解析：
server-id：设置server_id，一般设置为IP，同一局域网内注意要唯一。（必须）
log-bin：开启二进制日志功能，可以随便取，最好有含义（关键就是这里了）。（必须）
log-slave-updates：在作为从数据库的时候，有写入操作也要更新二进制日志文件。（主MySQL作为从库，需要这个参数）
binlog_ignore_db：复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步）。
其他参数：
binlog_cache_size=1M：为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存。
binlog_format=mixed：主从复制的格式（mixed,statement,row，默认格式是statement）。
expire_logs_days=7：二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。
slave_skip_errors=1062：跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致。
auto-increment-increment=2：表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1，取值范围是1 ~ 65535。
auto-increment-offset=2：表示自增长字段从哪个数开始，指字段一次递增多少，他的取值范围是1 ~ 65535。
binlog-do-db=数据库名字：设置需要复制的数据库。

从（slave）配置

从1：

从2：

参数解析：
server-id：设置server_id，一般设置为IP，同一局域网内注意要唯一。（必须）
relay-log：启用中继日志。（必须）
其他参数：
log_slave_updates=1：log_slave_updates表示slave将复制事件写进自己的二进制日志。
read_only=1：防止改变数据(除了特殊的线程)。

在两个主（master）MySQL上建立帐户并授权 slave

两个主（master）MySQL都要执行：

这里主要是要授予用户 slave REPLICATION SLAVE权限和REPLICATION CLIENT权限。

记录主（master）MySQL信息

主1：

主2：

分别记录下File和Position的值。执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化。

在四个MySQL上配置需要复制的主机（主从复制，主主复制）

在从1上执行，复制主1的数据：

在从2上执行，复制主2的数据：

在主1上执行，复制主2的数据：

在主2上执行，复制主1的数据：

命令解释：
master_host：Master 的IP地址
master_user： 在 Master 中授权的用于数据同步的用户
master_password： 同步数据的用户的密码
master_port： Master 的数据库的端口号
master_log_file： 指定 Slave 从哪个日志文件开始复制数据，即上文中提到的 File 字段的值
master_log_pos： 从哪个 Position 开始读，即上文中提到的 Position 字段的值
master_connect_retry：当重新建立主从连接时，如果连接失败，重试的时间间隔，单位是秒，默认是60秒。

启动四个服务器复制功能（四个MySQL都要执行以下命令）：

四个 MySQL 终端执行查看主从同步状态（四个MySQL都要执行以下命令）：

下面两个参数都是Yes，则说明主从配置成功！
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

参考

基于 Docker 的 MySQL 主从复制搭建