运维首页 > 数据运维 > 将 MariaDB 二进制日志文件和基于位置的复制转换为 GTID 复制

将 MariaDB 二进制日志文件和基于位置的复制转换为 GTID 复制

2023年 9月 23日 36.6k 0

在Percona Managed Services，我们管理Percona Server for MySQL、社区 MySQL 和 MariaDB。配置和管理 MariaDB GTID 复制时存在细微差别。在本博客中，我们将向您展示如何将 MariaDB 二进制日志文件和基于位置的复制转换为 GTID 复制。

在我的实验室中，我们有两个测试节点；两台服务器都将安装 Debian 11 和 MariaDB10.5.19，并安装 mariabackup。

主服务器是 deb11m8m（IP：192.168.56.190），副本服务器名称是 deb11m8s（IP：192.168.56.191）。

1. 在 Debian 11 的 PRIMARY 和 REPLICA 上安装 MariaDB

wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
chmod +x ./mariadb_repo_setup
./mariadb_repo_setup --mariadb-server-version="mariadb-10.5.19"
apt-get install mariadb-server mariadb-backup
systemctl daemon-reload
root@deb11m8:~#wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
downloads.mariadb.com (downloads.mariadb.com)|104.17.191.14|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 36188 (35K) [application/octet-stream]
Saving to: ‘mariadb_repo_setup’
2023-08-30 14:42:51 (26.0 MB/s) - ‘mariadb_repo_setup’ saved [36188/36188]
root@deb11m8m:~# chmod +x ./mariadb_repo_setup
root@deb11m8m:~# ./mariadb_repo_setup --mariadb-server-version="mariadb-10.5.19"
# [info] Checking for script prerequisites.
# [warning] Found existing file at /etc/apt/sources.list.d/mariadb.list. Moving to /etc/apt/sources.list.d/mariadb.list.old_1
# [info] MariaDB Server version 10.5.19 is valid
# [info] Repository file successfully written to /etc/apt/sources.list.d/mariadb.list
# [info] Adding trusted package signing keys...
# [info] Running apt-get update…
# [info] Done adding trusted package signing keys
root@deb11m8m:~#
root@deb11m8m:~# apt-get install mariadb-server mariadb-backup
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following packages were automatically installed and are no longer required:
libconfig-inifiles-perl libmecab2 libopengl0
Use 'apt autoremove' to remove them.
The following additional packages will be installed:
libdbd-mariadb-perl libmariadb3 mariadb-client-10.5 mariadb-client-core-10.5 mariadb-common mariadb-server-10.5 mariadb-server-core-10.5
Suggested packages:
mailx mariadb-test netcat-openbsd
TSelecting previously unselected package mariadb-client-core-10.5.
Preparing to unpack .../mariadb-client-core-10.5_1%3a10.5.19+maria~deb11_amd64.deb ...
Unpacking mariadb-client-core-10.5 (1:10.5.19+maria~deb11) ...
……
Setting up mariadb-server (1:10.5.19+maria~deb11) ...
Processing triggers for man-db (2.9.4-2) ...
Processing triggers for libc-bin (2.31-13+deb11u6) ...
root@deb11m8m:~#

1.1 重置root用户密码并创建复制用户

MariaDB [(none)]>Alter user 'root'@'localhost' identified by ‘#######’;
MariaDB [(none)]>GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION ;
MariaDB [(none)]flush privileges;
MariaDB [(none)]CREATE USER 'repl'@'%' IDENTIFIED BY ‘#######’;
MariaDB [(none)]GRANT RELOAD, SUPER, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%';
MariaDB [(none)]flush privileges;

1.2 在 PRIMARY 和 REPLICA 上配置 my.cnf

基本的

[mysqld]
pid-file = /run/mysqld/mysqld.pid
basedir = /usr
server_id=1
bind-address=192.168.56.190
log-bin = /var/lib/mysql/bin_log
expire_logs_days = 10
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
REPLICA
[mysqld]
pid-file = /run/mysqld/mysqld.pid
basedir = /usr
<b>server_id=2</b>
<b>bind-address=192.168.56.191</b>
log-bin = /var/lib/mysql/bin_log
expire_logs_days = 10
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci

2. 让我们使用流复制方法在 PRIMARY 和 REPLICA 之间设置二进制日志和基于位置的复制。

2.1 副本

root@deb11m8s: nc -l -p 2222 | mbstream  -x -C /var/lib/mysql

2.2 小学

