mariadb replication官方手册: MariaDB Replication - MariaDB Knowledge Base
1. 复制的基本概念和原理
mysql复制是指从一个mysql服务器(MASTER)将数据通过日志的方式经过网络传送到另一台或多台mysql服务器(SLAVE) , 然后在slave上重放(replay或redo)传送过来的日志 , 以达到和master数据同步的目的。
它的工作原理很简单。首先确保master数据库上开启了二进制日志 , 这是复制的前提。
在
slave准备开始复制时 , 首先要执行change master to语句设置连接到master服务器的连接参数 , 在执行该语句的时候要提供一些信息 , 包括如何连接和要从哪复制binlog, 这些信息在连接的时候会记录到slave的datadir下的master.info文件中 , 以后再连接master的时候将不用再提供这新信息而是直接读取该文件进行连接。在
slave上有两种线程 , 分别是IO线程和SQL线程。IO线程用于连接master, 监控和接受master的binlog。当启动IO线程成功连接master时 ,master会同时启动一个dump线程 , 该线程将slave请求要复制的binlog给dump出来 , 之后IO线程负责监控并接收master上dump出来的二进制日志 , 当master上binlog有变化的时候 ,IO线程就将其复制过来并写入到自己的中继日志(relay log)文件中。slave上的另一个线程SQL线程用于监控、读取并重放relay log中的日志 , 将数据写入到自己的数据库中。如下图所示。
站在slave的角度上看 , 过程如下 :
站在master的角度上看 , 过程如下(默认的异步复制模式 , 前提是设置了sync_binlog=1 , 否则binlog刷盘时间由操作系统决定) :
所以 , 可以认为复制大致有三个步骤 :
数据修改写入
master数据库的binlog中。slave的IO线程复制这些变动的binlog到自己的relay log中。slave的SQL线程读取并重新应用relay log到自己的数据库上 , 让其和master数据库保持一致。
从复制的机制上可以知道 , 在复制进行前 , slave上必须具有master上部分完整内容作为复制基准数据。例如 , master上有数据库A , 二进制日志已经写到了pos1位置 , 那么在复制进行前 , slave上必须要有数据库A , 且如果要从pos1位置开始复制的话 , 还必须有和master上pos1之前完全一致的数据。如果不满足这样的一致性条件 , 那么在replay中继日志的时候将不知道如何进行应用而导致数据混乱。也就是说 , 复制是基于binlog的position进行的 , 复制之前必须保证position一致。(注 : 这是传统的复制方式所要求的)
可以选择对哪些数据库甚至数据库中的哪些表进行复制。默认情况下 , MySQL的复制是异步的。slave可以不用一直连着master , 即使中间断开了也能从断开的position处继续进行复制。
MySQL 5.6对比MySQL 5.5在复制上进行了很大的改进 , 主要包括支持GTID(Global Transaction ID,全局事务ID)复制和多SQL线程并行重放。GTID的复制方式和传统的复制方式不一样 , 通过全局事务ID , 它不要求复制前slave有基准数据 , 也不要求binlog的position一致。
MySQL 5.7.17则提出了组复制(MySQL Group Replication,MGR)的概念。像数据库这样的产品 , 必须要尽可能完美地设计一致性问题 , 特别是在集群、分布式环境下。Galera就是一个MySQL集群产品 , 它支持多主模型(多个master) , 但是当MySQL 5.7.17引入了MGR功能后 , Galera的优势不再明显 , 甚至MGR可以取而代之。MGR为MySQL集群中多主复制的很多问题提供了很好的方案 , 可谓是一项革命性的功能。
复制和二进制日志息息相关 , 所以学习本章必须先有二进制日志的相关知识。
2. 复制的好处
围绕下面的拓扑图来分析 :
主要有以下几点好处 :
提供了读写分离的能力。
replication让所有的slave都和master保持数据一致 , 因此外界客户端可以从各个slave中读取数据 , 而写数据则从master上操作。也就是实现了读写分离。需要注意的是 , 为了保证数据一致性 , 写操作必须在
master上进行。通常说到读写分离这个词 , 立刻就能意识到它会分散压力、提高性能。
为
MySQL服务器提供了良好的伸缩(scale-out)能力。由于各个
slave服务器上只提供数据检索而没有写操作 , 因此"随意地"增加slave服务器数量来提升整个MySQL群的性能 , 而不会对当前业务产生任何影响。之所以"随意地"要加上双引号 , 是因为每个
slave都要和master建立连接 , 传输数据。如果slave数量巨多 ,master的压力就会增大 , 网络带宽的压力也会增大。数据库备份时 , 对业务影响降到最低。
由于
MySQL服务器群中所有数据都是一致的(至少几乎是一致的) , 所以在需要备份数据库的时候可以任意停止某一台slave的复制功能(甚至停止整个mysql服务) , 然后从这台主机上进行备份 , 这样几乎不会影响整个业务(除非只有一台slave, 但既然只有一台slave, 说明业务压力并不大 , 短期内将这个压力分配给master也不会有什么影响)。能提升数据的安全性。
这是显然的 , 任意一台
mysql服务器断开 , 都不会丢失数据。即使是master宕机 , 也只是丢失了那部分还没有传送的数据(异步复制时才会丢失这部分数据)。数据分析不再影响业务。
需要进行数据分析的时候 , 直接划分一台或多台
slave出来专门用于数据分析。这样OLTP和OLAP可以共存 , 且几乎不会影响业务处理性能。
3. 复制分类和它们的特性
MySQL支持两种不同的复制方法 : 传统的复制方式和GTID复制。MySQL 5.7.17之后还支持组复制(MGR)。
传统的复制方法要求复制之前 ,
slave上必须有基准数据 , 且binlog的position一致。GTID复制方法不要求基准数据和binlog的position一致性。GTID复制时 ,master上只要一提交 , 就会立即应用到slave上。这极大地简化了复制的复杂性 , 且更好地保证master上和各slave上的数据一致性。
从数据同步方式的角度考虑 , MySQL支持4种不同的同步方式 : 同步(synchronous)、半同步(semisynchronous)、异步(asynchronous)、延迟(delayed)。所以对于复制来说 , 就分为同步复制、半同步复制、异步复制和延迟复制。
3.1 同步复制
客户端发送DDL/DML语句给master , master执行完毕后还需要等待所有的slave都写完了relay log才认为此次DDL/DML成功 , 然后才会返回成功信息给客户端。同步复制的问题是master必须等待 , 所以延迟较大 , 在MySQL中不使用这种复制方式。
例如上图中描述的 , 只有3个slave全都写完relay log并返回ACK给master后 , master才会判断此次DDL/DML成功。
3.2 半同步复制
客户端发送DDL/DML语句给master , master执行完毕后还要等待一个slave写完relay log并返回确认信息给master , master才认为此次DDL/DML语句是成功的 , 然后才会发送成功信息给客户端。半同步复制只需等待一个slave的回应 , 且等待的超时时间可以设置 , 超时后会自动降级为异步复制 , 所以在局域网内(网络延迟很小)使用半同步复制是可行的。
例如上图中 , 只有第一个slave返回成功 , master就判断此次DDL/DML成功 , 其他的slave无论复制进行到哪一个阶段都无关紧要。
3.3 异步复制
客户端发送DDL/DML语句给master , master执行完毕立即返回成功信息给客户端 , 而不管slave是否已经开始复制。这样的复制方式导致的问题是 , 当master写完了binlog , 而slave还没有开始复制或者复制还没完成时 , slave上和master上的数据暂时不一致 , 且此时master突然宕机 , slave将会丢失一部分数据。如果此时把slave提升为新的master , 那么整个数据库就永久丢失这部分数据。
3.4 延迟复制
顾名思义 , 延迟复制就是故意让slave延迟一段时间再从master上进行复制。
4. 复制配置
4.1 配置一主一从
此处先配置默认的异步复制模式。复制和binlog息息相关
mysql支持一主一从和一主多从。但是每个slave必须只能是一个master的从 , 否则从多个master接受二进制日志后重放将会导致数据混乱的问题。
以下是一主一从的结构图 :
在开始传统的复制(非GTID复制)前 , 需要完成以下几个关键点 , 这几个关键点指导后续复制的所有步骤。
为
master和slave设定不同的server-id, 这是主从复制结构中非常关键的标识号。到了MySQL 5.7, 似乎不设置server id就无法开启binlog。设置server id需要重启MySQL实例。开启
master的binlog。刚安装并初始化的MySQL默认未开启binlog, 建议手动设置binlog且为其设定文件名 , 否则默认以主机名为基名时修改主机名后会找不到日志文件。最好设置
master上的变量sync_binlog=1(MySQL 5.7.7之后默认为1, 之前的版本默认为0) , 这样每写一次二进制日志都将其刷新到磁盘 , 让slave服务器可以尽快地复制。防止万一master的二进制日志还在缓存中就宕机时 ,slave无法复制这部分丢失的数据。最好设置
master上的redo log的刷盘变量innodb_flush_log_at_trx_commit=1(默认值为1) , 这样每次提交事务都会立即将事务刷盘保证持久性和一致性。在
slave上开启中继日志relay log。这个是默认开启的 , 同样建议手动设置其文件名。建议在
master上专门创建一个用于复制的用户 , 它只需要有复制权限replication slave用来读取binlog。确保
slave上的数据和master上的数据在"复制的起始position之前"是完全一致的。如果master和slave上数据不一致 , 复制会失败。记下
master开始复制前binlog的position, 因为在slave连接master时需要指定从master的哪个position开始复制。考虑是否将
slave设置为只读 , 也就是开启read_only选项。这种情况下 , 除了具有super权限(mysql 5.7.16还提供了super_read_only禁止super的写操作)和SQL线程能写数据库 , 其他用户都不能进行写操作。这种禁写对于slave来说 , 绝大多数场景都非常适合。
4.1.1 环境配置
一主一从师最简单的主从复制结构。本节实验环境如下:
master配置文件[mysqld] # master log-bin=master-bin sync-binlog=1 server-id=7 binlog_format=ROW #将二进制日志按行显示 , 保证二进制日志不丢失 skip-name-resolve # 不将IP地址解析为名称slave配置[mysqld] # slave log-bin=slave-bin relay_log=relay-log # 开启中继日志 server-id=11 read-only # 只读权限 , 对于普通用户无法修改 , 但是root用户可以进行修改 binlog_format=row # 二进制日志保存格式重启对应
mariadb服务[root@slave01 ~]# systemctl restart mariadb在master上创建复制专用的用户。
MariaDB [(none)]> create user 'repl'@'192.168.71.%' identified by 'P@ssword1!'; Query OK, 0 rows affected (0.012 sec) MariaDB [(none)]> grant REPLICATION SLAVE on *.* to 'repl'@'192.168.71.%'; Query OK, 0 rows affected (0.012 sec)将
slave恢复到master上指定的坐标。
4.1.2 将slave恢复到master 指定坐标
对于复制而言 , 有几种情况 :
待复制的
master没有新增数据 , 例如新安装的mysql实例。这种情况下 , 可以跳过恢复这个过程。待复制的
master上已有数据。这时需要将这些已有数据也应用到slave上 , 并获取master上binlog当前的坐标。只有slave和master的数据能匹配上 ,slave重放relay log时才不会出错。
第一种情况此处不赘述。第二种情况有几种方法 , 例如使用mysqldump、冷备份、xtrabackup等工具 , 这其中又需要考虑是MyISAM表还是InnoDB表。
在实验开始之前 , 首先在master上新增一些测试数据 , 以innodb和myisam的数值辅助表为例。
DROP DATABASE IF EXISTS backuptest;
CREATE DATABASE backuptest;
USE backuptest;
# 创建myisam类型的数值辅助表和插入数据的存储过程
CREATE TABLE num_isam (n INT NOT NULL PRIMARY KEY) ENGINE = MYISAM ;
DROP PROCEDURE IF EXISTS proc_num1;
DELIMITER $$
CREATE PROCEDURE proc_num1 (num INT)
BEGIN
DECLARE rn INT DEFAULT 1 ;
TRUNCATE TABLE backuptest.num_isam ;
INSERT INTO backuptest.num_isam VALUES(1) ;
dd: WHILE rn * 2 < num DO
BEGIN
INSERT INTO backuptest.num_isam
SELECT rn + n FROM backuptest.num_isam;
SET rn = rn * 2 ;
END ;
END WHILE dd;
INSERT INTO backuptest.num_isam
SELECT n + rn
FROM backuptest.num_isam
WHERE n + rn <= num;
END ;
$$
DELIMITER ;
# 创建innodb类型的数值辅助表和插入数据的存储过程
CREATE TABLE num_innodb (n INT NOT NULL PRIMARY KEY) ENGINE = INNODB ;
DROP PROCEDURE IF EXISTS proc_num2;
DELIMITER $$
CREATE PROCEDURE proc_num2 (num INT)
BEGIN
DECLARE rn INT DEFAULT 1 ;
TRUNCATE TABLE backuptest.num_innodb ;
INSERT INTO backuptest.num_innodb VALUES(1) ;
dd: WHILE rn * 2 < num DO
BEGIN
INSERT INTO backuptest.num_innodb
SELECT rn + n FROM backuptest.num_innodb;
SET rn = rn * 2 ;
END ;
END WHILE dd;
INSERT INTO backuptest.num_innodb
SELECT n + rn
FROM backuptest.num_innodb
WHERE n + rn <= num ;
END ;
$$
DELIMITER ;
# 分别向两个数值辅助表中插入100W条数据
CALL proc_num1 (1000000) ;
CALL proc_num2 (1000000) ;所谓数值辅助表是只有一列的表 , 且这个字段的值全是数值 , 从1开始增长。例如上面的是从1到100W的数值辅助表。
MariaDB [backuptest]> select * from backuptest.num_isam limit 10;
+----+
| n |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+----+
10 rows in set (0.001 sec)4.1.2.1 获取master binlog的坐标
如果master是全新的数据库实例 , 或者在此之前没有开启过binlog , 那么它的坐标位置是position=4。之所以是4而非0 , 是因为binlog的前4个记录单元是每个binlog文件的头部信息。
如果master已有数据 , 或者说master以前就开启了binlog并写过数据库 , 那么需要手动获取position。为了安全以及没有后续写操作 , 必须先锁表。
MariaDB [(none)]> flush tables with read lock;
Query OK, 0 rows affected (0.012 sec)注意 , 这次的锁表会导致写阻塞以及innodb的commit操作。
然后查看binlog的坐标。
MariaDB [(none)]> show master status ;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 | 670 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.000 sec)记住master-bin.000001和670。这里是指创建repl用户以及权限的命令
4.1.2.2 备份master数据到slave上
下面给出3种备份方式以及对应slave的恢复方法。建议备份所有库到slave上 , 如果要筛选一部分数据库或表进行复制 , 应该在slave上筛选(筛选方式见后文筛选要复制的库和表) , 而不应该在master的备份过程中指定。
方式一 : 冷备份直接
cp。这种情况只适用于没有新写入操作。严谨一点 , 只适合拷贝完成前master不能有写入操作。如果要复制所有库 , 那么直接拷贝整个
datadir。如果要复制的是某个或某几个库 , 直接拷贝相关目录即可。但注意 , 这种冷备份的方式只适合
MyISAM表和开启了innodb_file_per_table=ON的InnoDB表。如果没有开启该变量 ,innodb表使用公共表空间 , 无法直接冷备份。如果要冷备份
innodb表 , 最安全的方法是先关闭master上的mysql, 而不是通过表锁。
所以 , 如果没有涉及到
innodb表 , 那么在锁表之后 , 可以直接冷拷贝。最后释放锁。MariaDB [(none)]> flush tables with read lock; Query OK, 0 rows affected (0.012 sec) MariaDB [(none)]> show master status ; +-------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-------------------+----------+--------------+------------------+ | master-bin.000001 | 670 | | | +-------------------+----------+--------------+------------------+ 1 row in set (0.000 sec) shell> rsync -avz /data 192.168.71.11:/ MariaDB [(none)]> unlock tables; Query OK, 0 rows affected (0.000 sec)此处实验 , 假设要备份的是整个实例 , 因为涉及到了
innodb表 , 所以建议关闭mariadb。因为是冷备份 , 所以slave上也应该关闭mariadb。# master和slave上都执行 [root@master ~]# mysqladmin -uroot -p shutdown然后将整个
datadir拷贝到slave上(当然 , 有些文件是不用拷贝的 , 比如master上的binlog、mysql库等)。# 将master的datadir(/var/lib/mysql)拷贝到slave的datadir(/var/lib/mysql) [root@master ~]# rsync -avz /var/lib/mysql 192.168.71.11:/root/ #删除愿数据库中所有的二进制日志文件 [root@slave01 mysql]# ll /var/lib/mysql/ total 141764 -rw-rw---- 1 mysql mysql 417792 Nov 9 22:45 aria_log.00000001 -rw-rw---- 1 mysql mysql 52 Nov 9 22:45 aria_log_control -rw-rw---- 1 mysql mysql 790 Nov 9 22:45 ib_buffer_pool -rw-rw---- 1 mysql mysql 12582912 Nov 9 22:38 ibdata1 -rw-rw---- 1 mysql mysql 100663296 Nov 9 22:38 ib_logfile0 -rw-r--r-- 1 mysql mysql 14 Nov 9 22:38 mariadb_upgrade_info -rw-rw---- 1 mysql mysql 693 Nov 9 22:45 master-bin.000001 -rw-rw---- 1 mysql mysql 4096 Nov 9 22:45 master-bin.000001.idx -rw-rw---- 1 mysql mysql 20 Nov 9 22:40 master-bin.index -rw-rw---- 1 mysql mysql 6 Nov 9 22:45 master-bin.state -rw-rw---- 1 mysql mysql 0 Nov 9 22:40 multi-master.info drwx------ 2 mysql mysql 4096 Nov 9 22:38 mysql drwx------ 2 mysql mysql 20 Nov 9 22:38 performance_schema drwx------ 2 mysql mysql 8192 Nov 9 22:38 sys drwx------ 2 mysql mysql 20 Nov 9 22:38 test -rw-rw---- 1 mysql mysql 10485760 Nov 9 22:38 undo001 -rw-rw---- 1 mysql mysql 10485760 Nov 9 22:38 undo002 -rw-rw---- 1 mysql mysql 10485760 Nov 9 22:38 undo003 [root@slave01 mysql]# rm /var/lib/mysql/master-bin.* -rf需要注意 , 在冷备份的时候 , 需要将备份到目标主机上的
DATADIR/auto.conf删除 , 这个文件中记录的是mysql server的UUID, 而master和slave的UUID必须不能一致。这个文件是mysql的配置文件，mariadb没有这个文件, 直接拷贝即可。不用管该文件。然后重启
master和slave。因为重启了master, 所以binlog已经滚动了 , 不过这次不用再查看binlog坐标 , 因为重启造成的binlog日志移动不会影响slave。重启之后
master与slave的二进制日志状态[root@master ~]# mariadb MariaDB [(none)]> show master logs; +-------------------+-----------+ | Log_name | File_size | +-------------------+-----------+ | master-bin.000001 | 693 | | master-bin.000002 | 366 | | master-bin.000003 | 343 | +-------------------+-----------+ 3 rows in set (0.001 sec) [root@slave01 ~]# mariadb MariaDB [(none)]> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | slave-bin.000001 | 351 | | slave-bin.000002 | 328 | +------------------+-----------+ 2 rows in set (0.001 sec)方式二 : 使用
mysqldump进行备份恢复。这种方式简单的多 , 而且对于
innodb表很适用 , 但是slave上恢复时速度慢 , 因为恢复时数据全是通过insert插入的。因为mysqldump可以进行定时点恢复甚至记住binlog的坐标 , 所以无需再手动获取binlog的坐标。shell> mysqldump -uroot -p --all-databases --master-data=2 >dump.db注意 ,
--master-data选项将再dump.db中加入change master to相关的语句 , 值为2时 ,change master to语句是注释掉的 , 值为1或者没有提供值时 , 这些语句是直接激活的。同时 ,--master-data会锁定所有表(如果同时使用了--single-transaction, 则不是锁所有表)。因此 , 可以直接从
dump.db中获取到binlog的坐标。记住这个坐标。[root@master ~]# grep -i -m 1 'change master to' dump.db -- CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000002', MASTER_LOG_POS=154;然后将
dump.db拷贝到slave上 , 使用mysql执行dump.db脚本即可。也可以直接在master上远程连接到slave上执行。例如 :shell> mysql -uroot -p -h 192.168.71.11 -e 'source dump.db'方式三 : 使用
xtrabackup进行备份恢复。这里提前插入数据库数据
这是三种方式中最佳的方式 , 安全性高、速度快。因为
xtrabackup备份的时候会记录master的binlog的坐标 , 因此也无需手动获取binlog坐标。由于这里使用的是
mariadb直接使用mariabackup命令, 该命令与xtrabackup命令用法一致[root@master ~]# ll /usr/bin/mariabackup lrwxrwxrwx 1 root root 14 Oct 31 01:40 /usr/bin/mariabackup -> mariadb-backup [root@master ~]# ll /usr/bin/mariadb-backup -rwxr-xr-x 1 root root 28423568 Oct 31 01:40 /usr/bin/mariadb-backup注意 :
master和slave上都需要安装percona-xtrabackup。以全备份为例 :
[root@master ~]# mariadb-backup -u root /backup ... [00] 2024-11-10 00:15:16 Redo log (from LSN 47613 to 69811056) was copied. [00] 2024-11-10 00:15:16 completed OK!备份完成后 , 目录中的内容如下。其内文件如下 :
[root@master ~]# ll /backup/ total 113644 -rw-r----- 1 root root 417792 Nov 10 00:15 aria_log.00000001 -rw-r----- 1 root root 52 Nov 10 00:15 aria_log_control -rw-r----- 1 root root 285 Nov 10 00:15 backup-my.cnf drwx------ 2 root root 124 Nov 10 00:15 backuptest -rw-r----- 1 root root 12582912 Nov 10 00:15 ibdata1 -rw-r----- 1 root root 69775731 Nov 10 00:15 ib_logfile0 -rw-r----- 1 root root 34 Nov 10 00:15 mariadb_backup_binlog_info -rw-r----- 1 root root 100 Nov 10 00:15 mariadb_backup_checkpoints -rw-r----- 1 root root 510 Nov 10 00:15 mariadb_backup_info drwx------ 2 root root 4096 Nov 10 00:15 mysql drwx------ 2 root root 20 Nov 10 00:15 performance_schema drwx------ 2 root root 8192 Nov 10 00:15 sys drwx------ 2 root root 20 Nov 10 00:15 test -rw-r----- 1 root root 12582912 Nov 10 00:15 undo001 -rw-r----- 1 root root 10485760 Nov 10 00:15 undo002 -rw-r----- 1 root root 10485760 Nov 10 00:15 undo003其中
mariadb_backup_binlog_info中记录了binlog的坐标。记住这个坐标。[root@master ~]# cat /backup/mariadb_backup_binlog_info master-bin.000001 10056627 0-7-54然后将备份的数据执行"准备"阶段。这个阶段不要求连接
mysql, 因此不用给连接选项。[root@master ~]# mariadb-backup --prepare --target-dir=/backup/ .... [00] 2024-11-10 00:19:21 Last binlog file ./master-bin.000001, position 10056627 recovered pages: 0% 10% 24% 58% 97% 100% (0.0 seconds); tables to flush: 2 1 0 (0.0 seconds); [00] 2024-11-10 00:19:21 completed OK!最后 , 将
/backup目录拷贝到slave上进行恢复。恢复的阶段就是向MySQL的datadir拷贝。但注意 ,xtrabackup恢复阶段要求datadir必须为空目录。否则报错 :[root@master ~]# rsync -avz /backup 192.168.71.11:/root/ root@192.168.71.11's password: sending incremental file list backup/ backup/aria_log.00000001 .... [root@slave01 ~]# mariadb-backup --copy-back --target-dir=/root/backup/ mariadb-backup based on MariaDB server 11.4.4-MariaDB Linux (x86_64) [00] 2024-11-10 00:25:25 Original data directory /var/lib/mysql is not empty! [root@slave01 ~]# rm -rf /var/lib/mysql/* [root@slave01 ~]# mariadb-backup --copy-back --target-dir=/root/backup/ mariadb-backup based on MariaDB server 11.4.4-MariaDB Linux (x86_64) [01] 2024-11-10 00:26:13 Copying ./aria_log.00000001 to /var/lib/mysql/aria_log.00000001 [01] 2024-11-10 00:26:13 ...done [01] 2024-11-10 00:26:13 Copying ./aria_log_control to /var/lib/mysql/aria_log_control [01] 2024-11-10 00:26:13 ...done ....所以 , 停止
slave的mysql并清空datadir。[root@slave01 ~]# systemctl stop mariadb [root@slave01 ~]# rm -rf /var/lib/mysql/*恢复时使用的模式是"
--copy-back" , 选项后指定要恢复的源备份目录。恢复时因为不需要连接数据库 , 所以不用指定连接选项。[root@slave01 ~]# mariadb-backup --copy-back --target-dir=/root/backup/ mariadb-backup based on MariaDB server 11.4.4-MariaDB Linux (x86_64) [01] 2024-11-10 00:26:13 Copying ./aria_log.00000001 to /var/lib/mysql/aria_log.00000001 [01] 2024-11-10 00:26:13 ...done [01] 2024-11-10 00:26:13 Copying ./aria_log_control to /var/lib/mysql/aria_log_control [01] 2024-11-10 00:26:13 ...done .... [01] 2024-11-10 00:26:15 Copying ./test/db.opt to /var/lib/mysql/test/db.opt [01] 2024-11-10 00:26:15 ...done [01] 2024-11-10 00:26:15 Copying ./mariadb_backup_info to /var/lib/mysql/mariadb_backup_info [01] 2024-11-10 00:26:15 ...done [01] 2024-11-10 00:26:15 Copying ./xtrabackup_binlog_pos_innodb to /var/lib/mysql/xtrabackup_binlog_pos_innodb [01] 2024-11-10 00:26:15 ...done [00] 2024-11-10 00:26:15 completed OK!恢复完成后 ,
MySQL的datadir的文件的所有者和属组是mariadb-backup的调用者 , 所以需要改回mysql.mysql。[root@slave01 ~]# ll /var/lib/mysql/ total 45508 -rw-r----- 1 root root 417792 Nov 10 00:26 aria_log.00000001 -rw-r----- 1 root root 52 Nov 10 00:26 aria_log_control drwx------ 2 root root 124 Nov 10 00:26 backuptest -rw-r----- 1 root root 12582912 Nov 10 00:26 ibdata1 -rw-r----- 1 root root 12304 Nov 10 00:26 ib_logfile0 -rw-r----- 1 root root 510 Nov 10 00:26 mariadb_backup_info drwx------ 2 root root 4096 Nov 10 00:26 mysql drwx------ 2 root root 20 Nov 10 00:26 performance_schema drwx------ 2 root root 8192 Nov 10 00:26 sys drwx------ 2 root root 20 Nov 10 00:26 test -rw-r----- 1 root root 12582912 Nov 10 00:26 undo001 -rw-r----- 1 root root 10485760 Nov 10 00:26 undo002 -rw-r----- 1 root root 10485760 Nov 10 00:26 undo003 -rw-r----- 1 root root 29 Nov 10 00:26 xtrabackup_binlog_pos_innodb [root@slave01 ~]# chown -R mysql.mysql /var/lib/mysql [root@slave01 ~]# ll /var/lib/mysql/ total 45508 -rw-r----- 1 mysql mysql 417792 Nov 10 00:26 aria_log.00000001 -rw-r----- 1 mysql mysql 52 Nov 10 00:26 aria_log_control drwx------ 2 mysql mysql 124 Nov 10 00:26 backuptest -rw-r----- 1 mysql mysql 12582912 Nov 10 00:26 ibdata1 -rw-r----- 1 mysql mysql 12304 Nov 10 00:26 ib_logfile0 -rw-r----- 1 mysql mysql 510 Nov 10 00:26 mariadb_backup_info drwx------ 2 mysql mysql 4096 Nov 10 00:26 mysql drwx------ 2 mysql mysql 20 Nov 10 00:26 performance_schema drwx------ 2 mysql mysql 8192 Nov 10 00:26 sys drwx------ 2 mysql mysql 20 Nov 10 00:26 test -rw-r----- 1 mysql mysql 12582912 Nov 10 00:26 undo001 -rw-r----- 1 mysql mysql 10485760 Nov 10 00:26 undo002 -rw-r----- 1 mysql mysql 10485760 Nov 10 00:26 undo003 -rw-r----- 1 mysql mysql 29 Nov 10 00:26 xtrabackup_binlog_pos_innodb启动
slave的mariadb服务 , 并查看恢复是否成功。[root@slave01 ~]# systemctl start mariadb [root@slave01 ~]# mysql -uroot -p -e 'select * from backuptest.num_isam limit 10;' mysql: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb' instead Enter password: +----+ | n | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | | 10 | +----+
4.1.3 slave开启复制
经过前面的一番折腾 , 总算是把该准备的数据都准备到slave上 , 也获取到master上binlog的坐标(10056627)。
连接master时 , 需要使用change master to提供连接到master的连接选项 , 包括user、port、password、binlog、position等。
MariaDB [(none)]> change master to
master_host='192.168.71.7',
master_port=3306,
master_user='repl',
master_password='P@ssword1!',
master_log_file='master-bin.000001',
master_log_pos=10056627;完整的change master to语法如下 :
master_def:
MASTER_BIND = 'interface_name'
| MASTER_HOST = 'host_name'
| MASTER_USER = 'user_name'
| MASTER_PASSWORD = 'password'
| MASTER_PORT = port_num
| MASTER_CONNECT_RETRY = interval
| MASTER_HEARTBEAT_PERIOD = interval
| MASTER_LOG_FILE = 'master_log_name'
| MASTER_LOG_POS = master_log_pos
| RELAY_LOG_FILE = 'relay_log_name'
| RELAY_LOG_POS = relay_log_pos
| MASTER_DELAY = interval
| MASTER_SSL = {0|1}
| MASTER_SSL_CA = 'ca_file_name'
| MASTER_SSL_CAPATH = 'ca_directory_name'
| MASTER_SSL_CERT = 'cert_file_name'
| MASTER_SSL_CRL = 'crl_file_name'
| MASTER_SSL_CRLPATH = 'crl_directory_name'
| MASTER_SSL_KEY = 'key_file_name'
| MASTER_SSL_CIPHER = 'cipher_list'
| MASTER_SSL_VERIFY_SERVER_CERT = {0|1}
| MASTER_USE_GTID = {current_pos|slave_pos|no}
| MASTER_DEMOTE_TO_SLAVE = bool
| IGNORE_SERVER_IDS = (server_id_list)
| DO_DOMAIN_IDS = ([N,..])
| IGNORE_DOMAIN_IDS = ([N,..])然后 , 启动IO线程和SQL线程。可以一次性启动两个 , 也可以分开启动。
# 一次性启动、关闭
start slave;
stop slave;
# 单独启动
start slave io_thread;
start slave sql_thread;至此 , 复制就已经可以开始工作了。当master写入数据 , slave就会从master处进行复制。
例如 , 在master上新建一个表 , 然后去slave上查看是否有该表。因为是DDL语句 , 它会写二进制日志 , 所以它也会复制到slave上。
具体运行命令如下
MariaDB [(none)]> change master to
-> master_host='192.168.71.7',
-> master_port=3306,
-> master_user='repl',
-> master_password='P@ssword1!',
-> master_log_file='master-bin.000001',
-> master_log_pos=10056627;
Query OK, 0 rows affected, 1 warning (0.069 sec)
MariaDB [(none)]> start slave io_thread;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> start slave sql_thread;
Query OK, 0 rows affected (0.001 sec)4.2 配置一主多从
一主多从有两种情况 , 结构图如下。
以下是一主多从的结构图(和一主一从的配置方法完全一致) :
以下是一主多从 , 但某slave是另一群MySQL实例的master(级联模式) :
配置一主多从时 , 需要考虑一件事 : slave上是否要开启binlog? 如果不开启slave的binlog , 性能肯定要稍微好一点。但是开启了binlog后 , 可以通过slave来备份数据 , 也可以在master宕机时直接将slave切换为新的master。此外 , 如果是上面第二种主从结构 , 这台slave必须开启binlog。可以将某台或某几台slave开启binlog , 并在mysql动静分离的路由算法上稍微减少一点到这些slave上的访问权重。
上面第一种一主多从的结构没什么可解释的 , 它和一主一从的配置方式完全一样 , 但是可以考虑另一种情况 : 向现有主从结构中添加新的slave。所以 , 稍后先介绍这种添加slave , 再介绍第二种一主多从的结构。
例如在前文一主一从的实验环境下添加一台新的slave。
因为新的slave在开始复制前 , 要有master上的基准数据 , 还要有master binlog的坐标。按照前文一主一从的配置方式 , 当然很容易获取这些信息 , 但这样会将master锁住一段时间(因为要备份基准数据)。
深入思考一下 , 其实slave上也有数据 , 还有relay log以及一些仓库文件标记着数据复制到哪个地方。所以 , 完全可以从slave上获取基准数据和坐标 , 也建议这样做。
仍然有三种方法从slave上获取基准数据 : 冷备份、mysqldump和xtrabackup。
其实临时关闭一个slave对业务影响很小 , 所以我个人建议 , 新添加slave时采用冷备份slave的方式 , 不仅备份恢复的速度最快 , 配置成slave也最方便 , 这一点和前面配置"一主一从"不一样。但冷备份slave的时候需要注意几点 :
可以考虑将
slave1完全shutdown再将整个datadir拷贝到新的slave2上。建议新的
slave2配置文件中的"relay-log"的值和slave1的值完全一致 , 否则应该手动从slave2的relay-log.info中获取IO线程连接master时的坐标 , 并在slave2上使用change master to语句设置连接参数。
这里新添加一台slave02节点。安装mairadb
第二个从服务器上修改配置文件
[mysqld] log-bin=slave-bin relay_log=relay-log # 开启中继日志 server-id=29 read-only # 只读权限 , 对于普通用户无法修改 , 但是root用户可以进行修改 binlog_format=row # 二进制日志保存格式重启第二个从服务器
mysql数据库[root@slave02 ~]# systemctl start mariadb在生产中 , 如果之前的数据库数据已经很大 , 需要新加一个
slave数据库 , 可以将之前的数据库进行完全备份 , 对新的数据进行主从复制 : 在主服务器上备份当前的数据库信息MariaDB [backuptest]> show master logs; +-------------------+-----------+ | Log_name | File_size | +-------------------+-----------+ | master-bin.000001 | 512083127 | +-------------------+-----------+ 1 row in set (0.001 sec) [root@master ~]# mariadb-dump -A -F --single-transaction --master-data=1 > /data/all_bak.sql #主服务器上备份数据库信息 , --master-data=1的1适用于主从复制中 [root@centos7~]#scp /data/all_bak.sql 192.168.34.103:/root/ 然后将备份的数据库传到第二个从服务器的root目录下 all_bak.sql修改复制到第二个从服务器的备份数据 , 将主机名、账号、密码及端口写入即可 , 而实际的二进制文件的大小和文件名已经存在 , 在第二个从服务器导入时 , 就不需要再写
change master to这里需要检查导出数据库对应的实际
position位置[root@master ~]# grep -i "^CHANGE" /data/all_bak.sql CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000002', MASTER_LOG_POS=343;修改数据中对应该行位置, 修改为以下内容
CHANGE MASTER TO MASTER_HOST='192.168.71.7', MASTER_USER='repl', MASTER_PASSWORD='P@ssword1!', MASTER_PORT=3306, MASTER_LOG_FILE='master-bin.000002', MASTER_LOG_POS=343;
在第二个从服务器上修改完备份内容后 , 直接导入到
mysql数据库中 , 并进行复制主服务器数据库[root@slave02 ~]# mariadb MariaDB [(none)]> set sql_log_bin=off; MariaDB [(none)]> source all_bak.sql ; MariaDB [(none)]> set sql_log_bin=on; MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | backuptest | | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 6 rows in set (0.002 sec)验证一主多从服务器关系;
在主服务器上创建新的数据库 :
MariaDB [(none)]> create database db1; Query OK, 1 row affected (0.009 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | backuptest | | db1 | | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 7 rows in set (0.001 sec)在从服务器
1上查看是否复制了数据库db1[root@slave01 ~]# mariadb Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 8 Server version: 11.4.4-MariaDB-log 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 databases; +--------------------+ | Database | +--------------------+ | backuptest | | db1 | | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 7 rows in set (0.001 sec)在从服务器
2上查看是否复制了数据库db1MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | backuptest | | db1 | | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 7 rows in set (0.001 sec)
4.3 主从级联复制
此处实现的一主多从是下面这种结构 :
这种结构对MySQL复制来说 , 是一个很好的提升性能的方式。对于只有一个master的主从复制结构 , 每多一个slave , 意味着master多发一部分binlog , 业务稍微繁忙一点时 , 这种压力会加剧。而这种级联的主从结构 , 非常有助于MySQL集群的伸缩性 , 对压力的适应性也很强。
除上面一主多从、级联复制的方式可提升复制性能 , 还有几种提升
MySQL复制性能的方式 :
将不同数据库复制到不同
slave上。可以将
master上的事务表(如InnoDB)复制为slave上的非事务表(如MyISAM) , 这样slave上回放的速度加快 , 查询数据的速度在一定程度上也会提升。
回到这种主从结构 , 它有些不同 , master只负责传送日志给slave1、slave2和slave3 , slave 2_1和slave 2_2的日志由slave2负责传送 , 所以slave2上也必须要开启binlog选项。此外 , 还必须开启一个选项--log-slave-updates让slave2能够在重放relay log时也写自己的binlog , 否则slave2的binlog仅接受人为的写操作。
问 : slave能否进行写操作？重放relay log的操作是否会记录到slave的binlog中？
在
slave上没有开启read-only选项(只读变量)时 , 任何有写权限的用户都可以进行写操作 , 这些操作都会记录到binlog中。注意 , read-only选项对具有super权限的用户以及SQL线程执行的重放写操作无效。默认这个选项是关闭的。mysql> show variables like "read_only"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | OFF | +---------------+-------+在
slave上没有开启log-slave-updates和binlog选项时 , 重放relay log不会记录binlog。
所以如果slave02要作为某些slave的master , 那么在slave2上必须要开启log-slave-updates和binlog选项。为了安全和数据一致性 , 在slave02上还应该启用read-only选项。
环境如下 :
以下是master、slave1和slave2上配置文件内容。
# master上的配置
[mysqld] # master
log-bin=master-bin
sync-binlog=1
server-id=7
binlog_format=ROW #将二进制日志按行显示 , 保证二进制日志不丢失
skip-name-resolve # 不将IP地址解析为名称
# slave1上的配置
[mysqld] # slave
log-bin=slave-bin
relay_log=relay-log # 开启中继日志
server-id=11
read-only # 只读权限 , 对于普通用户无法修改 , 但是root用户可以进行修改
binlog_format=row # 二进制日志保存格式
log-slave-updates # 新增配置
# slave2上的配置
[mysqld]
log-bin=slave-bin
relay_log=relay-log # 开启中继日志
server-id=29
read-only # 只读权限 , 对于普通用户无法修改 , 但是root用户可以进行修改
binlog_format=row # 二进制日志保存格式
因为slave2目前是全新的实例 , 所以先将slave1的基准数据备份到slave2。由于slave1自身就是slave , 临时关闭一个slave对业务影响很小 , 所以直接采用冷备份slave的方式。
# 在slave2上执行
shell> mysqladmin -uroot -p shutdown
# 在slave1上执行 :
shell> mysqladmin -uroot -p shutdown
shell> rsync -avz --delete /var/lib/mysql/ 192.168.71.29:/var/lib/mysql/
shell> systemctl start mariadb冷备份时 , 以下几点千万注意 :
因为
slave2是slave1的从 , 所以在启动
MySQL前必须将备份到slave2上的和复制有关的文件都删除 , 包括 :master.info, 除非配置文件中指定了skip-slave-start, 否则slave2将再次连接到master并作为master的slave。relay-log.info, 因为slave1启动后会继续执行relay log中的内容(如果有未执行的) , 这时slave1会将这部分写入binlog并传送到slave2。删除
relay log文件。其实不是必须删除 , 但建议删除。删除
relay log index文件。删除
DATADIR/auto.conf。这个文件必须删除 , 因为这里面保留了mysql server的UUID, 而master和slave的UUID必须不能一致。在启动mysql的时候 , 如果没有这个文件会自动生成自己的UUID并保存到auto.conf中。
检查
slave1上从master复制过来的专门用于复制的用户repl是否允许slave2连接。如果不允许 , 应该去master上修改这个用户。因为
slave1是刚开启的binlog, 所以slave2连接slave1时的binlog position应该指定为4。即使slave1不是刚开启的binlog, 它在重启后也会滚动binlog。
所以 , 在slave2上继续操作 :
[root@slave02 ~]# ls /var/lib/mysql/
aria_log.00000001 ib_logfile0 performance_schema slave-bin.000001 slave-bin.state undo003
aria_log_control mariadb_backup_info relay-log.000003 slave-bin.000001.idx sys xtrabackup_binlog_pos_innodb
backuptest master.info relay-log.000004 slave-bin.000002 test
ib_buffer_pool multi-master.info relay-log.index slave-bin.000002.idx undo001
ibdata1 mysql relay-log.info slave-bin.index undo002
[root@slave02 ~]# rm -rf /var/lib/mysql/{master.info,relay-log*,slave-bin*}
[root@slave02 ~]# ls /var/lib/mysql/
aria_log.00000001 backuptest ibdata1 mariadb_backup_info mysql sys undo001 undo003
aria_log_control ib_buffer_pool ib_logfile0 multi-master.info performance_schema test undo002 xtrabackup_binlog_pos_innodb
[root@slave02 ~]# systemctl start mariadb最后连上slave2 , 启动复制线程。
shell> mysql -uroot -p
mysql> change master to
master_host='192.168.71.11',
master_port=3306,
master_user='repl',
master_password='P@ssword1!',
master_log_file='slave-bin.000001',
master_log_pos=4;
mysql> start slave;
mysql> show slave status\G以下是通过mysqldump命令实现级联模式
这里实验中级联的三个节点的配置按照之前保持不变
#在192.168.71.7充当master
#在192.168.71.11充当级联slave
#在192.168.71.29充当slave
# master上的配置
[mysqld] # master
log-bin=master-bin
sync-binlog=1
server-id=7
binlog_format=ROW #将二进制日志按行显示 , 保证二进制日志不丢失
skip-name-resolve # 不将IP地址解析为名称
# slave1上的配置
[mysqld] # slave
log-bin=slave-bin
relay_log=relay-log # 开启中继日志
server-id=11
read-only # 只读权限 , 对于普通用户无法修改 , 但是root用户可以进行修改
binlog_format=row # 二进制日志保存格式
log-slave-updates # 新增配置
# slave2上的配置
[mysqld]
log-bin=slave-bin
relay_log=relay-log # 开启中继日志
server-id=29
read-only # 只读权限 , 对于普通用户无法修改 , 但是root用户可以进行修改
binlog_format=row # 二进制日志保存格式 在
master节点中准备用户MariaDB [(none)]> create user 'repl'@'192.168.71.%' identified by 'P@ssword1!'; Query OK, 0 rows affected (0.012 sec) MariaDB [(none)]> grant REPLICATION SLAVE on *.* to 'repl'@'192.168.71.%'; Query OK, 0 rows affected (0.012 sec) create user 'repl'@'%' identified by 'P@ssword1!'; grant REPLICATION SLAVE on *.* to 'repl'@'%';导出数据
[root@master ~]# mysqldump -A -F --single-transaction --master-data=1 > /data/all.sql [root@master ~]# scp /data/all.sql root@192.168.71.11:/root/ [root@master ~]# scp /data/all.sql root@192.168.71.29:/root/配置中继节点
[root@slave01 ~]# vi /root/all.sql CHANGE MASTER TO MASTER_HOST='192.168.71.7', MASTER_USER='repl', MASTER_PASSWORD='P@ssword1!', MASTER_PORT=3306, MASTER_LOG_FILE='master-bin.000003',MASTER_LOG_POS=343;导入数据
MariaDB [(none)]> set sql_log_bin=0; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> source /root/all.sql; MariaDB [test]> show master logs; #记录二进制位置 , 给第三个节点使用 +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | slave-bin.000001 | 328 | +------------------+-----------+ 1 row in set (0.001 sec) MariaDB [test]> set sql_log_bin=0; Query OK, 0 rows affected (0.000 sec)开启
slaveMariaDB [test]> start slave; Query OK, 0 rows affected (0.002 sec)在第三个节点
slave上实现启动
mariadb并修改导入的sql文件[root@slave02 ~]# systemctl start mariadb [root@slave02 ~]# vi /root/all.sql CHANGE MASTER TO MASTER_HOST='192.168.71.11', MASTER_USER='repl', MASTER_PASSWORD='P@ssword1!', MASTER_PORT=3306, MASTER_LOG_FILE='slave-bin.000001', MASTER_LOG_POS=328;导入数据
MariaDB [(none)]> set sql_log_bin=0; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> source /root/all.sql; MariaDB [test]> set sql_log_bin=1; Query OK, 0 rows affected (0.000 sec)启动
slaveMariaDB [(none)]> start slave; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> show slave status \G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.71.11 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: slave-bin.000001 Read_Master_Log_Pos: 647 Relay_Log_File: relay-log.000003 Relay_Log_Pos: 874 Relay_Master_Log_File: slave-bin.000001 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: 647 Relay_Log_Space: 1177 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: Yes 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: Yes Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 11 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: optimistic SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Slave_DDL_Groups: 2 Slave_Non_Transactional_Groups: 0 Slave_Transactional_Groups: 0 Replicate_Rewrite_DB: 1 row in set (0.000 sec) ERROR: No query specified
4.4 主主复制
这里的主主复制是指将两个节点设置为互为主备。以此实现两个节点均可以实现读写。只是这样容易造成数据不一致。所以需要慎用。这里需要考虑的要点是自动增长id。
/etc/keepalived/keepalived.conf文件内容如下
配置一个节点使用奇数id
auto_increment_offset=1 #开始点
auto_increment_increment=2 #增长幅度另一个节点使用偶数id
auto_increment_offset=2
auto_increment_increment=2主主复制的配置步骤 :
各节点使用一个惟一
server_id都启动
binary log和relay log创建拥有复制权限的用户账号
定义自动增长
id字段的数值范围各为奇偶均把对方指定为主节点 , 并启动复制线程
示例配置如下:
master和slave节点配置文件# master01上的配置 [mysqld] # master log-bin=master01-bin sync-binlog=1 server-id=7 binlog_format=ROW #将二进制日志按行显示 , 保证二进制日志不丢失 relay_log=relay-log # 开启中继日志 skip-name-resolve # 不将IP地址解析为名称 auto_increment_offset=1 #开始点 auto_increment_increment=2 #增长幅度 # master02上的配置 [mysqld] # master02 log-bin=master02-bin sync-binlog=1 relay_log=relay-log # 开启中继日志 server-id=11 binlog_format=row # 二进制日志保存格式 auto_increment_offset=2 #开始点 auto_increment_increment=2 #增长幅度启动两个节点的
mariadb服务[root@master01 ~]# systemctl start mariadb [root@master02 ~]# systemctl start mariadb在
master01节点中查看当前的二进制日志文件大小[root@master01 ~]# mariadb Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 4 Server version: 11.4.4-MariaDB-log 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 master logs; +---------------------+-----------+ | Log_name | File_size | +---------------------+-----------+ | master01-bin.000001 | 331 | +---------------------+-----------+ 1 row in set (0.001 sec)在
master01节点创建复制权限的账号, 这里需要在查看二进制日志文件大小之后创建账号。目的是为了之后在同步的过程中把这里的账号也同时复制到master02上面。因此之后设置positions的时候直接设置为331MariaDB [(none)]> create user 'repl'@'192.168.71.%' identified by 'P@ssword1!'; Query OK, 0 rows affected (0.012 sec) MariaDB [(none)]> grant REPLICATION SLAVE on *.* to 'repl'@'192.168.71.%'; Query OK, 0 rows affected (0.008 sec)这里需要注意, 创建账号之后的二进制大小有变化, 由
331变为682MariaDB [(none)]> show master logs; +---------------------+-----------+ | Log_name | File_size | +---------------------+-----------+ | master01-bin.000001 | 682 | +---------------------+-----------+ 1 row in set (0.000 sec)在
master02节点上面启动复制线程MariaDB [(none)]> change master to master_host='192.168.71.7', master_port=3306, master_user='repl', master_password='P@ssword1!', master_log_file='master01-bin.000001', master_log_pos=331;在
master02主机上启动slaveMariaDB [(none)]> start slave; Query OK, 0 rows affected (0.002 sec) MariaDB [(none)]> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.71.7 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master01-bin.000001 Read_Master_Log_Pos: 682 Relay_Log_File: relay-log.000002 Relay_Log_Pos: 909 Relay_Master_Log_File: master01-bin.000001 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: 682 Relay_Log_Space: 1212 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: Yes 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: Yes Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 7 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: optimistic SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Slave_DDL_Groups: 2 Slave_Non_Transactional_Groups: 0 Slave_Transactional_Groups: 0 Replicate_Rewrite_DB: 1 row in set (0.001 sec) ERROR: No query specified在
master01上导入数据库 , 并查看数据库内容[root@master01 ~]# mariadb < hellodb_innodb.sql在
master02上查看复制master01主机的数据库是否成功 , 此时已经复制成功 , 实现主主单向复制 , 下来我们还需要实现主主双向复制[root@master01 ~]# mariadb Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 7 Server version: 11.4.4-MariaDB-log 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 databases; +--------------------+ | Database | +--------------------+ | hellodb | | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 6 rows in set (0.002 sec) [root@master02 ~]# mariadb Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 19 Server version: 11.4.4-MariaDB-log 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 databases; +--------------------+ | Database | +--------------------+ | hellodb | | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 6 rows in set (0.002 sec)在
master02主机中查看二进制日志情况MariaDB [(none)]> show master logs; +---------------------+-----------+ | Log_name | File_size | +---------------------+-----------+ | master02-bin.000001 | 331 | +---------------------+-----------+ 1 row in set (0.001 sec)在
master01主机上开启复制线程 , 指定master02主机的详细信息MariaDB [(none)]> change master to master_host='192.168.71.11', master_port=3306, master_user='repl', master_password='P@ssword1!', master_log_file='master02-bin.000001', master_log_pos=331;启动
master01主机的slave, 并查看状态MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.003 sec) MariaDB [(none)]> show slave status \G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.71.11 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master02-bin.000001 Read_Master_Log_Pos: 331 Relay_Log_File: relay-log.000002 Relay_Log_Pos: 558 Relay_Master_Log_File: master02-bin.000001 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: 331 Relay_Log_Space: 861 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: Yes 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: Yes Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 11 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: optimistic SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Slave_DDL_Groups: 0 Slave_Non_Transactional_Groups: 0 Slave_Transactional_Groups: 0 Replicate_Rewrite_DB: 1 row in set (0.001 sec)我们来看一下
master01主机创建一个表 , 并插入表信息 , 此时的ID号以奇数开始增涨MariaDB [(none)]> use hellodb; 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 MariaDB [hellodb]> create table t1(id int auto_increment primary key,name char(10)); Query OK, 0 rows affected (0.046 sec) MariaDB [hellodb]> insert t1(name)value('a'); Query OK, 1 row affected (0.009 sec) MariaDB [hellodb]> insert t1(name)value('b'); Query OK, 1 row affected (0.013 sec) MariaDB [hellodb]> select * from t1; +----+------+ | id | name | +----+------+ | 1 | a | | 3 | b | +----+------+ 2 rows in set (0.001 sec)在
master02主机上插入表格信息 , 此时在master02主机上插入信息 , 会以偶数递增序列号 , 不会和master01主机存在冲突MariaDB [(none)]> use hellodb; 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 MariaDB [hellodb]> insert t1(name)value('a'); Query OK, 1 row affected (0.009 sec) MariaDB [hellodb]> insert t1(name)value('b'); Query OK, 1 row affected (0.009 sec) MariaDB [hellodb]> select * from t1; +----+------+ | id | name | +----+------+ | 1 | a | | 3 | b | | 4 | a | | 6 | b | +----+------+ 4 rows in set (0.001 sec)
只不过这里可以通过在这两个节点上面配置keepalive实现一个VIP， 通过这个VIP同时访问后端的两台节点。可以避免配置这个自动增长。以此实现主主的复制的冗余模式。以下脚本和配置文件均来自于《高性能Linux服务器构建实战 系统安全、故障排查、自动化运维与集群架构 [高俊峰著]》
/etc/keepalived/keepalived.conf文件内容如下
global_defs {
notification_email {
acassen@firewal1.loc
failover@firewall.loc
sysadmin@firewal1.loc
}
notification_email_from Alexandre.Cassenefirewall.1oc
smtp_server 192.168.200.1
smtp_connect_timeout 30
router_id MySQLHA_DEVEL
}
vrrp_script check_mysqld {
script "/etc/keepalived/mysqlcheck/check slave.pl 127.0.0.1"#检测MysQL复制状态的脚本
interval 2
weight 21
}
vrrp_instance HA_1 {
state BACKUP # 在 DB1和DB2 上均配置为BACKUP
interface eth0
virtual_routerid 80
priority 100
advert_int 2
nopreempt 不抢占模式,只在优先级高的机器上设置即可,优先级低的机器可不设置
authentication {
auth_type PASS
auth_pass qweasdzxc
}
track_script {
check_mysqld
}
virtual_ipaddress {
192.168.88.10/24 dev eth0 #MySQl的对外服务Ip,即VIP
}
}其中,/etc/keepalived/mysqlcheck/check_slave.pl 文件的内容为:
#!/usr/bin/perl -w
use DBI;
use DBD::mysql;
# CONFIG VARIABLES
$SBM- 120;
$db="ixdba";
$host = $ARGV[0];
$port = 3306;
$user = "root";
$pw = "xxxxxx";
#SQL query
$query = "show slave status";
$dbh = DBI->connect ("DBI:mysql:$db:$host:$port",$user, $pw, { RaiseError=> 0,PrintError =>0 });
if (!defined ($dbh)) {
exit 1
}
$sqlQuery = $dbh->prepare ($query);
$sq1Query->execute;
$Slave_I0_Running = "";
$Slave_SQL_Running = "";
$Seconds_Behind_Master ="";
while (my $ref = $sq1Query->fetchrow hashref () ) {
$Slave_I0_Running = $ref->{'Slave_IO_Running');
$Slave_SQL_Running = $ref->('Slave_SQL_Running');
$Seconds_Behind_Master = $ref->('Seconds_Behind_Master');
}
$sqlQuery->finish;
$dbh->disconnect ();
if( $Slave_IO_Running eq "No" || $S1ave_SQL_Running eq "No"){
exit 1;
} else {
if ( $Seconds_Behind_Master > $SBM) {
exit 1;
} else {
exit 0;
}
}4.5. 复制相关信息
4.5.1 查看slave的信息
change master to后 , 在slave的datadir下就会生成master.info文件和relay-log.info文件 , 这两个文件随着复制的进行 , 其内数据会随之更新。
4.5.1.1 master.info
master.info文件记录的是IO线程相关的信息 , 也就是连接master以及读取master binlog的信息。通过这个文件 , 下次连接master时就不需要再提供连接选项。
以下是master.info的内容
[root@slave01 ~]# cat /var/lib/mysql/master.info
33 # 本文件的行数
master-bin.000001 # IO线程正从哪个master binlog读取日志
10056627 # IO线程读取到master binlog的位置
192.168.71.7 # master_host
repl # master_user
P@ssword1! # master_password
3306 # master_port
60 # master_retry , slave重连master的超时时间(单位秒)
1
1
30.000
0
0
using_gtid=0
do_domain_ids=0
ignore_domain_ids=0
END_MARKER4.5.1.2 relay-log.info
relay-log.info文件中记录的是SQL线程相关的信息。以下是relay-log.info文件的内容
[root@slave01 ~]# cat /var/lib/mysql/relay-log.info
5 # 本文件的行数
./relay-log.000002 # 当前SQL线程正在读取的relay-log文件
556 # SQL线程已执行到的relay log位置
master-bin.000001 # SQL线程最近执行的操作对应的是哪个master binlog
10056627 # SQL线程最近执行的操作对应的是master binlog的哪个位置
0 # slave上必须落后于master多长时间4.5.1.3 show slave status
在slave上执行show slave status可以查看slave的状态信息。
MariaDB [(none)]> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event # slave上IO线程的状态 , 来源于show processlist
Master_Host: 192.168.71.7
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 10056627
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 556
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes # IO线程的状态 , 此处为运行且连接状态
Slave_SQL_Running: Yes # SQL线程的状态 , 此处为运行状态
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table: # SQL线程的状态 , 此处为未运行状态
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 10056627
Relay_Log_Space: 859
Until_Condition: None # start slave语句中指定的until条件 , 例如 , 读取到哪个binlog位置就停止
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0 # SQL线程执行过的位置比IO线程慢多少
Master_SSL_Verify_Server_Cert: Yes
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 7 #master的server id
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: optimistic
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates # slave SQL线程的状态
Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
Replicate_Rewrite_DB:
1 row in set (0.001 sec)
因为太长 , 后面再列出show slave status时 , 将裁剪一些意义不大的行。
再次回到上面show slave status的信息。除了那些描述IO线程、SQL线程状态的行 , 还有几个log_file和pos相关的行 , 如下所列。
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 10056627
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 556
Relay_Master_Log_File: master-bin.000001
Exec_Master_Log_Pos: 10056627理解这几行的意义至关重要 , 前面因为排版限制 , 描述看上去有些重复。所以这里完整地描述它们 :
Master_Log_File:IO线程正在读取的master binlog;Read_Master_Log_Pos:IO线程已经读取到master binlog的哪个位置 ;Relay_Log_File:SQL线程正在读取和执行的relay log;Relay_Log_Pos:SQL线程已经读取和执行到relay log的哪个位置 ;Relay_Master_Log_File:SQL线程最近执行的操作对应的是哪个master binlog;Exec_Master_Log_Pos:SQL线程最近执行的操作对应的是master binlog的哪个位置。
所以 , (Relay_Master_Log_File, Exec_Master_log_Pos)构成一个坐标 , 这个坐标表示slave上已经将master上的哪些数据重放到自己的实例中 , 它可以用于下一次change master to时指定的binlog坐标。
与这个坐标相对应的是slave上SQL线程的relay log坐标(Relay_Log_File, Relay_Log_Pos)。这两个坐标位置不同 , 但它们对应的数据是一致的。
最后还有一个延迟参数Seconds_Behind_Master需要说明一下 , 它的本质意义是SQL线程比IO线程慢多少。如果master和slave之间的网络状况优良 , 那么slave的IO线程读速度和master写binlog的速度基本一致 , 所以这个参数也用来描述"SQL线程比master慢多少" , 也就是说slave比master少多少数据 , 只不过衡量的单位是秒。但需要注意 , 这个参数的描述并不标准 , 只有在网速很好的时候做个大概估计 , 很多种情况下它的值都是0 , 即使SQL线程比IO线程慢了很多也是如此。
4.5.1.4 slave信息汇总
上面的master.info、relay-log.info和show slave status的状态都是刚连接上master还未启动IO thread、SQL thread时的状态。下面将显示已经进行一段正在执行复制的slave状态。
首先查看启动io thread和sql thread后的状态。使用show processlist查看即可。
MariaDB [(none)]> start slave;
mysql> show processlist; # slave上的信息 , 为了排版 , 简化了输出
+----+-------------+-----------+--------------------------------------------------------+
| Id | User | Command | State |
+----+-------------+-----------+--------------------------------------------------------+
| 4 | root | Sleep | |
| 7 | root | Query | starting |
| 8 | system user | Slave_IO | Waiting for master to send event |
| 9 | system user | Slave_SQL | Slave has read all relay log; waiting for more updates |
+----+-------------+---------+--------------------------------------------------------+
可以看到 :
Id=8的线程负责连接master并读取binlog, 它是IO线程 , 它的状态指示"等待master发送更多的事件" ;Id=9的线程负责读取relay log, 它是SQL线程 , 它的状态指示"已经读取了所有的relay log"。
再看看此时master上的信息。
mysql> show processlist; # master上的信息 , 为了排版 , 经过了修改
+----+------+-----------------------+-------------+--------------------------------------+
| Id | User | Host | Command | State |
+----+------+-----------------------+-------------+--------------------------------------+
| 4 | root | localhost | Query | starting |
|----|------|-----------------------|-------------|--------------------------------------|
| 16 | repl | 192.168.71.11:51604 | Binlog Dump | Master has sent all binlog to slave; |
| | | | | waiting for more updates |
+----+------+-----------------------+-------------+--------------------------------------+master上有一个Id=16的binlog dump线程 , 该线程的用户是repl。它的状态指示"已经将所有的binlog发送给slave了"。
现在 , 在master上执行一个长事件 , 以便查看slave上的状态信息。
仍然使用前面插入数值辅助表的存储过程 , 这次分别向两张表中插入一亿条数据。
call proc_num1(100000000);
call proc_num2(100000000);然后去slave上查看信息 , 如下。因为太长 , 已经裁剪了一部分没什么用的行。
MariaDB [backuptest]> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.71.7
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 512083127
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 502023507
Relay_Master_Log_File: master-bin.000001
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: 512083127
Relay_Log_Space: 502023810
......从中获取到的信息有 :
IO线程的状态SQL线程的状态IO线程读取到master binlog的哪个位置 :512083127,Read_Master_Log_PosSQL线程已经执行到relay log的哪个位置 :502023507,Relay_Log_PosSQL线程执行的位置对应于master binlog的哪个位置 :512083127,Exec_Master_Log_Pos
可以看出 , IO线程比SQL线程超前了很多很多 , 所以SQL线程比IO线程的延迟较大。
4.6.2 MySQL复制如何实现断开重连
很多人以为change master to语句是用来连接master的 , 实际上这种说法是错的。连接master是IO线程的事情 , change master to只是为IO线程连接master时提供连接参数。
如果slave从来没连接过master , 那么必须使用change master to语句来生成IO线程所需要的信息 , 这些信息记录在master.info中。这个文件是change master to成功之后立即生成的 , 以后启动IO线程时 , IO线程都会自动读取这个文件来连接master , 不需要先执行change master to语句。
例如 , 可以随时stop slave来停止复制线程 , 然后再随时start slave , 只要master.info存在 , 且没有人为修改过它 , IO线程就一定不会出错。这是因为master.info会随着IO线程的执行而更新 , 无论读取到master binlog的哪个位置 , 都会记录下这个位置 , 如此一来 , IO线程下次启动的时候就知道从哪里开始监控master binlog。
前面还提到一个文件 : relay-log.info文件。这个文件中记录的是SQL线程的相关信息 , 包括读取、执行到relay log的哪个位置 , 刚重放的数据对应master binlog的哪个位置。随着复制的进行 , 这个文件的信息会即时改变。所以 , 通过relay-log.info , 下次SQL线程启动的时候就能知道从relay log的哪个地方继续读取、执行。
如果不小心把relay log文件删除了 , SQL线程可能会丢失了一部分相比IO线程延迟的数据。这时候 , 只需将relay-log.info中第4、5行记录的"SQL线程刚重放的数据对应master binlog的坐标"手动修改到master.info中即可 , 这样IO线程下次连接master就会从master binlog的这个地方开始监控。当然 , 也可以将这个坐标作为change master to的坐标来修改master.info。
此外 , 当mysql实例启动时 , 默认会自动start slave , 也就是MySQL一启动就自动开启复制线程。如果想要禁止这种行为 , 在配置文件中加上 :
[mysqld]
skip-slave-start4.6.3 相关变量
默认情况下 , slave连接到master后会在slave的datadir下生成master.info和relay-log.info文件 , 但是这是可以通过设置变量来改变的。
master-info-repository={TABLE|FILE}:master的信息是记录到文件master.info中还是记录到表mysql.slave_master_info中。默认为file。relay-log-info-repository={TABLE|FILE}:slave的信息是记录到文件relay-log.info中还是记录到表mysql.slave_relay_log_info中。默认为file。
IO线程每次从master复制日志要写入到relay log中 , 但是它是先放在内存的 , 等到一定时机后才会将其刷到磁盘上的relay log文件中。刷到磁盘的时机可以由变量控制。
另外 , IO线程每次从master复制日志后都会更新master.info的信息 , 也是先更新内存中信息 , 在特定的时候才会刷到磁盘的master.info文件 ; 同理SQL线程更新realy-log.info也是一样的。它们是可以通过变量来设置更新时机的。
sync-relay-log=N: 设置为大于0的数表示每从master复制N个事件就刷一次盘。设置为0表示依赖于操作系统的sync机制。sync-master-info=N: 依赖于master-info-repository的设置 , 如果为file, 则设置为大于0的值时表示每更新多少次master.info将其写入到磁盘的master.info中 , 设置为0则表示由操作系统来决定何时调用fdatasync()函数刷到磁盘。如果设置为table, 则设置为大于0的值表示每更新多少次master.info就更新mysql.slave_master_info表一次 , 如果设置为0则表示永不更新该表。sync-relay-log-info=N: 同上。
5. MYSQL复制常用操作
5.1 筛选要复制的库和表
复制过滤原理 : （黑、白名单）仅复制有限一个或几个数据库相关的数据 , 而非所有；由复制过滤器进行；
由两种实现思路:
服务器选项 : 主服务器仅向二进制日志中记录与特定数据库相关的事件 , 这种方式在主服务器上配置。此项和
binlog_format相关。参看 : https://mariadb.com/kb/en/library/mysqld-options/#-binlog-ignore-dbbinlog_do_db = 数据库白名单列表 , 多个数据库需多行实现 binlog_ignore_db = 数据库黑名单列表问题 : 基于二进制还原将无法实现 ; 不建议使用
从服务器
SQL_THREAD在replay中继日志中的事件时 , 仅读取与特定数据库(特定表)相关的事件并应用于本地问题 : 会造成网络及磁盘
IO浪费 建议使用此方法 , 在从服务器上配置从服务器上的复制过滤器相关变量
replicate_do_db= # 指定复制库的白名单 , 仅允许从复制这个库的二进制日志 replicate_ignore_db= # 指定复制库黑名单 , 除了这个数据库 , 其他都允许复制 replicate_do_table= # 指定复制表的白名单 replicate_ignore_table= # 指定复制表的黑名单 replicate_wild_do_table= foo%.bar% 支持通配符 replicate_wild_ignore_table=注意 : 跨库的更新将无法同步
默认情况下 , slave会复制master上所有库。可以指定以下变量显式指定要复制的库、表和要忽略的库、表 , 也可以将其写入配置文件。
Replicate_Do_DB: 要复制的数据库
Replicate_Ignore_DB: 不复制的数据库
Replicate_Do_Table: 要复制的表
Replicate_Ignore_Table: 不复制的表
Replicate_Wild_Do_Table: 通配符方式指定要复制的表
Replicate_Wild_Ignore_Table: 通配符方式指定不复制的表如果要指定列表 , 则多次使用这些变量进行设置。
需要注意的是 , 尽管显式指定了要复制和忽略的库或者表 , 但是master还是会将所有的binlog传给slave并写入到slave的relay log中 , 真正负责筛选的slave上的SQL线程。
另外 , 如果slave上开启了binlog , SQL线程读取relay log后会将所有的事件都写入到自己的binlog中 , 只不过对于那些被忽略的事件只记录相关的事务号等信息 , 不记录事务的具体内容。所以 , 如果之前设置了被忽略的库或表 , 后来取消忽略后 , 它们在取消忽略以前的变化是不会再重放的 , 特别是基于gtid的复制会严格比较binlog中的gtid。
总之使用筛选的时候应该多多考虑是否真的要筛选 , 是否是永久筛选。
5.1.1 第一种方法 : 在从服务器上指定特定数据库进行复制（推荐）
在
B从服务器修改/etc/my.cnf配置文件并重启mysql服务器server-id=2 #配置server-id , 让从服务器有唯一ID号 relay_log = relay-log #打开Mysql日志 , 日志格式为二进制 read_only = 1 #设置只读权限 log_bin = /data/logbin/mysql-bin #开启从服务器二进制日志 , （必须） log_slave_updates = 1 #使得更新的数据写进二进制日志中 replicate_do_db=hellodb # 过滤hellodb数据库 , 只复制此数据库在
C服务器修改mysql配置文件并重启mysql服务器server-id=3 #配置server-id , 让从服务器有唯一ID号 relay_log = relay-log #打开Mysql日志 , 日志格式为二进制 read_only = 1 #设置只读权限 log_bin = /data/logbin/mysql-bin #开启从服务器二进制日志 , （必须） log_slave_updates = 1 #使得更新的数据写进二进制日志中 replicate_do_db=hellodb # 过滤hellodb数据库 , 只复制此数据库在
A主服务器上hellodb数据库的teachers表中插入内容MariaDB>use hellodb; MariaDB [hellodb]> insert teachers(name)value('biu');在hellodb数据库的teachers表中插入内容在
A从服务器mysql数据库创建一个db1数据库MariaDB [hellodb]> create database db1; Query OK, 1 row affected (0.00 sec) MariaDB [hellodb]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | hellodb | | mysql | | performance_schema | | test | +--------------------+ 6 rows in set (0.00 sec)在
B从服务msyql服务器中查看A主服务器创建的数据库内容MariaDB [hellodb]> select * from teachers; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 4 Current database: hellodb +-----+---------------+-----+--------+ | TID | Name | Age | Gender | +-----+---------------+-----+--------+ | 1 | Song Jiang | 45 | M | | 2 | Zhang Sanfeng | 94 | M | | 3 | Miejue Shitai | 77 | F | | 4 | Lin Chaoying | 93 | F | | 5 | a | 0 | NULL | | 6 | biu | 0 | NULL | +-----+---------------+-----+--------+此时查看新建的
db1数据库无法复制MariaDB [hellodb]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | | mysql | | performance_schema | | test | +--------------------+在
C从服务器查看A主服务器创建的数据库MariaDB [hellodb]> select * from teachers; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 4 Current database: hellodb +-----+---------------+-----+--------+ | TID | Name | Age | Gender | +-----+---------------+-----+--------+ | 1 | Song Jiang | 45 | M | | 2 | Zhang Sanfeng | 94 | M | | 3 | Miejue Shitai | 77 | F | | 4 | Lin Chaoying | 93 | F | | 5 | a | 0 | NULL | | 6 | biu | 0 | NULL | +-----+---------------+-----+--------+此时查看新建的
db1数据库无法复制MariaDB [hellodb]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | | mysql | | performance_schema | | test | +--------------------+
5.1.2 在主服务器上设置复制指定的数据库和二进制日志（生产中不推荐）
vim /etc/my.cnf
[mysqld]
[mysqld]
server-id=1
log_bin=/data/logbin/mysql-bin
binlog_format=ROW
binlog_do_db=hellodb # 只复制hellodb数据库及二进制日志
binlog_do_db=db1 # 只复制db1数据库及二进制日志重启msyql服务 : systemctl restart mariadb
删除B和C从服务器的配置文件 : replicate_do_db=hellodb 并重启mysql服务 : systemctl restart mariadb,做此实验室需要将以上的实验配置文件先删除。
由于上面做的实验 , 创建了db1 , 当时从服务器未复制db1数据库 , 此时我们可以在主服务器上删除db1重新创建db1
在
A主服务器上删除db1数据库drop database db1;在
B和C从服务器都操作slave, 跳过当前错误复制过程MariaDB [hellodb]> stop slave; 先停止slave MariaDB [hellodb]> set global sql_slave_skip_counter=1; 跳过当前复制错误 MariaDB [hellodb]> start slave; 开启slave此时在
A主服务器上创建db1数据库MariaDB [hellodb]> create database db1;查看此时
B和C从服务器的数据库复制情况MariaDB [hellodb]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | hellodb | | mysql | | performance_schema | | test | +--------------------+在
A主服务器上查询白名单状态MariaDB [hellodb]> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 | 599 | hellodb,db1 | | +------------------+----------+--------------+------------------+
5.2 reset slave和reset master
reset slave会删除master.info/relay-log.info和relay log , 然后新生成一个relay log。但是change master to设置的连接参数还在内存中保留着 , 所以此时可以直接start slave , 并根据内存中的change master to连接参数复制日志。
reset slave all除了删除reset slave删除的东西 , 还删除内存中的change master to设置的连接信息。
reset master会删除master上所有的二进制日志 , 并新建一个日志。在正常运行的主从复制环境中 , 执行reset master很可能导致异常状况。所以建议使用purge来删除某个时间点之前的日志(应该保证只删除那些已经复制完成的日志)。
这里mysql9版本这里两个命令中reset slave已经替换为reset replica, reset master命令已经替换为RESET BINARY LOGS AND GTIDS。
5.3 show slave hosts
如果想查看master有几个slave的信息 , 可以使用show slave hosts。以下为某个master上的结果 :
MariaDB [(none)]> show slave hosts;;
+-----------+---------------+------+-----------+
| Server_id | Host | Port | Master_id |
+-----------+---------------+------+-----------+
| 11 | 192.168.71.11 | 3306 | 7 |
+-----------+---------------+------+-----------+可以看到 , 该show中会显示server-id、slave的主机地址和端口号、它们的master_id。
其中show结果中的host显示结果是由slave上的变量report_host控制的 , 端口是由report_port控制的。
例如 , 在slave1上修改其配置文件 , 添加report-host项后重启mariadb服务。
[mysqld]
report_host=192.168.100.12在slave1上查看 , host已经显示为新配置的项。
MariaDB [(none)]> show slave hosts;;
+-----------+----------------+------+-----------+
| Server_id | Host | Port | Master_id |
+-----------+----------------+------+-----------+
| 11 | 192.168.100.12 | 3306 | 7 |
+-----------+----------------+------+-----------+
1 row in set (0.000 sec)5.4 多线程复制
在老版本中 , 只有一个SQL线程读取relay log并重放。重放的速度肯定比IO线程写relay log的速度慢非常多 , 导致SQL线程非常繁忙 , 且实现到从库上延迟较大。没错 , 多线程复制可以解决主从延迟问题 , 且使用得当的话效果非常的好(关于主从复制延迟 , 是生产环境下最常见的问题之一 , 且没有很好的办法来避免。后文稍微介绍了一点方法)。
在MySQL 5.6中引入了多线程复制(multi-thread slave , 简称MTS) , 这个多线程指的是多个SQL线程 , IO线程还是只有一个。当IO线程将master binlog写入relay log中后 , 一个称为"多线程协调器(multithreaded slave coordinator)"会对多个SQL线程进行调度 , 让它们按照一定的规则去执行relay log中的事件。
需要谨记于心的是 , 如果对多线程复制没有了解的很透彻 , 千万不要在生产环境中使用多线程复制。它的确带来了一些复制性能的提升 , 并且能解决主从超高延迟的问题 , 但随之而来的是很多的"疑难杂症" , 这些"疑难杂症"并非是bug , 只是需要多多了解之后才知道为何会出现这些问题以及如何解决这些问题。稍后会简单介绍一种多线程复制问题 : gaps。
通过全局变量slave-parallel-workers控制SQL线程个数 , 设置为非0正整数N , 表示多加N个SQL线程 , 加上原有的共N+1个SQL线程。默认为0 , 表示不加任何SQL线程 , 即关闭多线程功能。
MariaDB [(none)]> show variables like "%parallel%";
+-------------------------------+------------+
| Variable_name | Value |
+-------------------------------+------------+
| skip_parallel_replication | OFF |
| slave_domain_parallel_threads | 0 |
| slave_parallel_max_queued | 131072 |
| slave_parallel_mode | optimistic |
| slave_parallel_threads | 0 |
| slave_parallel_workers | 0 |
+-------------------------------+------------+
6 rows in set (0.005 sec)显然 , 多线程只有在slave上开启才有效 , 因为只有slave上才有SQL线程。另外 , 设置了该全局变量 , 需要重启SQL线程才生效 , 否则内存中还是只有一个SQL线程。
例如 , 初始时slave上的processlist如下 :
设置slave_parallel_workers=2。
可见多出了两个线程 , 其状态信息是"Waiting for work from SQL thread"。
虽然是多个SQL线程 , 但是复制时每个库只能使用一个线程(默认情况下 , 可以通过--slave-parallel-type修改并行策略) , 因为如果一个库可以使用多个线程 , 多个线程并行重放relay log , 可能导致数据错乱。所以应该设置线程数等于或小于要复制的库的数量 , 设置多了无效且浪费资源。
5.4.1 多线程复制带来的不一致问题
虽然多线程复制带来了一定的复制性能提升 , 但它也带来了很多问题 , 最严重的是一致性问题。完整的内容见官方手册。此处介绍其中一个最重要的问题。
关于多线程复制 , 最常见也是开启多线程复制前最需要深入了解的问题是 : 由于多个SQL线程同时执行relay log中的事务 , 这使得slave上提交事务的顺序很可能和master binlog中记录的顺序不一致(除非指定变量slave_preserve_commit_order=1)。(注意 : 这里说的是事务而不是事件。因为MyISAM的binlog顺序无所谓 , 只要执行完了就正确 , 而且多线程协调器能够协调好这些任务。所以只需考虑innodb基于事务的binlog)
举个简单的例子 , master上事务A先于事务B提交 , 到了slave上因为多SQL线程的原因 , 可能事务B提交了事务A却还没提交。
是否还记得show slave status中的Exec_master_log_pos代表的意义？它表示SQL线程最近执行的事件对应的是master binlog中的哪个位置。问题由此而来。通过show slave status , 我们看到已经执行事件对应的坐标 , 它前面可能还有事务没有执行。而在relay log中 , 事务B记录的位置是在事务A之后的(和master一样) , 于是事务A和事务B之间可能就存在一个孔洞(gap) , 这个孔洞是事务A剩余要执行的操作。
正常情况下 , 多线程协调器记录了一切和多线程复制相关的内容 , 它能识别这种孔洞(通过打低水位标记low-watermark) , 也能正确填充孔洞。即使是在存在孔洞的情况下执行stop slave也不会有任何问题 , 因为在停止SQL线程之前 , 它会等待先把孔洞填充完。但危险因素太多 , 比如突然宕机、突然杀掉mysqld进程等等 , 这些都会导致孔洞持续下去 , 甚至可能因为操作不当而永久丢失这部分孔洞。
那么如何避免这种问题 , 出现这种问题如何解决？
如何避免gap。
前面说了 , 多个
SQL线程是通过协调器来调度的。默认情况下 , 可能会出现gap的情况 , 这是因为变量slave_preserve_commit_order的默认值为0。该变量指示协调器是否让每个SQL线程执行的事务按master binlog中的顺序提交。因此 , 将其设置为1 , 然后重启SQL线程即可保证SQL线程按序提交 , 也就不可能会有gap的出现。当事务
B准备先于事务A提交的时候 , 它将一直等待。此时slave的状态将显示 :Waiting for preceding transaction to commit # MySQL 5.7.8之后显示该状态 Waiting for its turn to commit # MySQL 5.7.8之前显示该状态尽管不会出现
gap, 但show slave status的Exec_master_log_pos仍可能显示在事务A的坐标之后。由于开启
slave_preserve_commit_order涉及到不少操作 , 它还要求开启slave的binlog--log-bin(因此需要重启mysqld) , 且开启重放relay log也记录binlog的行为--log-slave-updates, 此外 , 还必须设置多线程的并行策略--slave-parallel-type=LOGICAL_CLOCK。shell> mysqladmin -uroot -p shutdown shell> cat /etc/my.cnf log_bin=slave-bin log-slave-updates slave_parallel_workers=1 slave_parallel_type=LOGICAL_CLOCK shell>service mysqld start
如何处理已经存在的gap。
方法之一 , 是从
master上重新备份恢复到slave上 , 这种方法是处理不当的最后解决办法。正常的处理方法是 , 使用
START SLAVE [SQL_THREAD] UNTIL SQL_AFTER_MTS_GAPS;, 它表示SQL线程只有先填充gaps后才能启动。实际上 , 它涉及了两个操作 :填充
gaps自动停止
SQL线程(所以之后需要手动启动SQL线程)
一般来说 , 在填充完gaps之后 , 应该先reset slave移除已经执行完的relay log , 然后再去启动sql_thread。
5.4.2 多线程复制切换回单线程复制
多线程的带来的问题不止gaps一种 , 所以没有深入了解多线程的情况下 , 千万不能在生产环境中启用它。如果想将多线程切换回单线程 , 可以执行如下操作 :
START SLAVE UNTIL SQL_AFTER_MTS_GAPS;
SET @@GLOBAL.slave_parallel_workers = 0;
START SLAVE SQL_THREAD;5.5 slave升级为master的大致操作
当master突然宕机 , 有时候需要切换到slave , 将slave提升为新的master。但对于master突然宕机可能造成的数据丢失 , 主从切换是无法解决的 , 它只是尽可能地不间断提供MySQL服务。
假如现在有主服务器M , 从服务器S1、S2 , S1作为将来的新的master。
在将
S1提升为master之前 , 需要保证S1已经将relay log中的事件已经replay完成。即下面两个状态查看语句中SQL线程的状态显示为 : "Slave has read all relay log; waiting for the slave I/O thread to update it"。show slave status; show processlist;停止
S1上的IO线程和SQL线程 , 然后将S1的binlog清空(要求已启用binlog)。mysql> stop slave; mysql> reset master; mysql> reset slave all;在
S2上停止IO线程和SQL线程 , 通过change master to修改master的指向为S1, 然后再启动io线程和SQL线程。mysql> stop slave; mysql> reset slave all; mysql> change master to master_host=S1,... mysql> start slave;将应用程序原本指向
M的请求修改为指向S1, 如修改MySQL代理的目标地址。一般会通过MySQL Router、Amoeba、cobar等数据库中间件来实现。删除
S1上的master.info、relay-log.info文件 , 否则下次S1重启服务器会继续以slave角色运行。将来
M重新上线后 , 可以将其配置成S1的slave, 然后修改应用程序请求的目标列表 , 添加上新上线的M, 如将M加入到MySQL代理的读目标列表。
注意 : reset master很重要 , 如果不是基于GTID复制且开启了log-slave-updates选项时 , S1在应用relay log的时候会将其写入到自己的binlog , 以后S2会复制这些日志导致重复执行的问题。
其实上面只是提供一种slave升级为Master的解决思路 , 在实际应用中环境可能比较复杂。例如 , 上面的S1是S2的master , 这时S1如果没有设置为read-only , 当M宕机时 , 可以不用停止S1 , 也不需要reset master等操作 , 受影响的操作仅仅只是S1一直无法连接M而已 , 但这对业务不会有多大的影响。
相信理解了前面的内容 , 分析主从切换的思路应该也没有多大问题。
5.6 指定不复制到slave上的语句
前面说的筛选要复制的库和表可以用于指定不复制到slave上的库和表 , 但却没有筛选不复制到slave的语句。
但有些特殊情况下 , 可能需要这种功能。例如 , master上创建专门用于复制的用户repl , 这种语句其实没有必要复制到slave上 , 甚至出于安全的考虑不应该复制到slave上。
可以使用sql_log_bin变量对此进行设置 , 默认该变量的值为1 , 表示所有语句都写进binlog , 从而被slave复制走。如果设置为0 , 则之后的语句不会写入binlog , 从而实现"不复制某些语句到slave"上的功能。
例如 : 屏蔽创建repl用户的语句。
mysql> set sql_log_bin=0;
mysql> create user repl@'%' identified by 'P@ssword1!';
mysql> grant replication slave on *.* to repl@'%';
mysql> set sql_log_bin=1;在使用该变量时 , 默认是会话范围内的变量 , 一定不能设置它的全局变量值 , 否则所有语句都将不写binlog。
5.7 主从高延迟的解决思路
slave通过IO线程获取master的binlog , 并通过SQL线程来应用获取到的日志。因为各个方面的原因 , 经常会出现slave的延迟(即Seconds_Behind_Master的值)非常高(动辄几天的延迟是常见的 , 几个小时的延迟已经算短的) , 使得主从状态不一致。
一个很容易理解的延迟示例是 : 假如master串行执行一个大事务需要30分钟 , 那么slave应用这个事务也大约要30分钟 , 从master提交的那一刻开始 , slave的延迟就是30分钟 , 更极端一点 , 由于binlog的记录时间点是在事务提交时 , 如果这个大事务的日志量很大 , 比如要传输10多分钟 , 那么很可能延迟要达到40分钟左右。而且更严重的是 , 这种延迟具有滚雪球的特性 , 从延迟开始 , 很容易导致后续加剧延迟。
所以 , 第一个优化方式是不要在mysql中使用大事务 , 这是mysql主从优化的第一口诀。
在回归正题 , 要解决slave的高延迟问题 , 先要知道Second_Behind_Master是如何计算延迟的 : SQL线程比IO线程慢多少(其本质是NOW()减去Exec_Master_Log_Pos处设置的TIMESTAMP)。在主从网络状态良好的情况下 , IO线程和master的binlog大多数时候都能保持一致(也即是IO线程没有多少延迟 , 除非事务非常大 , 导致二进制日志传输时间久 , 但mysql优化的一个最基本口诀就是大事务切成小事务) , 所以在这种理想状态下 , 可以认为主从延迟说的是slave上的数据状态比master要延迟多少。它的计数单位是秒。
从产生
Binlog的master上考虑 , 可以在master上应用group commit的功能 , 并设置参数binlog_group_commit_sync_delay和binlog_group_commit_sync_no_delay_count, 前者表示延迟多少秒才提交事务 , 后者表示要堆积多少个事务之后再提交。这样一来 , 事务的产生速度降低 , slave的SQL线程相对就得到缓解。再者从
slave上考虑 , 可以在slave上开启多线程复制(MTS)功能 , 让多个SQL线程同时从一个IO线程中取事务进行应用 , 这对于多核CPU来说是非常有效的手段。但是前面介绍多线程复制的时候说过 , 没有掌握多线程复制的方方面面之前 , 千万不要在生产环境中使用多线程复制 , 要是出现gap问题 , 很让人崩溃。最后从架构上考虑。主从延迟是因为
slave跟不上master的速度 , 那么可以考虑对master进行节流控制 , 让master的性能下降 , 从而变相提高slave的能力。这种方法肯定是没人用的 , 但确实是一种方法 , 提供了一种思路 , 比如slave使用性能比master更好的硬件。另一种比较可取的方式是加多个中间slave层(也就是master->slaves->slaves) , 让多个中间slave层专注于复制(也可作为非业务的他用 , 比如用于备份)。使用组复制或者
Galera/PXC的多写节点 , 此外还可以设置相关参数 , 让它们对延迟自行调整。但一般都不需要调整 , 因为有默认设置。
还有比较细致的方面可以降低延迟 , 比如设置为row格式的Binlog要比statement要好 , 因为不需要额外执行语句 , 直接修改数据即可。比如master设置保证数据一致性的日志刷盘规则(sync_binlog/innodb_flush_log_at_trx_commit设置为1) , 而slave关闭binlog或者设置性能优先于数据一致性的binlog刷盘规则。再比如设置slave的隔离级别使得slave的锁粒度放大 , 不会轻易锁表(多线程复制时避免使用此方法)。还有很多方面 , 选择好的磁盘 , 设计好分库分表的结构等等 , 这些都是直接全局的 , 实在没什么必要在这里多做解释。
5.8 sql_slave_skip_counter跳过复制错误用法
在主服务器上查看一个表的详细情况
MariaDB [hellodb]> select * from teachers; 可以看到此时的teachers表序列号是4
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
MariaDB [hellodb]> desc teachers; 我们可以看到序列号是自动增长 , TID为主键
+--------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------------------+------+-----+---------+----------------+
| TID | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| Name | varchar(100) | NO | | NULL | |
| Age | tinyint(3) unsigned | NO | | NULL | |
| Gender | enum('F','M') | YES | | NULL | |
+--------+----------------------+------+-----+---------+----------------+在从服务器上查看一个表的详细情况
MariaDB [hellodb]> select * from teachers; 可以看到此时的teachers表序列号是4
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
MariaDB [hellodb]> desc teachers; 我们可以看到序列号是自动增长 , TID为主键
+--------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------------------+------+-----+---------+----------------+
| TID | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| Name | varchar(100) | NO | | NULL | |
| Age | tinyint(3) unsigned | NO | | NULL | |
| Gender | enum('F','M') | YES | | NULL | |
+--------+----------------------+------+-----+---------+----------------+下来我们先在B从服务器的teachers表添加数据 , 添加的haha的TID是6 , 就会与主服务器的TID存在冲突 , 就无法复制主无服务器的信息
MariaDB [hellodb]> insert teachers(name)value('biubiu');
Query OK, 1 row affected, 1 warning (0.01 sec)
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | lisi | 0 | NULL |
| 6 | haha | 0 | NULL |
| 7 | biubiu | 0 | NULL |
+-----+---------------+-----+--------+我们在主服务器的teachers表添加数据 , 此时添加的wang的TID是6
MariaDB [hellodb]> insert teachers(name)value('wang');
Query OK, 1 row affected, 1 warning (0.02 sec)
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | lisi | 0 | NULL |
| 6 | wang | 0 | NULL |
+-----+---------------+-----+--------+下来我们在B从服务器上查看slave状态 , 可以明确的看到错误信息 , 是因为主键6存在错误
MariaDB [hellodb]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.34.101
Master_User: rpl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 7924
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 8014
Relay_Master_Log_File: mysql-bin.000001
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: 1062
Last_Error: Could not execute Write_rows event on table hellodb.teachers; Duplicate entry '6' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000001, end_log_pos 7897
Skip_Counter: 0
Exec_Master_Log_Pos: 7730
Relay_Log_Space: 8504
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: 1062 错误编号
Last_SQL_Error: Could not execute Write_rows event on table hellodb.teachers; Duplicate entry '6' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000001, end_log_pos 7897
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1停止slave , 然后再设置跳过此次的错误信息
MariaDB [hellodb]> stop slave;我们在B服务器上进行修改跳过一个错误的操作
MariaDB [hellodb]> set global sql_slave_skip_counter=1; 由于只有一个错误 , 就修改为1查看跳过的信息
MariaDB [hellodb]> select @@sql_slave_skip_counter;
+--------------------------+
| @@sql_slave_skip_counter |
+--------------------------+
| 1 |
+--------------------------+开启slave
MariaDB [hellodb]> start slave;启动slave , 查看slave状态 , 并查看复制的结果 , 此时会跳过主服务器冲突的TID , 从服务器继续复制之后的信息
MariaDB [hellodb]> show slave status\G; 查看当前slave状态
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.34.101
Master_User: rpl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 8508
Relay_Log_File: mariadb-relay-bin.000004
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-bin.000001
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: 8508
Relay_Log_Space: 1693
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: 1
MariaDB [hellodb]> select * from teachers; 此时跳过了冲突的TID , 不会复制主服务器冲突TID的信息 , 直接复制下面的信息
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | lisi | 0 | NULL |
| 6 | haha | 0 | NULL |
| 7 | biubiu | 0 | NULL |
| 8 | zhangsan | 0 | NULL |
| 9 | wo | 0 | NULL |
+-----+---------------+-----+--------+冲突的TID=6 , 可以手动在B从服务器上修改 , 保证与主服务器一致
MariaDB [hellodb]> update teachers set name='wang' where tid=6; 修改tid=6的相关信息
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | lisi | 0 | NULL |
| 6 | wang | 0 | NULL |
| 7 | biubiu | 0 | NULL |
| 8 | zhangsan | 0 | NULL |
| 9 | wo | 0 | NULL |
+-----+---------------+-----+--------+另外还有第二个方法，slave_skip_errors 参数通常用于指定在复制过程中，从服务器遇到哪些错误代码时应该跳过错误并继续复制。默认情况下，当复制过程中出现错误时，复制会停止，以便手动解决数据不一致问题。使用 slave_skip_errors 可以配置从服务器在遇到指定错误时继续复制。
OFF：默认值，表示不跳过任何错误。
[list of error codes]：指定一个错误代码列表，从服务器在遇到这些错误时会跳过。
all：忽略所有错误消息并继续运行。这可能会导致数据不一致。
ddl_exist_errors：等价于一系列错误代码，包括但不限于
1007, 1008, 1050, 1051, 1054, 1060, 1061, 1068, 1094, 1146，这些错误通常与 DDL 操作相关，如数据库或表已存在或不存在的错误。
常见错误代码
1007：数据库已存在。1008：数据库不存在。1050：表已存在。1051：表不存在。1054：字段不存在。1060：字段重复。1061：重复键名。1062：主键冲突。1146：表不存在。
[root@slave1 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
跳过所有错误：
[mysqld]
slave_skip_errors=ALL
跳过1062,1053错误
[mysqld]
slave_skip_errors=1062,1053
#跳过DDL存在错误：
[mysqld]
slave_skip_errors=ddl_exist_errors
#可以通过以下命令查看当前 slave_skip_errors 参数的值：
SHOW VARIABLES LIKE 'slave_skip_errors';参考链接
MySQL之八---Mysql实现数据库主从复制、主主复制、级联复制、半同步复制及复制监控 - 一叶知秋~~ - 博客园