Transferring Backed-Up Data to a MariaDB Replica: Methods and Best Practices

MariaDB replication provides data redundancy, load distribution, and disaster recovery capabilities by maintaining synchronized copies of databases across multiple servers. When setting up a new replica or repairing a failed one, transferring backed-up data efficiently and correctly is crucial. This report explores various methods to transfer backed-up data to a MariaDB replica, with focus on minimizing downtime and ensuring data consistency.

Understanding MariaDB Replication Concepts

MariaDB replication involves a primary server (historically called “master”) that processes write operations and one or more replica servers (historically called “slaves”) that maintain copies of the data. Before transferring data to a replica, it’s important to understand that:

1. The replica must contain an identical copy of the primary’s data at a specific point in time
2. The replica needs information about where to start replication (binary log coordinates or GTIDs)
3. The transfer method must ensure data consistency without corrupting the replica database[2]

MariaDB has begun replacing the terms “master” and “slave” with “primary” and “replica” while maintaining backward compatibility for existing systems[1][2].

Using Mariabackup for Transferring Data to a Replica

Mariabackup (previously known as XtraBackup) provides an efficient physical backup method that can be used to set up or repair a replica with minimal downtime.

Step-by-Step Process

• Create a backup on the primary server:

mariabackup --backup \\
--target-dir=/var/mariadb/backup/ \\
--user=mariabackup --password=mypassword

When backing up from an existing replica instead of the primary, add the –slave-info option:

mariabackup --backup \\
--slave-info --safe-slave-backup \\
--target-dir=/var/mariadb/backup/ \\
--user=mariabackup --password=mypassword

• Prepare the backup:

mariabackup --prepare \\
--target-dir=/var/mariadb/backup/

This step is crucial as it makes the data files consistent for restoration.

• Copy the backup to the replica server:

rsync -avP /var/mariadb/backup replica_server:/var/mariadb/backup

• Restore the backup on the replica:

mariabackup --copy-back \\
--target-dir=/var/mariadb/backup/

Adjust file permissions if necessary:

chown -R mysql:mysql /var/lib/mysql

• Retrieve replication coordinates:

For binary log replication, check the xtrabackup_binlog_info file which contains the master log file name and position[7][12].

For GTID-based replication, inspect the last value in the xtrabackup_binlog_info file[12].

• Configure replication on the replica:

CHANGE MASTER TO
  MASTER_HOST='primary_host',
  MASTER_USER='replication_user',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='log_file_from_binlog_info',
  MASTER_LOG_POS=position_from_binlog_info;

• Start replication:

START SLAVE;

Advantages of Mariabackup

• Performs backup with minimal impact on the running server
• Provides faster restoration for large databases compared to logical backups
• Includes all necessary replication information automatically
• Can back up a live server without locking tables when used with -single-transaction[7][12]

Using mariadb-dump for Transferring Data to a Replica

Mariadb-dump (formerly mysqldump) creates logical backups in SQL format that can be used to transfer data to a replica.

Step-by-Step Process

• On an existing replica or primary server, create a backup:

mariadb-dump --master-data=1 --single-transaction \\
--routines --triggers --all-databases > backup.sql

The –master-data=1 option includes the CHANGE MASTER TO command with the correct replication coordinates[6].

• Stop the replica (if working with an existing replica):

STOP SLAVE;

• Transfer the backup file to the new replica server:

scp backup.sql newreplica:/path/to/

• Import the backup on the new replica:

mysql < backup.sql

The included CHANGE MASTER TO statement will set up replication automatically if –master-data=1 was used.

• Start replication:

START SLAVE;

Setting Up from an Existing Replica

If creating a backup from an existing replica rather than the primary:

• Stop the SQL thread on the existing replica:

STOP SLAVE SQL_THREAD;

• Get the current replication coordinates:

SHOW SLAVE STATUS\\G

Note the values of Relay_Master_Log_File and Exec_Master_Log_Pos[6].

• Create the dump with -master-data=2:

mariadb-dump --master-data=2 --all-databases > dumpfile

• Restart the replica:

START SLAVE;

• Load the dump on the new replica:

mysql < dumpfile

• Set up replication coordinates on the new replica:

CHANGE MASTER TO
MASTER_LOG_FILE='file_name',
MASTER_LOG_POS=file_pos;

Special Considerations and Scenarios

Working with Very Large Databases

For databases in the multi-terabyte range, traditional dump and load methods can cause extended downtime. Alternative approaches include:

1. Set up replication from a live server: Use Mariabackup with transaction consistency to create a backup without locking the primary, then start replication from the captured position[12][13].
2. Parallel operations: Use tools like MySQL Shell dump utilities which provide parallel dumping with multiple threads and compression[11].
3. Streaming backups: For AWS environments, consider using streaming methods to reduce intermediate storage requirements[8].
4. Incremental operations: If possible, divide the database into logical chunks and transfer them incrementally[13].

Minimizing Downtime

To minimize downtime during the transfer:

