Tutorial News Comments FAQ Related Articles

How To Create Master To Master Replication Using MariaDB GTID Method In Ubuntu 24

{{postValue.id}}

To Create Master To Master Replication Using MariaDB GTID Method In Ubuntu 24

Introduction

How to create Master to Master Replication using MariaDB GTID Method in Ubuntu 24 A Global Transaction Identifier (GTID) is a unique identifier assigned to each transaction committed on the primary server. This identifier is distinctive not only to the originating server but also across all servers within a given replication environment. Each transaction corresponds to exactly one GTID.

Procedure

Step 1: Check the OS version

root@linuxhelp:/# lsb_release -a
No LSB modules are available.
Distributor ID:	Ubuntu
Description:	Ubuntu 24.04.2 LTS
Release:	24.04
Codename:	noble

Step 2: Install the MariaDB server

root@linuxhelp:/# apt install mariadb-server
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
Setting up mariadb-plugin-provider-snappy (1:10.11.11-0ubuntu0.24.04.2) ...
Processing triggers for man-db (2.12.0-4build2) ...
Processing triggers for libc-bin (2.39-0ubuntu8.4) ...
Processing triggers for mariadb-server (1:10.11.11-0ubuntu0.24.04.2) ...

Step 3: Check the status of MariaDB service

root@linuxhelp:/# systemctl status mariadb.service
mariadb.service - MariaDB 10.11.11 database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; preset: enabled)
Active: active (running) since Mon 2025-04-21 04:32:35 IST; 33s ago

Step 4: Add few lines in Configuration File

root@linuxhelp:/# vim /etc/mysql/mariadb.conf.d/50-server.cnf
bind-address            = 192.168.112.129
[mariadb]
log-bin
server_id=1
log-basename=master1
binlog-format=mixed

Step 5: Restart the MariaDB service

root@linuxhelp:/# systemctl restart mariadb.service

Step 6: Create the User and password

root@linuxhelp:/# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 32
Server version: 10.11.11-MariaDB-0ubuntu0.24.04.2-log Ubuntu 24.04
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)]>
MariaDB [(none)]> create user 'user1'@'%' identified by '1234';
Query OK, 0 rows affected (0.001 sec)

Step 7: Give permission to the user

MariaDB [(none)]> grant replication slave on *.* to 'user1'@'%';
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]>

Step 8: View the master Status

MariaDB [(none)]> show master status\G
File : master1-bin.000001
Position : 645
Binlog_Do_DB :
Binlog_Ignore_DB :
1 row in set (0.000 sec)

Step 9: Check the OS version of your system (Server 2)

root@linuxhelp:/# lsb_release -a
No LSB modules are available.
Distributor ID:	Ubuntu
Description:	Ubuntu 24.04.2 LTS
Release:	24.04
Codename:	noble

Step 10: Install the MariaDB server

root@linuxhelp:/# apt install mariadb-server
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
Setting up mariadb-plugin-provider-snappy (1:10.11.11-0ubuntu0.24.04.2) ...
Processing triggers for man-db (2.12.0-4build2) ...
Processing triggers for libc-bin (2.39-0ubuntu8.4) ...
Processing triggers for mariadb-server (1:10.11.11-0ubuntu0.24.04.2) ...

Step 11: Check the status of MariaDB service

root@linuxhelp:/# systemctl status mariadb.service
mariadb.service - MariaDB 10.11.11 database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; preset: enabled)
Active: active (running) since Mon 2025-04-21 04:32:35 IST; 33s ago

Step 12: Add a Few lines in Configuration File

root@linuxhelp:/# vim /etc/mysql/mariadb.conf.d/50-server.cnf
bind-address            = 192.168.112.128
[mariadb]
log-bin
server_id=2
log-basename=master2
binlog-format=mixed

Step 13: Restart the MariaDB service

root@linuxhelp:/# systemctl restart mariadb.service

