What is Digg?Sponsored by Rockstar Games
Grand Theft Auto: Episodes from Liberty City view!
rockstargames.com - Out Now on Disc for Xbox 360. Includes The Lost and Damned plus the all-new The Ballad of Gay Tony.
68 Comments
- L4mb, on 03/16/2009, -0/+52Holy *****! A tech article on digg?
- phrstbrn, on 03/16/2009, -0/+24No binary log incremental backups? InnoDB Hot Copy? mysqlhotcopy (for MyISAM tables)?
Article should have been renamed "10 different ways to call `mysqldump`".
lame... - t0x2c, on 03/16/2009, -0/+13Don't be alarmed. It's basic IT.
- robdiggity, on 03/16/2009, -0/+13I think his point was that the article is not about a pot-smoking iPhone toting gravity defying mountain goat named Ron Paul.
- nourayehia, on 03/16/2009, -0/+11Yes i know, i wrote it :)
- XmlSamurai, on 03/16/2009, -0/+11Because his automatic Linux script is not going to work. Here is a better way of doing it with a quick shell script created in /bin like so:
Use touch to add the file call it backup_database.sh like so 'touch /bin/backup_database.sh', then make sure its executable with chmod "chmod +x /bin/backup_database.sh" then edit it & add the following:
#!/bin/sh
# backup mysql database
MyDate='/bin/date +%Y-%m-%d'
MyLocation='/foo/bar'
/usr/bin/mysqldump --opt --all-databases -u root -pPASSWORD > "$MyLocation"/db-`$MyDate`.sql
Next make sure you change '/foo/bar' to the location where you want the backups to be placed. Then just save the file and add the following line to your crontab:
15 2 * * * /bin/backup_database.sh - annjay, on 03/15/2009, -0/+10I have to manage many blog and this article will be really helpful for me...Thanks
- dsenman, on 03/16/2009, -0/+7The S3 solution is sexy - from a redundancy standpoint. Very handy for backing up your entire site (source code included). Just make sure you have a planned way to RESTORE it at some point too.
- Thomasaka, on 03/16/2009, -0/+7If you're not creating backups then you're an idiot who deserves to lose data.. and eventually you will. Just because it's never happened doesn't mean it's not possible.
- XmlSamurai, on 03/15/2009, -1/+7Not so sure about his automatic Linux backup method, it would be a lot better to have the date as year-month-day instead of month-day-year or searching for a certain date would be a nightmare. That is only if you are storing more than a few years worth of backups.
- inactive, on 03/15/2009, -4/+10great advice... thxs
- grabulsa, on 03/16/2009, -0/+4welcome to mysql 101
- Octanus, on 03/16/2009, -0/+5Except, you're actually deleting the newly made backup since you're using $date instead of $delete in your unlink command.
- t0x2c, on 03/16/2009, -0/+5Then you're doing it wrong. Set the backup script's priority to a lower setting than your database process and there shall be no problems. It would be best if you use the database's built in back up script however, as you wouldn't need two file buffers on the same set of data; but for most cases, process priority works fine.
- joshcxa, on 03/16/2009, -0/+4uh huh and how does this contribute to the info in teh article?
- nourayehia, on 03/15/2009, -2/+6Yeah, but if you're not a developer, you probably should go with an automatic backup utility like AutoMySQLBackup
- ironiridis, on 03/16/2009, -0/+4...eating bacon.
- AReallyGoodName, on 03/16/2009, -1/+5Even if you want free and open source you'd still go with something like PostgreSQL over mySQL.
Comparison I Googled to save me explaining it:
http://articles.techrepublic.com.com/5100-10878_11 ... - Vektuz, on 03/16/2009, -0/+2Buried for being a list. Enough with the lists.
- phrstbrn, on 03/16/2009, -0/+4HA is NOT a backup solution.
Replication doesn't prevent a DELETE * FROM `table` statement from destroying all of your data.... it replicates the delete - digg1520, on 03/16/2009, -0/+2Useless blogspam. The article basically lists the top 10 google results for "mysql backup" and doesn't address any of the really interesting points like data consistency, locking, InnoDB vs. MyISAM backup, incremental backups...
- Samurai77, on 03/16/2009, -0/+3Maybe I'm lazy, but I just use the backup feature in Webmin MySQL module. I off load it to another file system and it is then caught in the daily diff backups. Easy Squeezy.
- kelyar, on 03/16/2009, -0/+2lame - it is all about mysqldump.
you should know that mysqldump (while being extremely useful on small databases), becomes not so useful if you are running high traffic site with even medium db. it locks every table so your site just stops. There are some tricks to avoid it like nightly copying db files, etc =)
better google on hot backup - Meekus, on 03/16/2009, -0/+3Why in the hell is this getting dugg down?? This is a good simple, lean, mean, and moreover effective way to backup mysql dbs!
Oh, and very much agreed on the nomenclature of year and date. Putting Y first makes so much life so much easier down the road. - wendall911, on 03/16/2009, -1/+4If you read the article, you might have noticed that AutoMySQLBackup was mentioned.
- mrbutter, on 03/16/2009, -0/+3<?php
$date = date("m_d_y");
$delete = date("m_d_y", mktime(0, 0, 0, date("m") , date("d")-7, date("Y")));
exec("mysqldump -u***** -p****** ****** | gzip > /home/******/public_html/backups/*****_".$date.".gz") or die();
unlink("/home/*****/public_html/backups/******_".$date.".gz");
?>
I like mine. Makes one every night and then deletes the one made 7 days ago. Thus, there are always 7 available. - chadszinow, on 03/16/2009, -0/+2It all depends on what DB engine you chose in MySQL. If you use the older MyISAM which only has table locking then changing the priority will have next to no effect when backing up the database. MySQL will lock the table for writing as it moves from table to table and it will stay locked until the table backup query is complete.
A critically bad example of where mysqldump backup can kill a web app is the backing up a user table that is very large. The table is so large it takes 5 minutes to backup let's say. When a user logs into your web app the app wants to write to the user table to record when that user last logged in. Since the user table is being backed up the query waits in the queue for lock to release [5 minutes or less]. This causes users to hang at the login process which causes webserver processes to build up for these hanging users which causes a spike in server load.
Before you bag out using the older MyISAM engine in the first place in lieu of the row lockable innodb you should note there are certain situations where MyISAM outperforms innodb and sometimes is more efficient. - AReallyGoodName, on 03/16/2009, -0/+2I don't know mySQL specifically but every database engine i've ever dealt with has ways to backup without interferring with users on the database. It's just a too large a problem to ignore.
Postgres for example has the pg_start_backup() command that causes any writes to the database to go into a temporary cache file+journal until pg_stop_backup() is called. So the end user sees the database working fine and sees the database updating (the cache file takes priority) but the actual database files remain static and read-only.
This means the database can backup without any noticable disruption. I'm pretty sure mySQL does the same thing.
You really think databases can't be backed up without taking down the frontend?! - dwalker, on 03/19/2009, -0/+2Most shared web hosting accounts do not allow shell access (SSH) you will not be able to use many of those methods! :-(
If you don't have shell access try this its open source: : -)
http://www.dwalker.co.uk/phpmysqlautobackup/ - IsraGeek, on 03/16/2009, -0/+2Great advice. Thanks for sharing this.
- roxtar, on 03/16/2009, -0/+1That article is from 2002. Quite a bit's changed since then and most, if not all of those "No"s under to MySQL have become "Yes"s.
- findhostcoupons, on 03/22/2009, -0/+1Good article man, big thanks for sharing!
- wendall911, on 03/20/2009, -0/+1Doh! Don't you read the articles you write. ;)
- e2superman, on 03/16/2009, -0/+1They can be. Like I said I believe they simply table lock that table until the backup is done. MySQL commands that need that table simply queue until then. If a command queues that a web-app needs the browser will simply wait until that happens (queue is processed) or it times out.
- simd, on 03/16/2009, -0/+1Even better, put a test into your web page that checks to see if the DB is ok, and if not display a fail whale. That way, you don't need to take your site down unnecessarily, and it covers the eventuality of the DB failing. As it is, we back up millions of records with an automated script and have never had a problem.
- hawksfan03, on 03/16/2009, -0/+1just don't copy and past your URLs into the message box then they won't show up with your message.
- kylemech, on 03/16/2009, -0/+1...unless people use it like a verb and the audience understands it. It might look less professional, that's subjective (and I'd even agree).
- mrbutter, on 03/16/2009, -0/+1If you did it via PHP or something, it's possible your backup timed out (rendering it incomplete).
Best way is to do it via Shell (SSH) or the php exec() command. Prevents timeouts.
And also have multiple backups available. That way if your latest one is corrupt for some reason, maybe your one from the day before isn't. - NinjaBull, on 03/16/2009, -0/+1Cats?
- digg1520, on 03/16/2009, -0/+1The best way to do a consistent backup of both MyISAM and InnoDB is a LVM snapshot. There is a script called mylvmbackup that automates the snapshot and the backup.
- digg1520, on 03/16/2009, -0/+1Yes, the article is lame. The only way to do a 100% consistent backup of both MyISAM and InnoDB without downtime is a LVM snapshot. The article does not even mention that.
- ironiridis, on 03/16/2009, -0/+1I think it's hilarious that his example is flawed... take a close look at #2's code block.
- e2superman, on 03/16/2009, -0/+1What about when members change tables (add/update rows)? I assume the db backup (dumping) does a table lock while it is backing up the table. For multi GB tables the table lock would last a while and cause MySQL queries to back up which loads up the server as people keep hitting refresh on their browser.
- jriggs, on 03/16/2009, -0/+1...while downloading torrents.
- NinjaBull, on 03/16/2009, -0/+1haha wendall911
- NuPagady, on 03/16/2009, -0/+1This is actually a useful list.
- ChWia1c4, on 03/16/2009, -0/+0WOW O_o
- badgopher, on 03/16/2009, -0/+1MySQL Administrator ( http://dev.mysql.com/downloads/gui-tools/5.0.html ) is a pretty stupid simple way to do it on Windows.
- wendall911, on 03/16/2009, -0/+2Just like @phrstbrn pointed out in a comment thread above, replication does not prevent a 'DELETE * FROM ...' statement destroying all the data, since the delete is replicated.
It isn't lame to show different ways to automate mysql dumps. And relying on just the binary logs is a bad idea also. You can use binary logs in conjunction with full database backups, as documented in the mysql docs. I guess all the mysql "experts" in this thread didn't bother to read the docs so kindly provided on mysql.com. http://tinyurl.com/5gpowr
Useful article +1 -
Show 51 - 71 of 71 discussions
Browsing Digg on your phone just got easier with our enhancements to the 
© Digg Inc. 2009
— Content created and posted by Digg users is