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
Table of Contents
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 for You: Complete SQL and Databases Bootcamp
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.
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.
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.
If you are new to Linux and facing difficulty in working at Linux Bash prompt. We recommend that, you should read The Linux Command Line, 2nd Edition: A Complete Introduction by William Shotts.
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.
If you found this guide useful and need professional help with MySQL database management, backup strategies, or recovery solutions, feel free to check out my Fiverr profile.