MySQL主从复制

目录

概述 

原理：

搭建

服务器准备

开启两台服务器

主库配置

从库配置

测试

---

概述 

        主从复制是指将数据库的DDL和DML操作通过二进制日志传到从数据库中，然后再数据库上对这些日志重新执行（也叫重做），从而使得从库和主库的数据保持同步。

补充

DDL（数据定义语言）：主要命令有create、drop、alter；

DML（数据操作语言）：主要命令有：insert、update、delete；

DQL（数据查询语言）：由SELECT子句，FROM子句，WHERE子句组成的查询块

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

MySQL复制的优点主要包括：

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

🥈实现读写分离，降低主库的访问压力

🥉可以在从库中执行备份，以避免备份期间影响主库服务

原理：

 从上图来看，复制分为三步：

①：master主库在事务提交时，会把数据变更记录在二进制文件binlog中；

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

③：slave重做中继日志文件，将改变反映它自己的数据。

搭建

服务器准备

 准备两台服务器，192.168.213.135作为主服务器，192.168.213.134作为从服务器，两台服务器都需要开放指定的3306端口或者关闭防火墙；（两台服务器都是mysql5.7版本）

开放指定的端口

firewall-cmd --add-port=3306/tcp --permanent        #  开放3306端口       

firewall-cmd -reload                # 刷新防火墙规则

查看防火墙情况

 关闭防火墙

systemctl stop firewalld 

systemctl disable firewalld

开启两台服务器

systemctl start mysqld                # 开启MySQL服务器

systemctl enable mysqld                # 运行开机自启

主库配置

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

# 开启二进制日志

log-bin = /var/lib/mysql/bin-log

# mysql 服务ID，保证整个集群环境中唯一，取值为：1~ -1，默认值为1

server-id=1

# 是否只读，1表示只读，0表示读写

read-only=0

# 忽略的数据，指不需要同步的数据库

# binlog-ignore-db=mysql

#指定同步的数据库

# binlog-do-db=db01

 重启mysql服务

2.创建远程连接的账号，并赋予主从复制权限

grant replication slave on *.* to 'rep'@'192.168.213.%' identified by '123456';

3.通过指令，查看二进制日志坐标

show master status；

 字段说明：       

        file：从哪个日志文件开始推送日志文件

        position：从哪个位置开始推送日志文件

        binlog_igore_db：指定不需要同步的数据库

注意，如果主库与从库数据有差异，须手动将主库的数据与从库的数据保持一致，才可以进行主从复制。

🍬如果主库与从库已经一致或者都没数据，则不需要后面的配置

4.清空从库数据库（注意不能将原有的四个数据库也删除了）

 查看主库

 5.在主库中，进行锁表操作，防止在备份数据库时，有他人对主库进行增删改；

# 锁表只读

flush tables with read lock;

# 注意 如果超过设置时间不操作会自动解锁

show variables like '%timeout%'

6.备份数据库，并使用gzip进行压缩

[root@cotenos ~]# mysqldump -uroot -p -A -B | gzip > /backup/mysql-all-`date +%F-%T`.sql.gz

注意首先需要创建 /backup目录

查看备份文件是否存在

 7.进行解锁

unlock tables;

8.将主库备份的数据上传到从库上

在从库上创建 /backup目录用来存放主库上传的文件

[root@bogon ~]# mkdir /backup

[root@cotenos ~]# scp /backup/mysql-all-2023-03-05-17\:20\:00.sql.gz 192.168.213.134:/backup/

9.将主库的文件进行解压并写入从库的数据库中。

查看从服务器中文件是否存在

 [root@bogon ~]# zcat /backup/mysql-all-2023-03-05-17\:20\:00.sql.gz | mysql -uroot -p

 

8.进入从数据库查看 

[root@bogon ~]# mysql -uroot -p -e 'show databases;'

从库配置

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

# mysql 服务ID，保证整个集群环境中唯一，和主库的ID不一样即可

server-id=2

# 是否只读，代表只读，0表示读写

read-only=1

# 注意如果如果二进制时开启状态的化需要关闭二进制日志

 重启mysql服务器

2.登录mysql数据库，设定主从同步

# mysql8.0.23版本之前

 change master to 
     MASTER_HOST='192.168.213.135',
     MASTER_PORT=3306,
     MASTER_USER='rep',
     MASTER_PASSWAORD='123456',
     MASTER_LOG_FILE='bin-log.000007',
     MASTER_LOG_POS=448;

# 8.0.23版本之后

change REPLICATION SOURCE TO

        SOURCE_HOST='192.168.213.135',

        SOURCE_USER='rep',

        SOURCE_PASSWORD='123456',

        SOURCE_LOG_FILE='bin-log.000007',

        SOURCE_LOG_POS='448';

参数(8.0.23之前)	含义	参数（8.0.23之后）
MASTER_HOST	主库的ip地址	SOURCE_HOST
MASTER_PORT	连接主库的端口号	SOURCE_PORT
MASTER_USER	连接主库的用户名	SOURCE_USER
MASTER_PASSWORD	连接主库的密码	SOURCE_PASSWORD
MASTER_LOG_FILE	binlog日志文件名	SOURCE_LOG_FILE
MASTER_LOG_POS	binlog日志文件位置	SOURCE_LOG_POS

3.启动主从同步开关

# 8.0.23版本之前

mysql> start slave;

# 8.0.23版本之后

mysql> start replica

查看状态

mysql> show slave status\G;

 主从状况监制主要参数：

        Slave_IO_Running：IO线程是否打开 YES/NO/NULL

        Slave_SQL_Running: SQL线程是否打开 YES/NO/NULL

        Seconds_Behind_Master：NULL # 和主库比同步的延迟的秒数

可能导致主从延迟的因素：

        主从时钟是否一致

        网络通信是否存在延迟

        是否和日志的类型，数据过大有关

        从库性能，有没有开启binlog

        从库查询是否优化

测试

1.在主库上创建数据库、表，并插入数据

create database db01;

use db01;

create table tb_user(
     id int(11) primary key auto_increment,
     name varchar(20) not null
     );
insert into tb_user values (4,'Java'),(5,'c++');

 2.在从库中查询数据，检验主从是否同步