root@deb11m8m:/var/lib/mysql# mariabackup --stream=mbstream --parallel=4  --backup |  nc -w 2 192.168.56.191 2222
[00] 2023-08-30 18:02:08 Connecting to server host: localhost, user: root, password: set, port: not set, socket: /run/mysqld/mysqld.sock
[00] 2023-08-30 18:02:08 Using server version 10.5.19-MariaDB-1:10.5.19+maria~deb11-log
mariabackup based on MariaDB server 10.5.19-MariaDB debian-linux-gnu (x86_64)
[00] 2023-08-30 18:02:08 uses posix_fadvise().
[00] 2023-08-30 18:02:08 cd to /var/lib/mysql/
[00] 2023-08-30 18:02:08 open files limit requested 0, set to 1024
[00] 2023-08-30 18:02:08 mariabackup: using the following InnoDB configuration:
[00] 2023-08-30 18:02:08 innodb_data_home_dir =
[00] 2023-08-30 18:02:08 innodb_data_file_path = ibdata1:12M:autoextend
[00] 2023-08-30 18:02:08 innodb_log_group_home_dir = ./
[00] 2023-08-30 18:02:08 InnoDB: Using Linux native AIO
2023-08-30 18:02:08 0 [Note] InnoDB: Number of pools: 1
[00] 2023-08-30 18:02:08 mariabackup: Generating a list of tablespaces
[00] 2023-08-30 18:02:08 >> log scanned up to (10912597)
[00] 2023-08-30 18:02:08 mariabackup: Starting 4 threads for parallel data files transfer
[03] 2023-08-30 18:02:08 Streaming ./mysql/innodb_index_stats.ibd
[04] 2023-08-30 18:02:08 Streaming ibdata1
……..
'/var/lib/mysql/xtrabackup_backupfiles/'
[00] 2023-08-30 18:02:10 MySQL binlog position: filename 'bin_log.000002', position '326', GTID of the last change ''
[00] 2023-08-30 18:02:10 Streaming backup-my.cnf
[00] 2023-08-30 18:02:10 Streaming xtrabackup_info
[00] 2023-08-30 18:02:10 Redo log (from LSN 10912585 to 10912597) was copied.
[00] 2023-08-30 18:02:10 completed OK!

2.3 在REPLICA上，获取binlog信息

<root@deb11m8s:/var/lib/mysql# cat xtrabackup_binlog_info
bin_log.000002 326

2.4 在REPLICA上，准备备份

root@deb11m8s:/var/lib/mysql# mariabackup --prepare   --use-memory=1G  --target-dir=/var/lib/mysql
mariabackup based on MariaDB server 10.5.19-MariaDB debian-linux-gnu (x86_64)
[00] 2023-08-30 18:08:32 cd to /var/lib/mysql/
[00] 2023-08-30 18:08:32 open files limit requested 0, set to 1024
.
2023-08-30 18:08:32 0 [Note] InnoDB: Initializing buffer pool, total size = 1073741824, chunk size = 1073741824
2023-08-30 18:08:32 0 [Note] InnoDB: Completed initialization of buffer pool
2023-08-30 18:08:32 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=10912585,10912585
[00] 2023-08-30 18:08:32 Last binlog file , position 0
[00] 2023-08-30 18:08:32 completed OK!
root@deb11m8s:/var/lib/mysql#

2.5 设置复制

root@deb11m8s:/var/lib/mysql# chown -R mysql:mysql /var/lib/mysql
root@deb11m8s:/var/lib/mysql# systemctl start mysql
root@deb11m8s:/var/lib/mysql# mysql
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 6
Server version: 10.5.19-MariaDB-0+deb11u2-log Debian 11
MariaDB [(none)]> stop slave; reset slave; reset slave all; reset master;
Query OK, 0 rows affected, 1 warning (0.000 sec)
Query OK, 0 rows affected (0.001 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.009 sec)
ariaDB [(none)]> CHANGE MASTER TO
MASTER_HOST='192.168.56.190',
MASTER_USER='repl',
MASTER_PASSWORD='#######',
MASTER_PORT=3306,
MASTER_LOG_FILE='bin_log.000002',
MASTER_LOG_POS=326,
MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected (0.022 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.003 sec)
MariaDB [(none)]> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.190
Master_User: repl
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: bin_log.000002
Read_Master_Log_Pos: 326
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 553
Relay_Master_Log_File: bin_log.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
…
Exec_Master_Log_Pos: 326
Relay_Log_Space: 863
….
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
1 row in set (0.000 sec)

我们现在已经在主服务器和副本服务器之间设置了二进制日志和基于位置的复制。我们的下一步是将此复制转换为基于 GTID 的复制。

MariaDB 和 MySQL 有不同的 GTID 实现。

MariaDB GTID 由三个用破折号“-”分隔的数字组成。例如：

0-1-10：第一个数字0是域ID，第二个数字是服务器ID，第三个数字是序列号。

MySQL GTID 有两部分，source_id 和 transaction_id，用冒号 (:) 分隔。

例如，3E11FA47-71CA-11E1-9E33-C80AA9429562:23

source_id是源服务器的server_uuid，transaction_id是在源上提交的事务序列号。

我们继续吧。

在 PRIMARY 上，我们通过运行以下命令来启用 GTID。

