【MySQL系列】MySQL主从复制

作者：行百里er

博客：https://chendapeng.cn (opens new window)

提示

这里是行百里er 的博客：行百里者半九十，凡事善始善终，吾将上下而求索！

最近业务量有点大，I/O访问频率过高，之前单节点MySQL有点扛不住压力了，于是我找老板又搞了一台服务器，准备上MySQL的主从复制和读写分离，做多库的存储，提高单个机器的性能，老板欣然同意！

最重要的是，在搭建MySQL读写分离期间，有了这篇水文，很快啊，希望读者大大们耗子尾汁，多多点赞，谢谢朋友们！

# 主从复制

在业务复杂的系统中，有这么一个情景，有一句sql语句需要锁表，导致暂时不能使用读的服务，那么就很影响运行中的业务。

使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。

另外，可以做数据的热备份。

再一个，像我们的业务，业务量上来了，需要做架构的扩展，做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。

# 什么是主从复制

MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。

MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。

# MySQL主从复制原理

master服务器将数据的改变记录二进制binlog日志。当master上的数据发生改变时，则将其改变写入二进制日志中；
slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/O Thread请求master二进制事件；
同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后I/O Thread和SQL Thread将进入睡眠状态，等待下一次被唤醒。

也就是说：

从库会生成两个线程，一个I/O线程，一个SQL线程；
I/O线程会去请求主库的binlog，并将得到的binlog写到本地的relay-log(中继日志)文件中；
主库会生成一个log dump线程，用来给从库I/O线程传binlog；
SQL线程会读取relay log文件中的日志，并解析成sql语句逐一执行。

# MySQL主从复制形式

# 一主一从

一个主节点，一个从节点，简单方便，稍后我们就搭建这种一主一从的主从复制模式。

# 主主复制

主主复制：互为主从

容易产生的问题：数据不一致；因此慎用
考虑要点：自动增长id

一个节点id使用奇数：

auto_increment_offset=1
auto_increment_increment=2

1
2

另一个主节点id使用偶数：

auto_increment_offset=2
auto_increment_increment=2

1
2

# 一主多从

常用于扩展系统读取性能，因为读是在从库读取的。

# 多主一从

从MySQL 5.7版本开始支持，也叫多源复制，数据流向是从多个主库同步数据到一个从库：

多应用于：

数据汇总，可将多个主数据库同步汇总到一个从数据库中，方便数据统计分析。
读写分离，从库只用于查询，提高数据库整体性能。

# 级联复制

在主从复制的基础上，主从之间存在一个级联复制的从服务器，当级联复制的主机复制了主服务器的数据，级联复制主机充当为主服务器，从服务器复制级联复制主机的数据及二进制日志数据。

Tip：中间级联复制的主机无法将二进制日志传递到其他从服务器上，因此需要加上log_slave_updates选项，目的是为了将主服务器的二进制日志文件能写入到从服务器上。

# 搭建一主一从主从复制环境

# 1. 主机规划

序号	主机名称	IP地址	虚拟机OS	MySQL
1	mysql-master	192.168.2.158	CnetOS 7.8	5.7
2	mysql-slave	192.168.2.159	CnetOS 7.8	5.7

# 2. 安装MySQL

还未安装的，直接参考：

如何在CentOS7下快速安装MySQL5.7 (opens new window)

CV操作就行了。

# 3. 两台MySQL服务器创建数据库

两台主机均执行：

mysql> create database laogong;

该数据库用来指定同步的数据库名称。

# 4. 配置Master

# 4.1 配置主节点

在mysql-master节点下配置 /etc/my.cnf ，在[mysqld]模块下添加如下内容：

log-bin=master-bin
binlog-format=ROW
server-id=1
binlog-do-db=laogong

1
2
3
4

log-bin：定义二进制文件名称。

binlog-format：二进制日志格式，有row、statement、mixed三种格式。

row：把改变的内容复制过去，而不是把命令在从服务器上执行一遍。
statement：在主服务器上执行的SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。
mixed：默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。

server-id：服务器的id，各节点的id必须不一样。

binlog-do-db：要同步的数据库名称。

# 4.2 授权从服务器登录主服务器

--授权操作
set global validate_password_policy=0;
set global validate_password_length=1;
grant replication slave on *.* to 'root'@'%' identified by '123456';
--刷新权限
flush privileges;

1
2
3
4
5
6

# 5. 配置Slave

在mysql-slave节点配置 /etc/my.cnf ，在 [mysqld] 模块下添加：

log-bin=slave-bin
binlog-format=ROW
server-id=2

1
2
3

# 6. 重启Master，查看状态

mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |      154 | laogong      |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+

1
2
3
4
5
6

# 7. 重启Slave并进行相关配置

mysql> change master to master_host='192.168.2.158',master_user='root',master_password='123456',master_port=3306,master_log_file='master-bin.000001',master_log_pos=154;
mysql> start slave;
mysql> show slave status\G

1
2
3

查看slave状态时，可以看到有报错信息：

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

这个报错是因为我偷懒，在master主机安装好MySQL以后，我直接克隆的一台slave主机，导致auto.cnf文件中保存的UUID是重复的。

解决方法很简单，停掉slave的mysql实例，删除其 datadir 文件夹下的的 auto.cnf 文件，再启动备库实例，此时备库就会产生一个新的 auto.cnf 文件，也就会生成和master的mysql服务不一样的UUID。

此时再查看slave状态：

show slave status\G

就OK了。

# 8. 验证

在主库创建一个table：

mysql> use laogong;
Database changed
mysql> create table xblzer(id int,name varchar(20));
Query OK, 0 rows affected (0.05 sec)

1
2
3
4

从库读取：

mysql> use laogong;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-------------------+
| Tables_in_laogong |
+-------------------+
| xblzer            |
+-------------------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12

再在主库添加1条记录：

mysql> insert into xblzer values(1, 'laowang');

在从库查看：

mysql> select * from xblzer;
+------+---------+
| id   | name    |
+------+---------+
|    1 | laowang |
+------+---------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7

这样，MySQL一主一从的主从复制架构就搭建起来了。

那么，为了减轻每台MySQL主机的访问压力，还可以对MySQL进行读写分离，实际上，主从复制和读写分离一般就是联合使用的。我们实际生产环境使用的是sharding-jdbc来实现的读写分离。

还有其他的像 mycat ，amoeba等中间件也可以做读写分离，下次有机会再撸MySQL数据库的读写分离，本次导航就到此为止，下次再肝。。。

首发公众号 行百里er ，欢迎老铁们关注阅读指正。

#MySQL

上次更新: 2022/10/04, 18:14:30

← 【调优向】关于 MySQL 查询的优化【MySQL系列】真香！基于ShardingSphere-JDBC的MySQL读写分离→