Monday 10 August 2009

Transferring Large Databases

The following is a blog from 3 years ago.

I still refer to it on occasion, it's definitely still relevant if your using your own server.  However I do have a quicker method for when I'm doing this on Heart Internet - and thats to

> go to your control panel
> find and click on 'MySQL Databases' 
> find the text 'To restore a database which you've previously backed up, click'   -
> just locate the file and off it goes to install it for you.

Anyway here's the SSH way to do a large MySQL database

####################

One thing that has been slowing me down recently when transferring a website is transferring large databases. On searching it looks like being able to do this by SSH ( shell access ) should speed the process up greatly.

This article lists how to do this by mysqldump.

http://patchlog.com/databases/three-methods-to-transfer-a-mysql-database/

However after several attempts I could not get my SQL to save in the file properly and due to the old 'time is money' syndrome I ended up saving the file in the good old phpMy Admin panel. Saving the document was never my problem anyway it was always importing where it would crash on me or I'd spend time splitting the tables up!!
However if anyones got any tips on the export side of the above article it'd be good to know.

Anyway so here's the method I am now using which works for me.

1. Use the 'Export' feature in mysql and tick 'save as file' - compression 'none'
2. Upload the file to the server of the website you want your database to go to.
3. Use this statement in SSH ->
mysql -u "your username" -p "your_database" < filename.sql


If it's a really large file you may want to zip it up and then unzip it using SSH as well. My blog on 'transferring a site using SSH' at the end of July may help.

Transferring mysql database
Transferring mysql using SSH
Importing mysql database
Importing large mysql database

No comments: