MySQL主从复制原理及轻松实现步骤

前言

这几年一直在it行业里摸爬滚打，一路走来，不少总结了一些python行业里的高频面试，看到大部分初入行的新鲜血液，还在为各样的面试题答案或收录有各种困难问题

于是乎，我自己开发了一款面试宝典，希望能帮到大家，也希望有更多的Python新人真正加入从事到这个行业里，让python火不只是停留在广告上。

微信小程序搜索：Python面试宝典

或可关注原创个人博客：https://lienze.tech

也可关注微信公众号，不定时发送各类有趣猎奇的技术文章：Python编程学习

主从复制

什么是主从复制？

主从复制至少需要两台服务器，或两个mysql服务，可以配置一主多从，多主多从

建立与某个业务数据库一样的数据库环境，即为主从复制，一般情况下，主库用以写，而从库用以读

---

为什么要搭建主从复制？

1. 构建主从热备，当某天数据库宕机或或数据丢失情况，可以有备份数据库继续工作
2. 降低IO频次，多库之间可以合理分配读写压力，提高单个数据库服务的数据库访问压力
3. 隔离读写，在某些锁表情况下，可以使数据库读操作继续进行

---

主从复制原理

利用数据库bin-log二进制文件，该文件包含有数据库操作的所有SQL语句

复制该文件至其余数据库服务中并执行即可

---

主从复制过程

1. 当主库具有新数据时，主库会被从库请求，建立线程进行连接，用以传输binlog日志

2. 从库开启两个线程

A线程：也叫做IO线程，连接主库，并请求binlog中的更新记录至从库中，写入至从库的relaylog文件中

B线程：也叫做SQL线程，读取relaylog文件中的更新操作并执行

3. 如果，有多个从库同时存在，主库会为每个从库建立一个binlog输出线程

set global validate_password_policy=LOW;
set global validate_password_length=6;

环境

此处以一主一丛为例，系统环境

• 主库（master）：192.168.1.100
• 从库（slave）：192.168.1.101

主库配置

主库配置

server-id = 1
log-bin=mysql-bin # 开启log bin
expire_logs_days=7 # 日志保存时间

server-id：同步数据中必须包含server-id，用于标识该语句最初是从哪个server写入

每个slave端只能有一个线程在master端连接，如果两个salve端的server-id一致，一个连接成功之后，前一个连接将会被断开

主主同步时，避免数据同步陷入死循环

• 主库创建用户，用以从机连接获取binlog日志

grant replication slave on *.* to 'master'@'%' identified by '123456';
flush privileges;

grant all privileges on *.* to master@'%' identified by "123456";

• 查看master状态

show master status;

• 记录上条命令返回的binlog文件名，Position属性，从机连接的时候要用

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

从库配置

• 从库配置修改

server-id=11

master与slave端的server-id不能一样，salve端无需开启log-bin功能

• 从库指定master，执行如下

change master to master_host='101.200.197.159', master_port=3306, master_user='master', master_password='123456', master_log_file='mysql-bin.000001', master_log_pos=245;

• 启动从机

start slave;

同步特定的库

主机处配置

binlog-do-db=xxxx   # 二进制日志记录的数据库
binlog-ignore-db=xxxx # 二进制日志中忽略数据库

从机处配置

replicate-do-db    # 设定需要复制的数据库
replicate-ignore-db # 设定需要忽略的复制数据库
replicate-do-table  # 设定需要复制的表
replicate-ignore-table # 设定需要忽略的复制表
replicate-wild-do-table # 同replication-do-table功能一样，但是可以通配符
replicate-wild-ignore-table # 同replication-ignore-table功能一样，但是可以加通配符

常见错误

• mysql->mariadb版本问题

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from position > file size'

从MySQL5.6开始引入了binlog_checksum全局变量

即MySQL会将event的CRC32校验值也写入binlog，显然MariaDB在分析日志的时候不会考虑该信息，导致解析出错

show variables like '%binlog%';

set global binlog_checksum=NONE;