Listen to this Post
Introduction:
In the high-stakes world of database administration, downtime is the enemy, and data loss is a catastrophe. While traditional tools like `mysqldump` provide logical backups, they often lock tables and bring production systems to a halt. Enter Mariabackup, a powerful, open-source tool that performs physical, hot backups of MariaDB databases without interrupting service. This guide dives deep into Mariabackup, transforming you from a cautious administrator into a disaster recovery pro, ensuring your data is safe, consistent, and instantly recoverable.
Learning Objectives:
- Understand the critical difference between logical (
mysqldump) and physical (Mariabackup) backups. - Master the step-by-step process of performing a full hot backup of a live MariaDB database.
- Learn how to prepare and restore a backup to recover a failed database instance.
- Implement automation scripts to schedule regular, reliable backups.
You Should Know:
1. Installation and Initial Setup
Before we can protect our data, we need the right tools. Mariabackup is available in the official MariaDB repositories and can be installed alongside your existing MariaDB server. It works by copying the actual data files (InnoDB, Aria, etc.) while the database is running, which requires specific permissions to ensure data consistency during the copy process.
Step‑by‑step guide for Linux (Ubuntu/Debian):
First, ensure your package lists are updated and install the `mariadb-backup` package.
sudo apt update sudo apt install mariadb-backup -y
For RHEL/CentOS/Fedora, you would typically use:
sudo dnf install MariaDB-backup
Verification and Permissions:
Check the installation was successful and verify the version.
mariabackup --version
Mariabackup connects to the database server to perform the backup. You need a database user with specific privileges. Connect to your MariaDB instance and create a dedicated backup user.
CREATE USER 'backupuser'@'localhost' IDENTIFIED BY 'YourStrongPasswordHere'; GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON . TO 'backupuser'@'localhost'; FLUSH PRIVILEGES;
– RELOAD and LOCK TABLES are required to perform a non-blocking backup.
– PROCESS is needed to run the `SHOW ENGINE INNODB STATUS` command.
– REPLICATION CLIENT is necessary for reading binary log positions.
2. Performing Your First Hot Backup
Now that everything is set up, let’s perform a full backup of a database named my_company_db. The core command is mariabackup --backup. It copies all the data files to a specified directory, creating a physical snapshot of the data as it existed at the start of the backup process.
Step‑by‑step guide:
Create a directory to store your backup and execute the backup command.
sudo mkdir -p /backups/mariadb/full_backup_$(date +%Y%m%d_%H%M%S) sudo mariabackup --backup \ --target-dir=/backups/mariadb/full_backup_$(date +%Y%m%d_%H%M%S) \ --user=backupuser \ --password=YourStrongPasswordHere
What this does:
--backup: Instructs the tool to perform a backup operation.--target-dir: The directory where the backup files will be written.- The command connects to the database, copies all InnoDB data files (
.ibd), the system tablespace, and other essential files. - While copying, it monitors changes to the data files (the “changed page tracking”) and records them in a transaction log.
Important Note: The directory you back up to must be empty or non-existent. After the command finishes, you will have a raw copy of your data files, but they are not yet ready for a direct restore because they may contain inconsistent transactions that were in the middle of committing when the backup ran. This is where the “prepare” stage comes in.
3. Preparing the Backup for Restoration
A raw file copy is like a photograph of a busy highway; cars (transactions) were moving when the photo was taken. The “prepare” phase applies the recorded transaction logs to the data files, making them consistent and ready for use. This is a critical step performed on the backup itself, not the live database.
Step‑by‑step guide:
Navigate to your backup directory and run the prepare command. It’s safe to run this on the same server or a backup server, as it does not touch the running database.
sudo mariabackup --prepare --target-dir=/backups/mariadb/full_backup_[bash]
Replace `
` with the actual name of your backup directory. <h2 style="color: yellow;">What this does:</h2> <ol> <li><code>--prepare</code>: Instructs the tool to make the backup consistent.</li> <li>It reads the transaction log files (like <code>xtrabackup_logfile</code>) that were saved during the `--backup` phase.</li> </ol> <h2 style="color: yellow;">3. It performs two crucial operations:</h2> <ul> <li>Redo committed transactions: It applies all completed transactions from the log to the data files, ensuring no committed data is lost.</li> <li>Rollback uncommitted transactions: It undoes any transactions that were not yet committed when the backup finished.</li> </ul> <ol> <li>Once complete, the files in the target directory are a fully consistent, point-in-time snapshot of your database.</li> </ol> <h2 style="color: yellow;">4. Restoring a Database from a Prepared Backup</h2> A restore is the act of replacing the current (potentially corrupted) data directory with your prepared backup. This requires shutting down the MariaDB service to prevent any writes to the data directory during the copy. <h2 style="color: yellow;">Step‑by‑step guide:</h2> First, stop the MariaDB service. This will cause downtime. [bash] sudo systemctl stop mariadb
Ensure the data directory is empty. The default location is usually /var/lib/mysql/. Be absolutely certain of your paths before running rm commands.
sudo rm -rf /var/lib/mysql/
Now, use the `–copy-back` command to copy the prepared backup into the data directory. This preserves file permissions and ownership.
sudo mariabackup --copy-back --target-dir=/backups/mariadb/full_backup_[bash]
Finally, set the correct ownership (usually mysql:mysql) and start the service.
sudo chown -R mysql:mysql /var/lib/mysql sudo systemctl start mariadb
Your database should now be restored to the state at the time of the original backup.
5. Automating Backups with a Cron Job
Manual backups are prone to human error. Automating the process ensures your Recovery Point Objective (RPO) is consistently met. We will create a simple bash script and schedule it with cron.
Step‑by‑step guide:
Create a script file, for example, `/usr/local/bin/mariabackup_daily.sh`.
sudo nano /usr/local/bin/mariabackup_daily.sh
Add the following content, replacing the password and paths as needed.
!/bin/bash BACKUP_ROOT="/backups/mariadb" DATE=$(date +%Y%m%d_%H%M%S) BACKUP_DIR="$BACKUP_ROOT/full_backup_$DATE" USER="backupuser" PASSWORD="YourStrongPasswordHere" Create backup directory mkdir -p $BACKUP_DIR Perform the backup mariabackup --backup --target-dir=$BACKUP_DIR --user=$USER --password=$PASSWORD Check if backup was successful if [ $? -eq 0 ]; then echo "Backup completed successfully to $BACKUP_DIR" Prepare the backup so it's ready for restore immediately mariabackup --prepare --target-dir=$BACKUP_DIR else echo "Backup failed!" | mail -s "Mariabackup Failure" [email protected] fi Optional: Delete backups older than 7 days find $BACKUP_ROOT -maxdepth 1 -type d -name "full_backup_" -mtime +7 -exec rm -rf {} \;
Make the script executable and edit the crontab.
sudo chmod +x /usr/local/bin/mariabackup_daily.sh sudo crontab -e
Add the following line to run the backup every night at 2 AM.
0 2 /usr/local/bin/mariabackup_daily.sh
6. Comparison: Mariabackup vs. mysqldump
Understanding when to use which tool is key to a robust backup strategy. `mysqldump` is a logical backup tool, exporting data and structure as SQL statements. Mariabackup is a physical backup tool, copying the raw data files.
| Feature | Mariabackup (Physical) | mysqldump (Logical) |
| : | : | : |
| Backup Type | Copies data files directly. | Exports data as SQL statements. |
| Speed | Very fast, copies raw blocks. | Slower, especially for large databases. |
| Restore Speed| Very fast, just copies files back. | Slow, must replay all SQL insert statements. |
| Impact on Live DB| Minimal (hot backup). | Can lock tables (InnoDB with `–single-transaction` is non-blocking but still has overhead). |
| Granularity | Whole instance backup/restore. | Can backup/restore single tables or databases easily. |
| File Size | Larger, as it includes raw data and indexes. | Smaller, only contains data definitions and rows. |
| Use Case | Large production databases, disaster recovery. | Small databases, schema changes, migrations. |
7. Advanced: Point-in-Time Recovery (PITR)
While a full backup restores you to a specific moment, combining it with binary logs allows you to “replay” transactions up to a specific point in time, say, just before an accidental `DROP TABLE` command. This requires having binary logging enabled on your server (log_bin enabled in MariaDB config).
Step‑by‑step guide concept:
- Restore the last full backup as shown in Section 4.
- Extract the binary log position from the backup. Mariabackup saves this information in a file called `xtrabackup_binlog_info` inside the backup directory. It will look something like:
`mysql-bin.000003 482`
This means at the time of the backup, the server had applied all transactions up to position 482 in log file mysql-bin.000003.
3. Apply all subsequent binary logs starting from that position. Use the `mysqlbinlog` utility.
Apply all logs from the backup point up to a specific time or position. mysqlbinlog /var/log/mysql/mysql-bin.000003 --start-position=482 /var/log/mysql/mysql-bin.000004 | mysql -u root -p
To stop at a specific time (e.g., just before the disaster), you would use the `–stop-datetime` option with mysqlbinlog.
What Undercode Say:
- Hot Backups are Non-Negotiable: In any production environment with high availability requirements, relying solely on `mysqldump` is a risk. Mariabackup provides a safety net that doesn’t force you to choose between data integrity and uptime. It’s the industry standard for a reason, allowing for near-instantaneous restoration of massive datasets.
- Automation is Your Best Friend: The script provided isn’t just a convenience; it’s a compliance necessity. By automating the backup and preparation process, you eliminate human error and ensure your backups are always ready for a restore. Pairing this with a retention policy (deleting old backups) prevents storage bloat and keeps your disaster recovery process clean and efficient.
Prediction:
As data privacy regulations become stricter and the financial impact of downtime continues to escalate, we will see a shift away from generic backup strategies toward more intelligent, automated, and integrated solutions. The future of database protection lies in “self-healing” databases that can automatically detect corruption and initiate point-in-time recovery without human intervention. Tools like Mariabackup will evolve from command-line utilities into embedded services within database clusters, managed by AI that predicts failure points and pre-stages backups for instant failover, making data loss a relic of the past for MariaDB users.
🎯Let’s Practice For Free:
IT/Security Reporter URL:
Reported By: Mariadb Basededonnees – Hackers Feeds
Extra Hub: Undercode MoN
Basic Verification: Pass ✅
