Let me start out by saying: the best response to a disaster is backup you can count on.
Found a scary story today about hundreds of thousands of websites using Microsoft IIS and SQL Servers being affected by Internet-wide SQL injection attacks. The story originally reported by F-Secure is now on Slashdot as well.
On the IIS forum, panic is visible. Those who had backups are breathing a sigh of relief like one administrator who commented, "We have been hit by this as well. Lucky backup ran last night just prior to the attack."
Others without backups are just screwed.
F-secure reports in an update to the story, "Do note that this attack doesn't use any vulnerabilities in any of those two applications. What makes this attack possible is poorly written ASP and ASPX (.net) code."
Although this attack is targeted towards IIS and SQL Server, there are lessons to be learned for sites using other servers and databases. There are several guides available on the Internet that will show you how to secure your application against SQL Injection attacks, like http://www.blogger.com/img/gl.link.gifthis one that is focused on securing PHP and MySQL applications.
In this year's "Disaster is Inevitable--Are you Ready" presentation at the MySQL Conference (Yes, I have read Baron's post), I covered a few types of disasters. However, I missed an important kind of disaster: ones that are caused by SQL Injection. My next presentation on this topic will certainly cover this. BTW, if you missed my presentation, you can thank Artem Russakovskii, who took meticulous notes that you can read.
What saddens me is comments like, "but we have all patches applied to the version we are using." There is of course, a disconnect here as far as understanding the problem is concerned.
Patches don't secure you against SQL injection attacks; Properly written code does. Sanity check is very important!
Replication as a backup method won't help against SQL Injection
Based on my survey, a disturbingly high number of sites use replication as their backup strategy. If replication is your sole method of backup, then beware, SQL injection based disasters aren't going to help. Unless, of course, you have time delayed slaves and are able to stop replication before the slaves are affected.
Every year there are a number of backup related presentations at MySQL Conference. All, except one of the following, were presented this year!:
- What do you mean there's no backup? -- A timeless presentation by Mike Kruckenberg and Jay Pipes originally presented in 2006.
- Backup and Recovery Basics by Kai Voigt
- MySQL Backups go near continous by David Wartell
- MySQL Online Backup: An In-depth presentation by Chuck Bell
- Online Backup, Open Replication and a world of contribution by Lars Thalmann and Chuck Bell
- Performing MySQL Backups using LVM Snapshots by Lenz Grimmer
- Top 5 Considerations While Setting Up Your MySQL Backups
Showing posts with label backup. Show all posts
Showing posts with label backup. Show all posts
Saturday, April 26, 2008
Monday, April 07, 2008
Can backup really kill performance?
Yes, if you are running backing on a large database that is also handling production traffic (not a very smart idea to begin with). This is especially important for backups created using snapshots based on copy-on-write algorithm.
Brian makes an important point in a comment to my post regarding backup. He points out "Backups are always onerous on IO" and that a better way to backup is to use slaves or a standby master (if using multi master replication).
If you *must* run backups on a production server, then ibbackup becomes very important as it doesn't affect performance as much as the evil snapshots created by snapshot tools like fssnap and LVM. I have found that in our case purchasing ibbackup licenses were worth every penny.
In our environment, running backups using copy-on-write snapshots was killing performance. Writes would start stalling several hours into the backup process. It didn't help that backups would take 27 hours to finish. I moved most systems to using ibbackup and for those systems running backups hasn't been an issue at all.
Of course, if you must backup production servers, take snapshots to backup everything except the databases. That way the snapshots will be held for a much smaller period and you can continue backing up databases using ibbackup.
What about mysqldump? I don't consider mysqldump an appropriate tool for periodic backups. I can see it working for small databases but running it on enterprise level databases for daily backups is just not going to be feasible.
I would love to discuss backups more at the conference. I also would like to evaluate some of the backup vendors exhibiting at the conference.
Brian makes an important point in a comment to my post regarding backup. He points out "Backups are always onerous on IO" and that a better way to backup is to use slaves or a standby master (if using multi master replication).
If you *must* run backups on a production server, then ibbackup becomes very important as it doesn't affect performance as much as the evil snapshots created by snapshot tools like fssnap and LVM. I have found that in our case purchasing ibbackup licenses were worth every penny.
In our environment, running backups using copy-on-write snapshots was killing performance. Writes would start stalling several hours into the backup process. It didn't help that backups would take 27 hours to finish. I moved most systems to using ibbackup and for those systems running backups hasn't been an issue at all.
Of course, if you must backup production servers, take snapshots to backup everything except the databases. That way the snapshots will be held for a much smaller period and you can continue backing up databases using ibbackup.
What about mysqldump? I don't consider mysqldump an appropriate tool for periodic backups. I can see it working for small databases but running it on enterprise level databases for daily backups is just not going to be feasible.
I would love to discuss backups more at the conference. I also would like to evaluate some of the backup vendors exhibiting at the conference.
Sunday, April 06, 2008
Is Backup Really Irrelevant?
Brian Aker writes in his "PostgreSQL to scale to 1 billion users" post:
While I agree with most of Brian's statements in the article, I respectfully disagree with the statement above, especially the bolded part. Copy-on-write snapshots are EVIL for very large databases operating in a high I/O environment and backup, by no means, is entirely irrelevant. Please correct me if I am wrong but it is my understanding that both LVM and ZFS implement copy-on-write snapshots. Backup may be irrelevant for most sites but not for us.
If, however, by "irrelevant" Brian meant that not important in choosing one database over another, I can agree with that. Why? Because no one benchmarks backup methodologies until backup process starts becoming a major PITA.
Backup methods can be a performance killer when dealing with very large databases. If you're interested in finding out why, and more importantly how, ask me at the conference, come to my scaling MySQL and InnoDB on Solaris session, or check on this blog after the conference.
Backup is irrelevant for those of you who care about this discussion. LVM/ZFS snapshots are the rule of the land.
While I agree with most of Brian's statements in the article, I respectfully disagree with the statement above, especially the bolded part. Copy-on-write snapshots are EVIL for very large databases operating in a high I/O environment and backup, by no means, is entirely irrelevant. Please correct me if I am wrong but it is my understanding that both LVM and ZFS implement copy-on-write snapshots. Backup may be irrelevant for most sites but not for us.
If, however, by "irrelevant" Brian meant that not important in choosing one database over another, I can agree with that. Why? Because no one benchmarks backup methodologies until backup process starts becoming a major PITA.
Backup methods can be a performance killer when dealing with very large databases. If you're interested in finding out why, and more importantly how, ask me at the conference, come to my scaling MySQL and InnoDB on Solaris session, or check on this blog after the conference.
Friday, January 25, 2008
When no disaster recovery plan helps
Regardless of how "prepared" and "ready" one feel for a disaster, it will, in one form or another, inevitably happen. The best thing you could do is continuously revise and test your disaster recovery plan, strengthening it each time against any kind of disaster you can think of. Things generally go wrong when you least expect them to go wrong.
I was getting chills reading about Charter Communications, a St. Louis based ISP, accidentally deleted 14,000 active email accounts along with any attachments that they carried. All the deleted data of active customers is irretrievable. As someone who is responsible for data of one of the top 15 heaviest trafficked site in the world, according to Alexa, I know, I'd HATE to be in shoes of the person responsible for this.
As I was reading the news story, I was constantly thinking about the title of Jay and Mike's 2006 presentation: "What Do You Mean There's No Backup?"
Once a disaster happens, you can immediately think of the possible ways it could have been avoided. The real challenge is implementing ways of avoiding all types of disasters before they happen.
For instance, to protect against such a disaster, or at the very least, be able to recover from its effects, Charter communications could have:
1. fully tested the script on a QA/test box to ensure no test records of active users are deleted.
2. created a backup of the data by creating a file system snapshot just before running the script. That way deleted data can be recovered. Depending on your operating system/storage system, there are a lot of tools available that let you take file system snapshots such as fssnap (Solaris), LVM (Linux).
3. had a recoverable backup. There are a lot of cases out there where either no backup exists or the one that does exist, turns out to be corrupt. With a periodic backup, Charter could have, for instance, just announced to their customers that they lost their new emails since last week, instead of dropping the ball and saying that *all* their email is lost. Even having an off-site backup in this case would help if selective restore from that backup was possible.
BTW, Just a few days ago, I was testing a random sample of backups and found backups of a database to be corrupt. That triggered a system wide check of backups. The best way I have found is to have a list of backups from all databases sent to me by email. My report contains information about backups running at the time the script was generated and the backups that were created the previous night.
4. If the data deleted was on a database such as MySQL, recovery from this disaster would be possible by keeping a slave intentionally behind.
What are some of the other ways you can think of to avoid a disaster or to execute a recovery plan?
There are many ways a disaster like this can be triggered. A few, seemingly bizarre but very real, that come to my mind:
- What if you accidentally re-run a previously executed DELETE command, stored in your mysql client history, in a hurry, on the wrong server? Or you re-ran a disastrous command in your shell history in the wrong directory?
- What if you used a list of IDs generated from your QA/test machine to delete users from production machines/databases? Oh and the IDs were generated from an auto-increment column?
Can you think of more?
Sure, there are ways to prevent against each kind of disaster. The question then is: Are you prepared against 'all' of them?
The disaster recovery plan of your company may help your steer out of such a disaster, but in the case of Charter, their DR plan didn't cover this. They do, however, have plans to reimburse their customers $50. Don't know if that'd be sufficient to keep customers from switching.
If you are someone responsible for administering and executing disaster recovery plan(s) for your company, you may find my "Disaster is Inevitable--Are you ready" session at the MySQL Conference 2008 interesting. Plus, we can have great conversation afterward. :)
See also: disaster recovery, disaster recovery journal, mysql conference
I was getting chills reading about Charter Communications, a St. Louis based ISP, accidentally deleted 14,000 active email accounts along with any attachments that they carried. All the deleted data of active customers is irretrievable. As someone who is responsible for data of one of the top 15 heaviest trafficked site in the world, according to Alexa, I know, I'd HATE to be in shoes of the person responsible for this.
As I was reading the news story, I was constantly thinking about the title of Jay and Mike's 2006 presentation: "What Do You Mean There's No Backup?"
Once a disaster happens, you can immediately think of the possible ways it could have been avoided. The real challenge is implementing ways of avoiding all types of disasters before they happen.
For instance, to protect against such a disaster, or at the very least, be able to recover from its effects, Charter communications could have:
1. fully tested the script on a QA/test box to ensure no test records of active users are deleted.
2. created a backup of the data by creating a file system snapshot just before running the script. That way deleted data can be recovered. Depending on your operating system/storage system, there are a lot of tools available that let you take file system snapshots such as fssnap (Solaris), LVM (Linux).
3. had a recoverable backup. There are a lot of cases out there where either no backup exists or the one that does exist, turns out to be corrupt. With a periodic backup, Charter could have, for instance, just announced to their customers that they lost their new emails since last week, instead of dropping the ball and saying that *all* their email is lost. Even having an off-site backup in this case would help if selective restore from that backup was possible.
BTW, Just a few days ago, I was testing a random sample of backups and found backups of a database to be corrupt. That triggered a system wide check of backups. The best way I have found is to have a list of backups from all databases sent to me by email. My report contains information about backups running at the time the script was generated and the backups that were created the previous night.
4. If the data deleted was on a database such as MySQL, recovery from this disaster would be possible by keeping a slave intentionally behind.
What are some of the other ways you can think of to avoid a disaster or to execute a recovery plan?
There are many ways a disaster like this can be triggered. A few, seemingly bizarre but very real, that come to my mind:
- What if you accidentally re-run a previously executed DELETE command, stored in your mysql client history, in a hurry, on the wrong server? Or you re-ran a disastrous command in your shell history in the wrong directory?
- What if you used a list of IDs generated from your QA/test machine to delete users from production machines/databases? Oh and the IDs were generated from an auto-increment column?
Can you think of more?
Sure, there are ways to prevent against each kind of disaster. The question then is: Are you prepared against 'all' of them?
The disaster recovery plan of your company may help your steer out of such a disaster, but in the case of Charter, their DR plan didn't cover this. They do, however, have plans to reimburse their customers $50. Don't know if that'd be sufficient to keep customers from switching.
If you are someone responsible for administering and executing disaster recovery plan(s) for your company, you may find my "Disaster is Inevitable--Are you ready" session at the MySQL Conference 2008 interesting. Plus, we can have great conversation afterward. :)
See also: disaster recovery, disaster recovery journal, mysql conference
Monday, May 07, 2007
Can you trust your backups?
It is a good practice to check the integrity of your backups from time to time. You don't want something like this happening to you :)
time gunzip -f -v entrieva/db4/guestbook_M-070507.tar.gz
entrieva/db4/guestbook_M-070507.tar.gz:
gunzip: entrieva/db4/guestbook_M-070507.tar.gz: unexpected end of file
real 67m56.782s
user 48m40.006s
sys 14m14.584s
Subscribe to:
Comments (Atom)