How do i export data from phpmyadmin?

Last modified: Aug 26, 2020

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.

Advanced Support can help!

If you're having trouble with the steps in this article, additional assistance is available via Advanced Support, our premium services division. For more information on what Advanced Support can do for you, please click here.

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

If you require a refresher on accessing phpMyAdmin, feel free to review the documentation below:

• Managing a MySQL database with phpMyAdmin
• Managing a MySQL database with phpMyAdmin
• Managing a MySQL database with phpMyAdmin
• Managing a MySQL database with phpMyAdmin
• Managing a MySQL database with phpMyAdmin

Export

NOTE:
If your database is larger than 10MB, you may receive a timeout notice. If you experience this error due to your database size, you will want to use the command line method below.

1. Connect to your database using phpMyAdmin.
2. From the left-side, select your database.
   
   
3. Click the Export tab at the top of the panel.
   
   
   
   
4. Select the Custom option.
   
   
5. You can select the file format for your database. In this example, we will use the default SQL option.
   
   
6. Click Select All in the Export box to choose to export all tables.
   
   
7. At this stage make note of the WordPress prefix. It will be needed when you are adding the database to your new WordPress Hosting service. In this example it is wp_.
8. Select the check box, Add DROP TABLE / VIEW / PROCEDURE / FUNCTION.
   
   
   
   
9. If you wish to change the name of your backup database, you can do so in the File name template section .
   
   
   
   
10. Click Go.
11. This will export your database content in the form of an .sql file to your local computer.

Import

CAUTION:
If you import a backup file to a database that already has content, it will replace the existing content.

1. If you are planning to import to a brand new database, you will want to ensure that the database is created first. If you require a refresher on how to accomplish this, please view the following: How do I create a database? How do I create a database? How do I create a database? How do I create a database?
2. On the left-side, select your database.
   
   
3. Click on Import from the top set of tabs.
   

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

5. Browse to your local database 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.
7. Once completed, you should get a success message similar to the following.
   
   

wp-config

If you have a WordPress site, you may need to update the the table prefix. This is done by editing the wp-config file that exists in your site root. You will need to look for a line that looks similar to the following and modify it.

$table_prefix = 'wp_xxxxxxxxxxxx_'; 

You will need to replace the existing table prefix in the wp-config.php file with the one from your previous host. For example, if your tables looks similar to the following: wp_ary2vyj2rw_options then you will want to update your tables to:

$table_prefix = 'wp_ary2vyj2rw_'; 

Once the content is uploaded, the database is imported, and the database table prefix in the wp-conf file is updated, your site should be migrated.

Command Line

Larger databases may require you to export/import directly from the command line.

Exporting a MySQL database

1. Connect to your server using SSHSSH.

2. Navigate to the /html directory.

cd html

3. In order to export your database, we'll need information that is located in wp-config.php.

grep 'DB_' wp-config.php

• Make a note of your database name, username, password, hostname, and four digit port number. The port number is located at the end of the hostname.

define('DB_NAME', 'd92b2696256785');
/** MySQL database username */
define('DB_USER', 'd92b2696256785');
/** MySQL database password */
define('DB_PASSWORD', 'V|vU2QR,');
/** MySQL hostname */
define('DB_HOST', 'd92c2896243585.db.2696146.hostedresource.com:3315');

4. Use mysqldump to create a copy of the database.

 mysqldump -h host_name -P port_number -u user_name -p database_name > name_of_backup.sql

• If we use the example wp-config.php file from above, our command will look like this:

mysqldump -h d92c2896243585.db.2696146.hostedresource.com -P 3315 -u d92b2696256785 -p d92b2696256785 > DBbackup.sql

• If you would like to export the backup to a location other than /html, specify the file path at the end:

> path/to/DBbackup.sql

If you do not specify a directory when creating your database backup, the SQL file will be placed in the /html folder. If you choose to do this, make sure that you move the SQL backup soon after creating. /html is a web accessible directory and your database backup may be downloaded by anyone.

This will create a copy of your WordPress database and place it in the specified directory. You may then use SCP or FTP to download your database.

Importing a MySQL database

1. Upload your database to the server using FTP.

