大厂高频:讲一下MySQL主从复制

大家经常听说主从复制，那么主从复制的意义？能解决的问题有哪些？主从复制能解决的问题就是在我们平时开发的程序中操作数据库的时候，大多数的情况查询的操作大大超过了写的操作，也就说对数据库读取数据的压力比较大的问题，为了保证数据库操作的效率，我们一般把读的操作放在一个数据库执行（从库），比较耗时与频繁I/O的操作放在另外一个数据库（我们称之为主库）；这样做的意义就是减轻主库操作的压力，主从库可以各行其事，提高了数据库的操作效率！

一、主从复制的原理

主库会生成一个I/O操作线程进去写的的操作，而从库则生成两个线程，其一是I/O读取线程，其二是一个SQL线程。

总结如下三点：

1、主库将数据的操作记录到一个二进制日志中（即：binary log），也就是配置文件中指定的log-bin指定的文件就是日志文件。

2、从库将主库的日志文件拷贝到他的中继日志即配置文件中指定的relay log日志文件中，I/O线程去请求主库的bin-log日志，并将日志写入到relay log中继日志中，此时主库会生成一个log dump线程，用来给从库I/O线程传输bin-log日志件。

3、从库会更新relay log文件中的操作，将数据的改变在从库中进行数据重演即重新执行一次，即SQL线程执行操作，将日志文件中的记录变为数据操作行为再次执行，以达到主从数据最终一致性的目的。

二、主从复制原理图

注意

需要注意的是主从复制需要大量的I/O操作，所以延时是不可避免的。

三、准备工作

准备两台服务器(模拟一主一从)，并安装相同版本的MySQL数据库。

主库(master)地址：192.168.197.132 端口：3306。

从库(slave)地址：197.168.197.131 端口：3306。

版本：MySQL 8.0.25。

准备工作注意事项：

1、 每个master可以有多个slave，每个slave只属于一个master；

2、 数据库的版本必须保证一致；

3、 数据库的数据必须保证一致；

4、 主库与从库中配置文件中的server_id都必须唯一。

四、主库配置

配置文件修改my.cnf的[mysqld]栏位下，所有配置文件路径等不要出现中文、空格等非法操作。

4.1 配置文件

# 主机my.cnf配置文件如下：

#主服务器唯一ID

server-id=1

#启用二进制日志

log-bin=master-bin

#log-error=/opt/log #出错日志

#设置不要复制的数据库

binlog-ignore-db=testDB

#设置需要复制的数据库

binlog-do-db=mydb2

# 设置logbin格式，默认就是statement

# statement：#日志记录的是主机数据库的写指令 ,获取默认系统参数会出现数据不同步的情况，基本是可以忍受的，开发中大多数情况用的就是这种

# row：#基本不用

# mixed：#新出来的，与statement差不多，不会出现数据不同步现象

binlog_format=STATEMENT

4.2 操作命令

主库创建从库同步的用户，并授权。

操作命令如下：

# 创建slave用户

CREATE USER 'slave01'@'%';

# 设置密码

ALTER USER 'slave01'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

# 授权

GRANT REPLICATION SLAVE ON *.* TO 'slave01'@'%';

flush privileges;

查询主节点信息命令：

SHOW MASTER STATUS

结果如下图：

需要记录下Position值，需要在从库中设置同步起始值，特别重要，到此为止主库切记不要再动任何操作。

五、从库配置

从库的配置就更加少了，除了server-id依然要保持唯一性，需要再加一个中继日志配置即可。

5.1 配置文件

# 从机my.cnf配置文件如下

# server-id保证唯一并配置修改中继日志

server-id=2

# MySQL中继日志

relay-log=mysql-relay

注意

修改完配置文件需要重新启动数据库服务。

重启命令：

systemctl restart mysqldSUMMER

5.2 操作命令

从库执行配置主从关系：

CHANGE MASTER TO

MASTER_HOST = '192.168.197.132',

MASTER_PORT = 3306,

MASTER_USER = 'slave01',

MASTER_PASSWORD = '123456',

MASTER_LOG_FILE = 'master-bin-log.000001',

MASTER_LOG_POS = 1048;

语句详解：

master_port：主库的ip地址

master_port：主库的端口

master_user：用户名

master_password：密码

master_log_file：上节中主库查询的file项对应的值

master_log_pos：上节中主库查询的的值

启动slave同步：

START SLAVE;

查看同步状态：

SHOW SLAVE STATUS;

六、主从复制测试成果

在主库创建testdb数据库，刷新从库即可看到新建的testdb库；

主库testdb创建test1表，从库出现test1表。

主库修改数据，从库跟着改变。

总结

通过配置主从复制在一定程度上可以减轻服务器的压力，提高应用的体验；并且可以达到读库与写库在数据一致性方面达到最终一致性，写的操作必须操作于主库，读数据则没有限制。