MySQL Database Backup and Restore Techniques

Learn essential MySQL database backup and restore techniques. Our guide covers methods for creating backups, restoring data, and protecting your MySQL database from failures and data loss. #centlinux #linux #mysql

Why Database Backups are Important?

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.

MySQL Database Backup and Restore Techniques

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.

Recommended Training: The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert from Colt Steele

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

Display list of available databases.

show databases;

Output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| dev                |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

Display list of tables in dev database.

use dev;
show tables;

Output:

+---------------+
| Tables_in_dev |
+---------------+
| items         |
+---------------+
1 row in set (0.00 sec)

Display content of items table.

select * from items;

Output:

+------+-----------+
| 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

Let’s have a look at the contents of the dump file.

cat ~/dev_backup.dump

Output:

-- 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.

Must Read: How to enable MySQL Slow Query Log

Restore MariaDB Database from Logical Backup

To create a recovery scenario, we have to drop our database dev.

drop database dev;

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.

create database dev;
use dev;
source ~/dev_backup.dump

After restore, check content of items table.

select * from items;

Output:

+------+-----------+
| 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.

Frequently Asked Questions (FAQs)

1. Why is backing up a MySQL database important?

Backups protect your data from accidental loss, corruption, or system failures. They allow you to restore your database to a previous state if something goes wrong.

2. What are the common methods to back up a MySQL database?

The most common methods are:

• Logical backups (e.g., mysqldump) – exports SQL statements to recreate the database.
• Physical backups – copies database files directly.
• Automated tools (e.g., MySQL Enterprise Backup, Percona XtraBackup).

3. How do I restore a MySQL database from a backup?

You can restore using:

• Logical backups – by importing the SQL file using mysql command-line tool.
• Physical backups – by replacing database files in the MySQL data directory (requires server downtime).

4. Can I back up a MySQL database without stopping the server?

Yes, using tools like mysqldump (with proper locking options) or third-party tools like Percona XtraBackup, which allow hot backups with minimal downtime.

5. How often should I back up my MySQL database?

It depends on how often your data changes. For critical databases, daily backups are common, with additional incremental backups for high-frequency updates. Always test your backups to ensure they work!

Kali Linux Penetration Testing Bible

(465288)

$29.30 (as of May 31, 2025 17:49 GMT +00:00 – More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)

Final Thoughts

Congratulations on exploring MySQL database backup and restore techniques! By mastering these techniques, you can ensure the safety, integrity, and availability of your MySQL databases. Whether you’re performing routine backups or preparing for data recovery, the methods outlined in this guide will help you maintain a robust database management strategy.

Need expert AWS and Linux system administration? From cloud architecture to server optimization, I provide reliable and efficient solutions tailored to your needs. Hire me on Fiverr today!