Applies to :

• All Linux server

Requirements

To perform this task, you need to have :

• Knowledge in accessing the server via SSH.
• IP address or server hostname.
• Your server SSH/FTP username and password.

Instructions

This method is an alternative way to export and import your database when you have difficulties to import it via PHPMyAdmin panel due to big *.sql file size. Please follow below steps in order to export and import your database via Linux command line :

• Initiate SSH connection to your Linux server.

• Use cd command to change your directory location to a directory where your user has write access. For example (replace xxxx with your username) :

 # cd /home/xxxx/data/

• You can export the database (i.e. dbname) by run the following command:

 # mysqldump --add-drop-table -u username -p dbname > dbname.sql 

After you executed the above 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. 
dbname : the name of the database you are trying to export. 
dbname.sql : 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.

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

• To import a database from a *.sql file you can run below command :

 # mysql -u username -p dbname < dbname.sql

• If you got below error :

Got Error: 1045: Access denied for user 'username' (using password: YES) when trying to connect

This means you have entered an incorrect password. Please retype it carefully, or reset your password via your plesk control panel (if you are in Plesk platform)