Databases contains most of a company’s critical data. Therefore, it is important to periodically take backups of the databases to recover data after several types of failures such as operating system crash, hardware problem, database corruption, data poisoning, etc. Different Database Management Systems provides different types of backup methods.
In MariaDB Database, there are two methods to backup a MariaDB database.
1) Logical backups export information in records in plain text files.
2) Physical backups consist of copies of files and directories that store content.
In this article, we will take a Logical backup and use it to perform recovery. Please read my previous post ‘Install and configure MariaDB’, because we will use the same environment in this article.
Take Logical Backup of MariaDB Database:
Suppose we have MariaDB database dev that contains a table name items. Let’s have a look.
# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 13 Server version: 5.5.35-MariaDB MariaDB Server Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | dev | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.00 sec) MariaDB [(none)]> use dev; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [dev]> show tables; +---------------+ | Tables_in_dev | +---------------+ | items | +---------------+ 1 row in set (0.00 sec) MariaDB [dev]> select * from items; +------+-----------+ | id | name | +------+-----------+ | 1 | Pencil | | 2 | Eraser | | 3 | Sharpener | | 4 | Copy | +------+-----------+ 4 rows in set (0.00 sec)
To take backup of MariaDB database, we have mysqldump command.
# mysqldump -u root -p dev > ~/dev_backup.dump Enter password:
Let's have a look at the contents of the dump file.
# cat ~/dev_backup.dump
-- MySQL dump 10.14 Distrib 5.5.35-MariaDB, for Linux (x86_64)
--
-- Host: localhost Database: dev
-- ------------------------------------------------------
-- Server version 5.5.35-MariaDB
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `items`
--
DROP TABLE IF EXISTS `items`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `items` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `items`
--
LOCK TABLES `items` WRITE;
/*!40000 ALTER TABLE `items` DISABLE KEYS */;
INSERT INTO `items` VALUES (1,'Pencil'),(2,'Eraser'),(3,'Sharpener'),(4,'Copy');
/*!40000 ALTER TABLE `items` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2018-07-20 11:10:06
We have successfully took logical backup of our database.
Recover MariaDB Database from Logical Backup:
To create a recovery scenario, we have to drop our database dev.
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | dev | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.01 sec) MariaDB [(none)]> drop database dev; Query OK, 1 row affected (0.07 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.00 sec) MariaDB [(none)]>
We have dropped our database dev. Now, we will recover the database from our backup ~/dev_backup.dump.
First, we have to create a database with same name and use the source command to import the backup file.
MariaDB [(none)]> create database dev; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> use dev; Database changed MariaDB [dev]> source ~/dev_backup.dump Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 4 rows affected (0.06 sec) Records: 4 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) MariaDB [dev]> show tables; +---------------+ | Tables_in_dev | +---------------+ | items | +---------------+ 1 row in set (0.00 sec) MariaDB [dev]> select * from items; +------+-----------+ | id | name | +------+-----------+ | 1 | Pencil | | 2 | Eraser | | 3 | Sharpener | | 4 | Copy | +------+-----------+ 4 rows in set (0.00 sec)
We have successfully recovered our MariaDB database from logical backup.