Multi-Primary Replication for MySQL/MariaDB/Percona

The platform lets you easily set up two different types of database replication - primary-secondary and primary-primary - in order to solve a number of different problems with performance, to support database backups, alleviate system failures and much more. Information on how to configure a primary-secondary replication for your MySQL/MariaDB/Percona database can be found in the linked guide.

In this tutorial we are going to explain how to configure the primary-primary (multi-primary) replication.

Below we’ll cover the following topics:

Replication Use Cases
Automatic Installation
Manual Installation

Replication Use Cases

In general, multi-primary replication is a more complex solution than primary-secondary replication, which is why it is used less frequently. However, there are some use cases where it can be a better fit:

Replication	Primary-Secondary	Multi-Primary
Benefits	Simplicity, ease of setup and maintenance	Higher availability, load balancing, geographical distribution
Drawbacks	Single point of failure, write operations limited to primary	Complexity, potential for conflicts during concurrent writes
Use Cases	Read-heavy workloads, backup and disaster recovery, applications with strict consistency requirements	Write-heavy workloads, high availability and scalability, geo-distributed applications (local writes)

We recommend choosing the replication type based on your specific application needs and infrastructure requirements:

Primary-Secondary for simplicity, strong consistency, and read-heavy workloads.
Primary-Primary for high availability, write scalability, and distributed systems needing local writes.

Automatic Installation

Virtuozzo Application Management can automatically set up a highly available MySQL/MariaDB/Percona database cluster with the required replication type (including the primary-primary one) in just a few clicks. The option is available directly in the topology wizard during the environment creation:

Click the New Environment button at the top of the dashboard.
Select MySQL, MariaDB, or Percona database in the topology wizard.
Enable the Auto-Clustering option.
Choose the Primary-Primary replication type from the Scheme drop-down list.
Adjust other parameters if needed and click Create.

Alternatively, you can automatically create a dedicated environment with a pre-configured MySQL/MariaDB/Percona database cluster using the pre-packaged MySQL/MariaDB/Percona Cluster application available in the platform Marketplace.

Click the Marketplace button at the top of the dashboard.
Find the MySQL/MariaDB/Percona Cluster application using the search bar.
In the appeared dialog, choose preferred database stack and replication type.
Set other environment parameters and click Install.

That’s all! In a couple of minutes, your database cluster will be created and automatically configured according to the selected replication type.

Manual Installation

If you prefer to take full control over the replication process and want to set up and configure the Primary-Primary replication manually, you can follow the steps below.

Create Environments

The first thing that you need in order to configure a database replication is at least two database servers. For our example, let’s create two environments with MySQL instances.

Tip: The instruction below is suitable for both MariaDB and Percona database servers with minimal adjustments.

1. Log in to the platform dashboard and click the New Environment button.

2. In the Environment Topology wizard, we’ll select MySQL as our database. Set the cloudlet limit and name the first environment, for example, first-primary.

Click Create and wait a minute for your environment to be created.

3. Repeat the previous step to create the second-primary environment with the same database or just clone the first-primary environment.

Now you have two identical environments with MySQL databases. Let’s proceed to the replication configuration.

Configure First Primary

1. Let’s start with first-primary. Click the Config button for your first environment.

2. Go to the /etc/my.cnf file in the opened file manager, locate the server-id parameter and replace it with the following:

1
2
3
4
5
6
server-id = 1
binlog-do-db = example
binlog-do-db = teste
log-bin = /var/log/mysql/mysql-bin.log
auto_increment_increment = 1
auto_increment_offset = 1

Let’s consider in detail what exactly we are configuring with these options:

server-id - common option which is used by replication servers for identifying themselves. Should be unique for each server.
bin-log-do-db = example and bin-log-do-db = teste - tells the server to restrict binary logging to updates for the specified databases.
Note: These databases must not be created. Once you are comfortable with the multi-primary replication you can include your own databases.
log-bin = /var/log/mysql/mysql-bin.log - defines whether the binary log is enabled or not. The option value, if given, is the basename for the log sequence.
auto_increment_increment = 1 - used to control the interval between successive column values.
auto_increment_offset = 1 - in the context of the multi-primary replication can be used to avoid replication conflicts. In this article, we set the 1 value for the first-primary server and 2 for the second-primary.

Both auto_increment_increment and auto_increment_offset options are intended for use with multi-primary replication and should be set according to your application requirements. We suggest that you set auto_increment_offset option value to the same as the server-id parameter’s one, to avoid replication conflicts.

3. Save the changes and Restart the MySQL node.

Configure Second Primary

