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.
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:
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.
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.
cakedb-employee.sql
Now, check backup file is created or not using ‘ls’ command.
Single database backup:
Run the following command to create backup file for an existing database named ‘company’.
company-backup.sql
Now, check backup file is created or not using ‘ls’ command.
All databases backup:
Run the following command to create backup of all existing databases of MariaDB server.
all-backup.sql
Now, check backup file is created or not using ‘ls’ command.
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.
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.
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.
--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.
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.
> USE newdb;
> exit
The backup filename is cakedb-employee.sql. Run the command to restore the table in newdb database.
Now, check the table is imported or not.
> 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.
Now, log-in to server and check the database is imported or not.
> 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.
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.
