MySQLMariaDBPercona Database PrimarySecondary Replication

Content

Primary-secondary replication is used to solve many different problems with performance, supporting the backup of different databases, and as a part of a larger solution to alleviate system failures. It enables data from one database server (primary, previously known as “master”) to be replicated to one or more database servers (secondaries, previously known as “slaves”). The primary logs the updates, which then ripple through to the secondaries. The secondary outputs a message stating that it has received the update successfully, which allows sending subsequent updates. Primary-secondary replication can be either synchronous or asynchronous. The difference is simply the timing of propagation of changes. If the changes are made to the primary and secondary at the same time, it is synchronous. If changes are queued up and written later, it is asynchronous.

Below we’ll cover the following topics:

Replication Use Cases

The target usage for replication in MySQL/MariaDB/Percona databases includes:

Scale-out solutions
Data security
Analytics
Long-distance data distribution

How you can use such replication and take advantage of it:

Backups: To use replication as a backup solution, replicate data from the primary to a secondary database, and then back up the data. The secondary can be paused and shut down without affecting the running operation of the primary, so you can produce an effective snapshot of “live” data that would otherwise require the primary to be shut down.
Scale-out: You can use replication as a scale-out solution; that is, where you want to split up a load of database queries across multiple database servers, within some reasonable limitations. Because replication works from the distribution of one primary to one or more secondaries, using it for scale-out works best in an environment where you have a high number of reads and a low number of writes/updates.
Spreading the load: There may be situations when you have a single primary and wish to replicate different databases to different secondaries. For example, you may want to distribute sales data only to the required departments to help spread the load during data analysis.
Increasing the performance: As the number of secondaries connecting to a primary increases, the load, although minimal, also increases, as each secondary uses a client connection to the primary. As each secondary must receive a full copy of the primary binary log, the network load on the primary may also increase and create a bottleneck. If you are using a large number of secondaries connected to one primary, and that primary is also busy processing requests (for example, as part of a scale-out solution), then you may want to improve the replication process’s performance. One way to improve the performance of the replication process is to create a deeper replication structure that enables the primary to replicate to only one secondary, and for the remaining secondaries to connect to this primary secondary for their replication requirements.
Failover alleviating: You can set up a primary and a secondary (or several secondaries) and write a script that monitors the primary to check whether it is up. Then instruct your applications and the secondaries to change primary in case of failure.
Security: You can use SSL for encrypting the transfer of the binary log required during replication, but both primary and secondary must support SSL network connections. If either host does not support SSL connections, replication through an SSL connection is not possible. Setting up replication using an SSL connection is similar to setting up a server and client using SSL. You must obtain (or create) a suitable security certificate that you can use on the primary and a similar certificate (from the same certificate authority) on each secondary.

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-secondary 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-Secondary 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-Secondary replication manually, you can follow the steps below.

Create Environments

Tip: The instruction below is fully suitable for both MySQL and MariaDB database servers.

Let’s start with creating two environments for our primary and secondary databases.

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

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

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

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

Now you have two identical environments with MariaDB databases.

Configure Primary Database

Let’s configure the primary database now.

1. Click the Config button for your primary database.

2. Navigate to the my.cnf file and add (or uncomment if already present) the following properties as it is shown below:

1
2
3



server-id = 1
log-bin = mysql-bin
binlog-format = mixed

Note: We use the “mixed” binlog format (binlog-format = mixed) to allow a replication of operations with foreign keys. Do not use the “statement” format. Otherwise, you will get errors later on!

3. Save the changes and Restart MariaDB in order to apply the new configuration parameters.

4. Click the Open in Browser button for MariaDB and log in to phpMyAdmin admin panel using the database root credentials sent to you via email.

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

6. 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.

7. 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 secondary database.

Configure Secondary Database

Let’s go back to the platform dashboard and configure our secondary database.

1. Click the Config button for your secondary-db environment, open the my.cnf file, and add the following properties:

1
2

server-id = 2
slave-skip-errors = all

For this example, we allow our secondary database to skip all errors from primary (slave-skip-errors = all) in order not to stop normal secondary operation in case of errors on the primary database.

Note: Such skipping is generally not recommended. During the development stage it can help you to find and troubleshoot bugs. And in production (when your code is already tested), it is better to ignore only specific errors rather than all errors. It helps to avoid de-synchronization over some minor issues on the primary node, while not to miss any critical problems that can lead to data loss.

3. Next, open the /etc/phpMyAdmin/config.inc.php file and append it with the next option:

1

$cfg['AllowArbitraryServer'] = true;

4. Save the changes and Restart your secondary database server in order to apply the new configuration parameters.

5. Let’s configure secondary database server via the built-in Web SSH client. Connect to your database using credentials from the email received after the node creation (if cloned, use the same ones as on the initial DB).

1

mysql -u root -p

6. Provide replication source (primary database) details. Don’t forget to adjust code example below with your actual data:

SOURCE_HOST (MASTER_HOST) – URL or IP of the replication source (get it from the dashboard)
SOURCE_USER (MASTER_USER) – replication user name (created in the previous section)
SOURCE_PASSWORD (MASTER_PASSWORD) – replication user’s password (created in the previous section)
SOURCE_LOG_FILE (MASTER_LOG_FILE) – source’s log file (see the last step of the previous section)
SOURCE_LOG_POS (MASTER_LOG_POS) – source’s log position (see the last step of the previous section)

For MariaDB use the following command (deprecated but backward compatible on MySQL and Percona):

1
2
3
4
5
6



CHANGE MASTER TO
    MASTER_HOST='node410337-primary-db.domain.com',
    MASTER_USER='replication',
    MASTER_PASSWORD='passw0rd',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=865;

For MySQL (version 8.0.22 and later) and Percona the following approach is recommended:

1
2
3
4
5
6



CHANGE REPLICATION SOURCE TO
    SOURCE_HOST='node410337-primary-db.domain.com',
    SOURCE_USER='replication',
    SOURCE_PASSWORD='passw0rd',
    SOURCE_LOG_FILE='mysql-bin.000001',
    SOURCE_LOG_POS=865;

7. Now, you can to start replication with the appropriate command:

1

START REPLICA;

Note: In MySQL versions prior to 8.0.22 and in MariaDB prior to 10.5.1, the command is as follows:

1

START SLAVE;

8. To ensure that everything is configured correctly, log in to the secondary database admin panel and go to the Status tab. You should see that the replication is running, as shown below.