howto,MySQL,tips

Transfer database from one host to other using mysqldump and shell

3 Feb , 2010  

logomysql 300x199 Transfer database from one host to other using mysqldump  and shellTransferring 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

, , , ,


Comments are closed.

More in howto, MySQL, tips (143 of 198 articles)


You love coding in Netbeans and you find it pretty uneasy when it does not function the way you want ...