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

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.

Binary Log

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 and backup. In order to backup and later restore your database, binary log must be enabled.

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 or /etc/mysql/my.cnf. On ubuntu, it can also be /etc/mysql/mariadb.conf.d/50-server.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. After that, 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, which can greatly reduce the size of backup file.

mysqldump -u root -p database_name | gzip > database_name.sql.gz

Hint

If you want to automatically record the time when database is backed up, add the following text into the backup filename.

`date +"%Y-%m-%d"`

Like this

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.

Email the Backup

You can install the command line mail client mutt.

sudo apt install mutt

Then send the backup as an attachment.

echo "database backup" | EMAIL="[email protected]" mutt -s " database backup" -a database_name_`date +"%Y-%m-%d"`.sql.gz -- [email protected]

Where:

• The echo part is used as the email body.
• The EMAIL environment variable specifies the From: address.
• -s specifies the subject.
• -a attaches a file.
• The double dash are used to separate the attachment from the recipient email address.
• Gmail currently can receive attachment up to 50MB.

Auto Backup with Cron

Edit the root’s crontab file.

sudo crontab -e

Add the following line to automatically backup database every day.

@daily mysqldump -u root database_name | gzip > database_name_`date +"\%Y-\%m-\%d"`.sql.gz

The percent sign (%) in Cron is a meta-character, meaning end-of-file. We need to escape it with backslash to use its literal meaning.

Get Help

Notice that the --opt option is on by default when you run mysqldump. This option is a shorthand for a group of other options, including:

• –add-drop-table
• –add-locks
• –create-options
• –disable-keys
• –extended-insert
• –lock-tables
• –quick
• –set-charset

So when you run mysqldump, all of the above options are on by default. For more info, please check the manual: man mysqldump.

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.