运维 主从复制 概述

概述 主从复制是将主数据库的DDL和DML操作通过二进制日志传到从库服务器中，然后在从库上对这些日志重新执行(也叫重做)， 从而使得从库和主库的数据保持同步。主库称为master，从库称为slave

MySQL支持一台主库同时向多台从库进行复制，从库同时也可以作为其他从服务器的主库，实现链状复制

主从复制的作用

1、主库出现问题，可以快速切换到从库提供服务

2、实现读写分离，降低主库的访问压力。例如增删改的请求操作主库，查询的请求操作从库

3、可以在从库中执行备份，以避免备份期间影响主库服务。在备份期间会有全局锁，当前数据库处于只读状态

运维 主从复制 原理

原理 思考主库中的数据是如何同步到从库中的。主从复制基于主库的二进制日志文件

主库: 一旦发生增删改操作，以及执行DDL语句的时候，就会将所有的数据变更写入到binlog前缀的日志里面，binlog前缀的日志叫二进 制日志(我们前面学过)，也就是在二进制日志当中记录了主库所有的数据变更

从库:涉及到两组线程，来保证主从数据库的数据是一致的

1、IOthread: 发起请求，用于连接主库，实时读取主库当中的二进制日志，并且该线程会将读取到的日志数据写入到自身(从库)的日志里面 这个自身的日志就叫relay.log(中继日志)

2、SQLthread: 读取中继日志里面的数据，把中继日志里面的数据变化，记录到自身数据库(从库)的数据变化

例如 在主库中执行一条insert语句，这条insert语句就会被写入二进制日志，然后被从库的IOthread线程读取，并写入它自己从库里面的中继日志， 最后从库的SQLthread会读取中继日志，当读到这条insert语句的时候，就会在从库中也执行一次这条insert语句

严格一点来讲主从复制的过程的话，如下三步

1、master主库在事务提交时，会把数据变更记录在二进制日志文件binlog中

2、从库读取主库的二进制日志文件binlog，写入到从库的中继日志relay log

3、slave从库重做中继日志的时间，将改变反映它自己的数据

运维 主从复制 主库配置

准备两台mysql服务器。主库称为master，从库称为slave。这节课只需要操作主库(192.168.127.138)

1、master: 192.168.127.138

2、slave: 192.168.127.145

在主库服务器开放指定的3306端口号

firewall-cmd --zone=public --add-port=3306/tcp -permanent
firewall-cmd -reload

或者在主库服务器直接把防火墙关掉(我们推荐使用这种，不使用上面开放3306端口的方式)

systemctl stop firewalld
systemctl disable firewalld

检查一下主库的mysql的服务是否开启

systemctl status mysqld

上面是基本配置 下面是主库的配置

打开192.168.127.138这台服务器

1、修改配置文件 /etc/my.cnf

#mysql服务ID，保证整个集群环境中唯一，取值范围: 1~2^32-1，默认为1
server-id=1

#是否只读，1表示当前mysql服务器是只读型的，0表示读写型。由于是配置主库，所以需要设置为既能读也能改
read-only=0

#忽略的数据，指不需要同步的数据库。指定我们在同步的时候，会忽略哪些数据库，即哪些库的数据不会被同步过去。可选
#binlog-ignore-db=mysql
#指定同步的数据库。即哪些数据库要被同步过去。可选，也就是可不设置都没事
#binlog-do-db=db01

具体操作如下:

vim /etc/my.cnf

会出现一个文本编辑页面，使用键盘的向下箭头，在末尾行输入如下两行，然后保存退出

server-id=1 #配置服务器id
read-only=0 #当前mysql服务器是读写型的

2、重启mysql服务器

systemctl restart mysqld

3、登录mysql，创建远程连接的账号(如果有账号了，就当做是多创建一个)，并授予主从复制的权限 #先登录mysql

myslq -u root -p

#修改密码复杂度，0表示密码复杂度为简单类型，6表示密码长度为6为

set global validate_password.policy = 0;
set global validate_password.length = 6;

#创建huanf用户，并设置密码，%表示该用户可在任意主机连接该MySQL服务。例如用户名huanf，密码228675 create user 'huanf'@'%' identified with mysql_native_password by '228675'; #为huanf用户(也就是'huanf'@'%'用户)分配主从复制的权限 grant replication slave on . to 'huanf'@'%';

4、紧接着，查看二进制日志坐标

show master status;

查询出来的结果中，我们只要只关注三个字段，如下 (1)File字段: 表示我们会写到哪个日志文件，也就是从哪个日志文件开始推送日志文件。例如binlog.000007，表示从库以后就会读取这个文件 (2)Position字段: 表示位置，也就是从哪个位置开始推送日志。例如661，也就是从库以后就会从binlog.000007文件里面的661位置往后进行 同步数据，或者简单说就是从库无法读取到主库binlog.000007文件里面的661位置之前的数据，只能读取到661位置往后的数据 (3)binlog_ignore_db: 指定不需要同步的数据库

5、登录我们创建好的huanf用户

mysql -u huanf -p

暂时不要动，目前已经配置好主库了，下面开始配置从库

运维 主从复制 从库配置

准备两台mysql服务器。主库称为master，从库称为slave。这节课只需要操作从库(192.168.127.145)

1、master: 192.168.127.138

2、slave: 192.168.127.145

在从库服务器开放指定的3306端口号

firewall-cmd --zone=public --add-port=3306/tcp -permanent
firewall-cmd -reload

或者在从库服务器直接把防火墙关掉(我们推荐使用这种，不使用上面开放3306端口的方式)

