MySQL主从复制¶
零. 架构¶
master 192.168.100.101
salve 192.168.100.100
注意： master和slave的密码策略保持一致。可以使用mysql_secure_installation命令进行修改。
二. 主从复制的原理¶
1. 传统主从复制的一些概念¶
在master上开启二进制日志；创建用于主从复制的账户及授权；
在slave/上开启中继日志；设置change source to语句；
change source to语句的含义：从知道去哪连接的master是谁，使用哪个账号去连接，连接的密码又是什么，请求master上的哪个二进制文件，从该二进制文件的哪个位置开始。
在slave上启动主从复制 start replica;
slave会产生两个线程：
IP线程，连接master并向其发送请求，将获取到的二进制文件内容保存到中继日志中
SQL线程：读取中继日志中的内容并转换成SQL语句，然后执行。
二进制日志的记录方式：
基于语句statment
基于行ROW
基于混合mixed
二进制日志记录数据发送变化/更改的操作。如增删改表、建库、删库、删表等。
传统的主从复制是基于二进制实现的。
2. GTID¶
1). 事务
事务是将MySQL的多个相关联操作打包起来的工作单元，其具有以下四大特性
-
原子性 (Atomicity)：执行单元内的操作不可分割。要么全部提交成功，要么全部回滚失败。实现靠的是undo log（回滚日志），它记录数据修改前的旧值，一旦要回滚，就用旧值恢复数据。
-
一致性 (Consistency)：事务执行前后，数据必须符合所有约束规则（如外键、唯一索引等）。这是数据库的最终目标，原子性、隔离性和持久性共同为它服务。
-
隔离性 (Isolation)：多个事务并发执行时，互不干扰。实现靠的是锁和MVCC（多版本并发控制）。
-
持久性 (Durability)：一旦事务提交，数据就永久保存在硬盘上，即使宕机也不丢失。实现靠的是redo log（重做日志），它记录数据修改后的新值，用于崩溃恢复。
2). GTID
GTID是MySQL为每一个提交的事务分配的全局唯一标识符。GTID可以使得事物可以通过GTID来简单进行定位。
3. 主从复制的过程¶
a. 当主库与从库连接后，从库利用I/O线程登录主库用于复制的账户账号密码完成身份和权限的验证
b. 身份验证通过后，主库会为这个连接创建对应的Binlog Dump线程，专门负责把Binlog事件推送给从库的I/O线程。
c. 当主库提交事务后，主库的事务和其GTID会被写入到binlog二进制文件中
d. 复制账户利用主库的Binlog Dump线程从Binlog中读取这些事务事件（GTID作为事件头的一部分），推送给从库的I/O线程。I/O线程将其原样写入从库的中继日志（Relay Log） 中。
e. 从库上的SQL线程将中继日志中的事务在从库上执行，从而完成主从复制。
三. 实现主从复制¶
0. 配置时间同步¶
我们使用chrony工具实现时间同步
直接用我写好的脚本来安装，内容如下
#! /bin/bash
#安装时间同步服务chrony
mount /dev/sr0 /media/cdrom &>/dev/null
sleep 2
dnf -y install chrony &>/dev/null
sed -i 's/^pool/#pool/' /etc/chrony.conf
cat << EOF >> /etc/chrony.conf
server ntp.aliyun.com iburst
server ntp1.aliyun.com iburst
server ntp2.aliyun.com iburst
server ntp3.aliyun.com iburst
EOF
systemctl restart chronyd
sleep 5
if chronyc sources -v|grep -q '^\^\*';then
echo "chronyd同步成功"
else
echo "chronyd同步失败"
fi
将该脚本在两台主机上分别执行
1. 配置3作为master¶
a. 修改mysql的配置文件
[root@3 ~]# vi /etc/my.cnf
[client]
socket = /usr/local/mysql/mysql.sock #客户连接使用的socket
[mysqld]
server_id=10 #将这台 MySQL 服务器的 ID 指定为 10
port = 3306 #默认监听的端口为3306
user = mysql #mysql进程的维护用户为mysql
character-set-server = utf8mb4 #默认字符集
default_storage_engine = innodb #默认存储引擎
log_timestamps = SYSTEM #日志时间戳使用系统时间
socket = /usr/local/mysql/mysql.sock #服务端的socket文件存放的位置
basedir = /usr/local/mysql #mysql的安装目录
datadir = /data/mysql/data #mysql数据文件的存放目录
pid-file = /usr/local/mysql/mysql.pid #mysql进程id存放位置
log_error = /data/mysql/log/mysql-error.log #mysql错误日志的存放位置
sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
#STRICT_TRANS_TABLES 严格模式核心 — 对事务表启用严格模式，插入/更新数据时若值无效（如类型错误、超出长度），直接报错而非发出警告
#NO_ZERO_IN_DATE 不允许月份或日为零的日期，例如 '2010-00-01'、'2024-01-00' 会被拦截。现已被废弃，但不会报错
# 不允许 '0000-00-00' 作为合法日期。现已被废弃，但不会报错
#ERROR_FOR_DIVISION_BY_ZERO 除零操作直接报错，而非返回 NULL 并给警告
#NO_ENGINE_SUBSTITUTION 建表时如果指定了被禁用的存储引擎（如 Memory 被禁用），直接报错，而非自动替换为默认引擎（InnoDB）
skip-name-resolve #跳过DNS反向解析
innodb_print_all_deadlocks = 1 #每次发生死锁，都把详情写到错误日志里
log_bin = /data/mysql/log/binlog #开启二进制文件
binlog_format = ROW #以行格式记录二进制的内容
slow_query_log = ON #开启慢查询日志
slow_query_log-file = /data/mysql/log/slow.log
#记录慢查询日志的select语句
long_query_time = 1 #查询超时时间
log_queries_not_using_indexes = ON #查询语句没有使用后索引也会记录到慢查询日志文件中
gtid_mode=ON #开启GTID模式
enforce-gtid-consistency=ON #强制GTID一致性，开启后，违反 GTID 一致性的语句会被直接拒绝执行，确保复制不会出错。
log_slave_updates=ON #记录从库上执行的中继日志事务到从库自己的 binlog。
binlog_expire_logs_seconds=604800 #二进制日志binlog的自动过期时间设置为604800 秒
sync_binlog=1 #每次事务提交都强制将 binlog 刷入磁盘。
binlog_cache_size=4M #在事务期间用于缓存 binlog 写入的内存缓冲区大小。
max_binlog_cache_size=2G #单个事务的binlog缓存上限为2G
innodb_buffer_pool_size=10G #innodb缓冲池大小设置为10GB
innodb_log_file_size=1G #单个 InnoDB redo log（重做日志） 文件的大小，设为 1GB
innodb_log_buffer_size=64M #InnoDB redo log 的缓冲区大小，设为 64MB。
innodb_flush_log_at_trx_commit=1 #每次事务提交时，强制将 redo log 刷入磁盘。
innodb_file_per_table=1 #每个 InnoDB 表使用独立的表空间文件（表名.ibd），而不是全部存到共享的 ibdata1 中。
max_connections=5000 #MySQL 允许的最大并发连接数为 5000。
max_allowed_packet=64M #客户端与服务器之间传输的单个数据包最大为 64MB。
thread_cache_size=64 #MySQL 缓存待复用的线程数量为 64 个。
binlog-do-db=school #只主从复制school这个库
binlog-ignore-db=information_schema #不复制
binlog-ignore-db=mysql #不复制
binlog-ignore-db=performance_schema #不复制
binlog-ignore-db=sys #不复制
b. 重启服务
[root@3 log]# systemctl start mysqld
[root@3 log]# ss -tuan|grep 3306
tcp LISTEN 0 70 *:33060 *:*
tcp LISTEN 0 151 *:3306 *:*
c. 在3上创建用于主从复制的账户，并给予他相应权限
[root@3 ~]# mysql -uroot -p
mysql> CREATE USER 'repl_user'@'192.168.100.100' identified by 'Abcd1234';
Query OK, 0 rows affected (0.04 sec)
mysql> #创建用户'repl_user'@'192.168.100.100'，密码为'Abcd1234'
mysql> GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.100.100';
Query OK, 0 rows affected (0.01 sec)
mysql> #赋予该用户REPLICATION SLAVE（复制从库权限）和REPLICATION CLIENT（复制客户端权限）
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> #刷新权限
mysql> quit
Bye
d. 备份3上的所有库并将备份文件发送给2
[root@3 ~]# mysqldump -u root -p --single-transaction --routines --events --triggers -B school > backup-school.sql
[root@3 ~]# scp backup-school.sql root@192.168.100.100:/root
e. 查看主库状态
[root@3 ~]# mysql -u root -p
mysql> show binary log status \G
*************************** 1. row ***************************
File: binlog.000006
Position: 325
Binlog_Do_DB: school
Binlog_Ignore_DB: information_schema,mysql,performance_schema,sys
Executed_Gtid_Set: 06072465-7531-11f1-95a0-000c294f01ee:1
1 row in set (0.00 sec)
2. 配置2作为slave¶
a. 修改配置文件开启中继日志
[root@2 ~]# vi /etc/my.cnf
[client]
socket = /usr/local/mysql/mysql.sock #客户连接使用的socket
[mysqld]
server_id=20 #将这台 MySQL 服务器的 ID 指定为20，与master应当设置为不同的ID
port = 3306 #默认监听的端口为3306
user = mysql #mysql进程的维护用户为mysql
character-set-server = utf8mb4 #默认字符集
default_storage_engine = innodb #默认存储引擎
log_timestamps = SYSTEM #日志时间戳使用系统时间
socket = /usr/local/mysql/mysql.sock #服务端的socket文件存放的位置
basedir = /usr/local/mysql #mysql的安装目录
datadir = /data/mysql/data #mysql数据文件的存放目录
pid-file = /usr/local/mysql/mysql.pid #mysql进程id存放位置
log_error = /data/mysql/log/mysql-error.log #mysql错误日志的存放位置
sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
#STRICT_TRANS_TABLES 严格模式核心 — 对事务表启用严格模式，插入/更新数据时若值无效（如类型错误、超出长度），直接报错而非发出警告
#NO_ZERO_IN_DATE 不允许月份或日为零的日期，例如 '2010-00-01'、'2024-01-00' 会被拦截。现已被废弃，但不会报错
# 不允许 '0000-00-00' 作为合法日期。现已被废弃，但不会报错
#ERROR_FOR_DIVISION_BY_ZERO 除零操作直接报错，而非返回 NULL 并给警告
#NO_ENGINE_SUBSTITUTION 建表时如果指定了被禁用的存储引擎（如 Memory 被禁用），直接报错，而非自动替换为默认引擎（InnoDB）
skip-name-resolve #跳过DNS反向解析
log_bin = /data/mysql/log/binlog #开启二进制文件
binlog_format = ROW #以行格式记录二进制的内容
slow_query_log = ON #开启慢查询日志
slow_query_log-file = /data/mysql/log/slow.log
#记录慢查询日志的select语句
long_query_time = 1 #查询超时时间
log_queries_not_using_indexes = ON #查询语句没有使用后索引也会记录到慢查询日志文件中
gtid_mode=ON #开启GTID模式
enforce-gtid-consistency=ON #强制GTID一致性，开启后，违反 GTID 一致性的语句会被直接拒绝执行，确保复制不会出错。
log_slave_updates=ON #记录从库上执行的中继日志事务到从库自己的 binlog。
binlog_expire_logs_seconds=604800 #二进制日志binlog的自动过期时间设置为604800 秒
sync_binlog=1 #每次事务提交都强制将 binlog 刷入磁盘。
binlog_cache_size=4M #在事务期间用于缓存 binlog 写入的内存缓冲区大小。
max_binlog_cache_size=2G #单个事务的binlog缓存上限为2G
innodb_buffer_pool_size=10G #innodb缓冲池大小设置为10GB
innodb_log_file_size=1G #单个 InnoDB redo log（重做日志） 文件的大小，设为 1GB
innodb_log_buffer_size=64M #InnoDB redo log 的缓冲区大小，设为 64MB。
innodb_flush_log_at_trx_commit=1 #每次事务提交时，强制将 redo log 刷入磁盘。
innodb_file_per_table=1 #每个 InnoDB 表使用独立的表空间文件（表名.ibd），而不是全部存到共享的 ibdata1 中。
max_connections=5000 #MySQL 允许的最大并发连接数为 5000。
max_allowed_packet=64M #客户端与服务器之间传输的单个数据包最大为 64MB。
thread_cache_size=64 #MySQL 缓存待复用的线程数量为 64 个。
relay_log=/data/mysql/log/relay-log #指定中继日志（relay log） 的存放路径和文件名前缀。
relay_log_index=/data/mysql/log/relay-log.index
#中继日志索引文件路径
relay_log_recovery=ON #从库崩溃重启后，自动丢弃未执行完的中继日志，重新从主库获取，保证数据一致性。
relay_log_purge=ON #自动清理已执行完的旧中继日志文件，避免磁盘空间被撑满。
slave_parallel_type=LOGICAL_CLOCK #基于逻辑时钟的并行回放模式。允许同一时间点上没有冲突的事务并行回放，大幅提升从库性能
slave_parallel_workers=4 #并行复制的工作线程数 = 4。SQL 线程拆成 1 个协调线程 + 4 个工作线程并发回放事务。
slave_preserve_commit_order=ON #保证并行回放的事务提交顺序与主库一致，防止因提交乱序导致的数据不一致或主从不一致。
slave_checkpoint_period=2 #检查点周期 2 毫秒，控制并行复制协调线程检查工作线程进度的频率。
slave_checkpoint_group=512 #每处理 512 个事务做一次检查点，限制协调线程的批量大小。
slave_net_timeout=30 #设置从库与主库之间网络超时 30 秒，超过这个时间没收到数据就报错重连。
slave_compressed_protocol=1 #主从传输时启用压缩，减少网络带宽占用（CPU 换带宽）。
replicate-do-db=school #只复制 school 数据库的变更，其他库一律忽略。
replicate-ignore-db=information_schema #忽略 information_schema 库的复制。
replicate-ignore-db=mysql #忽略 mysql 库（用户权限表）的复制
replicate-ignore-db=performance_schema #忽略 performance_schema 库。
replicate-ignore-db=sys #忽略 sys 库。
b. 重启mysqld服务，然后将master备份内容导入到slave库中
[root@2 ~]# systemctl restart mysqld
[root@2 ~]# ss -tuanp |grep mysql
tcp LISTEN 0 70 *:33060 *:* users:(("mysqld",pid=12373,fd=18))
tcp LISTEN 0 4096 *:3306 *:* users:(("mysqld",pid=12373,fd=21))
[root@2 ~]# mysql -uroot -p<backup-school.sql
c. 查看school数据库是否导入成功
[root@2 ~]# mysql -uroot -p -e 'show databases;'
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
[root@2 ~]# mysql -uroot -p -e 'show databases;'
Enter password:
d. 在从服务器上设置主从连接的相关信息
mysql> CHANGE REPLICATION SOURCE TO
-> SOURCE_HOST='192.168.100.101',
-> SOURCE_USER='repl_user',
-> SOURCE_PASSWORD='Abcd1234',
-> SOURCE_PORT=3306,
-> SOURCE_LOG_FILE='binlog.000006',
-> SOURCE_LOG_POS=325,
-> #也可以使用自动定位，语法是 MASTER_AUTO_POSITION=1,
-> GET_SOURCE_PUBLIC_KEY=1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> START REPLICA;
mysql> #开启主从复制 停止主从复制的命令为stop replica;
Query OK, 0 rows affected (0.05 sec)
e. 查看主从复制状态
mysql> SHOW REPLICA STATUS\G
*************************** 1. row ***************************
Replica_IO_State: Waiting for source to send event
Source_Host: 192.168.100.101
Source_User: repl_user
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: binlog.000006
Read_Source_Log_Pos: 325
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 325
Relay_Source_Log_File: binlog.000006
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Replicate_Do_DB: school
Replicate_Ignore_DB: information_schema,mysql,performance_schema,sys
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Source_Log_Pos: 325
Relay_Log_Space: 530
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: No
Source_SSL_CA_File:
Source_SSL_CA_Path:
Source_SSL_Cert:
Source_SSL_Cipher:
Source_SSL_Key:
Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Source_Server_Id: 10
Source_UUID: 06072465-7531-11f1-95a0-000c294f01ee
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
Source_Retry_Count: 10
Source_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 21c6b6f7-7686-11f1-afec-000c29105f32:1-11
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Source_TLS_Version:
Source_public_key_path:
Get_Source_public_key: 1
Network_Namespace:
1 row in set (0.00 sec)
f. 如果实现锁过master表，则取消锁表
锁表命令为：LOCK TABLES school.students READ;
取消锁表命令为：UNLOCK TABLES;
四. 查看2及3的线程信息¶
1. 对于3¶
mysql> show processlist \G
*************************** 1. row ***************************
Id: 5
User: event_scheduler
Host: localhost
db: NULL
Command: Daemon
Time: 3329
State: Waiting on empty queue
Info: NULL
*************************** 2. row ***************************
Id: 9
User: repl_user
Host: 192.168.100.100:57934
db: NULL
Command: Binlog Dump
Time: 416
State: Source has sent all binlog to replica; waiting for more updates
Info: NULL
*************************** 3. row ***************************
Id: 11
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: init
Info: show processlist
3 rows in set, 1 warning (0.00 sec)
2. 2上的¶
mysql> SHOW PROCESSLIST \G
*************************** 1. row ***************************
Id: 5
User: event_scheduler
Host: localhost
db: NULL
Command: Daemon
Time: 1973
State: Waiting on empty queue
Info: NULL
*************************** 2. row ***************************
Id: 13
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: init
Info: SHOW PROCESSLIST
*************************** 3. row ***************************
Id: 14
User: system user
Host: connecting host
db: NULL
Command: Connect
Time: 536
State: Waiting for source to send event
Info: NULL
*************************** 4. row ***************************
Id: 15
User: system user
Host:
db: NULL
Command: Query
Time: 536
State: Replica has read all relay log; waiting for more updates
Info: NULL
*************************** 5. row ***************************
Id: 16
User: system user
Host:
db: NULL
Command: Connect
Time: 536
State: Waiting for an event from Coordinator
Info: NULL
*************************** 6. row ***************************
Id: 17
User: system user
Host:
db: NULL
Command: Connect
Time: 536
State: Waiting for an event from Coordinator
Info: NULL
*************************** 7. row ***************************
Id: 18
User: system user
Host:
db: NULL
Command: Connect
Time: 536
State: Waiting for an event from Coordinator
Info: NULL
*************************** 8. row ***************************
Id: 19
User: system user
Host:
db: NULL
Command: Connect
Time: 536
State: Waiting for an event from Coordinator
Info: NULL
8 rows in set, 1 warning (0.00 sec)
五. 验证主从复制¶
1. 在3上建库建表并插入数据¶
mysql> use school;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> INSERT INTO A VALUES('bob','工业大学');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO B VALUES('bob','嵌入式');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM A;
+-----------+--------------+
| 姓名 | 毕业院校 |
+-----------+--------------+
| 杨过 | 哈工大 |
| 郭靖 | 北大 |
| 黄蓉 | 武大 |
| 风清扬 | 西交大 |
| bob | 工业大学 |
+-----------+--------------+
5 rows in set (0.00 sec)
mysql> SELECT * FROM B;
+--------+-----------+
| 姓名 | 职业 |
+--------+-----------+
| 杨过 | 作家 |
| 杨逍 | 演员 |
| 孙杨 | 运动员 |
| 黄蓉 | 考研 |
| bob | 嵌入式 |
+--------+-----------+
5 rows in set (0.00 sec)
2. 回到2查看验证¶
mysql> USE school;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT * FROM A INNER JOIN B ON A.`姓名`=B.`姓名`;
+--------+--------------+--------+-----------+
| 姓名 | 毕业院校 | 姓名 | 职业 |
+--------+--------------+--------+-----------+
| 杨过 | 哈工大 | 杨过 | 作家 |
| 黄蓉 | 武大 | 黄蓉 | 考研 |
| bob | 工业大学 | bob | 嵌入式 |
+--------+--------------+--------+-----------+
3 rows in set (0.00 sec)