Introduction

Maintaining database replication integrity is paramount for businesses relying on databases. Replication ensures that changes made on the master database are reliably propagated to replica databases, guaranteeing data consistency and availability across the system. In this comprehensive guide, we’ll dive into the process of restoring a MariaDB database and repairing replication, in the context of a “last hope” solution.
These steps are essential for safeguarding data integrity, ensuring disaster recovery readiness, and maintaining high availability in distributed database environments.

Prerequisites

Before embarking on the restoration and replication repair journey, it’s essential to ensure you have everything in place:

  • Access to the MariaDB server with appropriate privileges.
  • Basic understanding of SQL commands for database management.
  • A recent backup of your MariaDB database made with mysqldump, crucial for data restoration. In this blog, I will use a dump of my troublesome database, that I did previously. The dump has to be made using the option “–master-data” “which automatically appends the CHANGE MASTER TO statement required on the replica to start the replication process”.

Step 1: Stop the Slave

Replication must be halted before initiating any restoration activities to prevent potential data inconsistencies. By stopping the slave, we ensure that no new changes are applied to the replica databases during the restoration process, maintaining data integrity.

root@srv2:~# mysql
MariaDB [(none)]> stop slave;
MariaDB [(none)]> show slave status\G 
*************************** 1. row *************************** 
Slave_IO_State: 
Master_Host: 10.x.x.x 
Master_User: replication_user 
Master_Port: 3306 
Connect_Retry: 60 
Master_Log_File: mariadb-bin.170722 
Read_Master_Log_Pos: 103504747 
Relay_Log_File: relay-bin.001548 
Relay_Log_Pos: 103505048 
Relay_Master_Log_File: mariadb-bin.170722 
<mark class="has-inline-color has-vivid-red-color"><strong>Slave_IO_Running: No 
Slave_SQL_Running: No </strong></mark>
Replicate_Do_DB: 
Replicate_Ignore_DB:

Note: Checking the replication status before stopping the slave is a good practice to confirm that replication is active and needs to be stopped.

Step 2: Prepare for Restoration

Preparing for restoration involves navigating to the backup directory and stopping the MariaDB service. Additionally, deleting the target database directory ensures a clean slate for the restoration process, preventing conflicts or errors.

root@srv1:~# ll /backup/SRV1/2024-03-13/ 
-rw-r--r-- 1 root root 97685528439 May 13 03:20 backup_2024-03-13 
root@srv1:~# cd /backup/SRV1/2024-03-13/

root@srv1:/backup/SRV1/2024-03-13# systemctl stop mariadb.service 
root@srv1:/backup/SRV1/2024-03-13# mysql 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

root@srv1:/backup/SRV1/2024-03-13# rm -rf /var/lib/mysql/dbtest/

Note: Ensure that the backup directory contains the necessary backup files and that sufficient disk space is available before proceeding with the restoration. Instead of deleting the database directory, you can also drop the database from mysql prompt.

Step 3: Restart MariaDB

Once the preparation is complete, restart the MariaDB service to initiate the restoration process. It’s important to ensure that you’re connected to MariaDB from the backup directory to run the restore command later.

root@srv1:/backup/SRV1/2024-03-13# systemctl start mariadb.service 
root@srv1:/backup/SRV1/2024-03-13# mysql

Note: After restarting MariaDB, verify that the service has started successfully and is accessible before proceeding with the restoration.

Step 4: Recreate the Database

After restarting MariaDB, recreate the target database using the appropriate SQL command. Verifying the successful creation of the database ensures that the restoration process can proceed smoothly.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| binlog             |
| information_schema |
| logs               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.002 sec)

MariaDB [(none)]> create database dbtest;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| binlog             |
| information_schema |
| logs               |
| mysql              |
| performance_schema |
| dbtest             |
| sys                |
+--------------------+
7 rows in set (0.000 sec)

Step 5: Proceed with the Restoration

Connect to the newly created database to prepare for the restoration of data from the backup file. This step sets the stage for applying the backed-up data to the database schema.

MariaDB [(none)]> use dbtest;

If replication was restarted when you started the mariadb service, it’s imperative to halt the slave again before proceeding further. This ensures that the restored data is not replicated until the process is completed.

MariaDB [dbtest]> stop slave;

Initiate the restoration process by sourcing the backup file containing the database dump. Monitoring the progress of the restoration in a separate terminal window allows for real-time tracking of the restoration activities.

MariaDB [dbtest]> source backup_2024-03-13;

Note: Keep an eye on the restoration progress to ensure it completes successfully without any errors or interruptions.

Step 6: Apply Replication Configuration

After the restore, search for and apply the replication configuration settings from the backup file to synchronize the replica databases with the master. This step is crucial for ensuring data consistency across the replication topology.
You can use the head command to find a similar line at the top of your dump:

CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.***, MASTER_LOG_POS=***;

Apply the command:.

MariaDB [dbtest]> CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.***, MASTER_LOG_POS=***;

Note: The line will be present in your dump file only if you use the option “–master-data” to create the dump.

Step 7: Start Replication

Once the restoration is complete and replication configuration is applied, restart the replication process. This allows data changes made on the master database to be propagated to the replica databases, ensuring synchronization.

MariaDB [dbtest]> start slave;

Step 8: Verify Replication

To ensure that replication is functioning correctly, verify the replication status. Monitoring parameters such as “Slave_IO_Running” and “Slave_SQL_Running” helps in identifying any issues and ensuring smooth replication operation. You can also control “second_behind_master” which is useful to know if the replica has catch up with the master. (“Difference between the timestamp logged on the master for the event that the replica is currently processing, and the current timestamp on the replica. “)

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 10.150.13.2
                  Master_User: replication_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mariadb-bin.***
          Read_Master_Log_Pos: ********
               Relay_Log_File: relay-bin.***
                Relay_Log_Pos: ********
        Relay_Master_Log_File: mariadb-bin.***
            <mark class="has-inline-color has-vivid-red-color"><strong> Slave_IO_Running: Yes
            Slave_SQL_Running: Yes</strong></mark>
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
... ...

Conclusion

By carefully following these steps, you can fix issues with your MariaDB replication and make sure your data stays safe and your database works reliably. Keeping an eye on your database and doing regular backups will help you avoid problems and keep everything running smoothly.

https://mariadb.com/kb/en/show-replica-status/
https://mariadb.com/docs/server/ref/mdb/cli/mariadb-dump/master-data/

Post Views: 535
Thumbnail [60x60]
by
Joan Frey