200字
MySQL 集群 Cluster
2026-03-01
2026-03-01
mysql

MySQL 集群 Cluster

服务性能扩展方式

  • Scale Up，向上扩展，垂直扩展
  • Scale Out，向外扩展，横向扩展

MySQL 主从复制

主从复制架构和原理

MySQL的主从复制

  • 读写分离
  • 复制：每个节点都有相同的数据集，向外扩展，基于二进制日志的单向复制

复制的功用

  • 负载均衡读操作
  • 备份
  • 高可用和故障切换
  • 数据分布
  • MySQL升级

复制架构

一主一从复制架构

Mysql主从复制插图

一主多从复制架构

Mysql主从复制插图1

主从复制原理

Mysql主从复制插图2

主从复制相关线程

  • 主节点：
    • 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#: 中继日志,保存从主节点复制过来的二进制日志,本质就是二进制日志

说明:

MySQL8.0 取消 master.info 和 relay-log.info文件

范例: 中继日志

[root@slave ~]#file /var/lib/mysql/mariadb-relay-bin.000001
/var/lib/mysql/mariadb-relay-bin.000001: MySQL replication log, server id 18
MySQL V5+, server version 10.3.17-MariaDB-log

[root@slave ~]#mysqlbinlog /var/lib/mysql/mariadb-relay-bin.000001|head
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200615 17:58:48 server id 18 end_log_pos 256 CRC32 0x7bd00c79 Start:
binlog v 4, server v 10.3.17-MariaDB-log created 200615 17:58:48
BINLOG '
WEbnXg8cAAAA/AAAAAABAAAAAAQAMTAuMy4xNy1NYXJpYURCLWxvZwAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

主从复制特点

  • 异步复制: 客户端性能良好
  • 主从数据不一致比较常见

各种复制架构

Mysql主从复制插图3

  • 一Master/一Slave
  • 一主多从
  • 从服务器还可以再有从服务器
  • Master/Master
  • 一从多主:适用于多个不同数据库
  • 环状复制

复制需要考虑二进制日志事件记录格式

  • STATEMENT（5.0之前）, Mariadb5.5 默认使用此格式
  • ROW（5.1之后，推荐）,MySQL 8.0 默认使用此格式
  • MIXED: Mariadb10.3 默认使用此格式

实现主从复制配置

官网参考

https://dev.mysql.com/doc/refman/8.0/en/replication-configuration.html
https://dev.mysql.com/doc/refman/5.7/en/replication-configuration.html
https://dev.mysql.com/doc/refman/5.5/en/replication-configuration.html
https://mariadb.com/kb/en/library/setting-up-replication/

主节点配置：

(1) 启用二进制日志

[mysqld]
log_bin=/data/logbin/mysql-bin

(2) 为当前节点设置一个全局惟一的ID号

[mysqld]
server-id=#
log-basename=master #可选项，设置datadir中日志名称，确保不依赖主机名

说明：

server-id的取值范围
1 to 4294967295 (>= MariaDB 10.2.2)，默认值为1，MySQL8.0默认值为1
0 to 4294967295 (<= MariaDB 10.2.1)，默认值为0，如果从节点为0，所有master都将拒绝此slave的连接

(3) 查看从二进制日志的文件和位置开始进行复制

SHOW MASTER STATUS;

(4) 创建有复制权限的用户账号

GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'HOST' IDENTIFIED BY 'replpass';

#MySQL8.0 分成两步实现
mysql> create user repluser@'10.0.0.%' identified by '123456';
mysql> grant replication slave on *.* to repluser@'10.0.0.%';

从节点配置：

(1) 启动中继日志

[mysqld]
server_id=#			 #为当前节点设置一个全局惟的ID号
log-bin
read_only=ON 			#设置数据库只读，针对supper user无效
relay_log=relay-log 		#relay log的文件路径，默认值hostname-relay-bin
relay_log_index=relay-log.index 		#默认值hostname-relay-bin.index

(2) 使用有复制权限的用户账号连接至主服务器，并启动复制线程

官方说明

https://dev.mysql.com/doc/refman/8.0/en/change-master-to.html

范例:

CHANGE MASTER TO MASTER_HOST='masterhost',
MASTER_USER='repluser',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='mariadb-bin.xxxxxx',
MASTER_LOG_POS=#
MASTER_DELAY = interval; #可指定延迟复制实现访问误操作,单位秒

