Export and import MySQL databases through command line

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.

 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.