Backup MySQL databases

There are so many ways to back up your MySQL databases (and tables) and all of them work just as well… until the database (DB) tables get really big.

You can ‘dump’ PhpMyAdmin

The popular PhpMyAdmin script allows you to ‘dump’ your MySQL DB tables just fine, but like I said, as soon as your DB tables get a bit larger than usual, the script will nearly always time-out and stall midway, at least with the version I worked with the last time. So it might not be the case any more, I wouldn’t know.

Using PuTTY and SSH

These days, this is what I personally use 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 directory, which could be:
path: /home/username/public_html
or
path: /home/username/htdocs

Backing up a MySQL Database

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

For example, If I had to backup a certain database with the following details:
MySQL user name: jds_user1,
MySQL database name: jds_db1

This is what I’d type:

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 folder, which you can then download off your web site (assuming your web site is www.example.com) just by pointing your browser to:

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

Remember to delete the file after use

Please remember to delete this backup file as soon as you’re done downloading it because, really, ANYONE could download it now, if it’s simply left there.

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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>