A short time ago I posted how I was Using XtraDB Backup to backup InnoDB. Overall, the blog post was positive, but experiences that others have had (and commented to on that blog post) have made me want to put out another short article about using XtraDB backup.

The first few points remain the same — the backup process is stable, we were able to use the binaries without compiling, and using Innobackupex as the wrapper script, analogous to Innobackup.pl.

However, we did figure out why Xtrabackup had to be run as the mysql user:

Xtrabackup writes to the data dictionary file (ibdata1, for example). We have not examined if it also writes to the data and index files (still ibdata1 by default, or the .ibd files when using innodb_file_per_table). [EDIT: The authors of Xtrabackup have commented below as to why the write occurs:

xtrabackup is kind of very small and restricted instance of InnoDB, and reuses a lot of InnoDB code.

InnoDB by default requires O_RDWR option on ibdata1 files at start, and xtrabackup therefore also did that. In the latest push to trunk it was fixed, now xtrabackup opens file with O_RDONLY flag.

When the new version is released, we will be sure to test it so that we can run the backup as a read-only user, and report back.]

On the one hand, Xtrabackup is a free tool. On the other hand, modifying InnoDB’s underlying files risks corrupting all the InnoDB tables in the system. Which is a tricky situation when it is your backup tool that might cause corruption that is beyond repair, as you do not know if you can trust your backups.
Read the rest of this entry »

Today’s trivial MySQL system variable:

old_alter_table

The interesting bit is that this is a system variable, and shows up in SHOW GLOBAL VARIABLES, but is not documented on the Server System Variables manual page.

Instead, it is documented on the manual page for Server options.

Unfortunately, that documentation is very sparse. It tells us:

old-alter-table is an option that can be set in an option file (such as /etc/my.cnf)

old_alter_table is the name of the variable.

And….that’s it. It is neither a system nor status variable, there is no scope, and no entry for whether or not it is dynamic. These last 2 are baffling, though they show up in other variables in the matrix on the “server options” manual page. The scope is either session or global; there is no NULL — the scope may not be *relevant*, but it still exists.

As well, either the variable can be settable on commandline, or not. There is no meaning to a NULL value in that column.

The most egregious issue is that there is no documentation whatsoever about what the variable does. What happens if I set old_alter_table to ON?

(My guess is that it’s a placeholder for the behavior of ALTER TABLE in 5.0 and earlier, perhaps it disables the use of ALTER TABLE ONLINE? My second guess is that whatever the functionality, it is not implemented yet, similar to date_format).

The July meeting of the Boston MySQL User Group will feature Eric Day, a prominent Drizzle developer, talking about Drizzle and Gearman:

In this talk we will discuss two growing technologies: Drizzle and Gearman.

We will explain what the Drizzle project is, what we aim to accomplish, and an overview of where we are at. We will also be introducing the fundamentals of how to leverage Gearman, an open-source, distributed job queuing system. Gearman’s generic design allows it to be used as a building block for almost any use - from speeding up your website to building your own Map/Reduce cluster. We will tie Drizzle and Gearman together and demonstrate how they work in a custom Search Engine application.

————————

Here is the URL for MIT’s Map with the location of this building:
http://whereis.mit.edu/map-jpg?selection=E51&Buildings=go

This map shows the MBTA Kendall Stop:
http://whereis.mit.edu/map-jpg?selection=L5&Landmarks=go
(the stop is in red on that map, and you can see E51 in the bottom right)

Here are the URL’s for the parking lots (free and open to the public after 3 pm):
http://whereis.mit.edu/map-jpg?selection=P4&Parking=go
http://whereis.mit.edu/map-jpg?selection=P5&Parking=go

Free pizza and soda will be served, so please RSVP accurately.

To RSVP anonymously, please login to the Meetup site with the e-mail address “admin at sheeri dot com” and the password “guest”.

For more information, see: http://mysql.meetup.com/137/

This is the 150th edition of Log Buffer, the weekly review of database blogs. Someone accidentally left Dave Edwards‘ cage unlocked, and he escaped, thus leaving me with the pleasurable duty of compiling the 150th weekly Log Buffer.

Many people other than Dave are finding release this week. Read the rest of this entry »

About six months ago, the question of storing images in a database came up. This is one of my favorite topics, and has many database-agnostic parts.

Personally, I think “tell me about storing images in a database” is actually a great interview question, because you will be able to see the difference between someone who has just memorized “what’s right” versus someone who is really thinking. It also helps you see how someone will communicate — if they just say “NEVER do it, it’s as bad as crossing the streams!” then they are a type of person that gives you a short answer, without much explanation, and without many nuances. (That may be what you are looking for, but usually you want someone who gives reasons for why they strongly feel one way or another).

Consider the following cases:
Read the rest of this entry »

As Baron points out, Percona’s Xtrabackup tool can be used just like InnoDB Hot Backup.

Many are wondering, “is it good enough?” In fact, I wondered the same thing, and after a few weeks of using and testing Xtrabackup (on machines that have MyISAM and InnoDB tables), I can say:

0) We have not run into any problems with backing up InnoDB tables; on the machines we’ve run it on (RHEL 5.3 and Debian) it has been stable.

1) We were able to use one of the binaries provided by Percona — we did not have to compile anything. Binaries are provided for 64-bit versions of Linux

