MariaDB Backups: How to Backup & Restore MariaDB Databases
MariaDB Backups: How to Backup & Restore MariaDB Databases. In this article, we discuss why backup and recovery are important and the advantages it offers. After, we illustrate how to backup and restore MariaDB databases.
In the digital age, data has developed as a fundamental aspect of both personal and professional domains. Data is kept in numerous forms like pictures, videos, emails, documents, etc. With the increase in cyber threats and natural disasters, it is essential to have aback up and recovery plan in place to secure your data.
So, let’s start with article MariaDB Backups: How to Backup & Restore MariaDB Databases.
What is Backup and Recovery in MariaDB
Well, backup and recovery is a way of keeping a copy of important data in a safe place and restoring it, if something happens to the original data. There are 3 types of backups available: full, incremental, and differential. Full backups save everything, incremental backups only save new data since the last backup, and differential backups save changes since the last full backup. All in all, backup and recovery technology helps with this process, keeping data safe and available for business reasons.
Backing up data is important to ensure that it is recoverable in case of any primary data loss or corruption. Such events are caused by hardware or software failure, data corruption, or even accidental deletion of data.
It is vital to store backup copies of data on separate mediums to protect against primary data loss or corruption. Protect the data by using an external drive or USB stick, disk storage system, or tape drive.
Create extra copies of essential data and store them in a different location in case something bad happens, like a natural disaster. This way, you may go back to a version of the data that wasn’t damaged or attacked.
Advantages of Database Backup and Recovery
Data loss have severe consequences, which is why it is crucial to take measures to back up and restore project management platforms. Here are some advantages of using data backup and recovery solutions:
Data Security
Investing in reliable data backup and recovery services is crucial for ensuring the security of project management data. Solid security measures protect against data sprawl, and breaches, and ensure compliance with legal standards. By investing in dependable solutions, businesses recover their data in case of disasters, maintain customers’ trust, and comply with regulations.
Data Recovery and Replication
The primary goal of data backup is to recover and restore data quickly, avoiding downtime and ensuring business continuity. Most data backup applications include replication features, which create and store real time copies of project data. In case of data loss, replication allows quick restoration of projects, making data backup an essential practice for any business.
Reliable Data Management
Data recovery is complicated and time consuming, causing disruptions to organizational data management. However, using the right data backup program simplifies the recovery process. A reliable data backup system enables frequent and consistent backups. Most solutions also create a comprehensive backup strategy for project management platforms, protecting data from errors without compromising efficiency. Restore points generated by these solutions help businesses recover from any data crisis with ease.
Cost Control
Organizational data loss leads to severe consequences such as loss of customers, lawsuits, project delays, decreased productivity, and financial loss. Although data backup, recovery solutions, and software may seem costly, the importance of data backup is undeniable. It is a wise long term investment to safeguard your business from catastrophic data loss.
Unhindered Performance
Manual data backup is time consuming and resource intensive, often causing strain on company servers when handling large amounts of data. Backup and recovery programs offer a solution to such problems, improving network speed and reducing storage expenses. Automated data backup solutions allow organizations to carry out daily tasks without any hindrances. Therefore, backing up data with automated solutions is essential.
MariaDB Backups: How to Backup and Restore Your MariaDB Databases
This section shows you how to backup and restore your MariaDB database to keep your data secure.
Verify MariaDB Server and Databases
Before starting, it is essential to check the status of your MariaDB and all database. First, verify the active status of the MariaDB service using the following command.
systemctl status mariadb
If the MariaDB service is in active status, you see the following screen.
As you see, MariaDB is installed and running in your server. Now, log in to your MariaDB server console as a root user with the following command.
mysql -u root -p
Enter your MariaDB root password then press the Enter key to connect to your MariaDB shell as shown below.
SHOW DATABASES;
You get a list of all databases in the following screen.
Backup and Restore MariaDB Database with mariadb-dump
Also, MariaDB has a built-in backup utility called mariadb-dump that allows you to backup a single, multiple and all database backup via command line. With mariadb-dump, export backup in text, CSV and XML formats.
Backup a Single Database
The basic syntax to dump a single database is shown below:
mariadb-dump --user=[database-user] --password=[password] [database-name] > [backup-name.sql]
For example, to create a backup of a single database named exampledb, run the following command.
mariadb-dump --user=root --password=password exampledb > exampledb-backup.sql
Backup Multiple Databases
The basic syntax to dump multiple databases is shown below:
mariadb-dump --user=[database-user] --password=[password] --databases [db1] [db2] > [backup-name.sql]
For example, to create a backup of multiple databases named testdb1 and testdb2, run the following command.
mariadb-dump --user=root --password=password --databases testdb1 testdb2 > testdb-backup.sql
Backup All Databases
The basic syntax to dump all databases is shown below:
mariadb-dump --user=[database-user] --password=[password] --all-databases > [backup-name.sql]
For example, to create a backup of all databases, run the following command.
mariadb-dump --user=root --password=password --all-databases > alldb-backup.sql
Now see all database backup files using the following command:
ls -l
You see all files in the following output.
-rw-r--r-- 1 root root 2505608 Jun 14 11:41 alldb-backup.sql
-rw-r--r-- 1 root root 1301 Jun 14 11:40 exampledb-backup.sql
-rw-r--r-- 1 root root 1651 Jun 14 11:40 testdb-backup.sql
Restore a Single Database
The basic syntax to import or restore a single database is shown below.
mariadb --user=[database-user] --password=[password] [dbname] < [db-backup.sql]
For example, to restore a backup from the exampledb-backup.sql file, run the following command.
mariadb --user=root --password=password exampledb < exampledb-backup.sql
Note: You will need to create an empty database before restoring the database.
Restore All Databases
The basic syntax to restore all databases is shown below.
mariadb --user=[database-user] --password=[password] --all-databases < [backup-name.sql]
For example, to import all database backup from the file alldb-backup.sql, run the following command.
mariadb --user=root --password=password --all-databases < alldb-backup.sql
Backup and Restore MariaDB Database with MariaDB-Backup
In this section, we show you how to backup and restore MariaDB database using mariadb-backup tool.
Install MariaDB-Backup
By default, the mariadb-backup tool is not installed in the Linux operating system. So you need to install it on your server.
apt install mariadb-backup -y
After the successful installation, verify the mariadb-backup version with the following command.
mariadb-backup --version
You see the following output.
mariadb-backup based on MariaDB server 10.6.12-MariaDB debian-linux-gnu (x86_64)
Backup a Single Database
The basic syntax to backup a single database is shown below.
mariadb-backup --user=[username] --password=[password] --backup --target-dir=[backup-path] --databases=[dbname]
Let’s create a backup of a single database named exampledb using the following command.
mariadb-backup --user=root --password=password --backup --target-dir=/opt --databases=exampledb
You see the backup process in the following screen.
Backup Multiple Databases
The basic syntax to backup multiple databases is shown below.
mariadb-backup --user=[username] --password=[password] --backup --target-dir=[backup-path] --databases=[db1, db2]
For example, to create a backup of two databases named test1db and test2db, run the following command.
mariadb-backup --user=root --password=password --backup --target-dir=/opt --databases testdb1,testdb2
Backup All Databases
The basic syntax to backup all databases is shown below.
mariadb-backup --user=[username] --password=[password] --backup --target-dir=[backup-path]
Run the following command to backup all databases.
mariadb-backup --user=root --password=password --backup --target-dir=/mnt
You see the backup operation in the following screen.
Restore MariaDB Database
Before importing the database from a backup file, prepare the database for restore. Do it with the following command.
mariadb-backup --user=root --password=password --prepare --target-dir=/opt/
Next, stop the MariaDB service and ensure that the data directory is empty.
systemctl stop mariadb
Then, restore the database using the following command.
mariadb-backup --user=root --password=password --copy-back --target-dir=/opt/
Finally, start the MariaDB service using the following command.
systemctl start mariadb
Thank you for reading MariaDB Backups: Backup and Restore MariaDB Databases. We shall conclude.
MariaDB Backups: How to Backup & Restore MariaDB Databases Conclusion
In this MariaDB backup and restore guide, we explained the importance of database backup then show you how to backup a database using mariadb-dump and mariadb-backup tools. We illustrated how to restore the MariaDB database using both tools. I hope you now use both tools to make database backup and restore easily.
Backup and recovery is crucial for protecting data against unexpected events like hardware failure, human error, cyber-attacks, or natural disasters. With the advantages that backup and recovery offer, including protection against data loss, quick recovery, reduced cost, compliance, competitive advantage, data integrity, and scalability, it is essential to have a backup and recovery plan in place. So, don’t wait until it’s too late; start implementing a backup and recovery plan today to safeguard your data and ensure business continuity.
