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:
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:
MySQL database name:
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:
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!