Backup MySQL databases

There are SO many ways to backup your MySQL databases (and tables) and all of them work just as well ..until the db tables get really big.

You can 'dump' PhpMyAdmin

The popular PhpMyAdmin allows you to 'dump' your MySQL db tables just fine like I said BUT as soon as your tables get larger than normal, the script will nearly always 'time-out' (at least with the version I worked with the last time) and stall midway. So it might not be the case anymore, I wouldn't know.

Using Putty and SSH

These days, this is what I personally do to backup (and restore) my MySQL databases. I use PuTTY and SSH. After you figure out how to backup and restore your MySQL databases this way, you will quickly realise why I prefer this method - it's FAST!

I've got Putty...

Good, now log on to your server with SSH and get to your WWW root, which could be:
path: /home/username/public_html
or
path: /home/username/htdocs

Backing up MySQL

To backup any single database, you just have to type:

mysqldump --add-drop-table -u dbusername -p dbname > dbname.bak.dump
// or better yet...
mysqldump --opt -u dbusername -p dbname > dbname.bak.dump

e.g. If I had to backup a certain database with the following details:
MySQL username is jds_user1,
MySQL database name is jds_db1

This is what I'd type in:

mysqldump --add-drop-table -u jds_user1 -p jds_db1 > jds_db1.bak.dump
// or better yet...
mysqldump --opt -u jds_user1 -p jds_db1 > jds_db1.bak.dump

Now, as soon as you type in your password at the prompt and hit Enter, you will find a file named jds_db1.bak.dump in your WWW root which you can download off your website by just pointing your browser to:

http://www.example.com/jds_db1.bak.dump

MySQL backup tip!

If you're impatient like me and can't stand downloading large files generally, tarball (compress) the dumped file for a much smaller download!

Remember to delete the file after use

Please remember to delete this file (or move this file up out of your WWW root) as soon as you're done using it because really, ANYONE, could now download it if it's left on your WWW root.