MariaDB [(none)] SET GLOBAL gtid_domain_id = 1;
MariaDB [(none)] set global gtid_strict_mode=1 ;
And persist in my.cnf
Verify GTID is enabled with below query
MariaDB [(none)]> select @@server_id,@@bind_address,@@log_bin,@@gtid_strict_mode,@@gtid_domain_id;
+-------------+----------------+-----------+--------------------+------------------+
| @@server_id | @@bind_address | @@log_bin | @@gtid_strict_mode | @@gtid_domain_id |
+-------------+----------------+-----------+--------------------+------------------+
| 1 | 192.168.56.190 | 1 | 1 | 1 |
+-------------+----------------+-----------+--------------------+------------------+
1 row in set (0.000 sec)

复制品上

MariaDB [(none)]>SET GLOBAL gtid_domain_id = 2;
MariaDB [(none)]set global gtid_strict_mode=1 ;
And persist in my.cnf
Verify GTID is enabled with below query
MariaDB [(none)]select @@server_id,@@bind_address,@@log_bin,@@gtid_strict_mode,@@gtid_domain_id;
+-------------+----------------+-----------+--------------------+------------------+
| @@server_id | @@bind_address | @@log_bin | @@gtid_strict_mode | @@gtid_domain_id |
+-------------+----------------+-----------+--------------------+------------------+
| 2 | 192.168.56.191 | 1 | 1 |2 |;
+-------------+----------------+-----------+--------------------+------------------+
1 row in set (0.000 sec)

在 REPLICA 上，我们需要停止复制并从输出中提取Relay_Master_Log_File和Exec_Master_Log_Pos的值。

MariaDB [test_db]STOP SLAVE;
Query OK, 0 rows affected (0.007 sec)
MariaDB [test_db] show slave statusG
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.56.190
Master_User: repl
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: bin_log.000002
Read_Master_Log_Pos: 1206
Relay_Log_File: mysqld-relay-bin.000007
Relay_Log_Pos: 553
Relay_Master_Log_File: bin_log.000002
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: 1206
Relay_Log_Space: 863
……..
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.000 sec)

在我们的例子中，Relay_Master_Log_File值为bin_log.000002， Exec_Master_Log_Pos为 1206。

在 PRIMARY 上，我们可以使用以下查询来获取与 PRIMARY 上的这些二进制日志坐标相对应的 GTID 位置。

MariaDB[test_db] SELECT BINLOG_GTID_POS('bin_log.000002', 1206),@@hostname;
+-----------------------------------------+------------+
| BINLOG_GTID_POS('bin_log.000002', 1206) | @@hostname |
+-----------------------------------------+------------+
| 1-1-1,0-1-4   |
+-----------------------------------------+------------+
1 row in set (0.000 sec)

我们现在可以通过执行以下语句来修改 REPLICA 服务器 deb11m8s 上的副本配置：

MariaDB [test_db]SET GLOBAL gtid_slave_pos = '1-1-1,0-1-4';
MariaDB [test_db] CHANGE MASTER TO master_use_gtid=slave_pos;
MariaDB [test_db]START SLAVE;
MariaDB [test_db] show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.190
Master_User: repl
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: bin_log.000002
Read_Master_Log_Pos: 1391
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 910
Relay_Master_Log_File: bin_log.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: 1391
Relay_Log_Space: 1220
…….
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
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: Slave_Pos
Gtid_IO_Pos: 1-1-2,0-1-4
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: 1
1 row in set (0.000 sec)

SHOW SLAVE STATUS 输出Using_Gtid: Slave_Pos 和Gtid_IO_Pos: 1-1-2,0-1-4指示从属设备使用GTID 来跟踪复制。我们也可以使用上述命令在其他副本上设置 GTID 复制。

运行时 CHANGE MASTER TO master_use_gtid=slave_pos; 并设置 MASTER_USE_GTID 复制参数，您可以选择启用全局事务 ID 以使用 current_pos或Slave_pos值。

使用值 current_pos 会导致副本根据 gtid_current_pos 系统变量设置其位置，该变量是 gtid_binlog_pos 和 gtid_slave_pos 的并集。使用值slave_pos 会导致副本根据gtid_slave_pos 系统变量设置其位置。如果您在副本上写入任何本地事务，则在使用值 current_pos 时可能会遇到问题。详情请参考https://mariadb.com/kb/en/gtid/。

在 Community MySQL 或 Percona Server for MySQL 上，启用 GTID 复制的参数是
gtid-mode=ONforce-gtid-consistency。

命令是：

change master to
master_host = '192.168.1.120',
master_port=3306,
master_user = 'repl',
master_password = 'password',
master_auto_position=1;

结论

我们可以看到，在 MariaDB 复制上启用 GTID 的过程很简单，但命令和参数有点不同。

共饮一杯
数据运维
0 0

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF，云和恩墨技术通讯（2024年7月刊）
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论

共饮一杯

这个人很懒，什么都没有留下～

文章8405 评论0