Welcome to WebmasterWorld Guest from 54.166.33.25

Forum Moderators: coopster & jatar k

db backup, a good code for mysql dump

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

WebmasterWorld Senior Member 10+ Year Member



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 Sep 6, 2012 (gmt 0)

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



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 Sep 6, 2012 (gmt 0)

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



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.
 

Featured Threads

My Threads

Hot Threads This Week

Hot Threads This Month