2. In order to import your database backup, we'll need information that is located in wp-config.php.

grep 'DB_' wp-config.php 

• Make a note of your database name, username, password, hostname, and four digit port number. The port number is located at the end of the hostname.

define('DB_NAME', 'd92b2696256785');
/** MySQL database username */
define('DB_USER', 'd92b2696256785');
/** MySQL database password */
define('DB_PASSWORD', 'V|vU2QR,');
/** MySQL hostname */
define('DB_HOST', 'd92c2896243585.db.2696146.hostedresource.com:3315');

3. Use mysql to import your database.

 mysql -h host_name -P port_number -u user_name -p database_name < path/to/backup.sql

• If we use the example wp-config.php file from above, our command will look like this:

mysql -h d92c2896243585.db.2696146.hostedresource.com -P 3315 -u d92b2696256785 -p d92b2696256785 < data/backup1.sql

This should have imported your database. If needed, be sure to remove the uploaded SQL file from your web-accessible directory (/html). Otherwise, anyone can download it from the web.

WP-CLI

Export a MySQL database

Before starting, keep in mind that to use wp-cli for database export or import, your site must have WordPress 3.7 or later, and a correctly configured wp-config.php file.

1. Log into your server via SSH.
2. Use the command cd to navigate to the directory where your WordPress install is located. By default this will be /html, but use the file path that is applicable to your site.

   cd html/

3. Use the command wp to export your database.

   wp db export filename.sql

4. You should receive an output, Success: Exported to 'filename.sql' to confirm the database was successfully exported.
5. A sql file will be exported into the directory that you ran the wp command from.

Import a MySQL database

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:

1. Use SFTP to upload your SQL file to where our WordPress install is located. By default this will be /html. Use the file path that is applicable to your site
2. Log into your server via SSH.
3. Use the command cd to navigate into the directory where you uploaded your sql file.

   cd html/

4. Import the database by executing the following command:

   wp db import filename.sql

5. You should recieve an output Success: Imported from 'filename.sql'. to confirm the import was successful.

For additional information on using wp-cli for the export and/or import of databases (including possible variables) feel free to review the third-party resources below:

• https://developer.wordpress.org/cli/commands/db/export/
• https://developer.wordpress.org/cli/commands/db/import/

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 a MySQL database

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 '' (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 a MySQL database

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?

1. 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 '' (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.

If your import still fails, check the .sql file for “create table” lines. If they exist, comment them out using a '#'. Grid hosting is a shared environment and users do not have create table permissions.

WP-CLI

Export

Before starting, keep in mind that to use wp-cli for database export or import, your site must have WordPress 3.7 or later, and a correctly configured wp-config.php file.

1. Log into your server via SSH.
2. Use the command cd to navigate to the directory where your WordPress install is located. By default this will be /html, but use the file path that is applicable to your site.
   

   cd domains/example.com/html/

3. Use the command wp to export your database

   wp db export filename.sql

4. You should receive an output, Success: Exported to 'filename.sql' to confirm the database was successfully exported.
5. A sql file will be exported into the directory that you ran the wp command from.

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 or the File Manager to upload your SQL file to where our WordPress install is located. By default this will be /html. Use the file path that is applicable to your site
3. Log into your server via SSH.
4. Use the command cd to navigate into the directory where you uploaded your sql file.

5. cd domains/example.com/html/

6. Import the database by executing the following command:

   wp db import filename.sql

7. You should recieve an output Success: Imported from 'filename.sql'. to confirm the import was successful.

For additional information on using wp-cli for the export and/or import of databases (including possible variables) feel free to review the third-party resources below:

• https://developer.wordpress.org/cli/commands/db/export/
• https://developer.wordpress.org/cli/commands/db/import/

Instructions

Command Line

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

You must be able to log into your server with SSH.

• 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. 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 '' (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 '' (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.

If you run into file size errors you can try to increase the server's limit through the php.ini file. Plesk has an article on the topic here.

How do I export selected data from phpMyAdmin?

How do I export from phpMyAdmin to excel?

How do I export a CSV file from phpMyAdmin?

How do I import and export from phpMyAdmin?