Welcome to WebmasterWorld Guest from

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

db backup, a good code for mysql dump

1:07 am on Sep 6, 2012 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Apr 21, 2002
posts: 1541
votes: 0

I want to have an easy link in my admin area, where I can just click on database backup and download my database locally. Is there some simple mysql query that can do this?
1:44 am on Sept 6, 2012 (gmt 0)

Moderator from GB 

WebmasterWorld Administrator brotherhood_of_lan is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Jan 30, 2002
votes: 1

No script would suit all requirements, especially for very large databases. You'd want to be careful about how the data dump affects any live websites or services.

At its simplest, 2 steps
Step 1. [dev.mysql.com...]
Step 2. [hypexr.org...]

There are windows programs for SCP also, it's a very fast way to shift data between places.
7:07 am on Sept 6, 2012 (gmt 0)

Administrator from US 

WebmasterWorld Administrator incredibill is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Jan 25, 2005
votes: 88

You can backup and restore MySQL from the command line or using PHPMyAdmin

$ mysqldump --opt -u [uname] -p[pass] [dbname] > [backupfile.sql]

$ mysql -u [uname] -p[pass] [db_to_restore] < [backupfile.sql]

Additionally, if you're on a control panel like Plesk or cPanel, they typically include PHPMyAdmin by default so you can use that. They also provide some site backup facilities as well but I'm not sure if the end user can do this or just the admin as I own the server so I have full control.

I prefer the command line script options because then you can set up cron jobs to do it daily and download it locally via an FTP program with a scheduler like CuteFTP. My databases are automatically downloaded nightly to my local computer so in the event of disaster I've only got a few customers missing from the copy of the database. Use a rolling backup, 7 days should be enough, with weekly or monthly baselines to avoid real disaster as once a file gets really mangled or hacked you're backing up bad data. I've seen people using weekly rollover backups without baselines not find out their database was really messed up until it had overwritten all 7 previous copies of the file. They had to resort to their last full site backup a couple of months old, it wasn't pretty.