Back Up and Restore MariaDB Databases From the Command line

Last Updated: October 8th, 2022 Xiao Guoan (Admin) 2 Comments

MariaDB is a community-driven variant of MySQL. In this tutorial, I will show 
you how to back up 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 records 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 back up and later restore your database, binary log must
be enabled.

To enable the binary log, edit MariaDB server 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, find the following line in [mysqld] section.

#log_bin     = /var/log/mysql/mysql-bin.log

Remove the # character to uncomment this line. 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 
SQL dump file.


Backing Up Multiple Databases


To back up multiple databases, you need to add the --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 the  --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 the 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 Databas 


First, create a database on the target machine using mysql

sudo mysql -u root
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 on the target machine will be intact.


Store the Database Backup on a Cloud Storage Service


You can use the Duplicati backup tool to automatically send the database backup
files to a cloud storage. All files will be encrypted before sending out to the
cloud storage.


Use Duplicati to Back Up Files on Debian, Ubuntu, Linux Mint



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.
https://www.linuxbabe.com/mariadb/master-slave-replication-ubuntu-18-04-18
-10