Support / KnowledgeBase

 
Search the KnowledgeBase Search

Export and import MySQL databases

  • Applies to: All DV, Grid

  • Difficulty: Medium

  • Time needed: 20 minutes

  • Tools needed: SSH

 
  • Applies to: Grid
    • Difficulty: Medium
    • Time Needed: 20
    • Tools Required: SSH
  • Applies to: All DV
    • Difficulty: Medium
    • Time Needed: 20
    • Tools Required: SSH

CloudTech_ProductPage_217x57

CloudTech Can Help!

If you’re having trouble with the steps in this article, additional assistance is available via CloudTech, our premium services division. Our expert engineers can import or export your MySQL database quickly and painlessly, . For more information on what CloudTech can do for you, please click here.

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.

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 relatively small (10MB or less) databases easily.

NOTE:

If your database is larger than 10MB, you may receive a timeout notice. The timeout will require you to resubmit the same file to continue the import. If you have a large database and want to avoid this, you may 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.

    phpMyAdmin_export_figure_1

  2. Click on Export from the top set of tabs.

    phpMyAdmin_export_figure_2

  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.

    phpMyAdmin_export_figure_3

  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.

    phpMyAdmin_export_figure_4

  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.

    phpMyAdmin_export_figure_5

  6. Click the Go button to save the file to your local computer.

    phpMyAdmin_export_figure_6

Import

  1. Make sure the database you need has already been created. If it has not, please first create the 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.

    phpMyAdmin_import_figure_1

  3. Click on "Import" from the top set of tabs.

    gs_phpMyAdmin_import_figure_2

  4. Click on the "Choose File" button.

    gs_phpMyAdmin_import_figure_3

  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.

    gs_phpMyAdmin_import_figure_4

    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 (replace 00000 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 Grid 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 Grid, 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 Grid. 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 Grid for instructions.

Import

  1. Make sure the database you need has already been created. If it has not, please first create the 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 Grid 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.

Instructions

Method 1 - phpMyAdmin

Export

  1. Select your database from the list on the left.

    phpMyAdmin select database

  2. Click on "Export" from the top set of tabs.

    Phpmyadmin export.png

  3. 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."

    Phpmyadmin export select all.png

  4. Make sure both the "Structure" and "Data" boxes are selected on the right. They should be, by default.

    In the Structure section, check the "Add DROP TABLE / DROP VIEW" box 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.

    Phpmyadmin export structure data drop table.png

  5. Check the "Save as file" box. Also select a compression option (such as "gzipped") if you want to compress the backup before downloading it from the server.

    Phpmyadmin export save file.png

  6. Click the "Go" button, and, at the prompt, save the file to your local computer.

    Phpmyadmin go.png

Import

  1. Make sure the database you need has already been created. If it has not, please first create the 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.

    phpMyAdmin select database

  3. Click on "Import" from the top set of tabs.

    Phpmyadmin import.png

  4. Click on the "Browse" button next to "Location of the text file."

    Phpmyadmin import browse.png

  5. Browse to your local SQL file and click "Open." If it is a zipped file, please unzip the file first.
  6. Click the "Go" button at the bottom. Wait while your database imports. Depending on the size, this can take a few minutes.

    Phpmyadmin go.png

    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.

Export

  1. Log into your server via SSH.
  2. Use the command cd to navigate to a directory where your user has write access. Example:
    cd /var/www/vhosts/example.com/httpdocs
  3. Export the database by executing the following command:
    mysqldump --add-drop-table -u admin -p`cat /etc/psa/.psa.shadow` 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 admin specifies the database username.
      • Username is "admin" and the password is a hashed version of your Plesk admin password.
    • dbname is the name of the database you are trying to export.
    • dbname.sql is the name you want to give your backup file, and can be whatever you want.
    • 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. Remove the SQL file from your web-accessible directory, if you created it in 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 'admin@example.com' (using password: YES) when trying to connect

This means you have entered an incorrect password. Please retype it carefully, or reset your password in the AccountCenter. See How can I change my Plesk admin password? for instructions.

Import

  1. Use FTP to upload your SQL file to your server. You can upload it to your default FTP directory. See Step 1 in the "Export" instructions above for another suggestion. Alternately, you can use scp to upload your file via SSH.
  2. Log into your server via SSH.
  3. Use the command cd to navigate into the directory where you uploaded your backup file in Step 1. If you uploaded the backup into your public htttpdocs directory, go here:
    cd /var/www/vhosts/example.com/httpdocs/
  4. Import the database by executing the following command:
    mysql -u admin -p`cat /etc/psa/.psa.shadow` dbname < dbname.sql

    OR:

    mysql -u admin -p`cat /etc/psa/.psa.shadow` 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 -u admin -p`cat /etc/psa/.psa.shadow` 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.

  5. 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 'admin@example.com' (using password: YES) when trying to connect

You have entered an incorrect password. Please retype it carefully, or reset your password in the AccountCenter. See How can I change my Plesk admin password? 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 -u admin -p`cat /etc/psa/.psa.shadow` dbname < 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.

 

Continue