Linux- MySQL主从复制总结

主从复制架构和原理

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
• 一从多主:适用于多个不同数据库

环状复制

• 复制需要考虑二进制日志事件记录格式
• 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/8.0/en/replication-configuration.html
https://mariadb.com/kb/en/setting-up-replication/

主节点配置：

(1) 启用二进制日志

[mysqld]
log_bin

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

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

说明：

server-id的取值范围
1 to 4294967295 (>= MariaDB 10.2.2)，默认值为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) 使用有复制权限的用户账号连接至主服务器，并启动复制线程

CHANGE MASTER TO MASTER_HOST='masterhost',
MASTER_USER='repluser',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='mariadb-bin.xxxxxx',
MASTER_LOG_POS=#;
START SLAVE [IO_THREAD|SQL_THREAD];
SHOW SLAVE STATUS;

主从复制相关

限制从服务器为只读

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   

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

保证主从复制的事务安全

在master节点启用参数：

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到磁盘