At Percona Managed Services, we manage Percona Server for MySQL, Community MySQL, and MariaDB. There are slight differences when configuring and managing MariaDB GTID replication. In this blog, we’ll show you how to convert MariaDB binary log file and position-based replication to GTID replication.
In my lab, we have two test nodes; both servers will have Debian 11 and MariaDB10.5.19 with mariabackup installed.
The PRIMARY server is deb11m8m (IP: 192.168.56.190), and the REPLICA server name is deb11m8s (IP: 192.168.56.191).
1. Install MariaDB on Debian 11 on PRIMARY and REPLICA
|
1 |
wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup<br>chmod +x ./mariadb_repo_setup<br>./mariadb_repo_setup --mariadb-server-version="mariadb-10.5.19"<br> apt-get install mariadb-server mariadb-backup<br>systemctl daemon-reload<br>root@deb11m8:~#wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup<br> https://downloads.mariadb.com/MariaDB/mariadb_repo_setup<br>downloads.mariadb.com (downloads.mariadb.com)|104.17.191.14|:443... connected.<br>HTTP request sent, awaiting response... 200 OK<br>Length: 36188 (35K) [application/octet-stream]<br>Saving to: 'mariadb_repo_setup'<br>2023-08-30 14:42:51 (26.0 MB/s) - 'mariadb_repo_setup' saved [36188/36188]<br>root@deb11m8m:~# chmod +x ./mariadb_repo_setup<br>root@deb11m8m:~# ./mariadb_repo_setup --mariadb-server-version="mariadb-10.5.19"<br># [info] Checking for script prerequisites.<br># [warning] Found existing file at /etc/apt/sources.list.d/mariadb.list. Moving to /etc/apt/sources.list.d/mariadb.list.old_1<br># [info] MariaDB Server version 10.5.19 is valid<br># [info] Repository file successfully written to /etc/apt/sources.list.d/mariadb.list<br># [info] Adding trusted package signing keys...<br># [info] Running apt-get update…<br># [info] Done adding trusted package signing keys<br>root@deb11m8m:~#<br>root@deb11m8m:~# apt-get install mariadb-server mariadb-backup<br>Reading package lists... Done<br>Building dependency tree... Done<br>Reading state information... Done<br>The following packages were automatically installed and are no longer required:<br> libconfig-inifiles-perl libmecab2 libopengl0<br>Use 'apt autoremove' to remove them.<br>The following additional packages will be installed:<br> libdbd-mariadb-perl libmariadb3 mariadb-client-10.5 mariadb-client-core-10.5 mariadb-common mariadb-server-10.5 mariadb-server-core-10.5<br>Suggested packages:<br> mailx mariadb-test netcat-openbsd<br>TSelecting previously unselected package mariadb-client-core-10.5.<br>Preparing to unpack .../mariadb-client-core-10.5_1%3a10.5.19+maria~deb11_amd64.deb ...<br>Unpacking mariadb-client-core-10.5 (1:10.5.19+maria~deb11) ...<br>……<br>Setting up mariadb-server (1:10.5.19+maria~deb11) ...<br>Processing triggers for man-db (2.9.4-2) ...<br>Processing triggers for libc-bin (2.31-13+deb11u6) ...<br>root@deb11m8m:~# |
1.1 Reset root user password and create a replication user
|
1 |
MariaDB [(none)]>Alter user 'root'@'localhost' identified by '#######';<br>MariaDB [(none)]>GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION ;<br>MariaDB [(none)]flush privileges;<br>MariaDB [(none)]CREATE USER 'repl'@'%' IDENTIFIED BY '#######';<br>MariaDB [(none)]GRANT RELOAD, SUPER, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%';<br> MariaDB [(none)]flush privileges; |
1.2 Configure my.cnf on PRIMARY and REPLICA
PRIMARY
|
1 |
[mysqld]<br>pid-file = /run/mysqld/mysqld.pid<br>basedir = /usr<br>server_id=1<br>bind-address=192.168.56.190<br>log-bin = /var/lib/mysql/bin_log<br>expire_logs_days = 10<br>character-set-server = utf8mb4<br>collation-server = utf8mb4_general_ci |
|
1 |
REPLICA<br>[mysqld]<br>pid-file = /run/mysqld/mysqld.pid<br>basedir = /usr<br><b>server_id=2</b><br><b>bind-address=192.168.56.191</b><br>log-bin = /var/lib/mysql/bin_log<br>expire_logs_days = 10<br>character-set-server = utf8mb4<br>collation-server = utf8mb4_general_ci |
2. Let’s set up binary log and position-based replication between PRIMARY and REPLICA with the stream copy method.
2.1 on REPLICA
|
1 |
root@deb11m8s: nc -l -p 2222 | mbstream -x -C /var/lib/mysql |
2.2 on PRIMARY
|
1 |
root@deb11m8m:/var/lib/mysql# mariabackup --stream=mbstream --parallel=4 --backup | nc -w 2 192.168.56.191 2222<br>[00] 2023-08-30 18:02:08 Connecting to server host: localhost, user: root, password: set, port: not set, socket: /run/mysqld/mysqld.sock<br>[00] 2023-08-30 18:02:08 Using server version 10.5.19-MariaDB-1:10.5.19+maria~deb11-log<br> mariabackup based on MariaDB server 10.5.19-MariaDB debian-linux-gnu (x86_64)<br>[00] 2023-08-30 18:02:08 uses posix_fadvise().<br>[00] 2023-08-30 18:02:08 cd to /var/lib/mysql/<br> [00] 2023-08-30 18:02:08 open files limit requested 0, set to 1024<br> [00] 2023-08-30 18:02:08 mariabackup: using the following InnoDB configuration:<br> [00] 2023-08-30 18:02:08 innodb_data_home_dir =<br> [00] 2023-08-30 18:02:08 innodb_data_file_path = ibdata1:12M:autoextend<br> [00] 2023-08-30 18:02:08 innodb_log_group_home_dir = ./<br> [00] 2023-08-30 18:02:08 InnoDB: Using Linux native AIO<br> 2023-08-30 18:02:08 0 [Note] InnoDB: Number of pools: 1<br> [00] 2023-08-30 18:02:08 mariabackup: Generating a list of tablespaces<br> [00] 2023-08-30 18:02:08 >> log scanned up to (10912597)<br> [00] 2023-08-30 18:02:08 mariabackup: Starting 4 threads for parallel data files transfer<br>[03] 2023-08-30 18:02:08 Streaming ./mysql/innodb_index_stats.ibd<br>[04] 2023-08-30 18:02:08 Streaming ibdata1<br>……..<br>'/var/lib/mysql/xtrabackup_backupfiles/'<br> [00] 2023-08-30 18:02:10 MySQL binlog position: filename 'bin_log.000002', position '326', GTID of the last change ''<br> [00] 2023-08-30 18:02:10 Streaming backup-my.cnf<br>[00] 2023-08-30 18:02:10 Streaming xtrabackup_info<br>[00] 2023-08-30 18:02:10 Redo log (from LSN 10912585 to 10912597) was copied.<br> [00] 2023-08-30 18:02:10 completed OK! |
2.3 On REPLICA, get binlog information
|
1 |
<root@deb11m8s:/var/lib/mysql# cat xtrabackup_binlog_info<br>bin_log.000002 326 |
2.4 On REPLICA, prepare the backup
|
1 |
root@deb11m8s:/var/lib/mysql# mariabackup --prepare --use-memory=1G --target-dir=/var/lib/mysql<br>mariabackup based on MariaDB server 10.5.19-MariaDB debian-linux-gnu (x86_64)<br>[00] 2023-08-30 18:08:32 cd to /var/lib/mysql/<br>[00] 2023-08-30 18:08:32 open files limit requested 0, set to 1024<br>.<br>2023-08-30 18:08:32 0 [Note] InnoDB: Initializing buffer pool, total size = 1073741824, chunk size = 1073741824<br>2023-08-30 18:08:32 0 [Note] InnoDB: Completed initialization of buffer pool<br>2023-08-30 18:08:32 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=10912585,10912585<br>[00] 2023-08-30 18:08:32 Last binlog file , position 0<br>[00] 2023-08-30 18:08:32 completed OK!<br>root@deb11m8s:/var/lib/mysql# |
2.5 Set up the replication
|
1 |
root@deb11m8s:/var/lib/mysql# chown -R mysql:mysql /var/lib/mysql<br>root@deb11m8s:/var/lib/mysql# systemctl start mysql<br>root@deb11m8s:/var/lib/mysql# mysql<br>Welcome to the MariaDB monitor. Commands end with ; or g.<br>Your MariaDB connection id is 6<br>Server version: 10.5.19-MariaDB-0+deb11u2-log Debian 11<br>MariaDB [(none)]> stop slave; reset slave; reset slave all; reset master;<br>Query OK, 0 rows affected, 1 warning (0.000 sec)<br>Query OK, 0 rows affected (0.001 sec)<br>Query OK, 0 rows affected (0.000 sec)<br>Query OK, 0 rows affected (0.009 sec)<br>ariaDB [(none)]> CHANGE MASTER TO<br> MASTER_HOST='192.168.56.190',<br> MASTER_USER='repl',<br> MASTER_PASSWORD='#######',<br> MASTER_PORT=3306,<br> MASTER_LOG_FILE='bin_log.000002',<br> MASTER_LOG_POS=326,<br> MASTER_CONNECT_RETRY=10;<br>Query OK, 0 rows affected (0.022 sec)<br>MariaDB [(none)]> start slave;<br>Query OK, 0 rows affected (0.003 sec)<br>MariaDB [(none)]> show slave statusG<br>*************************** 1. row ***************************<br> Slave_IO_State: Waiting for master to send event<br> Master_Host: 192.168.56.190<br> Master_User: repl<br> Master_Port: 3306<br> Connect_Retry: 10<br> Master_Log_File: bin_log.000002<br> Read_Master_Log_Pos: 326<br> Relay_Log_File: mysqld-relay-bin.000002<br> Relay_Log_Pos: 553<br> Relay_Master_Log_File: bin_log.000002<br> Slave_IO_Running: Yes<br> Slave_SQL_Running: Yes<br> …<br> Exec_Master_Log_Pos: 326<br> Relay_Log_Space: 863<br> ….<br> Master_SSL_Crlpath:<br> Using_Gtid: No<br> Gtid_IO_Pos:<br> Replicate_Do_Domain_Ids:<br> Replicate_Ignore_Domain_Ids:<br> Parallel_Mode: optimistic<br> SQL_Delay: 0<br> SQL_Remaining_Delay: NULL<br> Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates<br> Slave_DDL_Groups: 0<br>Slave_Non_Transactional_Groups: 0<br> Slave_Transactional_Groups: 0<br>1 row in set (0.000 sec) |
We have now set up binary log and position-based replication between the PRIMARY and REPLICA servers. Our next step is to convert this replication to GTID-based replication.
MariaDB and MySQL have different GTID implementations.
MariaDB GTID consists of three numbers separated with dashes ‘-‘. For example:
0-1-10: The first number, 0, is the domain ID, the second number is the server ID, and the third number is the sequence number.
MySQL GTID has two parts, source_id and transaction_id, separated by a colon character (:) .
e.g., 3E11FA47-71CA-11E1-9E33-C80AA9429562:23
The source_id is the source server’s server_uuid, and the transaction_id is the transaction sequence number committed on the source.
Let’s continue.
On PRIMARY, we enable GTID by running the below command.
|
1 |
MariaDB [(none)] SET GLOBAL gtid_domain_id = 1;<br>MariaDB [(none)] set global gtid_strict_mode=1 ;<br>And persist in my.cnf <br>Verify GTID is enabled with below query <br>MariaDB [(none)]> select @@server_id,@@bind_address,@@log_bin,@@gtid_strict_mode,@@gtid_domain_id;<br>+-------------+----------------+-----------+--------------------+------------------+<br>| @@server_id | @@bind_address | @@log_bin | @@gtid_strict_mode | @@gtid_domain_id |<br>+-------------+----------------+-----------+--------------------+------------------+<br>| 1 | 192.168.56.190 | 1 | <b>1 | 1 |<br>+-------------+----------------+-----------+--------------------+------------------+<br>1 row in set (0.000 sec)</b> |
ON REPLICA
|
1 |
MariaDB [(none)]>SET GLOBAL gtid_domain_id = 2;<br>MariaDB [(none)]set global gtid_strict_mode=1 ;<br>And persist in my.cnf <br>Verify GTID is enabled with below query <br>MariaDB [(none)]select @@server_id,@@bind_address,@@log_bin,@@gtid_strict_mode,@@gtid_domain_id;<br>+-------------+----------------+-----------+--------------------+------------------+<br>| @@server_id | @@bind_address | @@log_bin | @@gtid_strict_mode | @@gtid_domain_id |<br>+-------------+----------------+-----------+--------------------+------------------+<br>| 2 | 192.168.56.191 | 1 | 1 |2 |;<br>+-------------+----------------+-----------+--------------------+------------------+<br>1 row in set (0.000 sec) |
On REPLICA, we need to stop replication and extract the values of Relay_Master_Log_File and Exec_Master_Log_Pos from the output.
|
1 |
MariaDB [test_db]STOP SLAVE;<br>Query OK, 0 rows affected (0.007 sec)<br>MariaDB [test_db] show slave statusG<br>*************************** 1. row ***************************<br> Slave_IO_State:<br> Master_Host: 192.168.56.190<br> Master_User: repl<br> Master_Port: 3306<br> Connect_Retry: 10<br> Master_Log_File: bin_log.000002<br> Read_Master_Log_Pos: 1206<br> Relay_Log_File: mysqld-relay-bin.000007<br> Relay_Log_Pos: 553<br> Relay_Master_Log_File: bin_log.000002<br> Slave_IO_Running: No<br> Slave_SQL_Running: No<br> Replicate_Do_DB:<br> Replicate_Ignore_DB:<br> Replicate_Do_Table:<br> Replicate_Ignore_Table:<br> Replicate_Wild_Do_Table:<br> Replicate_Wild_Ignore_Table:<br> Last_Errno: 0<br> Last_Error:<br> Skip_Counter: 0<br> Exec_Master_Log_Pos: 1206<br> Relay_Log_Space: 863<br> ……..<br>Slave_Non_Transactional_Groups: 0<br> Slave_Transactional_Groups: 0<br>1 row in set (0.000 sec) |
In our case, the Relay_Master_Log_File value is bin_log.000002, and the Exec_Master_Log_Pos is 1206.
On PRIMARY, we could use the below query to get the GTID position that corresponds to these binary log coordinates on PRIMARY.
|
1 |
MariaDB[test_db] SELECT BINLOG_GTID_POS('bin_log.000002', 1206),@@hostname;<br> +-----------------------------------------+------------+<br>| BINLOG_GTID_POS('bin_log.000002', 1206) | @@hostname |<br>+-----------------------------------------+------------+<br>| 1-1-1,0-1-4 |<br>+-----------------------------------------+------------+<br>1 row in set (0.000 sec) |
We can now modify the replica configuration on the REPLICA server deb11m8s by executing the following statements:
|
1 |
MariaDB [test_db]SET GLOBAL gtid_slave_pos = '1-1-1,0-1-4';<br>MariaDB [test_db] CHANGE MASTER TO master_use_gtid=slave_pos;<br>MariaDB [test_db]START SLAVE;<br>MariaDB [test_db] show slave statusG<br> *************************** 1. row ***************************<br> Slave_IO_State: Waiting for master to send event<br> Master_Host: 192.168.56.190<br> Master_User: repl<br> Master_Port: 3306<br> Connect_Retry: 10<br> Master_Log_File: bin_log.000002<br> Read_Master_Log_Pos: 1391<br> Relay_Log_File: mysqld-relay-bin.000002<br> Relay_Log_Pos: 910<br> Relay_Master_Log_File: bin_log.000002<br> Slave_IO_Running: Yes<br> Slave_SQL_Running: Yes<br> Replicate_Do_DB:<br> Replicate_Ignore_DB:<br> Replicate_Do_Table:<br> Replicate_Ignore_Table:<br> Replicate_Wild_Do_Table:<br> Replicate_Wild_Ignore_Table:<br> Last_Errno: 0<br> Last_Error:<br> Skip_Counter: 0<br> Exec_Master_Log_Pos: 1391<br> Relay_Log_Space: 1220<br> …….<br> Master_SSL_Key:<br> Seconds_Behind_Master: 0<br> Master_SSL_Verify_Server_Cert: No<br> Last_IO_Errno: 0<br> Last_IO_Error:<br> Last_SQL_Errno: 0<br> Last_SQL_Error:<br> Replicate_Ignore_Server_Ids:<br> Master_Server_Id: 1<br> Master_SSL_Crl:<br> Master_SSL_Crlpath:<br> Using_Gtid: Slave_Pos<br> Gtid_IO_Pos: 1-1-2,0-1-4<br> Replicate_Do_Domain_Ids:<br> Replicate_Ignore_Domain_Ids:<br> Parallel_Mode: optimistic<br> SQL_Delay: 0<br> SQL_Remaining_Delay: NULL<br> Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates<br> Slave_DDL_Groups: 0<br> Slave_Non_Transactional_Groups: 0<br> Slave_Transactional_Groups: 1<br> 1 row in set (0.000 sec) |
The SHOW SLAVE STATUS output Using_Gtid: Slave_Pos and Gtid_IO_Pos: 1-1-2,0-1-4 indicates that the slave uses GTIDs to track replication. We could use the above command to set up GTID replication on other replicas as well.
When running CHANGE MASTER TO master_use_gtid=slave_pos; and setting the MASTER_USE_GTID replication parameter, you have the option of enabling Global Transaction IDs to use either the current_pos or slave_pos values.
Using the value current_pos causes the replica to set its position based on the gtid_current_pos system variable, which is a union of gtid_binlog_pos and gtid_slave_pos. Using the value slave_pos causes the replica to instead set its position based on the gtid_slave_pos system variable. You may run into issues when you use the value current_pos if you write any local transactions on the replica. For details, please refer to https://mariadb.com/kb/en/gtid/.
On Community MySQL or Percona Server for MySQL, the parameter to enable GTID replication is
gtid-mode=ON enforce-gtid-consistency.
The command is:
|
1 |
change master to<br>master_host = '192.168.1.120',<br>master_port=3306,<br>master_user = 'repl',<br>master_password = 'password',<br>master_auto_position=1; |
We can see that the procedure to enable GTIDs on MariaDB replication is simple, but the commands and parameters are a bit different.
As mentioned, Percona offers Support and Managed Services for Community versions of MariaDB. At the same time, we encourage MariaDB users to explore Percona Server for MySQL as an alternative. Should you make the decision to migrate to Percona Software for MySQL, our experts will assist throughout the migration process and support you after the migration is complete.
Contact us to discuss migration options
Hope this is helpful for your daily MySQL and MariaDB management.
Resources
RELATED POSTS
