MariaDB Dump Tutorial

Many websites are now using MariaDB server to store their valuable data. Data is the most important content of any website. So you need to keep regular backups of the database and this will help you to restore your data if your website crashes or the data is corrupted by any system failure. This tutorial is divided into two parts. In the first part, you will learn to take a backup of MariaDB using the dump tool and in the second part, you will learn to restore the data from backup.

Part-1: MariaDB backup/dump

You can take a backup of the MariaDB server in 2 ways. These are physical backups and logical backups. All necessary SQL statements which are required to restore the databases such as create, insert etc. are stored in a logical backup. On the other hand, specific data files and directories are stored in a physical backup. Many tools are available to take backups of your database server. The use of the common tools, mysqldump and mysqlhotcopy, are explained in this section.

mysqldump

This tool can be used to make a logical backup of the MariaDB server. It is the best backup tool for small sized databases. But if the database size is large then you will need long times for restoring the data from this backup. It dumps data in SQL format. So, the data can be easily imported on different database servers. Database procedures, functions, views and events are dumped at the time of backup. So you can automatically recreate these at the time of restore. The syntax for mysqldump is given below:

mysqldump –u [username] –p [DATABASE name] > [backup filename].sql

According to the syntax, the backup file will be created at the current location. You can create backup of single table or single database or all databases. These three types of backups are shown in the following examples.

Log-in to MariaDB server and run the commands to check all existing databases of server.

> SHOW DATABASES;

Single table backup:

Suppose, you have to keep backup of table ‘employee’ of database ‘cakedb’ .

Run the following command from the terminal to create the backup file.

$ mysqldump –u root –p –single-transaction –quick –lock-tables=false cakedb employee >
cakedb–employee.sql

Now, check backup file is created or not using ‘ls’ command.

$ ls

Single database backup:

Run the following command to create backup file for an existing database named ‘company’.

$ mysqldump –u root –p company –single-transaction –quick –lock-tables=false >
company–backup.sql

Now, check backup file is created or not using ‘ls’ command.

$ ls

All databases backup:

Run the following command to create backup of all existing databases of MariaDB server.

$ mysqldump –u root –p –all-databases –single-transaction –quick –lock-tables=false >
all–backup.sql

Now, check backup file is created or not using ‘ls’ command.

$ ls

mysqlhotcopy

This tool is used for physical backup of database server. It is faster than other backup tools but it can only create a backup on the same machine where the database directory resides. It can also be used for single table or multiple databases or those databases which are match by particular regular expression. The syntax of mysqlhotcopy is given below.

mysqlhotcopy [options] [DATABASE name] [/path/TO/new_directory]

Single Database Backup

You require root permission to execute mysqlhotcopy command. Run the following command with root previlege and mysql root password to create backup files of ‘company’ database in ‘backups’ folder.

$ sudo mysqlhotcopy –u root –p mynewpassword company backups

By default, the backup file will create in /var/lib/mysql/backups.

Multiple Database Backup

You can also create a backup on a different location. Run the following command to create backup files of two databases named ‘company’ and ‘cakePHP’ in /home/ubuntu/backups.

$ sudo mysqlhotcopy –u root –p mynewpassword company cakedb /home/ubuntu/backups
–allowold –keepold

Part-2: MariaDB Restore

You can restore the data from the backup file by using mysql client. The general syntax for restoring database using mysql is given below.

mysql –u [username] –p [DATABASE name] < [backup filename].sql

Different types of restore examples are shown in the following examples.

Restore single table

You have to create a new database to restore table from a backup file. Run the command to create new database and select database.

> CREATE DATABASE newdb;
> USE newdb;
> exit

The backup filename is cakedb-employee.sql. Run the command to restore the table in newdb database.

$ mysql –u root –p newdb < cakedb–employee.sql

Now, check the table is imported or not.

> USE newdb;
> SHOW TABLES;

Restore single database

You can restore all tables from the backup file of any particular database. In the following command. All tables of company database will be restored in newdb database.

$ mysql –u root –p newdb < company–backup.sql

Now, log-in to server and check the database is imported or not.

> USE newdb;
> SHOW TABLES;

Restore all database

you can restore all databases of any MariaDB server to another MariaDB server from backup file. In that case, all existing databases will be overwritten by databases and tables of backup file. Run the following command to do this task.

$ mysql –u root –p < all–backup.sql

Creating backup files and restoring data from backup files are very necessary tasks for developing any type of database related projects. You can keep regular backup of your database files and restore the data when require by following above steps.