MySQL 主从同步原理与实践

MySQL 主从同步

一、为什么要实现主从同步

高并发阶段，数据库压力会非常大。然而实际上大部分的网站、 app，其实都是读多写少。针对这个情况，可以维持一个主库（数据写入），主库挂多个从库（数据读取），主库会自动把数据给同步到从库上去，一写多读，减少数据库的查询压力，从而提高并发能力。

二、MySQL 主从复制原理

主库将变更写入 binlog 日志，然后从库连接到主库之后，从库有一个 IO 线程，将主库的 binlog 日志拷贝到自己本地，写入一个 relay 中继日志中。接着从库中有一个 SQL 线程会从中继日志读取 binlog，然后执行 binlog 日志中的内容，也就是在自己本地再次执行一遍 SQL，这样就可以保证自己跟主库的数据是一样的。

1. 从库同步主库数据的过程是串行化的，也就是说主库上并行的操作，在从库上会串行执行。由于从库从主库拷贝日志以及串行执行 SQL 的特点，在高并发场景下，从库的数据一定会比主库慢一些，是有延时的。所以经常出现，刚写入主库的数据可能是读不到的，要过几十毫秒，甚至几百毫秒才能读取到。

2. 如果主库突然宕机，然后恰好数据还没同步到从库，那么有些数据可能在从库上是没有的，有些数据可能就丢失了。

所以 MySQL 实际上在这一块有两个机制，一个是半同步复制，用来解决主库数据丢失问题；一个是并行复制，用来解决主从同步延时问题。

（1） 半同步复制，也叫 semi-sync 复制，指的就是主库写入 binlog 日志之后，就会将强制此时立即将数据同步到从库，从库将日志写入自己本地的 relay log 之后，接着会返回一个 ack 给主库，主库接收到至少一个从库的 ack 之后才会认为写操作完成了。

（2）并行复制，指的是从库开启多个线程，并行读取 relay log 中不同库的日志，然后并行重放不同库的日志，这是库级别的并行。

MySQL 主从同步延时 show status可以查看 Seconds_Behind_Master，可以看到从库复制主库的数据落后了几ms。

一般来说，如果主从延迟较为严重，有以下解决方案：

1. 分库，将一个主库拆分为多个主库，每个主库的写并发就减少了几倍，此时主从延迟可以忽略不计。
2. 打开 MySQL 支持的并行复制，多个库并行复制。如果说某个库的写入并发就是特别高，单库写并发达到了 2000/s，并行复制还是没意义。
3. 重写代码，写代码的同学，要慎重，插入数据时立马查询可能查不到。
   如果确实是存在必须先插入，立马要求就查询到，然后立马就要反过来执行一些操作，对这个查询设置直连主库。

三、MySQL主从复制实践

MySQL主从复制一般情况下我们会设置需要同步的数据库，使用参数配置选项：binlog-do-db，可以在master上指定需要同步的数据库；replicate-do-db，在从数据看上指定需要同步的数据库。（一般只设定master上的binlog-do-db即可，不需要两个同时设定。以防万一，在slave也可以加上replicate-ignore-db）。

3.1 环境准备

准备好两个数据库，手动将主库数据同步到从库。

（1）备份数据： mysqldump -uroot -p123456 oyc > /home/data/oyc.sql
（2）导入到从库

3.2 主库配置

（1）配置主库配置文件

vim /etc/my.cnf
配置：
log-bin=mysql-bin #开启二进制日志
server-id=1 #设置server-id

（2）创建同步用户并授权

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY '123456';#创建用户 

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';#分配权限 

mysql> flush privileges;   #刷新权限

（3）重启MySQL并查看主库状态

systemctl restart mysqld

mysql> show master status;
±-----------------±---------±-------------±-----------------±------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±-----------------±---------±-------------±-----------------±------------------+
| mysql-bin.000001 | 154 | | | |
±-----------------±---------±-------------±-----------------±------------------+
1 row in set (0.00 sec)

3.3 主库配置

(1) 配置从库配置文件

vim /etc/my.cnf
 
server-id=2                  #设置server-id，必须唯一
replicate-do-db=oyc
replicate-do-table=oyc.user

（# 只同步哪些数据库，除此之外，其他不同步  
binlog-do-db = oycblog  
# 不同步哪些数据库  
binlog-ignore-db = mysql  
binlog-ignore-db = test  
binlog-ignore-db = information_schema  ）

（2） 重启MySQL并配置主从关系

systemctl restart mysqld

---

执行mysql指令：
CHANGE MASTER TO
MASTER_HOST=‘146.56.192.87’,
MASTER_USER=‘repl’,
MASTER_PASSWORD=‘123456’,
MASTER_LOG_FILE=‘mysql-bin.000001’,
MASTER_LOG_POS=154;

mysql> start slave;

mysql> show slave status\G;

mysql> stop slave;

3.4 其他配置

忽略错误

mysql> stop slave;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 ;       #跳过一个事务
mysql> start slave