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
wget http://domain_on_old_host.com/database.tar |
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 |
Note:
- Dumping or exporting of your database to .sql file may take some time, depending upon the size of the database, so please be patient.
- You can skip creating tar of your dump file and can simply import it to new host.
- You must have a configured domain on both old and new hosts, to successfully use the command wget. Otherwise you can use FTP to first download the file locally and then upload it to new host. Some hosting providers also provide testing domain you can leverage that for the same.
- Be sure about what you are going to do.
- There is no space between -p and password.
Stay Digified !!
Sachin Khosla