Step 14: Login into MySQL and Stop the slave

MariaDB [(none)]> stop slave;
Query OK, 0 rows affected, 1 warning (0.000 sec)

Step 15: Change the Master to server 1

MariaDB [(none)]> change master to master_host='192.168.112.129', master_user='user2', master_password='1234', master_log_file='master1-bin.000001', master_log_pos=645;
Query OK, 0 rows affected, 1 warning (0.027 sec)

Step 16: Start the slave

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.003 sec)

Step 17: Check the slave status

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.112.129
Master_User: user1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master1-bin.000001
Read_Master_Log_Pos: 645
Relay_Log_File: master2-relay-bin.000002
Relay_Log_Pos: 557
Relay_Master_Log_File: master1-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Step 18: Create the User and password

root@linuxhelp:/# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 32
Server version: 10.11.11-MariaDB-0ubuntu0.24.04.2-log Ubuntu 24.04
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)]>
MariaDB [(none)]> create user 'user2'@'%' identified by '5678';
Query OK, 0 rows affected (0.001 sec)

Step 19: Give permission to the user

MariaDB [(none)]> grant replication slave on *.* to 'user2'@'%';
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]>

Step 20: View the master Status

MariaDB [(none)]> show master status\G
File : master2-bin.000001
Position : 645
Binlog_Do_DB :
Binlog_Ignore_DB :
1 row in set (0.000 sec)

Step 21: Login into MySQL and Stop the slave(Server 1)

MariaDB [(none)]> stop slave;
Query OK, 0 rows affected, 1 warning (0.000 sec)

Step 22: Change the Master to server 1

MariaDB [(none)]> change master to master_host='192.168.112.128', master_user='user2', master_password='5678', master_log_file='master2-bin.000001', master_log_pos=645;
Query OK, 0 rows affected, 1 warning (0.027 sec)

Step 23: Start the slave

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.003 sec)

Step 24: Check the slave status

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.112.128
Master_User: user2
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master2-bin.000001
Read_Master_Log_Pos: 645
Relay_Log_File: master1-relay-bin.000002
Relay_Log_Pos: 557
Relay_Master_Log_File: master2-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Step 25: Changing to GTID Method

root@linuxhelp:/# vim /etc/mysql/mariadb.conf.d/50-server.cnf
[mariadb]
gtid-domain-id=1

Step 26: Set the global variable gtid domain to 1

MariaDB [(none)]> set global gtid_domain_id = 1;
Query OK, 0 rows affected (0.001 sec)

Step 27: View the master status

MariaDB [(none)]> show master status\G
File : master1-bin.000002
Position : 344
Binlog_Do_DB :
Binlog_Ignore_DB :
1 row in set (0.000 sec)

Step 28: Run the following Command

MariaDB [(none)]> SELECT BINLOG_GTID_POS ('master1-bin.000002' , 344 );
BINLOG_GTID_POS ('master1-bin.000002' , 344 )  : 0-1-2 
1 row in set (0.000 sec)

Step 29: Stop the slave(Server2)

MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.003 sec)

Step 30: Set the gtid slave position

MariaDB [(none)]> SET GLOBAL gtid_slave_pos = '0-1-2';
Query OK, 0 rows affected (0.013 sec)

Step 31: Change master to gtid slave position

MariaDB [(none)]> CHANGE MASTER TO master_use_gtid=slave_pos;
Query OK, 0 rows affected (0.010 sec)

Step 32: Start the slave

MariaDB [(none)]> CHANGE MASTER TO master_use_gtid=slave_pos;
Query OK, 0 rows affected (0.021 sec)

Step 33: View the Slave status

MariaDB [(none)]> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.112.129
Master_User: user1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master1-bin.000002
Read_Master_Log_Pos: 344
Relay_Log_File: master1-relay-bin.000002
Relay_Log_Pos: 645
Relay_Master_Log_File: master1-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Master_SSL_Crlpath: 
Using_Gtid: Slave_Pos
Gtid_IO_Pos: 0-1-2

