How to Backup and Restore MySQL Database from Linux Command Line

I wrote this post so that I can easily retrieve information that I pulled from several pages of the internet. I have this need to migrate some large MySQL database to another server and I need to do it from the command line of Linux. To make this information at least a little complete, below is the outline of my situation and what I need to do.

  • The OS of origin and destination servers are both CentOS 5
  • The control panel of the origin is cPanel and the destination is a custom made control panel
  • Databases are MySQL
  • Most websites are Drupal and some are WordPress
  • My machine where I am accessing my server is Windows
  • The SSH client I am using is PuTTy, it's free
  • I'm a Windows guy making mess in Linux. :)

I wrote before about how to migrate large database by the use of BigDump. You can follow the instruction of that older post if you are not comfortable in using Linux command line.

Export the database from the origin
You need the following: database_username, database_name and database_password. With your SSH client go to the folder where you are going to dump your backup file. In my case, I worked under public_html directory so that I can easily transfer the file to another server.

Issue the following command:
mysqldump -u database_username database_name -p > dump.sql

It will ask you some password, enter the password of the database_username. Make sure that the database_username is the one associated with the database_name. The code above will create a backup of your database_name database to a file named dump.sql under the directory where you are working.

Next is to rename the dump.sql file with different extension because most server will not allow visitors to download .sql file.

mv dump.sql dump.gxx, you can use any extension you want, in my case I use .gxx in place of .sql.

Import the database to the destination server
Create in the destination server a new database, user for the new database, and password for the user to that database. Let us say that the three you created are respectively, database_name, database_user, and database_password.

Download the dump file to the destination server from the origin server. Issue the following command:

mv dump.gxx dump.sql


What is the meaning of the code above?
The line wget will download the file dump.gxx to the destination server. That is the reason why I work under public_html directory of the origin server so that I can access the file easily. The code mv dump.gxx dump.sql rename the file dump.gxx to dump.sql.

Finally start the import process,
mysql -u database_username -p -D database_name --default_character_set utf8 < dump.sql

It will ask you a password, give the database_password you just created.

That's it. Congratulations, you successfully migrated a large database to new server.