Mysqldump Backup and Restore for Beginner

MySQL is a great database system that is very stable, reliable and popular among website owners. Even it’s a stable database, it doesn’t mean that it is crash free. So, we still need to do some prevention tasks such as backup. There are many ways of backup MySQL database. We can use third party MySQL backup software such as iPerius Backup, Uranium Backup and many more. But, actually MySQL already equipped with built in backup and restore feature, called mysqldump. This tutorial is going to show you some example how to use mysqldump backup and restore feature. 

How to Backup MySQL database using mysqldump

First, we will learn how to backup database using mysqldump. We can select a single database on the backup but also we can backup the entire databases in a single backup file. mysqldump is a great, powerful and effective tool to backup MySQL database. It will create .sql file that includes DROP table, Create table and INSERT INTO sql statement of the source database. So, it will be easier to restore the entire database in the future. 

In order to take backup using mysqldump, you will need MySQL username and password that has privileges to perform the backup task. For example, I use my root user and password: 12345678.

A. Backup a single database using mysqldump

The following command will backup database “test” and save the backup as “test_backup.sql”

mysqldump -u root -p12345678 test > test_backup.sql

The general syntax for backup single database using mysqldump is:

mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

You can also provide a complete path for the backup destination for example:

mysqldump -u root -p12345678 test > /tmp/test_backup.sql

B. Backup the entire databases

The following command will backup all databases in MySQL server

mysqldump -u root -p12345678 --all-databases > all_databases_backup.sql

How to restore MySQL Database using mysqldump

To restore the database, you will need to create the database first. Otherwise you won’t be able to restore the database from the dump file. 

Create the database. See my example below to create test database on the MySQL server.

dhani@debian-vm:/tmp/backup$ mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.1.26-MariaDB-0+deb9u1 Debian 9.1

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create database test;
Query OK, 1 row affected (0.00 sec)

Now restore the database with this command

mysqldump -u root -p12345678 test < test_backup.sql

If the destination database does not exist yet, you will get the following error

dhani@debian-vm:/tmp/backup$ mysqldump -u root -pgamblis test < test_backup.sql
-- MySQL dump 10.16  Distrib 10.1.26-MariaDB, for debian-linux-gnu (x86_64)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version       10.1.26-MariaDB-0+deb9u1

/*!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 utf8mb4 */;
/*!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 */;
mysqldump: Got error: 1049: "Unknown database 'test'" when selecting the database

So, we’ve learned how to perform mysqldump backup and restore via command line. There are many options you can play with. Please visit the following page for more information and examples:

• Backup and Restore MySQL Database using mysqldump command

Thanks for reading this mysqldump backup and restore tutorial. Please consider share this article if you think it’s useful.