Have you ever got a script timeout error while backing up your MySQL database via phpMyAdmin? Well if you never did, you will most likely get this error in future when your database grows to something over 3-4 MB. It is very important to do a backup of your MySql database on regular basis. Backing up a small database via phpMyAdmin is as easy as 123 but with larger database e.g. 2GB can turn out to be a nightmare. Here is an easy way to get around this problem.

You will need Shell access (SSH) to your server. Ask your hosting provider to enable it for you if it is not enabled by default. Secondly, you will need an SSH client. I would suggest “PuTTy” that you can download for free.

Backing up the Database

Open Putty by double clicking it and enter your details to login. For most hosting companies, this will bring you into the FTP root folder. Enter the following command:

mysqldump --opt -Q -u database_username -p database_name > /path_to/your_root_folder/database_name.sql

replace “database_username” with name of your MySQL databse username and “database_name” with your database name. Similarly, replace “/path_to/your_root_folder/” with a complete physical path to your root folder. e.g. /home/username/public_html/ and “database_name.sql” with name of the backup database file e.g. my-database.sql

Hitting the enter button will backup your huge database to the path specified in a few seconds. You can also have the output in a compressed format e.g gzip. Just follow the same instruction using this command:

mysqldump --opt --user=database_username --password=database_password database_name | gzip > /path_to/your_root_folder/database_name.sql.gz

Restore MySQL Database

Follow the above steps untill you login to your website through SSH client and type in the following command:

mysql -u database_username -p database_name < /path/to/database_name.sql

You will be asked for a database password. Enter the database password and voila!…