How to Backup and Restore MariaDB database

Creating a Backup

It is very important to back up MariaDB databases, and databases in general. The database often contains most of a company’s mission-critical data (sales, clients, etc.). Performing backups enables a system administrator to recover data after several types of events:

  • Operating system crash
  • Power failure
  • Filesystemcrash
  • Hardwareproblem
  • Security breach
  • Databasecorruption
  • Data poisoning

There are two ways to back up MariaDB:

  • Logical
  • Physical (raw)

Logical Backups

Logical backups export information and records in plain text files, while physical backups consist of copies of files and directories that store content. Logical backups have these characteristics:

  • The database structure is retrieved by querying the database.
  • Logical backups are highly portable, and can be restored to another database provider (such as Postgres) in some cases.
  • Backup is slower because the server must access database information and convert it to a logical format.
  • Performed while the server is online.
  • Backups do not include log or configuration files.

Physical Backups

Physical backups have these characteristics:

  • Consist of raw copies of database directories and folders.
  • Output is more compact.
  • Backups can include log and configuration files.
  • Portable only to other machines with similar hardware and software.
  • Faster than logical backup.
  • Should be performed while the server is offline, or while all tables in the database are locked, preventing changes during the backup.

Performing a Logical Backup

A logical backup can be done with the mysqldump command:

# mysqldump -u root -p inventory > /backup/inventory.dump

Here, root -> User name to connect to MariaDB for backup -p -> Prompt for password for this user inventory -> Selected database for backup /backup/inventory.dump ->Backup file

# mysqldump -u root -p --all-databases > /backup/mariadb.dump

A dump of this kind will include the mysql database, which includes all user information.

The output of a logical backup will appear to be a series of SQL statements. As an example, here is a snippet from a dump of the mysql database:

/*!40000 ALTER TABLE `user` DISABLE KEYS */;
INSERT INTO `user` VALUES ('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y'
/*!40000 ALTER TABLE `user` ENABLE KEYS */;

Notice the encrypted password for mobius is easily visible, so take care with storage of backups of this kind. Also, individual tables are locked and unlocked by default as they are read from during a logical backup.

Useful Options

–add-drop-table Tells MariaDB to add a DROP TABLE statement before each CREATE TABLE statement.
–no-data Dumps only the database structure, not the contents.
–lock-all-tables No new record can be inserted anywhere in the database while the copy is finished. This option is very important to ensure backup integrity.
–add-drop- database Tells MariaDB to add a DROP DATABASE statement before each CREATE DATABASE statement.

Performing a Physical Backup

Several tools are available to perform physical backups, such as ibbackup, cp, mysqlhotcopy, and lvm. A MariaDB physical backup task can use the known benefits of LVM snapshots. The following process will back up MariaDB using LVM.

Verify where MariaDB files are stored:

# mysqladmin variables | grep datadir
| datadir      |   /var/lib/mysql/     |

Verify which logical volume hosts this location:

# df /var/lib/mysql
Filesystem                  1K-blocks   Used      Available   Use%   Mounted on
/dev/mapper/vg0-mariadb     51475068    7320316   41516928    15%    /var/lib/mysql

This shows that the volume group is vg0 and the logical volume name is mariadb. Verify how much space is available for the snapshot:

# vgdisplay vg0 | grep Free
Free PE / Size      15321 / 61.29 GB

This shows that 61.29 GB are available for a snapshot. Connect to MariaDB, flush the tables to disk, and lock them (alternately, shut down the mariadb service):

# mysql -u root -p

In another terminal session, create the LVM snapshot:

# lvcreate -L20G -s -n mariadb-backup /dev/vg0/mariadb

The LVM snapshot must be large enough to contain any data that changes in the database from the moment the database is unlocked until the backup is complete. If it is not, the snapshot will become invalid when its storage fills up and the backup will fail.

In the original MariaDB session, unlock the tables (or, bring the mariadb service up):

MariaDB [(none)]> UNLOCK TABLES;

The snapshot can now be mounted at an arbitrary location:

# mkdir /mnt/snapshot
# mount /dev/vg0/mariadb-backup /mnt/snapshot

From here, any standard file system backup can be used to store a copy of /var/lib/mysql as mounted under /mnt/snapshot.

# umount /mnt/snapshot
# lvremove /dev/vg0/mariadb-backup

Restoring a Backup

Logical restore

A logical restore can be done with the command mysql:

# mysql -u root -p inventory < /backup/mariadb.dump

Here, root - User to connect with to restore the MariaDB backup (generally root or some other superuser) -p - Password for this user inventory - Selected database for restore backup /backup/mariadb.dump - Backup file

Physical restore

Verify where MariaDB files are stored:

# mysqladmin variables | grep datadir
|   datadir    |     /var/lib/mysql/      |

To do a physical restore, the mariadb service must be stopped:

# systemctl stop mariadb

Remove the actual content:

# rm -rf /var/lib/mysql/*

From here, any standard file system restore can be used to restore a copy from backup to /var/lib/mysql.