How to Back Up and Restore MariaDB Databases From the Command line
MariaDB is a community-driven variant of MySQL. In this tutorial I will show you how to backup and restore MariaDB databases using the mysqldump utility.
mysqldump is the utility that we will use to backup our MariaDB database. It’s designed specifically for backup purpose. The cool thing about mysqldump is that you don’t need to stop MariaDB service to make a backup. It can be used to backup a single database, multiple databases and all databases. By default it will create a dump file which contains all the statements needed to re-create the database.
The binary log logs every change to the database. Binary log can be statement-based and row-based. It’s main purpose is to allow replication. In order to backup and later restore your database, binary log must be enabled as well.
To enable the binary log, edit MariaDB config file. The name of the config file maybe different on different linux distributions. Normally its located at /etc/my.cnf. On ubuntu, it’s /etc/mysql/mariadb.conf.d/mysqld.cnf.
Open the config file, in [mysqld] section, find the following line
#log_bin = /var/log/mysql/mysql-bin.log
Remove the pound sign. Save and close the file. If you can’t find it, then manually add it.
Restart MariaDB database server.
sudo systemctl restart mysql or sudo service mysql restart
Now binary log is enabled.
Backing Up a Single Database
To back up a single database, issue the following command at the shell prompt
mysqldump -u root -p database_name > database_name.sql
Replace database_name with your actual database name. database_name.sql is the dump file.
Backing Up Multiple Databases
To backup multiple databases, you need to add –databases option to mysqldump.
mysqldump -u root -p --databases db_name1 db_name2 ... > multi_database.sql
Backing Up All Databases
To backup all of your databases, you need to add –all-databases option to mysqldump.
mysqldump -u root -p --all-databases > all-databases.sql
Back Up MariaDB Database with Compression
To compress the .sql file, just pipe the output of mysqldump to gzip.
mysqldump -u root -p database_name | gzip > database_name.sql.gz
If you want to automatically record the time when database is backed up, add the following text into the backup filename.
mysqldump -u root -p database_name > database_name_`date +"%Y-%m-%d"`.sql
Restore A Single Database
First create a database on the target machine using mysql
mysql -u root -p create database database_name; exit;
Then restore the backup to your database
mysql -u root -p database_name < database_name.sql
Restore Multiple Databases
mysql -u root -p < multi-databases.sql
Existing databases on the target machine will be intact.
Restore All Databases
mysql -u root -p < all-databases.sql
The SQL statements in the all-databases.sql file will recreate all your databases in MariaDB. Existing databases in on the target machine will be intact.
For more options of mysqldump, execute mysqldump –help
From time to time, you need to check that your backup is working correctly. In an ideal world, technology is perfect. But in reality, it could fail for whatever reason.