Step 34: Changing to GTID Method

root@linuxhelp:/# vim /etc/mysql/mariadb.conf.d/50-server.cnf
[mariadb]
gtid-domain-id=1

Step 35: Set the global variable gtid domain to 1

MariaDB [(none)]> set global gtid_domain_id = 1;
Query OK, 0 rows affected (0.001 sec)

Step 36: View the master status

MariaDB [(none)]> show master status\G
File : master2-bin.000002
Position : 344
Binlog_Do_DB :
Binlog_Ignore_DB :
1 row in set (0.000 sec)

Step 37: Run the following Command

MariaDB [(none)]> SELECT BINLOG_GTID_POS ('master2-bin.000002' , 344 );
BINLOG_GTID_POS ('master1-bin.000002' , 344 )  : 0-2-2 
1 row in set (0.000 sec)

**Step 38: Stop the slave(Server 1)

MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.003 sec)

Step 39: Set the gtid slave position

MariaDB [(none)]> SET GLOBAL gtid_slave_pos = '0-1-2';
Query OK, 0 rows affected (0.013 sec)

Step 40: Change master to gtid slave position

MariaDB [(none)]> CHANGE MASTER TO master_use_gtid=slave_pos;
Query OK, 0 rows affected (0.010 sec)

Step 41: Start the slave

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.021 sec)

Step 42: View the Slave status

MariaDB [(none)]> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.112.129
Master_User: user1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master1-bin.000002
Read_Master_Log_Pos: 344
Relay_Log_File: master1-relay-bin.000002
Relay_Log_Pos: 645
Relay_Master_Log_File: master1-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Master_SSL_Crlpath: 
Using_Gtid: Slave_Pos
Gtid_IO_Pos: 0-1-2

Step 43: Check the Replication status by creating Databases in both server

MariaDB [(none)]> create database master1
Query OK, 0 rows affected (0.013 sec)
MariaDB [(none)]> show databases;
+-----------------------------+
| Database                      |
+------------------------------+
| information_schema  |
| master1                         |
| mysql                             |
| performance_schema |
| sys                                   |
+------------------------------+
4 rows in set (0.002 sec)

Step 44: Check the Replication status by creating Databases in both server(Server 2)

MariaDB [(none)]> create database masrter2
Query OK, 0 rows affected (0.013 sec)
MariaDB [(none)]> show databases;
+-----------------------------+
| Database                      |
+------------------------------+
| information_schema  |
| master1                         |
| master2                         |
| mysql                             |
| performance_schema |
| sys                                   |
+------------------------------+
4 rows in set (0.002 sec)

Conclusion:

We have reached the end of this article. In this guide, we have walked you through the steps required to master to master MariaDB replication in GTID Method on ubuntu 24.4. Your feedback is much welcome.

Tags:
Author: 

Comments ( 0 )

No comments available

Frequently asked questions ( 5 )

Q

1) What are the benefits of MariaDB Replication?

A

• High Availability
• Load Balancing
• Data Backup

Q

2) What is Master-Slave Replication in MariaDB?

A

In MariaDB, master-slave replication is a method for preserving exact duplicates of a database on several servers.
Master Server: This is the central server with the authoritative and original database copy.
Slave Server(s): The data from the master server is replicated on these other servers.

Q

2) What is Master-Slave Replication in MariaDB?

A

In MariaDB, master-slave replication is a method for preserving exact duplicates of a database on several servers.
Master Server: This is the central server with the authoritative and original database copy.
Slave Server(s): The data from the master server is replicated on these other servers.

Q

3) Is MariaDB replication synchronous?

A

No, standard MariaDB replication is not synchronous. It is asynchronous by default.

Q

4) Is MariaDB replication free?

A

MariaDB is a free, open-source relational database management system.

Load more

Back To Top!