Export and import MySQL databases

Overview

This article will show you how to export a copy of your MySQL database, and how to import an SQL database backup file to your database server. This is useful for making personal backups of your site, and for importing backups from other servers.

READ ME FIRST

This article is provided as a courtesy. Installing, configuring, and troubleshooting third-party applications is outside the scope of support provided by (mt) Media Temple. Please take a moment to review the Statement of Support.

Instructions

Method 1 - phpMyAdmin

You can use phpMyAdmin to import and export your database, as long as it is relatively small (10MB or less). If your database is larger than 10MB, please skip to Method 2 - command line.

First, log into phpMyAdmin. For instructions, please see: Managing a MySQL database with phpMyAdmin.

Once you're logged into phpMyAdmin, follow the steps outlined below.

Export

1. In phpMyAdmin, select your database from the list on the left.
   
2. Click on Export from the top set of tabs.
   
3. Click the radio button for "Custom" to view all available options. Select the tables from the list that you would like to back up. If you want to back up the entire database, click Select All.
   
4. By default, your database name will be used to name the exported database file. You can change this by entering your desired file name in the File name template box. You can also select a compression option (such as "gzipped") if you want to compress the backup before downloading it from the server.
   
5. Make sure radio button for "structure and data" is selected from the "Dump Table" option list. This should be the default setting.
   In the Structure section, check the box for "Add DROP TABLE / DROP VIEW / PROCEDURE / FUNCTION / EVENT statement" if you want this export to be able to replace existing tables of the same name. If you want to merge this backup with another database, do not select this.
   
6. Click the Go button to save the file to your local computer.
   

Import

1. Make sure the database you need has already been created. If it has not, please first create the database:
   • How do I create a database?
   CAUTION:

   If you import a backup file to a database that already has content, it will replace the existing content.
2. In phpMyAdmin, select your database from the list on the left.
   
3. Click on "Import" from the top set of tabs.
   
4. Click on the "Choose File" button.
   
5. Browse to your local SQL file and click "Open." If it is a zipped file, be sure to unzip the file first.
6. Click the "Go" button towards the bottom of the page. Wait while your database imports. Depending on the size, this can take a few minutes.
   
   You should get a message like this:
   "Import has been successfully finished, X queries executed."
   If you instead receive an error, please try the command line method below.

Method 2 - command line

This method works for all database sizes, including very large ones.

You must be able to log into your server with SSH. Please see this article for details: Connecting via SSH to your server.

Export

1. Log into your server via SSH.
2. Use the command cd to navigate to a directory where your user has write access. For example (replace00000 with your site number):

   cd /home/00000/data/

3. Export the database by executing the following command:

   
   mysqldump --add-drop-table -h internal-db.s00000.gridserver.com -u username -p dbname > dbname.sql
   

   Once you execute this command, you will be prompted for your database password. Type in the password and hit enter. Your database will now start exporting. When it is done, you will see the command prompt again. If it is a large database, this may take a few minutes.
   NOTE:

   The following variables need to be replaced with your own information:

   • -u username specifies the database username.
   • -p designates that you will be entering a password.
     • Database users on the (gs) Grid-Service shows you how to add a database username and password. The username will be formatted like db00000_name.
   • dbname is the name of the database you are trying to export. On the (gs) Grid-Service, the database name will be formatted like db00000_database.
   • dbname.sql is the name you want to give your backup file, and can be whatever you want.
   • -h specifies a host. The internal host name must be specified on the (gs) Grid-Service. It will be formatted like internal-db.s00000.gridserver.com.
   • Omit the --add-drop-table argument if you plan to merge this backup with an existing database when you import it. This option means the backup will totally replace the old database when it is imported.
4. You can now download the resulting SQL file. Connect to your server with FTP, navigate to the directory where you created the dump file, and download it.
5. If you created the SQL file in a web-accessible directory, such as your html folder, you should delete it after downloading a copy. Otherwise, anyone can download it from the web.

If you get an error that looks like this:

Got Error: 1045: Access denied for user 'db00000@internal-db.s00000.gridserver.com' (using password: YES) when trying to connect

This means you have entered an incorrect password. Please retype it carefully, or reset your password via the AccountCenter Control Panel. See Database users on the (gs) Grid-Service for instructions.

Import

1. Make sure the database you need has already been created. If it has not, please first create the database:
   • How do I create a database?
   CAUTION:

   If you import a backup file to a database that already has content, it will replace the existing content.
2. Use FTP to upload your SQL file to your server. You can upload it to your default FTP directory. Or, see Step 1 in the "Export" instructions above for another suggestion. Alternately, you can use scp to upload your file via SSH.
3. Log into your server via SSH.
4. Use the command cd to navigate into the directory where you uploaded your backup file in Step 1. If you uploaded the backup to your data directory, go here (replace 00000 with your site number):

   cd /home/00000/data/

5. Import the database by executing the following command:

   mysql -h internal-db.s00000.gridserver.com -u username -p dbname < dbname.sql

   OR:

   mysql -h internal-db.s00000.gridserver.com -u username -p dbname -e 'source dbname.sql'

   Once you execute this command, you will be prompted for your database password. Type it in and hit enter. Your database will now import. It may take a few minutes if you have a large database. When the import is done, you will be returned to the command prompt.
   
   
   NOTE:

   • Variables are the same as in Step 3 from the Export section above. Please check Step 3 in the "Export" section to make sure you are correctly replacing the example code with your own information.

   • dbname.sql is the actual name of your SQL file.

   • If you have a gzipped backup of your database, you can use this line instead:

   gunzip < dbname.gz | mysql -h internal-db.s00000.gridserver.com -u username -p dbname

   You can enter in your own username, database name, and backup file name, as before. dbname.gz is the name of your gzipped backup file. Use "unzip" instead of "gunzip" for zipped files.
6. Remove the SQL file from your web-accessible directory, if you uploaded it to a public folder. Otherwise, anyone can download it from the web.

If you get an error that looks like this:

Got Error: 1045: Access denied for user 'db00000@internal-db.s00000.gridserver.com' (using password: YES) when trying to connect

You have entered an incorrect password. Please retype it carefully, or reset your password via the AccountCenter Control Panel. See Database users on the (gs) Grid-Service for instructions.

If you get an SQL error during the import, you can force it to finish by adding "-f" to the command, which stands for "force." For example:

mysql -f -h internal-db.s00000.gridserver.com -u username -p dbname -e 'source dbname.sql'

This can help you finish an import if you have a few corrupt tables, but need to get the database as a whole imported before you do anything else.

Additional Resources

To automate your database backups, you may want to consider following this guide written especially for (mt) Media Temple database backups. (mt) Media Temple is not affiliated with this third-party website and takes no responsibility for its content.