基于GTID的Mariadb主从复制

GTID 概述

  在MariaDB中，只要开启了binlog，所有的DML和DDL都会形成事件并且写入其中，并且每一件事务都会生成全局唯一的事务ID：Global transaction ID（全局事务ID），简称GTID。
 GTID是属于全局唯一，所以无需过多配置，从机可通过GTID同步主机binlog文件，从而形成主从架构，在实际生产环境中能够提高数据库容错率；

注：
1.MariaDB的GTID和MySQL不兼容，并且在MariaDB 10.0.2之前配置非常麻烦
2.从Mariadb10.5起，Master和Slave术语逐渐被primary和replica替换
3.GTID是默认开启的，无需在my.cnf里配置gtid_mode=on

 GTID由3个整数构成，用“-”进行分割，例如：

0-1-10

• 第一位：0，domain ID，架构是1主多从，默认为0；如果有多主N从，domain ID为多个；
• 第二位：1，Server ID,在my.cnf中定义；
• 第三位：10，binlog中的事务ID

查看、使用GTID

• 在primary中查看GTID

# 查看primary binlog记录点
mysql> SHOW MASTER STATUS;
+--------------------+------------+----------------+--------------------+
| File               |   Position | Binlog_Do_DB   | Binlog_Ignore_DB   |
|--------------------+------------+----------------+--------------------|
| master1-bin.000003 |      22438 | test        |                    |
+--------------------+------------+----------------+--------------------+
1 row in set

# 通过记录点查询GTID
mysql> SELECT BINLOG_GTID_POS('master1-bin.000003', 22438);
+------------------------------------------------+
| BINLOG_GTID_POS('master1-bin.000003', 22438)   |
|------------------------------------------------|
| 0-1-1925                                       |
+------------------------------------------------+
1 row in set

或者

mysql> SELECT @@global.gtid_binlog_pos;
+----------------------------+
| @@global.gtid_binlog_pos   |
|----------------------------|
| 0-1-1925                   |
+----------------------------+
1 row in set

• 在replica查看使用

SELECT @@GLOBAL.gtid_slave_pos

配置主从同步my.cnf配置文件

配置primary my.cnf

编辑my.cnf，增加如下内容：

# 开启binlog
log-bin

# 配置MasterID，数字唯一，不可重复
server-id = 1

# 更改binlog记录方式为ROW
binlog_format=ROW

# 清理超过30天的binlog日志
expire_logs_days = 30

# 统一主从复制LOG名称,包含如下：log-bin, log-bin-index, relay-log, relay-log-index, general-log-file,
# log-slow-query-log-file, log-error-file, and pid-file.
log-basename = master1

# 开启binlog的数据库，多个用,分割
binlog-do-db = test

#########################
# 以下为GTID主从复制选项#
#########################

# 开启半同步
plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl-semi-sync-master-enabled = 1
rpl-semi-sync-slave-enabled = 1

# 配置Master,ServerID,不能和Slave重复
server-id = 1

# 开启Master记录Slave binlog日志，前提Slave需要开启binlog
log-slave-updates=1

# 开启GTID严格模式
gtid_strict_mode=1

# 开启Slave并行复制
slave_parallel_threads=4

# 开启Slave从Master读取binlog时进行完整校验
master-verify-checksum=1

配置Slave my.cnf

如果Slave只需要读，在后面增加read only=1即可，如果有多个从机，需要创建多个文件，更改server-id 和log-basename即可

# 开启binlog
log-bin

# 配置SlaveID
server-id = 2

# binlog名称
log-basename = Slave2

# 开启副本记录binlog日志
log-slave-updates = ON

# binlog记录方式为行
binlog_format=ROW

# 30天内清理binlog日志
expire_logs_days = 30

# 开启半同步
plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl-semi-sync-master-enabled = 1
rpl-semi-sync-slave-enabled = 1

# 开启Slave并行复制
slave_parallel_threads=4

# 开启Slave从Master读取binlog时进行完整校验
master-verify-checksum=1

# 只允许读，不允许写
# read only=1

创建同步账户、授权

在Master执行

