Transferring your blog or web site from one host to another can sometimes be very cumbersome if not troublesome. There may be instances when you get stuck at few points and one of the major loop hole is to transfer data from one host to another. Most of us use browser based user friendly phpmyadmin application for almost every database operation. But it becomes difficult to export/import data using phpadmin when your backup file is exceptionally large.
So to overcome this shortcoming of phpmyadmin, we can export the old database and then import it using the shell or from command line. To do so, you need to have shell access to both of your hosting account. If you want to transfer database within the same host then its even better.
Read the syntax carefully and you should be good to go, if you are stuck in middle of something, leave a comment.
Transferring database within same host
mysqldump -h oldhost -u oldusername -poldpassword olddbname | mysql -h newhost -u newusername -pnewpassword newdbname -- there should be any space between -p and your password
Transferring database between two difference hosts.
When transferring database from one host to another, you might face certain challenges. Follow the steps one by one to transfer successfully.
Export database in the mysql dump file.
mysqldump -h oldhost -u oldusername -poldpassword olddbname > backup_file_name.sql -- this will generate a file in the current directory with the name backup_file_name.sql
create a .tar file of your sql dumps file.
-- in the shell type tar -cvf database.tar backup_file_name.sql -- this will create the tar file that can be easily and quickly downloaded on the other host
Download the file on another host
Untar the downloaded file.
tar -xvf database.tar --untar the file
Finally, import the data into the new database.
mysql -h newhost -u newusername -pnewpassword newdbname < backup_file_name.sql
Stay Digified !!