1. Use -single-transaction with mariadb-dump to avoid locking tables during backup[4][12].
2. For Mariabackup, combine with -single-transaction for consistent backups without locking[7].
3. Consider setting up replication first, then switching over when the replica is caught up:
   • Create the backup
   • Set up the replica
   • Let replication catch up
   • Perform a quick switchover[13]

GTID-Based Replication

Global Transaction Identifiers (GTIDs) provide better crash safety and simpler replication management:

1. To use GTID replication, ensure both servers have GTID enabled[8].
2. When transferring backed-up data, check the xtrabackup_binlog_info file for GTID position[12].
3. Configure replication with MASTER_USE_GTID = slave_pos for automatic positioning[12].

Conclusion and Best Practices

When transferring backed-up data to a MariaDB replica, you should choose your method based on several key factors:

1. Database size: If you are handling a small to medium database, then mariadb-dump is a simple and reliable option. However, for large databases (multi-GB or TB), Mariabackup provides significantly better performance.
2. Downtime tolerance: If minimal downtime is required, then you should use Mariabackup with --single-transaction or set up replication in advance.
3. Data consistency: Always use transaction-consistent backup methods to ensure replica integrity.
4. Future maintenance: GTID-based replication simplifies fail-over and replica management for long-term operations.

By following these guidelines and choosing the appropriate method for your specific scenario, you can effectively transfer backed-up data to MariaDB replicas while maintaining data integrity and minimizing service disruption.

Sources

[1] Setting up a Replica with Mariabackup – MariaDB Knowledge Base https://mariadb.com/kb/en/setting-up-a-replica-with-mariabackup/

[2] Setting Up Replication – MariaDB https://mariadb.com/kb/en/setting-up-replication/

[3] Exporting data from a MySQL DB instance by using replication https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Exporting.NonRDSRepl.html

[4] MariaDB – Repair Replication Through DB Restore (mysqldump) https://www.dbi-services.com/blog/mariadb-repair-replication-through-db-restore/

[5] MariaDB Replication – ADMIN Magazine https://www.admin-magazine.com/Archive/2016/36/Setting-up-MariaDB-replication-with-the-help-of-XtraBackup

[6] mariadb-dump – MariaDB Knowledge Base https://mariadb.com/kb/en/mariadb-dump/

[7] MariaDB – Repair Replication Through DB Restore (mariabackup) https://www.dbi-services.com/blog/mariadb-repair-replication-through-db-restore-mariabackup/

[8] Working with MariaDB replication in Amazon RDS https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_MariaDB.Replication.html

[9] 2.1 Backing Up a Replica Using mysqldump https://dev.mysql.com/doc/mysql-backup-excerpt/5.7/en/replication-solutions-backups-mysqldump.html

[10] Full Backup and Restore with Mariadb-backup https://mariadb.com/kb/en/full-backup-and-restore-with-mariabackup/

[11] MySQL Replication :: 3.1.1 Backing Up a Replica Using mysqldump https://dev.mysql.com/doc/mysql-replication-excerpt/8.0/en/replication-solutions-backups-mysqldump.html

[12] mariadb – Can I initialize a replica server without locking the master … https://stackoverflow.com/questions/79006909/can-i-initialize-a-replica-server-without-locking-the-master-server-during-an-in

[13] migrate giant 17TB DB from mariadb10.4 to MySQL 8 – Reddit https://www.reddit.com/r/mysql/comments/rdh5o9/migrate_giant_17tb_db_from_mariadb104_to_mysql_8/

[14] Making Backups with mariadb-dump https://mariadb.com/kb/en/making-backups-with-mariadb-dump/

[15] How to import data into master/replica structure in MySQL https://stackoverflow.com/questions/75970834/how-to-import-data-into-master-replica-structure-in-mysql

[16] Best backup strategy for MySQL (MariaDB) replication set https://dba.stackexchange.com/questions/68091/best-backup-strategy-for-mysql-mariadb-replication-set

[17] 8.2 Backing up and Restoring a Replica Database https://dev.mysql.com/doc/mysql-enterprise-backup/8.0/en/advanced.replica-backup-restore.html

[18] Backup Mariadb from replica – Server Fault https://serverfault.com/questions/1158323/backup-mariadb-from-replica

[19] How to Backup and Restore a MariaDB database with Master and … https://dba.stackexchange.com/questions/341554/how-to-backup-and-restore-a-mariadb-database-with-master-and-slave-replication-c

[20] 2.6. Backing up MariaDB data | Red Hat Product Documentation https://docs.redhat.com/fr/documentation/red_hat_enterprise_linux/9/html/configuring_and_using_database_servers/backing-up-mariadb-data_using-mariadb

[21] Full Restore of a MySQL or MariaDB Galera Cluster from Backup https://severalnines.com/blog/updated-full-restore-mysql-or-mariadb-galera-cluster-backup/

[22] How To Import and Export Databases in MySQL or MariaDB https://www.digitalocean.com/community/tutorials/how-to-import-and-export-databases-in-mysql-or-mariadb