# 创建同步账户
CREATE USER 'replication_user'@'%' IDENTIFIED BY 'test123456';
#授权
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';

制作Master备份文件

导出Master库可以使用mysqldump逻辑备份，缺点是恢复到从库时间过长，并且恢复从库恢复期间的时候是要锁定主库的，导致主库无法写入，只能读。
假如是生产环境推荐晚上业务量少的时候进行。或者是使用MariaDB-backup进行物理备份和恢复，这个速度非常快，几分钟之内就会恢复完成；
强烈推荐使用此方式

进行物理备份及恢复

首先需要安装MariaDB-backup，添加生产数据库对应的YUM源，后面的mariadb-10.3为对应的版本，可以根据实际情况选择。目前支持：10.2 10.3 10.4 10.5 10.6

在Master执行

curl -LsS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version="mariadb-10.3"

YUM安装mariabackup；

yum install MariaDB-backup

获取bin-log位置点
Slave同步时需要有binlog点和pos_id，需要提前记住

MariaDB [(none)]> SHOW MASTER STATUS;
+--------------------+-----------+--------------+------------------+
| File               | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+-----------+--------------+------------------+
| master1-bin.000002 | 561866201 | test      |                  |
+--------------------+-----------+--------------+------------------+

查询GTID

MariaDB [(none)]> SELECT BINLOG_GTID_POS('master1-bin.000002', 561866201);
+----------------------------------------------------+
| BINLOG_GTID_POS('master1-bin.000002', 561884824)   |
|----------------------------------------------------|
| 0-1-1910                                           |
+----------------------------------------------------+
1 row in set

开始创建物理备份

mariabackup --backup \ # 创建指定动作为备份
--slave-info --safe-slave-backup \ # 创建从机备份
--target-dir=/backup/ \ # 指定备份目录
--user=root --password=test123456 # 指定用户名和密码

创建SLave恢复文件

mariabackup --prepare --target-dir=/backup/

通过scp复制恢复文件至2台Slave

scp -r backup/ root@192.168.1.171:/home/user
scp -r backup/ root@192.168.1.172:/home/user

恢复备份至Slave
恢复时需要停掉2台Slave mariadb服务，并且清空mariadb数据目录
在Slave执行

# 停止服务
systemctl stop mariadb

