How to Backup and Restore MySQL Database from Linux Command Line
Submitted by Jhun Vert on December 29, 2012 - 5:08pm
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_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
mv dump.sql dump.gxx, you can use any extension you want, in my case I use
.gxx in place of
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,
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?
wget http://www.example.com/dump.gxx 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
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.