How to Backup and Restore MySQL Database on Linux

Introduction

It is imperative to make regular backups of all your data in case you lose it. Have you lost your MySQL database and are busy restoring a copy from the last backup?

In this tutorial, we introduce two easy ways to back up and restore your MySQL database.

Prerequisites

A Linux operating system
MySQL installed
An existing database
The mysqldump tool (should be included in your MySQL software)

Backup from the command line with mysqldump

The mysqldump client utility dumps a database, including the SQL statements needed to rebuild it.

By default, the dump file includes the SQL commands to restore tables and data.

To back up your MySQL database, the general syntax is.

sudo mysqldump -u [user] -p [database_name] > [filename].sql

• Replace [user] with your username and password (if needed).
• The [database_name] is the path and filename of the database.
• The > command specifies the output.
• [filename] is the path and filename you want to save the dump file as.

Other examples.

Performing a backup of the entire database management system.

mysqldump –all-databases –single-transaction –quick –lock-tables=false > full-backup-$(date +%F).sql -u root -p

To include more than one database in the backup dump file:

sudo mysqldump -u [user] -p [database_1] [database_2] [database_etc] > [filename].sql

How to Restore MySQL with mysqldump

Step 1: Create New Database

On the system that hosts the database, use MySQL to create a new database.

Make sure you’ve named it the same as the database you lost. This creates the foundation file that mysqldump will import the data into. Since the dump file has the commands to rebuild the database, you only need to create the empty database.

Step 2: Restore MySQL Dump

To restore a MySQL backup, enter:

mysql -u [user] -p [database_name] < [filename].sql

Make sure to include [database_name] and [filename] in the path.

Most likely, on the host, [database_name] can be in a root directory, so you may not need to add this path. Make sure you specify the exact path of the dump file you want to recover, including the server name if needed.

Backing up or restoring MySQL with phpMyAdmin

If you run phpMyAdmin, backing up and restoring your MySQL database is simple.

The export function is used as a backup, while the import function is used to restore.

Step 1: Create a MySQL Database Backup

1. Open phpMyAdmin. On the directory tree on the left, click the database you want to back up.

This should open the directory structure in the right-hand window. You’ll also notice that, in the directory tree on the left, all the assets under the main database are highlighted.

2. Click Export on the menu across the top of the display.

You’ll see a section called “Export Method.” Use Quick to save a copy of the whole database. Choose Custom to select individual tables or other special options.

Leave the Format field set to SQL, unless you have a good reason to change it.

3. Click Go. If you select Quick, your web browser will download a copy of the database into your specified downloads folder. You can copy that to a safe location.

Step 2: Clear the Old Database Information

It’s important to clear out old data before restoring a backup. If there’s any old data, it isn’t overwritten when you restore it. This can create duplicate tables, causing errors and conflicts.

1. Open phpMyAdmin, on the navigation pane on the left, choose the database you want to restore.

2. Click the check all box near the bottom. Then, use the drop-down menu labeled With selected to select Drop.

3. The tool should prompt you to confirm that you want to go forward. Click yes.

This will get rid of all the existing data, clearing the way for your restoration.

Step 3: Restore Your Backed up MySQL Database

In phpMyAdmin, the Import tool is used to restore a database.

1. On the menu across the top, click Import.

2. The first section is labeled File to import. A couple of lines down, there’s a line that starts with “Browse your computer,” with a button labeled Choose File. Click that button.

3. Use the dialog box to navigate to the location where you’ve saved the export file that you want to restore. Leave all the options set to default. (If you created your backup with different options, you can select those here.)

4. Click Go.

Conclusion

Now you know how to back up and restore a MySQL Database using phpMyAdmin or mysqldump.

Hopefully, you only need this article to show you how to export a database in phpMyAdmin. But even if you’re not that lucky, backing up or restoring is a fairly simple task.

If you want to learn more about how to perform regular MySQL backups, check out our article on setting up master-slave replication in MySQL. And to learn about different ways to analyze and repair your database, make sure to read our article on how to repair a MySQL database.