2) Innobackupex is the script analogous to Innobackup. Basically, this is a wrapper script for Xtrabackup that will copy the non-InnoDB tables.

3) Usually we run InnoDB Hot Backup from a non-privileged user. We had to run Xtrabackup as the mysql user; we think this is because it writes a state file in the datadir, but we did not dig too hard — once we saw that running it from the mysql user worked, we just left it like that.

4) Xtrabackup does not have a –compress option like InnoDB hot backup does. Having the option would be useful, but I believe this option will come soon.

Currently, for clients who want a hot backup solution but do not want to pay for InnoDB Hot Backup, Pythian recommends Xtrabackup.

Today’s contradiction:

MySQL has server variables named new and old.

The new variable can be set per-session and globally, and is dynamic. The old variable is not dynamic, and only global in scope. Both default to FALSE in MySQL 5.1.

According to the manual, the new variable:

was used in MySQL 4.0 to turn on some 4.1 behaviors, and is retained for backward compatibility.

That same page notes the following about the old variable:

when old is enabled, it changes the default scope of index hints to that used prior to MySQL 5.1.17. That is, index hints with no FOR clause apply only to how indexes are used for row retrieval and not to resolution of ORDER BY or GROUP BY clauses.

That’s right — the old variable changes some index hint behavior to before 5.1.17, and the new variable is provided for backwards compatibility of a 4.0 function.

Perhaps now that they have already used the vague terms, MySQL will stick to descriptive names, such as old_index_hints and new_behavior (or, even better, specifying what that new behavior is. If not in the variable itself, at least in the manual!)

Yesterday, The Pythian Group issued a press release about my book, Pythian’s partnership with Sun, and our new “MySQL Adoption Accelerator Package”. I am not a marketing guru, but I can tell you what we the package means in terms of new work that the MySQL teams have been doing.

Basically, the MySQL Adoption Accelerator Package combines customized training with a comprehensive audit of systems. The name “Adoption Accelerator” makes it sound like it’s only for new applications that are almost ready to go live. What the program actually does is have us evaluate your systems, and intensively train you in the areas you want and need. The program is designed to suit all your needs, whether it’s teaching you about one topic (say, query optimization) or an entire range of topics, from Architecture to ZFS (special issues with running MySQL on ZFS, that is, but that did not fit a cute “from A-Z” model…).

Whether you have already adopted MySQL or are thinking of converting from Oracle, DB2, Microsoft SQL Server or even sqlite, this new package may be what you need.

And now, the full text of the press release, for the curious:

‘MySQL Administrator’s Bible’ Hits the Bookstands: Pythian Launches MySQL Accelerator Adoption Package

The Pythian Group, the leading provider of remote database services, is pleased to announce that the much-anticipated MySQL Administrator’s Bible, written by employee Sheeri K. Cabral, is now available.
Read the rest of this entry »

In the wake of Meetup.com changing their sponsorship agreements, Technocation, Inc., an international not-for-profit group, has set up a fund for user group sponsorships. You can use the button below to donate any amount of money in US funds via PayPal:

(all monies sent through that button will be earmarked as a directed donation to the “User Group Fund”. In the interest of not cluttering up this blog post with a Donate button for each currency, you can use PayPal to send funds in *any* currency to “donate@technocation.org”. Just be sure to specify if you want the money to go to specifically to the User Group Fund.*)

Note that meetup.com’s fees are $144 per year ($12 per month).

Four years ago, MySQL and Meetup.com entered into an agreement. I have no idea of the details of this sponsorship, though from reading in between the lines, I believe the sponsorship was an in-kind sponsorship — that is, no money exchanged hands, but there were mutually agreed upon benefits. I have no idea what the benefits to Meetup.com were — publicity or free consulting, perhaps.

What I do know is that Meetup.com is changing the way they are doing sponsorships. Read the rest of this entry »

A year ago, the outline was being written. A lot of work was crammed into the intervening months, and I am happy and proud to announce that the MySQL Administrator’s Bible has been published, and is sitting on the shelf at many major booksellers already. The official publication date is today — Monday, May 11th, 2009 — although some stores have had copies for a week, including Amazon.com.

The MySQL Administrator’s Bible, published by Wiley Press (available on Amazon.com at http://www.amazon.com/MySQL-Administrators-Bible-Sheeri-Cabral/dp/0470416912/, fully covers how to administer MySQL 5.1. It is suitable for people new to MySQL, although as an experienced MySQL DBA I can say that I learned a lot while researching and writing this book, and I believe that even veteran DBAs can learn from this book.

This book for anyone who wants to learn how to use and administer MySQL. Unlike other beginning books, however, it will appeal to DBAs who are experienced in other database systems such as Oracle, Postgres, Microsoft SQL Server, DB2, Sybase, Firebird. For example, the book contains a chapter on how MySQL’s SQL is different from the SQL standard. Basic SQL has been added as a feature of the companion website, so that those who do not know how to form a query can learn, but those who are well versed in SQL do not feel they have wasted money buying a book where they already know some of the material in it.

The detailed table of contents can be viewed or downloaded as a PDF. Or, if scanning the index is your cup of tea, it is also available as a PDF. The cover price is $49.99, but Amazon.com is selling it for $31.49.