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 IF NOT EXISTS, 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 the 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 that show the 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 the 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 with the logical backup is that they always mean the full table scan.

If everything is set up correctly, InnoDB can copy all the contents of a table into the buffer pool during the table scan. Such behavior causes problems of the logical backup in the case of the 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.

mysqlhotcopy

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

The basic usage of 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 snapshots, 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 your backup location
  • From a shell, unmount the snapshot using:

    umount snapshot

SQLBackupAndFTP – Backup Tool

If you use Windows then SQLBackupAndFTP 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 the SQLBackupAndFTP tool to your MariaDB. To connect SQLBackupAndFTP to your database press “Connect to Database Server” and choose the way to connect to your MariaDB. You can connect using “MySQL Server (TCP/IP)” or “MySQL Server (phpMyAdmin)”. Enter your credentials and press “Test Connections” to check the connections.
   
2. Select all databases that should be backed up
   
3. Choose a place where you prefer to store your MariaDB database backups. To do it click on the “plus” at the “Store backups in selected destinations” section and select a destination in the opened window.
   
4. Create a MariaDB database backup schedule. To do it turn on that option at the “Schedule backups” section, the advanced settings can be founded by clicking the “Gear” button.
   
5. The backup job is ready and will run according to the specified schedule. If you need to make a backup immediately just press “Run Now”.
   

That was a short tutorial on how to backup MariaDB, if you need to restore the backups that were made via SQLBackupAndFTP then just click on the dots opposite the backup you need to restore at the right pan and choose “Restore from Backup…”.

Also, you can check how to restore the backups you have via SQLBackupAndFTP restore job at the following blog post.