RHEL7: Backup and restore a MariaDB database.

Share this link

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. You can also have a look at the mydumper tool.
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.

1 Star2 Stars3 Stars4 Stars5 Stars (4 votes, average: 3.25 out of 5)
Loading...

Leave a Reply

2 Comments on "RHEL7: Backup and restore a MariaDB database."

Notify of
Sort by:   newest | oldest
ky13
Member
ky13

Another way to backup a database (or several at once):

mysqldump -u UserName -pYourPW –databases db1 db2 > output.sql

wpDiscuz

RHCSA7: Task of the day

Allowed time: 5 minutes.
Create a user called tom. Create a directory named /private. Use an acl to only allow access (rwx) to tom to the private directory.

RHCE7: Task of the day

Allowed time: 5 minutes.
Set up time synchronization with default configuration.

Poll for favorite RHEL 7 book

What is your favorite RHEL 7 book to prepare RHCSA & RHCE exams?

View Results

Loading ... Loading ...

Poll for most difficult RHCSA 7 topic

What do you think is the most difficult RHCSA 7 topic?

View Results

Loading ... Loading ...

Poll for most difficult RHCE 7 topic

What do you think is the most difficult RHCE 7 topic?

View Results

Loading ... Loading ...