Today, data is part of what sustains the life of any organization. More often than not, an organization would store its data in its choice of database solution. Many database management systems are available; an excellent example is MySQL.
Whether storing employee details, customer and sales data, or internal documentation, backing up your database is non-negotiable. In MySQL, you can back up and restore databases using the mysqldump tool, and we’ll learn how to do it in this blog post.
Requirements
This tutorial will demonstrate how to back up and restore MySQL databases. As such, you must already have the following in place.
- A MySQL server installation. This tutorial uses MySQL on Ubuntu 22.04.
- A sample MySQL database with data. You’ll use this database for the backup and restore process. Don’t worry if you don’t have an existing database to play with. We have a section showing how to create the sample database.
Create Sample Databases and Tables
Before we begin, let’s create two sample databases with data for the demonstration. We will create two databases called catalog and sales.
Enter the mysql prompt.
# As the root user sudo mysql # As another MySQL user mysql -u <username> -p
Run the SQL code below to create the catalog database and books table:
## Create the catalog database CREATE DATABASE catalog; USE catalog; ## Create the books table CREATE TABLE books ( id INT NOT NULL AUTO_INCREMENT, title VARCHAR(255) NOT NULL, author VARCHAR(255) NOT NULL, PRIMARY KEY (id) );
Now, run the below code to insert ten records into the books table:
## Insert records into the books table
INSERT INTO books (title, author) VALUES
('The Great Gatsby', 'F. Scott Fitzgerald'),
('To Kill a Mockingbird', 'Harper Lee'),
('1984', 'George Orwell'),
('Animal Farm', 'George Orwell'),
('Pride and Prejudice', 'Jane Austen'),
('Jane Eyre', 'Charlotte Bronte'),
('Wuthering Heights', 'Emily Bronte'),
('The Catcher in the Rye', 'J.D. Salinger'),
('One Hundred Years of Solitude', 'Gabriel Garcia Marquez'),
('The Lord of the Rings', 'J.R.R. Tolkien');
Next, create another database called sales with a table called customers:
## Create the sales database CREATE DATABASE sales; USE sales; ## Create the customers table CREATE TABLE customers ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL, phone VARCHAR(20) NOT NULL, address VARCHAR(255) NOT NULL );
Now, insert these ten fictitious customer records into the customers table:
## Insert records into the customers table
INSERT INTO customers (name, email, phone, address) VALUES
('John Doe', 'john.doe@example.com', '555-1234', '123 Main St'),
('Jane Smith', 'jane.smith@example.com', '555-5678', '456 Broadway'),
('Bob Johnson', 'bob.johnson@example.com', '555-9876', '789 Park Ave'),
('Alice Lee', 'alice.lee@example.com', '555-5555', '555 Elm St'),
('David Kim', 'david.kim@example.com', '555-4444', '444 Pine St'),
('Emily Davis', 'emily.davis@example.com', '555-7777', '777 Oak St'),
('Tom Wilson', 'tom.wilson@example.com', '555-8888', '888 Maple Ave'),
('Mary Brown', 'mary.brown@example.com', '555-2222', '222 Cherry St'),
('Bill Jones', 'bill.jones@example.com', '555-1111', '111 Walnut St'),
('Samantha Green', 'samantha.green@example.com', '555-3333', '333 Cedar St');
In the end, you now have two databases with sample data. To view them:
select * from catalog.books; select * from sales.customers;
Finally, type exit and press Enter to exit the mysql prompt:
Now that we have two sample databases, tables, and data to work with, let’s start working with the MySQLDump examples.
MySQLDump Example 1: Backup Entire Databases
To back up an entire database, use the following command:
Replace username with your MySQL username, database_name with the database name you want to back up, and db_backup.sql with the name you want to give to the backup file. You will be prompted for your MySQL password.
mysqldump -u username -p database_name > db_backup.sql
In the below example, I used the root MySQL account to back up the catalog database to the ~/catalog_db_backup.sql file.
While backing up databases individually makes organizing easier, you can back up multiple or all databases in one go.
Use the –databases option followed by a list of databases to back up multiple databases in a single file. For example, the below command backs up the catalog and sales databases into the ~/catalog_sales_db_backup.sql file.
mysqldump -u root -p --databases catalog sales > ~/catalog_sales_db_backup.sql
If you wish to backup all databases on the MySQL server, use the –all-databases or -A argument.
The example below performs a mysqldump all databases backup to the ~/all_db_backup.sql file.
mysqldump -u root -p --all-databases > ~/all_db_backup.sql # OR mysqldump -u root -p -A > ~/all_db_backup.sql
MySQLDump Example 2: Backup the Database Schema without Data
You can use the –no-data option to back up a MySQL database schema without its data. This MySQLDump no data backup exports the database structure, including the tables and fields, to a file.
This type of mysql backup is useful when you create new databases with a predefined structure, like a template.
For example, run the following command to create the mysql no data backup of the sales database. The resulting backup is the ~/sales_schema_backup.sql file.
mysqldump -u root -p --no-data sales > ~/sales_schema_backup.sql
MySQLDump Example 3: Backup Database Tables
Sometimes, backing up specific tables instead of an entire database makes much more sense. Specific databases backup can be smaller in size and portable. Also, you can restore more granular data instead of restoring all tables from a database backup.
To create a database table backup, the syntax is as follows:
mysqldump [OPTIONS] database_name [table1 table2...] > table_backup.sql
For example, Run the below to run to back up the customers table from the sales database. The mysqldump table backup file is ~/sales_customers_table_backup.sql.
mysqldump -u root -p sales customers > ~/sales_customers_table_backup.sql
Restoring MySQL Database from MySQLDump Backup
Things can go wrong. And they do from time to time. A database admin may unintentionally drop a database or purge a table. And when it happens, you’d be glad you’ve made database backups with MySQLDump.
But to restore MySQL databases, you’ll do so inside the mysql shell.
In this example, let’s use the **sales** database for demonstration. Let’s first take a full backup of the **sales** database.
mysqldump -u root -p sales > ~/sales_db_backup.sql
Now that you’ve taken the backup, let’s simulate a data loss by dropping the sales database. Enter the mysql shell:
mysql -u root -p
Run the below commands. The first command deletes the sales database, and the second command lists all databases on the server.
drop database sales; show databases;
As you can see from the below result, the sales database has been deleted.
Now, let’s restore it. To do so, create a blank database with the same name.
create database sales;
Once the blank database has been created, restore the mysqldump backup database.
use sales; source ~/sales_db_backup.sql;
Finally, let’s list the contents of the customers table inside the sales database.
select * from customers;
Restoring MySQLDump Table Backup
In this example, we’ll restore a single table into a database. Specifically, we’ll restore the customers table to the sales database.
First, let’s make sure that the customers table is deleted.
use sales; drop table customers; show tables;
As you can see, there’s no table in the sales database anymore.
Now, let’s restore a prior MySQLDump table backup:
source ~/sales_customers_table_backup.sql;
Confirm the table has been restored.
show tables; select * from customers;
Conclusion
Database backup is an essential operational task that has to happen regularly whether you do it daily, weekly, or whenever; you’d be confident that you can recover from database loss at any time.
MySQLDump is uncomplicated enough to use that there’s really no reason for any DB admin not to use it. MySQLDump is flexible that it lets you back up entire databases or selected tables only.
