mysql主从架构

mysql主从架构是一套非常基础的高可用架构，主要依赖复制技术来实现。

1.复制原理

mysql复制功能主要使用三个线程实现：

1.Binary log dump thread（二进制日志转储线程）:当副本连接时发送二进制日志

2.Replication I/O receiver thread（复制 I/O 接收器线程）: I/O（接收器）线程,连接到源服务器要求它发送其二进制日志中记录的更新，复制源服务器发送的二进制日志保存到中继日志（relay log）

3.Replication SQL applier thread （复制 SQL 应用程序线程）：复制中继日志并执行其中包含的记录

主库执行DDL、DML更新binlog
二进制日志转储线程检测到binlog更新主动发送binlog给从库
从库I/O线程接收到binlog后写入到relaylog中
从库SQL线程检测到relaylog更新，复制relaylog到SQL线程并执行SQL。

2.配置主从

1.主库建立唯一的服务器 ID

mysql> SET GLOBAL server_id = 2;

持久化修改：

在/etc/my.cnf添加server-id = 2

查看server_id变量

mysql> show global variables like 'server_id';

2.主库开启二进制日志

查看二进制日志是否开启

mysql> SHOW VARIABLES LIKE 'log_bin';

除了使用mysqld命令启动mysql二进制日志是默认不开启的，其他情况都是二进制日志默认是开启的

如果不开启，需要修改/etc/my.cnf配置文件

在[mysqld]添加

log_bin=ON

重启mysql

3.创建复制用户

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY '123456';

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

查看用户

mysql> select Host,User from mysql.user;

4.获取主库二进制日志坐标

先锁表，阻止InnoDB进行Commit操作，待副本复制好后可以释放锁，保证主库和从库的数据一致性（会话退出也会自动释放锁）

mysql> FLUSH TABLES WITH READ LOCK;

确定当前二进制日志文件名和位置

mysql> SHOW MASTER STATUS\G

5.对主库进行快照

可以选择物理备份、逻辑备份等方法。。。

我这里用的是docker，我直接对主库容器进行镜像，再用这个镜像开启容器作为mysql从库

查看容器

创建镜像

docker commit 10d74d587551 mysql8-slave

查看镜像

释放主库的read lock

mysql> UNLOCK TABLES;

6.启动从库

docker run --name=mysql8-slave10 --restart on-failure -p 3310:3306 -d mysql8-slave:latest

连接上从库服务器

docker exec -it mysql8-slave10 bash

修改从库的server_id

SET GLOBAL server_id = 21;

持久化修改：

在/etc/my.cnf添加server-id = 21

从库如果不需要binlog可以关闭该功能，毕竟开启需要消耗资源

如果这个从库是用作备份或者故障恢复等需要用到binlog可以开启binlog

关闭从库binlog

在/etc/my.cnf添加
[mysqld]
# 关闭二进制日志
skip-log-bin

7.在从库设置主库复制信息

MySQL 8.0.23之前使用这种格式:

mysql> CHANGE MASTER TO

-> MASTER_HOST='source_host_name',

-> MASTER_USER='replication_user_name',

-> MASTER_PASSWORD='replication_password',

-> MASTER_LOG_FILE='recorded_log_file_name',

-> MASTER_LOG_POS=recorded_log_position;

MySQL 8.0.23之后使用这种格式:

mysql> CHANGE REPLICATION SOURCE TO

-> SOURCE_HOST='source_host_name',

-> SOURCE_USER='replication_user_name',

-> SOURCE_PASSWORD='replication_password',

-> SOURCE_LOG_FILE='recorded_log_file_name',

-> SOURCE_LOG_POS=recorded_log_position;

我连接的是本机的3309端口

CHANGE REPLICATION SOURCE TO SOURCE_HOST='10.0.12.12',SOURCE_USER='repl',SOURCE_PASSWORD='123456',master_port=3309,SOURCE_LOG_FILE='mysql-bin.000001',SOURCE_LOG_POS=1284;

8.从库启动slave

mysql> start slave;

查看slave状态

mysql> SHOW SLAVE STATUS\G

我第一次启动发现启动失败

报错：

Last_IO_Error: Error connecting to source 'repl@10.0.12.12:3309'. This was attempt 1/86400, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

意思是连接失败，需要caching_sha2_password安全连接

从MySQL 8.0 中，默认身份验证插件caching_sha2_password

解决办法：

重新设置主库复制信息，加上了主库公钥的位置获取公钥：

CHANGE REPLICATION SOURCE TO SOURCE_HOST='10.0.12.12',SOURCE_USER='repl',SOURCE_PASSWORD='123456',master_port=3309,SOURCE_LOG_FILE='mysql-bin.000001',SOURCE_LOG_POS=1284,Master_public_key_path='/var/lib/mysql/public_key.pem',Get_master_public_key=1;

再次启动又出现错误：

Last_IO_Error: Fatal error: The replica I/O thread stops because source and replica have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

意思是主库和从库的MySQL服务器UUID相同了

解决办法：

删除从库的UUID，重启从库重新生成UUID

rm -rf /var/lib/mysql/auto.cnf

这两个为yes表示启动成功

9.测试

主库操作

mysql> create database mytest;
Query OK, 1 row affected (0.02 sec)mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mytest             |
| performance_schema |
| sys                |
| test888            |
+--------------------+
6 rows in set (0.03 sec)

从库同步

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mytest             |
| performance_schema |
| sys                |
| test888            |
+--------------------+
6 rows in set (0.00 sec)