RHEL7: Backup and restore a MariaDB database.

Note: This is an RHCE 7 exam objective.

Prerequisites

First, you need to install a MariaDB database.

Then, you need to create a simple database schema, otherwise you will have nothing to back up.

Backup Procedure

To back up the database called test, type:

# mysqldump --user=root --password="your password" --result-file=test.sql test

Note1: You can specify one or several databases at the end of the line.
Note2: The –single-transaction option can also be used to back up one or several databases in a single transaction.

You get the following content in the test.sql file:

-- MySQL dump 10.14  Distrib 5.5.35-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- 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 `addresses`
--

DROP TABLE IF EXISTS `addresses`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `addresses` (
  `name` varchar(20) DEFAULT NULL,
  `address` varchar(40) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `addresses`
--

LOCK TABLES `addresses` WRITE;
/*!40000 ALTER TABLE `addresses` DISABLE KEYS */;
/*!40000 ALTER TABLE `addresses` 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 2014-07-16 12:42:41

Restore Procedure

Now, to restore the same content into the database, type:

# mysql --user=root --password="your password" test<test.sql

Note1: The content of the SQL file (here called test.sql) automatically deals with table re-creations.
Note2: If you restore the database in a server where it doesn’t already exist, you will need to create it before (the SQL file doesn’t re-create the database itself): mysql> create database test;

Additional Resources

Although mysqldump is the tool to use during the exam because it is directly available, it is not the only solution to back up your MySQL/MariaDB database (see additional details here).
Beyond the exam objectives, there are several ways to migrate MySQL databases from one server to another.
You can also be interested in this architecture reference document about deploying MySQL databases on Red Hat Ceph storage.
Additional precautions can be needed when loading big MySQL databases.
The Percona website provides an interesting article about Restoring MySQL Logical Backup at Maximum Speed.