Export and Importing MySQL Database via SSH


There are times when the usual methods of backing up a database do not cut the mustard – especially when it comes to Magento databases . Normally a Magento database can be backed up via the tool>backups module within the backend – however you may find that on some servers the process of backing up the database might time-out in your browser.
At times like these it is useful to know the following techniques for backing up and restoring via SSH.
First of all to backup your database you will need to firstly connect to your website via SSH – normally like so (on MAC):

ssh username@ip.add.re.ss

– enter your password
This will normally get you in to your website – that is if you have SSH enabled for your domain on your chosen hosting platform.
Next we will perform a nice clean backup – what I tend to do if it is a Magento website is to navigate to the backups folder within the Magento var folder by doing the following:

cd httpdocs/var/backups

If that folder structure does not match your own server path then try typing just “ls” to see the contents of the current directory you are in.
Now you are within your backups folder we can perform a backup – the reason we navigated here is so that when we save the file we will save it into the current folder. Perform the following to backup your database:

mysqldump -u username -ppassword databasename > mybackup.sql
Notice the lack of a space between the -p and the password? That’s intentional. The above command will save a backup to an sql file in your folder – and rather quicker that through the browser too!
Now to recover a database you simply need to stay within that folder and have a nice blank database handy.

mysql -u newdbuser -pnewdbpass newdatabasename < mybackup.sql
This will import the database dump back into the new database rather quickly too. This is good if you want to make a drastic change very quickly – simply backing up the existing database, importing into a new one and then changing your local.xml will allow you to work on a carbon copy of the Magento database – do what you must to test something and then change your local.xml back to the original quick as a flash.

Leave a Reply

You must be logged in to post a comment.