How to Back up the Database in MariaDB?
I want to back up the database in MariaDB.
Problem
How to back up the database in MariaDB?
Solution
Before doing a backup, it is highly recommended to ensure that there are no transactions in the database. Maybe you can turn off the application that is connected to the database or turn off the connection to the database so that it will produce a good backup file. In this article, I have 3 databases to use as an example, like the image below:
And below are the commands to back up the MariaDB database:
1. Back up the entire database
If you want to back up all databases in MariaDB, use the format below:
mariadb-dump -u username -p --all-databases > backup_file_name.sql
So, run the command below to back up your entire database:
mariadb-dump -u root -p --all-databases > backup_all_databases.sql
2. Backup a database
If you want to back up a database in MariaDB, use the format below:
mariadb-dump -u username -p db_name > backup_file_name.sql
So, run the command below if you want to back up the nodes database:
mariadb-dump -u root -p nodes > backup_nodes_db.sql
3. Backup more than one database
If you want to back up more than one database, use the format below:
mariadb-dump -u username -p db1_name db2_name > backup_file_name.sql
So, if you want to back up the nodes and image databases, use the format below:
mariadb-dump -uroot -p --databases nodes image > backup_nodes_and_image_db.sql
4. Backup database only
When you back up a database, by default, the database will be backed up in its entirety, both the database and the database structure. But sometimes you just want to back up the database without the structure, so you can use the format below:
mariadb-dump -u username -p --no-create-db --no-create-info db_name > dbname_db_only.sql
So, if you want to back up the database only for the nodes database, you can use the command below:
mariadb-dump -u root -p --no-create-db --no-create-info nodes > nodes_db_only.sql
5. Backup the database structure only
If you want to back up the database structure only, without the need to back up the database, then you can use the format below:
mariadb-dump -u username -p -–no-data db_name > dbname_structure_only.sql
So, use the command below if you want to back up the structure only for the nodes database:
mariadb-dump -u root -p --no-data nodes > nodes_structure_only.sql
6. Backup table only
If you want to back up the specific table only in a database, you can use the format below:
mariadb-dump -u username -p db_name table1 table2 > backup_table1_table2_dbname.sql
So, if you want to back up 2 tables in the nodes database for babel and category tables, use the command below:
mariadb-dump -uroot -p nodes babel category > backup_babel_category_nodes.sql
7. Backup with compression
By default, when people perform database backups, they will usually use .sql as an extension of the database backup file. But actually, you can compress the database backup files when you do a backup. There are 2 methods of compressing database backups, namely using the tar.gz method and the .bz2 method. If you want to use the first method, use the format below:
mariadb-dump -u username -p db_name | gzip -9 -c > db_backup_file.sql.gz
Suppose you want to compress the nodes database, then use the command below:
mariadb-dump -u root -p nodes | gzip -9 -c > nodesdb_backup_file.sql.gz
And if you want to use bz2 compression when backing up databases, then make sure that the bz2 package is already installed on your server. If the package is not already installed, use the command below:
RockyLinux/AlmaLinux/CentOS
dnf install bzip2
Ubuntu/Debian
sudo install bzip2
OpenSUSE
zypper install bzip2
After you install the package, try to back up your database using the format below:
mariadb-dump -u username -p db_name | bzip2 > database.sql.bz2
If you want to back up your database, for example nodes database, run the below command:
mariadb-dump -u root -p nodes | bzip2 > nodes_backup_db.sql.bz2
Below is a comparison image of the size of the backup file that does not use compression, uses .tar.gz compression, and .bz2 compression:
Note
You should know that the mariadb-dump command has many useful options, including the ‐-single-transaction and ‐-lock-tables options. If you have a database that uses the InnoDB storage engine, then you should use the ‐-single-transaction option because this option starts a transaction before dumping and reads data from a consistent snapshot without locking the tables for extended periods, allowing concurrent reads and writes. However, if you are using the MyISAM storage engine, you can use the ‐-lock-tables option when backing up the database in MariaDB. If your database has a different storage engine, it is recommended to back up the database partially; some tables that use InnoDB use the ‐-single-transaction option, and some tables that use MyISAM use the ‐-lock-tables option when backing up the database.