MariaDB Backup and Restore Overview

In this article, we are going to discuss some popular ways of MariaDB backup and restore. And the first question you can face using MariaDB is what is the difference between Logical and Physical backups?

The Difference between logical and physical backups

While the logical MariaDB backup is just a set of  SQL statements such as CREATE TABLE, CREATE DATABASE and INSERT which are necessary to restore the data, the physical MariaDB backup implies a process of copying of physical directories or data files on a disk.

Please consider the following facts about logical and physical backups:

  1. All logical database backups are more flexible than physical backups. This makes it possible to restore the database on other MariaDB versions, hardware configurations, or even on another DBMS. The physical backup cannot be imported on different DBMS, different hardware, or potentially even a different MariaDB version.
  2. The logical database backup is large in size than the same physical backup.
  3. To restore logical database backup takes more time than to restore the same physical backup.
  4. The logical backup doesn’t contain configuration files and  the log files as its part
  5. It is easier to restore the logical MariaDB backup than the physical one since the first is written in a human-readable format, unlike the binary physical MariaDB backup.

MariaDB Backup and Restore – the Ways how to Perform it

There are a lot of ways of MariaDB backup and restore, let’s look at some of them.

Mysqldump Tool

First, we need to say that mysqldump utility creates logical backups. In other words, it creates a SQL script that, being executed, re-creates your database.

Using mysqldump tool is suitable if you need to backup a relatively small database. But if your database is quite big the backup file might be too large and it will take more time to restore.

It is also important to remember that mysqldump can dump the data not only into SQL format but also into others, such as XML or CSV. It means that data can be easily imported into another version of MySQL, MariaDB or even different DBMS.

Here are a few simple examples which show MariaDB backup and restore process. Use the following syntax to backup a single database:

shell> mysqldump db_name > backup-file.sql

And to restore a logical backup of MariaDB database use the next command:

shell> mysql db_name < backup-file.sql
 InnoDB logical backup specifics

The InnoDB engine uses the buffer pool, and it is crucial for its performance. This buffer stores indexes and data from its tables in memory. It’s important that the buffer contains the most frequently requested information if all InnoDB data do not fit into the memory. So all data that you have accessed the last time is a candidate for the insertion into the buffer pool. If the configurations are incorrect, then InnoDB can copy all contents of a table into the buffer pool during the table scan. So, the problem of the logical backup is that they always mean the full table scan.

If everything set up correctly, InnoDB can copy all contents of a table into the buffer pool during the table scan. Such behaviour causes problems of the logical backup in case of InnoDB engine as it always evokes the full table scan.

But you can avoid this issue in the following way. All you need to do is to increase the value of the innodb_old_blocks_time system variable that represents the time in milliseconds that must pass before a recent page that was accessed is put into the “new” sublist in the buffer pool.  All data that have been accessed once should remain in the “old” sublist. It means that such data will be excluded from the buffer pool soon.


Being a Perl script, mysqlhotcopy simply copies database files into another location on the same machine. It runs on UNIX and suitable for MyISAM and ARCHIVE tables only.

The basic usage os mysqlhotcopy is as simple as

shell> mysqlhotcopy db_name_1 ... db_name_n /path/to/new_directory

Note, though, that to backup your tables successfully this utility requires the following rights and privileges :

    • Read access to the files for the tables that you are backing up
    • SELECT privilege for those tables
  • RELOAD and LOCAL TABLES privileges

To restore tables that were backed up with mysqlhotcopy you just need to copy them back to the original location and restore their owner information:

service mysqld stop
cp -r <backup_folder>/* <mysql_data_folder (usually /var/lib/mysql/<db>)>
chown -R mysql:mysql /var/lib/mysql/*
service mysqld start

Filesystem snapshots

If your filesystem supports snapshot, only a few filesystems support snapshots. During the snapshot, the table must be locked. To make a snapshot follow these simple steps:

  • Execute FLUSH TABLES WITH READ LOCK from the MySQL Client. Note, that the client must remain open.
  • From a shell, execute
    mount vxfs snapshot
  • The client can execute UNLOCK TABLES.
  • Copy the snapshot files to you backup location
  • From a shell, unmount the snapshot using
  • umount snapshot

MySQLBackupFTP – Backup Tool

If you use Windows then MySQLBackupFTP is probably the easiest way to backup MariaDB automatically. It’s a GUI utility that uses mysqldump to created logical backups, but does it in a convenient and simple way.

All you need is to create a backup job, which will backup your MariaDB automatically. Follow these simple steps.

  1. Download, install and connect  MySQLBackupFTP tool to your MariaDB. To connect MySQLBackupFTP to your database press “Connect to MySQL Server” and choose the way to connect to your MariaDB. You can connect using “TCP / Ip (default method)” or “phpMyAdmin”. Enter your credentials and press “Test MySQL Connections” to check the connections. MariaDB Backup and Restore
  2. Select all database which you need to backup from the list on the left side.MariaDB Backup and Restore
  3. Choose a place to store your MariaDB database backups. To do it click “Add backup destination” link and select a destination in the opened window.MariaDB Backup and Restore
  4. Create MariaDB database backup schedule. Press “Schedule this job” and then click “Advanced backup schedule”. In the “Advanced Backup Schedule”  window  create a backup schedule. To apply all settings press “Save & Close”.MariaDB Backup and Restore
  5. The backup job is ready. You can run it according to the schedule by clicking “Save” button, or if you need to make a backup immediately press “Run Now”.MariaDB Backup and Restore

Leave a Reply

Your email address will not be published.