systemctl stop firewalld
systemctl disable firewalld

检查一下从库的mysql的服务是否开启

systemctl status mysqld

上面是基本配置

下面是从库的配置

打开192.168.127.145这台服务器

1、修改配置文件 /etc/my.cnf

#mysql服务ID，保证整个集群环境中唯一，取值范围: 1~2^32-1，默认为1
#注意我们主库已经设置为1了，这里的从库我们就设置为2。从库id只要不跟主库重复，并且不超出范围就行
server-id=2

#是否只读，1表示当前mysql服务器是只读型的，0表示读写型。对于从库来说，最大的功能是备份主库的数据，从库不需要增
#删改的功能，因此我们设置为只读型(只对普通用户来说是只读，如果用户有超级管理员super权限那么是可读写的，我们在具体操作会有解决)
read-only=1

具体操作如下:
vim /etc/my.cnf
会出现一个文本编辑页面，使用键盘的向下箭头，在末尾行输入如下三行，然后保存退出
server-id=2 #配置从库的mysql服务器id
read-only=1 #当前mysql服务器是只读型的
super-read-only=1 #即使是超级管理员super权限，也是只读

2、重启mysql服务器

systemctl restart mysqld

3、这个从库如何才能关联上节课的主库

change replication source to source_host='xxx.xxx',source_user='xxx',source_password='xxx',source_log_file='xxx',source_log_pos=xxx;

上述是8.0.23之后的语法，如果mysql是8.0.23之前的版本，那么就执行如下SQL，效果是一样的

change master to master_host='xxx.xxx.xxx.xxx',master_user='xxx',master_password='xxx',master_log_file='xxx',master_log_pos=xxx;

上面的xxx解释如下 (1)source_host: 要连接哪个主库，后面指定ip地址即可 (2)source_user: 要连接主库的哪个用户名，后面指定用户名即可 (3)source_password: 用户(你在上一行指定的用户)的密码是什么，后面指定密码即可 (4)source_log_file: 要读取主库的哪个二进制日志文件，也就是用于同步主库的数据 (5)source_log_pos: 要读取主库二进制文件的哪个位置开始的数据，也就是同步主库的哪里开始的数据

具体操作如下(需要登录进mysql从库192.168.127.145，才能进行如下操作):

mysql -u root -p
change replication source to source_host='192.168.127.138',source_user='huanf',source_password='228675',source_log_file='binlog.000007',source_log_pos=661;

注意: huanf、228675、binlog.000007、661是上节课我们主库配置的，不知道怎么写可以去上节课看看

4、在从库开启同步，即可同步主库的数据。也就是开启主从复制。具体操作如下(需要登录进mysql从库192.168.127.145，才能进行如下操作)

start replica; #8.0.22版本之后
start slave; #8.0.22版本之前

5、查看从库的状态。可以看到当前从库关联的是哪个主库、主库的端口号、主库的用户名、...。具体操作如下(需要登录进mysql从库192.168.127.145，才能进行如下操作)

xxxxxxxxxx
show replica status; #8.0.22版本之后。或者show replica status\G; 。后者的命令可以在内容不变的情况下让页面简洁
show slave status; #8.0.22版本之前

\G: 表示把每一列的数据展示为每一行的数据，可以让数据展示更精简

我们在查看从库状态时，会查看到从库的配置信息，里面的内容代表什么，如下 (1)Source_Host: 当前关联的主库的ip地址 (2)Source_User: 当前关联的主库的用户名 (3)Source_Port: 当前关联的主库的端口号 (4)Source_Log_File: 从主库的哪个二进制文件开始读取 (5)Source_Log_Pos: 从主库的二进制文件的哪个位置开始读取 (6)Relay_Log_File: 涉及到的中继日志是哪个 (7)Relay_Log_Pos: 中继日志的位置 (8)Replica_IO_Running: IO线程(读取二进制日志并且写入中继日志，在前面的a_120_0课学过)运行是否正常，也就是主从复制是否正常，例如yes (9)Replica_SQL_Running: SQL线程运行是否正常，也就是主从复制是否正常，例如yes

接下来就是验证当我们在主库中进行相关操作之后，具体的数据能不能同步到从库，下面就进行测试

运维 主从复制 测试

在主库执行如下，查看一下当前主库有哪些表

show databases;

在从库执行如下，查看一下当前从库有哪些表，注意一下此时是初始数据

show databases;

然后在主库执行如下的增删改操作，按理来说我们在主库执行的DDL和DML语句都会同步到从库中，验证如下

create database if not exists bilibili;
use bilibili;
create table stu(
    id int primary key comment '主键ID',
    name varchar(10) comment '姓名',
    age int comment '年龄'
)engine=innodb default charset=utf8mb4;
insert into stu values (1,'tom',1),(3,'cat',3),(8,'rose',8),(11,'jetty',11),(19,'lily',19),(25,'luci',25);
show tables;
select * from stu;

然后去从库看一下，有没有数据变更，如下

show databases;
use bilibili;
select * from stu;

发现数据都同步过来了，在从库有了主库的数据

最后验证一下如果在主库进行更新、删除语句，会在从库也同步吗，如下

update stu set age = 18;
delete from stu where id = 1;

在从库查看，最终验证

select * from stu;

发现数据也都同步过来了，在从库有了主库的数据，这两个mysql服务器的数据是同步的

主从复制的使用场景:

当并发需求大，我们可以把查询的操作安排在从库，分担主库压力。还可用于备份数据库，避免了全局锁对数据库造成不一致的问题