MySQL的主从复制
读写分离
复制：每个节点都有相同的数据集，向外扩展，基于二进制日志的单向复制
复制的功用
负载均衡读操作
备份
高可用和故障切换
数据分布
MySQL升级
主从复制原理
主从复制相关线程
主节点：
dump Thread：为每个Slave的I/O Thread启动一个dump线程，用于向其发送binary log events
从节点：
I/O Thread：向Master请求二进制日志事件，并保存于中继日志中
SQL Thread：从中继日志中读取日志事件，在本地完成重放
跟复制功能相关的文件：
master.info：用于保存slave连接至master时的相关信息，例如账号、密码、服务器地址等
relay-log.info：保存在当前slave节点上已经复制的当前二进制日志和本地relay log日志的对应关
系
mysql-relay-bin.00000#: 中继日志,保存从主节点复制过来的二进制日志,本质就是二进制日志
主从复制特点
异步复制: 客户端性能良好
主从数据不一致比较常见
一Master/一Slave
一主多从
从服务器还可以再有从服务器
Master/Master
一从多主:适用于多个不同数据库
环状复制
复制需要考虑二进制日志事件记录格式
案例：一主一从复制
环境准备；
主服务器：172.31.0.28
从服务器：172.31.0.38
两台安装相同版本的mysql软件
[root@centos8 ~]# yum install mysql-server -y
[root@centos8 ~]# yum install mysql-server -y
主改配置
[root@centos8 ~]# cat /etc/my.cnf
[mysqld]
server-id=28
log-bin=/data/mysql/mysql-bin
创建目录存放二进制日志
[root@centos8 ~]# mkdir /data/mysql -p
改所属组
[root@centos8 ~]# chown -R mysql.mysql /data/mysql/
[root@centos8 ~]# chown -R mysql.mysql /var/lib/mysql
重启服务
[root@centos8 ~]# systemctl enable --now mysqld
进入mysql
[root@centos8 ~]# mysql
# 查看日志，记录下来后面从同步要用
mysql> show master logs;
+------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 | 179 | No |
| mysql-bin.000002 | 156 | No |
+------------------+-----------+-----------+
# 创建一个数据库
mysql> create database db1;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
# 创建用户管理主从复制
mysql> create user 'repluser'@'172.31.0.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
# 授权
mysql> grant replication slave on *.* to repluser@'172.31.0.%';
Query OK, 0 rows affected (0.00 sec)
从改配置
[root@localhost ~]# cat /etc/my.cnf
[mysqld]
server-id=38
# 重启
[root@centos8 ~]# systemctl enable --now mysqld
# 登陆mysql，这里是没有设置密码
[root@localhost ~]# mysql
# 查看当前所有数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
# 查看change格式
mysql> help change master to
# 添加如下设置
mysql> CHANGE MASTER TO
MASTER_HOST='172.31.0.28',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=156;
# 启动slave
mysql> start slave;
# 查看sql和io状态 看到Slave_IO_Running: Yes 和Slave_SQL_Running: Yes 即是主从复制成功
mysql> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.31.0.28
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 1295
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 1463
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1295
Relay_Log_Space: 1676
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 28
Master_UUID: 761c8309-b8e1-11eb-bb76-000c29acf5a4
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
# 再次查看所有数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
案例：在上面的基础上实现一主多从
# 新增一台服务器：172.31.0.48
安装相同版本mysql
[root@centos8 ~]# yum install mysql-server -y
# 改配置文件
[root@localhost ~]# cat /etc/my.cnf
[mysqld]
server-id=48
read_only=ON
# 启动
[root@centos8 ~]# systemctl enable --now mysqld
# 登陆mysql，这里是没有设置密码
[root@localhost ~]# mysql
# 查看当前所有数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
# 添加如下设置
mysql> CHANGE MASTER TO
MASTER_HOST='172.31.0.28',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=156;
# 启动slave
mysql> start slave;
# 查看sql和io状态 看到Slave_IO_Running: Yes 和Slave_SQL_Running: Yes 即是主从复制成功
mysql> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.31.0.28
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 1295
Relay_Log_File: centos8-relay-bin.000002
Relay_Log_Pos: 1463
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1295
Relay_Log_Space: 1674
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 28
Master_UUID: 761c8309-b8e1-11eb-bb76-000c29acf5a4
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
# 再次查看所有数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
在从节点清除信息
# 注意：以下都需要先 STOP SLAVE
RESET SLAVE #从服务器清除master.info ，relay-log.info, relay log ，开始新的relay log
RESET SLAVE ALL #清除所有从服务器上设置的主服务器同步信息，如HOST，PORT, USER和 PASSWORD等
# 系统变量，指定跳过复制事件的个数
SET GLOBAL sql_slave_skip_counter = N
# 服务器选项，只读系统变量，指定跳过事件的ID
[mysqld]
slave_skip_errors=1007|ALL
实现主从级联复制
需要在中间的从服务器启用以下配置，实现中间slave节点能将master的二进制日志在本机进行数据库更新，并且也同时更新本机的二进制，从而实现级联复制
环境准备：
# 在172.31.0.28充当master
# 在172.31.0.38充当级联slave
# 在172.31.0.48充当slave
## 在172.31.0.28充当master改配置文件
[root@centos8 ~]# cat /etc/my.cnf
[mysqld]
server-id=28
log-bin=/data/mysql/mysql-bin
# 重启
[root@centos8 ~]# systemctl enable --now mysqld
# 登录mysql，没有密码
[root@centos8 ~]# mysql
# 创建用户管理主从复制
mysql> create user 'repluser'@'172.31.0.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
# 授权
mysql> grant replication slave on *.* to repluser@'172.31.0.%';
Query OK, 0 rows affected (0.00 sec)
## 在172.31.0.38充当级联slave
[root@centos8 ~]# cat /etc/my.cnf
[mysqld]
server-id=38
log_slave_updates
log-bin=/data/mysql/mysqld-bin
# 重启
[root@centos8 ~]# systemctl enable --now mysqld
# 登录mysql，没有密码
[root@centos8 ~]# mysql
# 添加
CHANGE MASTER TO
MASTER_HOST='172.31.0.28',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=156;
# 启动slave
mysql> start slave;
# 查看sql和io状态 看到Slave_IO_Running: Yes 和Slave_SQL_Running: Yes 即是主从复制成功
mysql> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.31.0.28
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 867
Relay_Log_File: centos8-relay-bin.000002
Relay_Log_Pos: 1035
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 867
Relay_Log_Space: 1246
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 28
Master_UUID: ecd17bd7-b954-11eb-8928-000c29acf5a4
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
# 这里的刷新授权就不用再级联数据库创建管理slave用户
mysql> flush privileges;
# 查看所有数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| information_schema |
| mysql |
| performance_schema |
| sys |
| tesedb |
+--------------------+
6 rows in set (0.00 sec)
## 在172.31.0.48充当slave
[root@centos8 ~]# cat /etc/my.cnf
[mysqld]
server-id=48
read-only
# 重启
[root@centos8 ~]# systemctl enable --now mysqld
# 登录mysql，没有密码
[root@centos8 ~]# mysql
# 添加172.31.0.38的二进制日志
CHANGE MASTER TO
MASTER_HOST='172.31.0.38',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysqld-bin.000002',
MASTER_LOG_POS=156;
# 查看sql和io状态 看到Slave_IO_Running: Yes 和Slave_SQL_Running: Yes 即是主从复制成功
mysql> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.31.0.38
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 1055
Relay_Log_File: centos8-relay-bin.000002
Relay_Log_Pos: 1035
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1055
Relay_Log_Space: 1243
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 38
Master_UUID: 53470252-b955-11eb-a77c-000c29169a81
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
# 查看所有数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| information_schema |
| mysql |
| performance_schema |
| sys |
| tesedb |
+--------------------+
6 rows in set (0.00 sec)