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 back up our MariaDB database. It’s designed specifically for backup purposes. The cool thing about mysqldump is that you don’t need to stop MariaDB service to make a backup. It can be used to back up a single database, multiple databases, and all databases. By default, it will create a dump file that 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. Its 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 may be 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.

Note: You should not manually delete the binary log file (/var/log/mysql/mysql-bin.log) after it’s enabled, or MariaDB might be unable to start.

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 back up 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 back up 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 the database is backed up, add the following text to the backup filename.

`date +"%Y-%B-%d_%R"`

Like this

mysqldump -u root -p database_name > database_name_`date +"%Y-%B-%d_%R"`.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-%B-%d-%R"`.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 is used to separate the attachment from the recipient’s email address.
• Gmail currently can receive attachments up to 50MB.

Auto Backup with Cron

Edit the root’s crontab file.

sudo crontab -e

Add the following line to automatically backup the database every day.

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

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

You can also create a cron job to automatically delete old backups. For example, you can delete all backups made in January on the first day of March by adding the following line in the crontab file, assuming the backup is stored under the /root/ directory.

0 0 1 3 * rm /root/*January*.sql.gz

To delete backups made in February on the first day of April, add the following line.

0 0 1 4 * rm /root/*Feburary*.sql.gz

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.

Reduce Binary Log Size

If you find your binary log files use a huge amount of disk space, you should configure MySQL/MariaDB to automatically purge the binary logs. Add the following line in your configuration file, which will purge binary log files older than 3 days.

binlog_expire_logs_seconds = 259200

Then restart MySQL/MariaDB.

sudo systemctl restart mysql

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.

Using phpMyAdmin to Export Database

The mysqldump utility requires the user to have the PROCESS privilege in order to dump tablespaces. Sometimes, a database user owns a database but doesn’t have the PROCESS privilege, so the user can’t use mysqldump. If phpMyAdmin is installed on the same server, then the user can export the database in phpMyAdmin web interface, without the need of PROCESS privilege.

• How to Install phpMyAdmin with Nginx (LEMP) on Ubuntu 20.04 LTS
• How to Install phpMyAdmin with Apache (LAMP) on Ubuntu 20.04
• Install phpMyAdmin with Apache (LAMP) on Debian 10 Buster

Log into phpMyAdmin web interface, select your database, then go to the Export tab to export the database.

Setting Up MariaDB Master-Slave Replication

When you are taking backups of the databases, it can interrupt the workload of your MariaDB server. A good practice is to set up Master-Slave replication and do the backup on the slave server, so the workload on the master server won’t be interrupted.

• How to Set Up MariaDB Master-Slave Replication on Ubuntu 18.04, 20.04