A lot of beginners are very much dependent upon phpMyAdmin for their daily work. However a lot of them are not familiar with the basic command line tool like mysql and mysqldump. These two commands can help a lot when I am working on servers without phpMyAdmin installation.

I will show you how to quickly backup and restore database between your local computer and server.

We have a database called dbproject_local which I will migrate to server. The server already has database dbproject_server.

Export database content from localhost

You can do this from phpMyAdmin or the command line mysqldump tool. My preferred way is the mysqldump command. The basic command for mysqldump is

mysqldump -u<username> -p<password> -h<hostname> databasename 

This will output the whole sql file in the console. To save it to file, I will just redirect it to a file. Now to export my database dbproject_local, here is the command

$ mysqldump -uroot -psecretpassword -hlocalhost dbproject_local > dbproject_local.sql

If you open dbproject_local.sql you should see the complete dump with structure and data.

Import database content in server

Copy the file to the server, use scp or ftp or any other method you are familier with. Login to the server with SSH. I will now import the sql file using the mysql command. The syntax for this is similar to mysqldump. Infact it is exactly same, just replace mysqldump with mysql

mysql -u<username> -p<password> -h<hostname> databasename < sqlfilepath

To import our earlier file dbproject_local.sql, here is the syntax

server$ mysql -ubibek -pshrestha -hlocalhost dbproject_server < dbproject_local.sql

Note that, in some servers mysql does not run in localhost and you have to specify different hostname. Example in mediatemple grid service you have to specify the database name as

server$ mysql -udb999user -p12341234 -hhostname db999user_dbname < /path/to/dbproject_local.sql

There a lot of other options as well. You can look at the full instruction through man page or the general mysql and mysql help command

$ mysql --help $ mysqldump --help $ man mysql $ man mysqldump

Leave me your comments and feedbacks.