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="you@example.com" mutt -s " database backup" -a database_name_`date +"%Y-%m-%d"`.sql.gz -- you@gmail.com
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.
Perfect info of doing MYSQL backup!
Thanx!
Stavros