START SLAVE [IO_THREAD|SQL_THREAD];
SHOW SLAVE STATUS;

#查看 relaylog 事件
SHOW RELAYLOG EVENTS in 'relay-bin.00000x';

范例：新建主从复制

CHANGE MASTER TO MASTER_HOST='192.168.122.10',
MASTER_USER='repluser', MASTER_PASSWORD='ayaka', MASTER_PORT=3306,
MASTER_LOG_FILE='binlog.000003', MASTER_LOG_POS=631;

#主节点
[root@master ~]#dnf -y install mariadb-server

[root@master ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=8
log-bin
[root@master ~]#systemctl restart mariadb
[root@master ~]#mysql

#查看二进制文件和位置
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 28052 |
| mariadb-bin.000002 | 545 |
+--------------------+-----------+
2 rows in set (0.001 sec)

#创建复制用户
MariaDB [(none)]> grant replication slave on *.* to repluser@'10.0.0.%'
identified by 'ayaka';

#从节点
[root@slave ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=18
[root@slave ~]#systemctl restart mariadb
[root@slave1 ~]#mysql
MariaDB [(none)]> help change master to
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.0.0.8',
MASTER_USER='repluser', MASTER_PASSWORD='ayaka', MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000002', MASTER_LOG_POS=545;
MariaDB [(none)]> start slave;
#'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
MariaDB [(none)]> show slave status\G
#'SHOW SLAVE STATUS' is deprecated and will be removed in a future release. Please use SHOW REPLICA STATUS instead
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.8
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000002
Read_Master_Log_Pos: 26987890
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 26987902
Relay_Master_Log_File: mariadb-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: 26987890
Relay_Log_Space: 26988213
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: 8
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the
slave I/O thread to update it
Slave_DDL_Groups: 34
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 100006
1 row in set (0.000 sec)

注意：

MySQL 8.0 之后主库要求必须在加密通道（SSL）下才能发送这种密码

-- 修改同步用户的加密插件为旧版模式
ALTER USER 'repluser'@'%' IDENTIFIED WITH mysql_native_password BY 'ayaka';
-- 刷新权限
FLUSH PRIVILEGES;

范例：主服务器非新建时,主服务器运行一段时间后，新增从节点服务器

如果主节点已经运行了一段时间，且有大量数据时，如何配置并启动slave节点

  • 通过备份恢复数据至从服务器
  • 复制起始位置为备份时，二进制日志文件及其POS
#在主服务器完全备份
[root@master ~]#mysqldump -A -F --single-transaction --master-data=1 > /backup/fullbackup_`date +%F_%T`.sql

[root@master ~]#ll /backup/
total 2988
-rw-r--r-- 1 root root 3055918 Nov 27 17:41 fullbackup_2019-11-27_17:41:17.sql

[root@master ~]#scp /backup/fullbackup_2019-11-27_17\:41\:17.sql 192.168.8.11:/data/

#建议优化主和从节点服务器的性能
MariaDB [hellodb]> set global innodb_flush_log_at_trx_commit=2
MariaDB [hellodb]> set global sync_binlog=0 #不再主动强制将 Binlog 同步到磁盘，而是交给操作系统来决定什么时候刷盘

MariaDB [hellodb]> set global innodb_flush_log_at_trx_commit=2;
Query OK, 0 rows affected (0.001 sec)
MariaDB [hellodb]> show variables like 'sync_binlog';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| sync_binlog | 0 |
|---------------------+-------+
5 rows in set (0.001 sec)

#将完全备份还原到新的从节点
[root@slave ~]#dnf -y install mariadb-server

[root@slave ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=11
read-only

[root@slave ~]#systemctl restart mariadb
#配置从节点，从完全备份的位置之后开始复制
[root@slave ~]#grep '^CHANGE MASTER' /data/fullbackup_2019-11-27_17\:41\:17.sql
CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000003', MASTER_LOG_POS=389;
[root@slave ~]#vim /data/fullbackup_2019-11-27_17\:41\:17.sql
CHANGE MASTER TO
MASTER_HOST='10.0.0.8',
MASTER_USER='repluser',
MASTER_PASSWORD='ayaka',
MASTER_PORT=3306,
		MASTER_LOG_FILE='mariadb-bin.000003', MASTER_LOG_POS=389;


[root@slave ~]#mysql < /data/fullbackup_2019-11-27_17\:41\:17.sql
[root@slave ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.11-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.0.0.8
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000003
Read_Master_Log_Pos: 389
Relay_Log_File: mariadb-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mariadb-bin.000003
Slave_IO_Running: No
Slave_SQL_Running: No
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: 389
Relay_Log_Space: 256
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: NULL
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: 0
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
MariaDB [(none)]> start slave;

主从复制相关

限制从服务器为只读

read_only=ON
#注意：此限制对拥有SUPER权限的用户均无效

注意：以下命令会阻止所有用户, 包括主服务器复制的更新

FLUSH TABLES WITH READ LOCK;

在从节点清除信息

注意：以下都需要先 STOP SLAVE

RESET SLAVE #从服务器清除master.info ，relay-log.info, relay log ，开始新的relay log
RESET SLAVE ALL #清除所有从服务器上设置的主服务器同步信息，如HOST，PORT, USER和PASSWORD 等

复制错误解决方法

可以在从服务器忽略几个主服务器的复制事件，此为global变量，或指定跳过事件的ID

注意: Centos 8.1以上版本上的MariaDB10.3主从节点同时建同名的库和表不会冲突，建主键记录会产生冲突

#系统变量，指定跳过复制事件的个数
SET GLOBAL sql_slave_skip_counter = N

#服务器选项，只读系统变量，指定跳过事件的ID
[mysqld]
slave_skip_errors=1007|ALL

范例：复制冲突的解决

#CentOS7上Mariadb5.5 在slave创建库和表,再在master上创建同名的库和表,会出现复制冲突,而在
CentOS8上的Mariadb10.3上不会冲突
#如果添加相同的主键记录都会冲突
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.39.8
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000002
Read_Master_Log_Pos: 26988271
Relay_Log_File: mariadb-relay-bin.000003
Relay_Log_Pos: 557
Relay_Master_Log_File: mariadb-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1007
Last_Error: Error 'Can't create database 'db4'; database
exists' on query. Default database: 'db4'. Query: 'create database db4'
Skip_Counter: 0
Exec_Master_Log_Pos: 26988144
Relay_Log_Space: 26988895
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1007 #错误编码
Last_SQL_Error: Error 'Can't create database 'db4'; database
exists' on query. Default database: 'db4'. Query: 'create database db4'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 8
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Slave_DDL_Groups: 37
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 100006
1 row in set (0.000 sec)

#方法1
MariaDB [(none)]> stop slave;
MariaDB [(none)]> set global sql_slave_skip_counter=1;
MariaDB [(none)]> start slave;

#方法2
[root@slave1 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
slave_skip_errors=1007|ALL
[root@slave1 ~]#systemctl restart mariadb

发生的原因

手动操作污染：在配置好主从同步之前或期间，有人在从库上执行过 create database db4;。

重复同步：从库之前已经同步过这个库，但因为某种原因（如重做从库）导致同步位点（Position）重置到了该操作之前。

清理不彻底：之前删库没删干净，或者数据目录下残留了 db4 的文件夹。

START SLAVE 语句，指定执到特定的点

START SLAVE [thread_types]
START SLAVE [SQL_THREAD] UNTIL MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS =
log_pos
START SLAVE [SQL_THREAD] UNTIL RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS =
log_pos
thread_types:
[thread_type [, thread_type] ... ]
thread_type: IO_THREAD | SQL_THREAD

保证主从复制的事务安全

参考https://mariadb.com/kb/en/library/server-system-variables/

在master节点启用参数： 双1最安全

sync_binlog=1 #每次写后立即同步二进制日志到磁盘，性能差
#如果用到的为InnoDB存储引擎：
innodb_flush_log_at_trx_commit=1 #每次事务提交立即同步日志写磁盘
sync_master_info=# #次事件后master.info同步到磁盘

在slave节点启用服务器选项：

skip-slave-start=ON #不自动启动slave

在slave节点启用参数：

sync_relay_log=# #次写后同步relay log到磁盘
sync_relay_log_info=# #次事务后同步relay-log.info到磁盘
mysql 备份还原实战案例
MySQL主从以外的其他集群 及维...

评论

© Ayaka's Blog. All Rights Reserved. / RSS / Sitemap
Powered by Halo & Theme-Fuwari
冀ICP备2026000147号-1 | 冀公网安备13063502000135号