Introduction

Something terrible has happened.  A MySQL database has been lost, and you’re scrambling to restore a copy from your last backup.

This guide will help you become the hero your database needs.  You’ll learn about the mysqldump command for restoring backups, (and for creating them, just in case). If you’ve got access to the phpMyAdmin tool, skip ahead to Part C.

The mysqldump client utility is included with the mysql-client, so it should already be installed.  It can be used to dump a database for copying, and it can generate output in comma-separated values (CSV), delimited text, or Extensible Markup Language (XML) formats.

Prerequisites

  • A Linux operating system
  • MySQL installed
  • An existing database

Tools/Software

  • Mysqldump utility (should be included with your MySQL software)
  • Terminal / Command Line
  • (optional) phpMyAdmin

Tutorial

Part A: Creating a MySQL database backup with mysqldump

The mysqldump command has several switches that you can use to specify where, what, and how you want your dump file processed.  By default, the dump file includes the sql commands to restore the tables and data.

In a terminal window, type the following:

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, with [filename] being the path and filename you want to save the dump file as.

There are many different options you can use along with the mysqldump command.  For instance, you might want to create a gzipped (compressed) file.  Use the following command:

[filename].sql.gz

Another option is to include more than one database in the backup dump file:

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

Other options include backing up all databases, or selecting specific tables.  Check the manual page for more information.

Part B: Restoring a MySQL backup dump file with mysqldump

Step 1: Decompress the file

If you created a zipped backup file, make sure to decompress it first.  In a terminal, type:

sudo gunzip [file_name].sql.gz

If you’ve used a different compression format, use the appropriate tool to extract it first.  (For example, if you used bz2 compression, you’d use the bunzip2 command to decompress.)

Step 2: Create the 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 3: Restore the backup database

In a terminal window, type:

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

Make sure that [databse_name] and [filename] include the path.  It’s likely that on the host machine, [database_name] can be in a root directory, so you may not need to add the path.  However, backups are often stored on separate machines.  Make sure that you specify the exact path for the dump file you’re restoring, including server name (if needed).

Part C: Using phpMyAdmin

If you’re running phpMyAdmin – and it’s free, so there’s no reason not to – backing up and restoring your MySQL database is simple.  The export function is used as a backup, and the import function is used to restore.

Step 1: Creating a backup

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.

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 Formatfield set to SQL,unless you have a good reason to change it.

Click Go.  If you selected 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.  This can create duplicate tables, causing errors and conflicts!

Open phpMyAdmin.

On the navigation pane on the left, choose the database you want to restore.

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

The tool should prompt you to confirm that you really 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 database

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

On the menu across the top, click Import.

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.

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

Click Go.

Conclusion

If you’re lucky, you only need this article to show you how to export a database in phpMyAdmin.  But even if you’re not that lucky, it’s a fairly simple task to back up or restore a MySQL database, even from a command line.