Importing and Exporting MySQL Databases in Linux


This guide will outline the basic steps to import and export databases from a Secure Shell (SSH) session on a cloud instance or dedicated server with Linux as the operating system. These steps are often required in circumstances where a database backup is needed or a database migration is taking place. The steps listed are intended for generic Linux configurations. cPanel and generic Linux instructions will make use of the same authentication and export / import processes. Plesk configurations vary slightly and appropriate steps are also provided. You will need to be able to login to your server via SSH to access the command line. Steps to login via SSH can be found at <http://www.codero.com/knowledge-base/questions/114/How+do+I+connect+to+my+server+using+SSH>


Exporting a Database

To export a database on a Linux system from SSH we will use the ‘mysqldump’ command. Issuing the command by itself will show basic usage:

root@server:/# mysqldump
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump –help

To use mysqldump you will run the command followed with the database name and direct the output to a new file as shown in the example below:

root@server:/# mysqldump exampledb > exampledbexport.sql

After running the command as the root user it will return you to a shell. If no errors were displayed you should see the SQL file created with the name you specified in the same working folder. In the output below we used ‘ls’ and ‘du’ to list the new file and show the human readable disk usage for the new export.

root@server:/# ls exampledbexport.sql
exampledbexport.sql
root@server:/# du -h exampledbexport.sql
50K exampledbexport.sql

If you would like to export all of the databases on the server you would use the same ‘mysqldump’ command but you’ll omit the database selection and declare all databases as shown below:

root@server:/# mysqldump --all-databases > fullmysqlexport.sql

After the command is run, if no errors were presented you should have a new file called fullmysqlexport.sql that will contain all of the MySQL databases from the given host. Please note that depending on the system usage this can be a very resource heavy operation and it can consume a large amount of disk space if the databases contain a considerable amount of data. In most cases dumping individual databases is preferred.


Exporting a Database With Credentials

In the examples above we are accessing the server as the root user and we are not telling mysqldump which password to connect with. If you attempt to use mysqldump and receive an error pertaining to passwords being invalid or not being used at all you will need to specify the password manually. The MySQL root password can be different from the password for the root user for the server. When using mysqldump as the root user it will typically pull in the contents of the file /root/my.cnf which will have the current MySQL root password. If this file does not exist, has the incorrect MySQL password, or if you are logged in with a user other than root you will likely see the following error:

root@server:/# mysqldump exampledb > exampledb.sql
mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect

The solution is to run mysqldump with the –p flag which will allow you to specify the password:
root@server:/# mysqldump -p exampledb > exampledb.sql
Enter password:
root@server:/#


Exporting a Database With Credentials Under Plesk

If you have Plesk installed the MySQL password will typically be stored in /etc/psa/.psa.shadow and you will use that file to authenticate. The contents of the /etc/psa/.psa.shadow is essentially the encrypted version of the Plesk admin password so if you should ever want to change the mysql password you would want to change the admin user password through Plesk. Please keep in mind that it is not advised to edit the mysql admin password for plesk servers through either editing the /etc/psa/.psa.shadow or through mysql itself as it can break Plesk. With the command below you can call mysqldump and have it use the contents of that file as the password to authenticate with. If no errors are shown the database export is successful and you should have the .sql saved in your working directory when it returns the shell prompt:

root@pleskserver:/# mysqldump -u admin -p´cat /etc/psa/.psa.shadow ´ databasename > databasename.sql
root@pleskserver:/# du -h *.sql
100K databasename.sql
root@pleskserver:/#


Importing a Database

The steps required to import a database are similar to an export but instead of using the ‘mysqldump’ command we will use the ‘mysql’ command. With a .sql file from a previous database export we can recreate a database and populate the tables with the contents. If your source file is not already uploaded to the server you will need to upload it with a FTP or SFTP client. With cPanel or Plesk you also have the option to upload the SQL file from the File Managers that are internal to the Control Panel interfaces. Regardless of the means of getting the file onto the server you will need to pay attention to the full path that the SQL file is located at.

If you upload the file with SFTP as the root user it will likely default to /root/

If you upload the file with FTP (not advised to use as root) it will upload the .sql file to the specified destination or by default the home folder for the user you login with.

If you upload the .sql file from a Plesk Panel file manager it will be located under /var/www/vhosts/yourdomainname.com/

Any SQL files uploaded from the file manager within cPanel will be found in /home/CPANELUSER/

From SSH you will need to navigate to the location where the SQL file is stored. Various configurations are shown below with example output.


General Import Database Examples

Import / Restore Database From An Export With cPanel

root@cpanelserver:~# pwd
/root
root@cpanelserver:~# du -h *.sql
101K databaseexport.sql
root@cpanelserver:~# mysql –u root databasename < cpdbname.sql

If the import was successful it should return to a shell without any errors displayed. If you receive an invalid password please specify the –p option and enter the password for the MySQL root user when prompted.

root@cpanelserver:~#

root@cpanelserver: /# cd /home/siteuser
root@cpanelserver:/home/siteuser/# du -h *.sql
120K cpdbname.sql
root@cpanelserver:/home/siteuser/# mysql databasename < cpdbname.sql
root@cpanelserver: /home/siteuser/#


Import / Restore Database From An Export With Plesk

root@pleskserver: /# cd /var/www/vhosts/site.com
root@pleskserver: /var/www/vhosts/site.com/# du -h *.sql
100K databasename.sql
root@pleskserver:/var/www/vhosts/site.com/# mysql -u admin -p´cat /etc/psa/.psa.shadow ´ databasename < databasename.sql
root@pleskserver: /var/www/vhosts/site.com/#

 

Posted in
Last update:
2016-05-03 11:50
Average rating:0 (0 Votes)