How to configure GTID-based replication on MySQL servers

Organizations rely on databases to store data and allow efficient retrieval of it. Robust replication helps organizations meet availability goals and ensure the data is where users need it when they need it. There are many database options, but this article focuses on MySQL. Specifically, it explains using Global Transaction Identifiers (GTIDs) in MySQL replication. It also provides instructions for configuring source and replica servers with GTID enabled.

What is replication?

Replication is a process where multiple databases maintain copies of the same data. When one database instance updates its data, it sends those changes, as quickly as possible, to all the other databases.

MySQL uses the source-replica replication architecture. One of the databases is chosen as a source (it provides the replication), and the others are replicas (read-only databases that receive a copy).

What is a Global Transaction Identifier?

GTIDs bring transaction-based replication to the MySQL databases. With GTID replication, every transaction can be identified and tracked as it is committed on the originating source server and applied by replicas. You don't have to refer to any log files when starting the replica servers. GTID replication uses auto-positioning replication, which reduces the work spent on configuration.

It is very easy to determine if the source and replica are consistent. If all transitions committed on the source are also committed on the replicas, consistency is guaranteed. GTIDs use unique identifiers, which can be viewed by examining the binary log and are used for debugging purposes. The figure below depicts the status of a source MySQL server with GTID enabled.

Image

To read more about creating and managing GTIDs, visit the official MySQL documentation. And to learn about using MySQL in Red Hat Enterprise Linux (RHEL), consult RHEL's Using MySQL documentation.

Implement GTID replication on MySQL servers

The following steps cover the configuration and connection of replication between two MySQL servers. One is the designated source and the other is the replica.

[ Learn Linux basics in 30 days with Red Hat Enterprise Linux Technical Overview course. ]

Ensure both machines have the MySQL server and client installed, and then follow the steps below.

1. Configure a MySQL source server

First, configure the source server by editing the MySQL configuration file. Pay close attention to ensuring the server-id value is unique.

Include the following options in the /etc/my.cnf.d/mysql-server.cnf file under the [mysqld] section:

bind-address=<source_ip_address>
log_bin=<path_to_source_server_log>

For example, specify the log file is found along this path: log_bin=/var/log/mysql/mysql-bin.log.

server-id=<id>

The server-id must be unique; otherwise, replication won't work.

gtid_mode=ON
enforce-gtid-consistency=ON

Because you made changes to the configuration file, you must restart the mysqld service:

$ sudo systemctl restart mysqld.service

2. Configure a MySQL replica server

Now move to the replica server and edit its configuration file, again ensuring the server-id is unique.

Include the following options in the /etc/my.cnf.d/mysql-server.cnf file under the [mysqld] section (if you want to read more about the options, refer to the MySQL documentation).

log_bin=<path_to_source_server_log>

For example, the source server's log file might be here: log_bin=/var/log/mysql/mysql-bin.log.

relay-log=path_to_replica_server_log

The replication server's log might be along this path: relay-log=/var/log/mysql/mysql-relay-bin.log.

server-id=<id>

The server-id must be unique; otherwise, replication won't work.

gtid_mode=ON
enforce-gtid-consistency=ON
log-replica-updates=ON
skip-replica-start=ON

You changed the configuration file on the replica server, so don't forget to restart the mysqld service:

$ sudo systemctl restart mysqld.service

Skip to the bottom of list

Image

Download now

3. Create a replication user on the MySQL source server

Now that the replication configuration is in place on both servers, the next step is to configure the necessary user account on the source server. Run the following commands from the MySQL command prompt.

Create a replication user:

mysql> CREATE USER 'replication_user'@'replica_server_ip' IDENTIFIED WITH mysql_native_password BY 'password';

Grant the user replication permissions:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'replica_server_ip';

Reload the grant tables in the MySQL database:

mysql> FLUSH PRIVILEGES;

Set the source server to the read_only state:

mysql> SET @@GLOBAL.read_only = ON;

4. Connect the replica server to the source server

The final piece is to connect the replica server with the source server. These steps are also conducted at the MySQL prompt on the replica server.

Set the replica server to the read_only state:

mysql> SET @@GLOBAL.read_only = ON;

Configure the replication source:

mysql> CHANGE REPLICATION SOURCE TO
    -> SOURCE_HOST='source_ip_address',
    -> SOURCE_USER='replication_user',
    -> SOURCE_PASSWORD='password',
    -> SOURCE_AUTO_POSITION=1;

Start the replica thread in the MySQL replica server:

mysql> START REPLICA;

Now unset the read_only state on both servers, and you can make changes to the source server. These changes are sent to the replica:

mysql> SET @@GLOBAL.read_only = OFF;

[ The OpenShift for Developers eBook will get you started with containers quickly. ]

Use GTID

There is no reason not to use the GTID configuration for MySQL server replication. It makes rollouts, debugging, and configuration much easier for administrators. There may be some minor limitations to GTID-based replication, but the pros outweigh the cons. It is even easier to configure than configuring replication without GTID, so go for it.