Pythian Group

This is the first post in a series dedicated to exploring the backup and availability options in SQL Server 2005 and 2008. It is aimed at anyone unfamiliar with the database backup options in SQL Server 2005 and 2008. I’m not going to explore every single option or scenario, the goal is to give you the language and the tools to do deep dives where you need to.

SQL Server 2005 has several DBA-job-saving options available to the would-be administrator. Think of a Database Backup as the technology to save data and Database and Availability as the technology to keep it online and available to it’s consumers.

A very brief introduction to SQL Server databases

Its important to have a few SQL Server database basics in order to understand the backup options. If you know what a recovery model is, and the difference between an .ldf and .mdf file, you can skip this section. If this is as good as a foreign language to you, read on.

Read the rest of this entry . . .

Recently while we were building a slave with a newer version of MySQL 5.1 from an InnoDB Hot backup, the following error occurred when we ran mysql_upgrade:

mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Running 'mysql_fix_privilege_tables'...
ERROR 13 (HY000) at line 311: Can't get stat of './mysql/general_log.CSV' (Errcode: 2)
ERROR 13 (HY000) at line 316: Can't get stat of './mysql/slow_log.CSV' (Errcode: 2)
FATAL ERROR: Upgrade failed

The problem is that in MySQL 5.1, it is possible to log the slow query log and general log to tables in the mysql schema (source: Selecting General Query and Slow Query Log Output Destinations). These tables are created by default as CSV tables for performance reasons, and are created even if MySQL is set not to log to tables.

CSV tables, however, are not copied as part of the InnoDB Hot Backup process (by the wrapper script innobackupex.pl), thus creating this error. The fix to get the slave working Read the rest of this entry . . .

If you are using InnoDB Hot Backup utility and the innobackup.pl wrapper script, be very careful if you are not running backups under the system mysql user. There is a bug which causes InnoDB Hot Backup to sometimes report a successful backup when it actually failed. Read the rest of this entry . . .

A few days ago I had a new idea for a blog post. A post about what it really takes to be a good database administrator. I began by researching what others had done on the topic. At the end of this post you will find links to six of the posts I found that provided some insight into this question. Even after uncovering this information, I thought I could add something to the mix from my own experiences. So here we go!

Read the rest of this entry . . .

I finished uploading the backup presentation that I did last Monday at the Boston MySQL User’s Group. It is here: http://www.paragon-cs.com/presentations.

I cover the basics of backup/recovery and disaster planning. Total time is about an hour and three minutes. It was a lot of fun and the Bostonians seem to appreciate it. Thanks, Sheeri for filming and editing!

The Boston MySQL User Group was lucky enough to get Keith Murphy to speak at the June User Group meeting, about backups.

The slides are at:
http://www.paragon-cs.com/presentations/backups.pdf

Direct play the video at:
http://technocation.org/node/559/play

Direct download the video (351 MB) at:
http://technocation.org/node/559/download

Links referred to in the presentation:

MyLVMBackup by Lenz Grimmer
http://lenz.homelinux.org/mylvmbackup/

InnoDB Hot Backup:
Prices are at:
http://www.innodb.com/hot-backup/order/
and at the time of this writing are:
1-Year License € 390 USD$ 605 per server
Perpetual License € 990 USD$ 1540 per server

Zmanda
prices are at: http://network.zmanda.com/shop/home.php?cat=1,3
Current prices are between the range of $100-$450, and a license is needed for each server and each client. So if you have 1 server and 3 clients, you need 4 licenses.

Online Backup
This is interesting reading about online backup, an article about how the online backup works at: http://blogs.mysql.com/peterg/2008/05/19/mysql-60-feature-2-online-backup/

This page (at the bottom, under “Replication”) states that the desired behavior is that the binary log does not log the restore or the backup. However, it does state that this is not implemented yet, nor even decided upon.

There’s also a great page on Online Backup at the MySQL Forge: http://forge.mysql.com/wiki/OnlineBackup

And if you’re short on time, the manual has a short page at http://www.nongnu.org/rdiff-backup/
duplicity: http://duplicity.nongnu.org/

It was mentioned that some people use a code repository to backup their database: http://krow.livejournal.com/593424.html

I’ll preface this post with a note that the story itself is not really work- or DBA-related, but the lessons learned certainly are. I consider myself fairly conscientious when it comes to internet security and backing up my most important files (pictures of kids, music, etc), and I am diligent about taking at least a weekly backup of those files so that if (when) something catastrophic happens, I am ready. Once again, as my short life as a DBA has proven, theory and practicality rarely meet.

Monday night, I needed a stress reliever. My family and I recently moved to Ottawa from Wichita and it has been a five-month-long ordeal. I used to be an avid gamer, but with changing jobs, selling the house, moving, family issues, etc., I haven’t had a lot of time to kick back and relax. I decided to visit one of my favorite websites, www.armchairgeneral.com, to see if they had any good reviews of new games to play. One in particular caught my eye, called “Mount and Blade”. Looks like an interesting twist on your standard medieval-based RPG — the combat is in first person. The game is still in beta, but can be downloaded by anyone wanting to play it. So I downloaded it from a link on the game’s website that pointed me to CNet.

Not the wisest decision in my life, as about 10 minutes later my virus scan software (AVG) warned of viruses and Trojan horses. I quarantined everything that it found, but it wasn’t enough — I was officially infected for the first time in four years. I tried for an hour that night to undo the damage, but didn’t have much success. This bug was nasty — it even went so far as to detect that I tried to go back to a previous System Restore Point and it erased them. My colleagues at Pythian took an immediate interest in my dilemma and suggested several helpful tools (like Process Explorer) that I also tried with limited success.

Read the rest of this entry . . .

In 3 words:

They already do.

MySQL Enterprise is more than just a binary. http://mysql.com/products/enterprise/ has the details on the other features MySQL Enterprise includes.

One of these features is the MySQL Enterprise Monitor, which is closed source, proprietary alerting software.

So when bloggers make statements such as:

MySQL will start offering some features (specifically ones related to online backups) only in MySQL Enterprise. This represents a substantive change to their development model — previously they have been developing features in both MySQL Community and MySQL Enterprise. However, with a shift to offering some features only in MySQL Enterprise, this means a shift to development of those features occurring (and thus code being tested) only in MySQL Enterprise.

source

All they are doing is showing their lack of knowledge about what MySQL Enterprise really is. Complaining that MySQL will likely charge for online backups is a valid one, but currently InnoDB Hot Backup is for sale only. There are solutions out there that do not cost anything, but nothing that MySQL produces. The only free [mostly] online backup product is mysqlhotcopy, and that has limitations such as “for MyISAM tables only”.

While I have said that software should be open source and free, where “free” means “free as in water”, that does not mean that we should take it for granted that free software will always be there. The path to that is to have a contrast of software one pays for, and software one does not.

Of course, that is not why MySQL charges for the software. They charge for some of their products because without money, they cease to exist, and then even the free version stops being developed on.

“A necessary evil” is a dangerous phrase, and can lead to a slippery slope….but in this case, I do not envision that it is a big problem. Maybe I will regret saying this if things keep getting more and more closed, but I do not believe so.

And I, too, am frustrated that we have been told that “online backups are coming” only to find out that they may not come in the means I expected. However, software is not bug-free, often does not come out on time (MySQL 5.1 itself is a year late!), and sometimes, promises get broken.

My faith in MySQL is unbroken, and I hope most others reading this feel this way as well.

I try to do a decent job of advocating for caring about good backups and business continuity strategies in my 7 Deadly Habits article.

But this one beats them all:

Just too funny and great not to share. Found via this reddit article, where there is a lively discussion underway.