MSYQL8主从复制（一主一从）配置教程

一、MySQL主从复制

1、作用

• 避免数据库的单点故障
• 实现多个MySQL间的数据一致

2、工作原理

• 核心依赖：二进制日志

1. 主库将写操作记录到二进制日志
2. 从库产生IO线程，连接主库，读取二进制日志内容，记录到本地的中继日
3. 从库产生SQL线程，读取中继日志的操作，在本地执
4. 存在问题：数据复制延迟，关注延迟时间

        ①增加网络带宽

        ②使用高IO的磁盘， SSD固态硬盘

3、常见架构

• 一主一从
• 一主多从
• 双主复制

4、准备环境

主机ip	角色
192.168.0.14	master
192.168.0.9	slave

•          
  • 两台机器分别安装好MSYQL环境，本文使用版本为：msyql-8.0.39
  • 保证两台机器可以互相访问，

  • 验证：

    mysql -uroot -h内网ip -P3306 -p
    mysql> show databases; #正常访问
    mysql> exit
    ss -ntl  #3306端口存在

二、主从配置

1、主库配置（192.168.0.14）

1、修改mysql配置文件

vim /data/mysql/my.cnf

#配置主库唯一ID，启用二进制日志（上面已经写了这就不加了）。
[mysqld]
# 主服务器唯一ID
server-id=1

2、重启mysql

#重启命令
service mysql restart
#查询mysql运行状态
service mysql restart

2、从库配置（192.168.0.9）

1、修改mysql配置文件

vim /data/mysql/my.cnf

#配置从库唯一ID，启用中继日志，启用二进制日志（上面已经写了这就不加了）。
[mysqld]
# 从服务器唯一ID
server-id=2
# 启用中继日志
relay-log=mysql-relay

2、重启mysql

#重启命令
service mysql restart
#查询mysql运行状态
service mysql restart

3、主数据库创建用户slave并授权 

1、创建用户slave并授权

#登录
mysql
#创建用户
create user 'slave'@'%' identified with mysql_native_password by 'xxxxxxx';
#授权
grant replication slave on *.* to 'slave'@'%';
#刷新权限
flush privileges;

2、从数据库验证slave用户是否可用

#从数据库服务器测试主数据库
mysql -uslave -h192.168.0.14 -P3306 -p

4、主数据库查询服务ID及Master状态

#登录
mysql
#查询server_id是否可配置文件中一致
show variables like 'server_id';
#若不一致，可设置临时ID（重启失效）
set global server_id = 1;
#查询Master状态，并记录 File 和 Position 的值
show master status;
#注意：执行完此步骤后退出主数据库，防止再次操作导致 File 和 Position 的值发生变化

5、从数据库中设置主数据库

#登录
mysql
#查询server_id是否可配置文件中一致
show variables like 'server_id';
#若不一致，可设置临时ID（重启失效）
set global server_id = 2;
#设置主数据库参数(File 和 Position的值必须和Master查出来的一致)
change master to master_host='192.168.0.14',master_port=3306, master_user='slave',master_password='xxxxxx',master_log_file='mysql-bin.000002',master_log_pos=156;
#开始同步
start slave;
#若出现错误，则停止同步，重置后再次启动
stop slave;
reset slave;
start slave;
#查询Slave状态
show slave status;
#查看是否配置成功
#查看参数 Slave_IO_Running 和 Slave_SQL_Running 是否都为yes，则证明配置成功。若为no，则需要查看对应的 Last_IO_Error 或 Last_SQL_Error 的异常值。

6、测试

#在主数据库创建数据库test
create database test;
#从数据库查看
show databases;
#在主数据库创建表
use test;
create table t_user(id int, name varchar(20));
#插入数据
insert into t_user values(1, 'Test');
#在从数据库查看
use test;
select * from t_user;
#其他增删改查操作请自行测试

7、遇到问题

        1、本次配置由于主数据库非新安装数据库并且有项目一直在连接访问，导致主库中的Position值一直在变化，从库配置好并启动主从复制后，从库状态中Slave_SQL_Running一直是No状态。因此，配置主从复制前，务必断开主库的其它访问连接，保证数据库不会发生数据变化，binlog文件的postion才能保证不变化