MySQL8.0.20主从复制

1、什么是主从复制

• 主从复制：是用来建立一个和主数据库完全一样的数据库环境,称为从数据库,主数据库一般是准实时的业务数据库。

2、主从复制的作用

• 做数据的热备,作为从数据库主数据库服务器故障后,可切换到从数据库继续工作,避免数据丢失。
• 架构的扩展,业务量越来越大,I/O(网络)访问频率过高,单机无法满足，此时做多库的存储，降低I/O(磁盘)访问的频率，提高单个机器的I/O(网络)性能。
• 读写分离,使数据库能支撑更大的并发,在报表中尤其重要,由于部分报表sql语句非常的慢,导致锁表，影响前台服务,如果前台使用主数据库,报表使用从数据库，那么报表sql将不会造成前台锁,保证了前台速度。

3、主从复制的原理

• 数据库有个bin-log二进制文件，记录了所有sql语句。
• 我们的目标就是把主数据库的bin-log文件的sql语句复制过来。
• 让其在从数据的relay-log重做日志文件中再执行一次这些sql语句即可。

4、搭建主从复制过程

4.1、拉取Mysql镜像

docker pull mysql:latest

4.2、启动主数据库

docker run -d -p 3307:3306 --name mysql-master 
-v /mydata/mysql/master/log:/var/log/mysql  
-v /mydata/mysql/master/data:/var/lib/mysql 
-v /mydata/mysql/master/conf:/etc/mysql 
-e MYSQL_ROOT_PASSWORD=123456 
--restart=always 
--privileged=true 
mysql:latest

#注：有可能-e环境没有生成

默认Mysql挂载位置为：/var/lib/mysql,第一次启动会出现以下错误：
Failed to access directory for --secure-file-priv. Please make sure that directory exists and is accessible by MySQL Server. Supplied value : /var/lib/mysql-files
在[mysqld]添加以下内容即可：
secure_file_priv=/var/lib/mysql

4.3、配置主数据库

[mysqld]
user=mysql
character-set-server=utf8
default_authentication_plugin=mysql_native_password
secure_file_priv=/var/lib/mysql
expire_logs_days=7
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
max_connections=1020

server-id=1 
log_bin=mysql-bin 
read-only=0 
binlog-do-db=gulimall_ums 
binlog-do-db=gulimall_pms
binlog-do-db=gulimall_oms 
binlog-do-db=gulimall_sms 
binlog-do-db=gulimall_wms 
binlog-do-db=gulimall_admin 
replicate-ignore-db=mysql 
replicate-ignore-db=sys 
replicate-ignore-db=information_schema 
replicate-ignore-db=performance_schema

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

binlog-do-db：为需要同步的数据库
replicate-ignore-db：不需要同步的数据库

4.4、启动从数据库

docker run -d -p 3317:3306 --name mysql-slave 
-v /mydata/mysql/slave/log:/var/log/mysql  
-v /mydata/mysql/slave/data:/var/lib/mysql 
-v /mydata/mysql/slave/conf:/etc/mysql 
-e MYSQL_ROOT_PASSWORD=123456 
--restart=always 
--privileged=true 
mysql:latest

#注：有可能-e环境没有生成

4.5、配置从数据库

[mysqld]
user=mysql
character-set-server=utf8
default_authentication_plugin=mysql_native_password
secure_file_priv=/var/lib/mysql
expire_logs_days=7
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
max_connections=1020

server-id=2 
log-bin=mysql-bin 
read-only=1 
binlog-do-db=gulimall_ums 
binlog-do-db=gulimall_pms
binlog-do-db=gulimall_oms 
binlog-do-db=gulimall_sms 
binlog-do-db=gulimall_wms 
binlog-do-db=gulimall_admin 
replicate-ignore-db=mysql 
replicate-ignore-db=sys 
replicate-ignore-db=information_schema 
replicate-ignore-db=performance_schema

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

binlog-do-db：为需要同步的数据库
replicate-ignore-db：不需要同步的数据库

4.6、客户端工具连接主从数据库

1、防火墙开放两台主从的端口
firewall-cmd --add-port=3317/tcp --permanent
firewall-cmd --add-port=3307/tcp --permanent

2、重启防火墙规则
firewall-cmd --reload

3、修改root用户Host权限
update user set Host='%' where User='root';

4、远程连接授权
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'WITH GRANT OPTION;

5、刷新Mysql权限
FLUSH PRIVILEGES;

6、更改加密规则
ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;

7、重置root用户密码
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'password';

1. 利用Navicat客户端分别连接主从数据库

4.7、主从配置信息

1. 首先查看主从数据库是否开启日志

show variables like 'log%'
或
show variables like '%log_bin%';

2. 请确保主从一定要开启bin-log日志功能

3. 主机数据库需要添加一个账户用来从机访问不能使用root用户访问

#创建一个用户
CREATE user 'backup'@'%' identified by '123456';

#赋予在 '主' '从' 数据库之间同步数据的权限
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'backup'@'%';

#刷新权限
FLUSH PRIVILEGES;

#查看master状态
show master status;

4. 从机数据库配置主库连接

#配置主库连接
CHANGE MASTER TO master_host = '192.168.47.155',
master_user = 'backup',
master_password = '123456',
master_log_file = 'mysql-bin.000007',
master_log_pos = 156,
master_port = 3307;

#启动从库同步
start slave;

#查看slave状态
show slave status;

5. 出现以下情况证明主从搭建成功进行同步验证操作

4.8、主从复制验证

1. 创建一个名称为：gulimall_oms 的数据库

CREATE DATABASE IF NOT EXISTS gulimall_oms
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_0900_ai_ci;

2. 导入数据库表信息

gulimall_oms.sql

3. 主从数据库同步成功