# 清空目录
 rm -rf /home/mysql/*

锁住Master库避免有事务发生，更改GTID
在Master执行

FLUSH TABLES WITH READ LOCK

开始恢复

mariabackup --copy-back --target-dir=/home/user/

授权

chown -R mysql:mysql /home/mysql/

启动服务

systemctl start mariadb

待2台Slave恢复完成后，解锁Master
在Master执行

UNLOCK TABLES;

进行逻辑恢复备份及恢复

在Master执行备份

mysqldump --master-data=2 --single-transaction --routines --all-databases -uroot -p -v >> all.sql

锁库
执行如下语句锁库
在Master执行

FLUSH TABLES WITH READ LOCK;

恢复Slave备份
在2个Slave恢复备份

mysql -u root -p -v -f < all.sql

恢复完成解锁
在Master执行

UNLOCK TABLES;

启动Slave

开始配置同步信息

CHANGE MASTER TO
  MASTER_HOST='192.168.1.154',
  MASTER_USER='replication_user',
  MASTER_PASSWORD='password',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='master1-bin.000002',
  MASTER_LOG_POS=561866201,
  MASTER_USE_GTID=slave_pos,
  MASTER_CONNECT_RETRY=10;

启动从机
在2台Slave执行

START SLAVE;

查看从机工作状态

SHOW SLAVE STATUS \G

查看从机工作状态，出现如下两个选项说明启动成功:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

***************************[ 1. row ]***************************
Slave_IO_State                | Waiting for master to send event
Master_Host                   | 192.168.1.170
Master_User                   | replication_user
Master_Port                   | 3306
Connect_Retry                 | 10
Master_Log_File               | master1-bin.000003
Read_Master_Log_Pos           | 812
Relay_Log_File                | Slave1-relay-bin.000002
Relay_Log_Pos                 | 645
Relay_Master_Log_File         | master1-bin.000003
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_v1 event on table vipshop.family_info; Duplicate entry '1' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log master1-bin.000003, end_log_pos 781
Skip_Counter                  | 0
Exec_Master_Log_Pos           | 344
Relay_Log_Space               | 1423
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         | None
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_v1 event on table vipshop.family_info; Duplicate entry '1' for key 'PRIMARY', 
Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; 
the event's master log master1-bin.000003, end_log_pos 781
Replicate_Ignore_Server_Ids   | 
Master_Server_Id              | 1
Master_SSL_Crl                | 
Master_SSL_Crlpath            | 
Using_Gtid                    | Slave_Pos
Gtid_IO_Pos                   | 0-1-1914
Replicate_Do_Domain_Ids       | 
Replicate_Ignore_Domain_Ids   | 
Parallel_Mode                 | conservative
SQL_Delay                     | 0
SQL_Remaining_Delay           | None
Slave_SQL_Running_State       | 

出现报错：
could not execute Write_rows_v1 event on table vipshop.family_info; Duplicate entry '1' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY;

修改SQL_SLAVE_SKIP_COUNTER值
SQL_SLAVE_SKIP_COUNTER设置从主节点跳过接下来的N个事件。这对于从库导致的复制停止中恢复非常有用

停止从机

SLAVE STOP

修改值为1

SET GLOBAL sql_slave_skip_counter = 1;

启动从机

SLAVE START

查询

SHOW SLAVE STATUS \G

在出现如下工作正常

Slave_IO_Running              | Yes
Slave_SQL_Running             | Yes

测试

创建一个表插入几行数据

create table family_info (
  family_ID int(10) UNSIGNED not null auto_increment comment '主键，不能为空',
  family_name varchar(20) not null comment '姓名，不允许为空',
  sex char(1) not null comment '性别，不允许为空',
  birthday date comment '出生日期',
  occupation varchar(15) comment '职业',
  adress varchar(100) comment '地址',
  Tel varchar(20) comment '联系电话',
  create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP comment '创建时间',
  update_time timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE     CURRENT_TIMESTAMP COMMENT '更新时间',
  primary key (family_ID)
 ) comment = '家庭信息表' ENGINE = InnoDB DEFAULT CHARSET = utf8 AUTO_INCREMENT = 1;

插入数据

INSERT INTO family_info (family_name,sex,birthday,occupation,adress,Tel)
                          VALUES
                          ('李四','女','1983-03-06','技术员','山东省菏泽市牡丹区东城街道','5463210');
                                                
INSERT INTO family_info (family_name,sex,birthday,occupation,adress,Tel)
                          VALUES
                          ('张三','男','1984-03-06','文员','山东省菏泽市牡丹区南城街道','5461111');

INSERT INTO family_info (family_name,sex,birthday,occupation,adress,Tel)
                          VALUES
                          ('王五','男','2001-11-00','主管','山东省菏泽市牡丹区西城街道','6630111');

在Slave1和2查询，在Master插入的数据完全查询正常；

SELECT * FROM family_info \G
***************************[ 1. row ]***************************
family_ID   | 1
family_name | 李四
sex         | 女
birthday    | 1983-03-06
occupation  | 技术员
adress      | 山东省菏泽市牡丹区东城街道
Tel         | 5463210
create_time | 2021-11-10 17:20:48
update_time | 2021-11-10 17:20:48
***************************[ 2. row ]***************************
family_ID   | 2
family_name | 张三
sex         | 男
birthday    | 1984-03-06
occupation  | 文员
adress      | 山东省菏泽市牡丹区南城街道
Tel         | 5461111
create_time | 2021-11-10 17:28:27
update_time | 2021-11-10 17:28:27
***************************[ 3. row ]***************************
family_ID   | 3
family_name | 王五
sex         | 男
birthday    | 2001-11-00
occupation  | 主管
adress      | 山东省菏泽市牡丹区西城街道
Tel         | 6630111
create_time | 2021-11-10 17:30:32
update_time | 2021-11-10 17:30:32

同步完成