Let’s move on to the second-primary environment. The steps are similar to the ones we’ve just performed for the first server.

1. Hover over and click Config to open the file manager for the MySQL node.

2. Open the same /etc/my.cnf file and replace the server-id parameter, just like you did for the first server:

1
2
3
4
5
6
server-id = 2
binlog-do-db = example
binlog-do-db = teste
log-bin = /var/log/mysql/mysql-bin.log
auto_increment_increment = 1
auto_increment_offset = 2

Note: We’ve changed the values of server-id and auto_increment_offset options, as they should be unique for each server.

3. Once again, Save the changes and Restart the MySQL node.

Enable Primary-Primary Replication

1. Click the Open in Browser button for MySQL node in the first-primary environment and log in to the phpMyAdmin admin panel using the database root credentials sent to you via email.

2. Navigate to the User accounts tab and click Add user account.

3. Specify the name and password for your replication user.

Next, scroll down and tick the replication client and replication slave administration privileges.

Click Go at the bottom of the page to create the user.

4. Switch to the Status tab to ensure that replication is configured correctly.

Pay attention to the log File and Position values, as they will be needed to set up the second primary database.

5. Repeat the 1-4 steps for the second MySQL server, creating the same-named user and noting the log file parameters.

Note: In case you’ve used cloning for the second environment creation:

Credentials for the MySQL server are the same as for the initial node.
You need to set a unique UUID for the cloned database server. For example, you can change it from the Variables tab in the phpMyAdmin panel (search for and edit the server_uuid variable). Or you can remove the /var/lib/mysql/auto.cnf file and restart the MySQL node - a new UUID will be generated automatically.

6. Now we can activate the replication. You have several ways to do this:

use the dedicated Replication tab in the phpMyAdmin panel (available since MySQL 8.0.22 and MariaDB 10.5.1 versions)
run the necessary SQL queries in the SQL tab of phpMyAdmin panel
connect to the database via SSH (e.g., Web SSH) and execute the SQL commands in the MySQL shell

For our example, we’ll go with the second option and start with the second-primary environment. Open the SQL tab in phpMyAdmin panel and run the following queries:

1
2
3
STOP REPLICA;
CHANGE REPLICATION SOURCE TO SOURCE_HOST = 'first-primary', SOURCE_USER = 'replicator', SOURCE_PASSWORD = 'passw0rd', SOURCE_LOG_FILE = 'binlog_file_name', SOURCE_LOG_POS = binlog_file_position;
START REPLICA;

Note: For legacy MySQL versions (prior to 8.0.22) and for MariaDB, use the following commands instead:

1
2
3
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST = 'first-primary', MASTER_USER = 'replicator', MASTER_PASSWORD = 'passw0rd', MASTER_LOG_FILE = 'binlog_file_name', MASTER_LOG_POS = binlog_file_position;
START SLAVE;

Here you need to substitute values for the following parameters:

SOURCE_HOST (MASTER_HOST) - URL or IP of the other primary (get it from the dashboard)
SOURCE_USER (MASTER_USER) - replication user name (created at the beginning of the section)
SOURCE_PASSWORD (MASTER_PASSWORD) - replication user’s password (created at the beginning of the section)
SOURCE_LOG_FILE (MASTER_LOG_FILE) - source’s log file (see in the Status tab of other primary)
SOURCE_LOG_POS (MASTER_LOG_POS) - source’s log position (see in the Status tab of other primary)

You can ignore the “Unrecognized statement type” warnings in the editor, your queries will be executed anyway.

6. You can check your replication status in the Status tab or use the SHOW REPLICA STATUS; (SHOW SLAVE STATUS;) command.

7. Repeat 6-7 steps for your other MySQL server (first-primary in our case). Don’t forget to substitute the parameters’ values with the correct data from the second-primary server.

Congratulations! Replication has been successfully enabled on both servers.

Verify Replication

Finally, let’s ensure everything works like a charm.

1. Log onto the first database server and create a new example database:

As you can see, it is already marked as replicated in the Primary replication column.

Note: The name of this new database corresponds to the one we’ve specified in the servers’ configuration file (binlog-do-db = example). For further usage, you can create your own database and substitute binlog-do-db parameter’s value with its name.

2. Now, switch to the second server in your MySQL cluster and verify that the newly created database is replicated. To check that replication works both ways, let’s add a new table to this database.

3. Lastly, switch back to the admin panel of the first MySQL server and make sure replicated database contains a new table now.

You can also perform some other actions (operate with records, triggers, indexes, etc.) to check that all data is instantly replicated inside your MySQL cluster.

That was so easy! Enjoy complete data security with the platform hosting.