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

Note: To logically back up all databases, use option –all-databases:

# 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:

LOCK TABLES `user` WRITE;
/*!40000 ALTER TABLE `user` DISABLE KEYS */;
INSERT INTO `user` VALUES ('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y'
,'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'
,'Y','','','','',0,0,0,0,'',''),('localhost.localdomain','root','','Y','Y','Y','
Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','
Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'',''),('127.0.0.1','root','','Y','Y'
,'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'
,'Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'',''),('::1','root','','Y','Y'
,'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'
,'Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'',''),('localhost','','','N','
N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','
N','N','N','N','N','N','N','N','','','','',0,0,0,0,'',''),('localhost.localdomai
n','','','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N'
,'N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0,'',''),('localh
ost','mobius','*84BB5DF4823DA319BBF86C99624479A198E6EEE9','N','N','N','N','N','N
','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N
','N','N','N','','','','',0,0,0,0,'','');
/*!40000 ALTER TABLE `user` ENABLE KEYS */;
UNLOCK TABLES;

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.

Note: mysqldump requires at least the Select privilege for dumped tables, SHOW VIEW for dumped views, and TRIGGER for dumped triggers.

Useful Options

OPTION	DESCRIPTION
–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.

Note: The key benefit of this methodology is that it is very quick, and keeps the downtime of the database short. This is a great argument for putting the database files on a dedicated LVM partition.

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
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;

Note: This step is very important so that no new records are inserted into the database while the snapshot is created. Do not close this session. As soon as the client disconnects, this lock is lifted. The database must remain locked until the LVM snapshot is created.

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.

Note: Do not forget to delete the snapshot once it has been backed up.

# 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