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

(one note, I have updated a previous blog post by adding the video for the LISA presentation I gave entitled "How to Stop Hating MySQL")OpenSQL Camp is in full swing! Baron Schwartz has done an amazing job organizing this free unconference.We are well into the 2nd session of the day, and the quality of the presentations is excellent (though I expected that!) and it is always great to see people.Some pictures....

I was surprised to find out that Innobase's Innobackup script did not read the socket file location from the "my.cnf" file.  So I added in as a feature.A few notes:\tThese modifications are against version 1.4.0 of the script.  A quick search for the "socket" keyword in the 1.5.0 version of the script did not reveal any simillar functionality to what I'm proposing here.\tThese modifications have not actually bee

So you want to store URLs in MySQL, and the URLs have those annoying "%20%27%7C%26%5E%2B%2D%25" symbols? And you want to be able to show your users some kind of human-readable information. You might want to consider using this trick. Take this list of commonly escaped characters as an example:%20 - space%27 - '%7C - |%26 - &%5E - ^%2B - +%2D - -%25 - %So, how about we do some search'n'replace on that?mysql> SET @url:='%20%27%7C%26%5E%2B%2D%25';Query OK, 0 rows affected (0.00 sec)mysql> SELECT @url as original, -> REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( -> REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( -> @test,'%20',' '), -> '%27','\\''), -> '%7C','|'), -- REPLACE() is case sensitive -> '%7c','|'), -- so we have -> '%26','&'), -> '%5E','^'),  []

Yesterday evening, a friend of mine had some issues with installing DBD::mysql, and asked if I had encountered the same issue. The problem, as the output from "make test" showed, was that certain symbols was missing:# Tried to use 'DBD::mysql'.# Error: Can't load '/Users/westerlund/src/perl/DBD-mysql-4.008/blib/arch/auto/DBD/mysql/mysql.bundle' for module DBD::mysql: dlopen(/Users/westerlund/src/perl/DBD-mysql-4.008/blib/arch/auto/DBD/mysql/mysql.bundle, 2): Symbol not found: _is_prefixFair enough, this is related to a 64-bit issue with MySQL---at least with my Perl version, which is now: Summary of my perl5 (revision 5 version 10 subversion 0) configuration: Platform: osname=darwin, osvers=9.5.0, archname=darwin-thread-multi-64int-2levelIf you try to link to a x86_64 version of MySQL, then you get the above mentioned error. So, I downloaded an x86 version of MySQL and tried again. The output from  []

The unsung heroes of InnoDB are the logfiles. They are what makes InnoDB automatic crash recovery possible.Database administrators of other DBMS may be familiar with the concept of a "redo" log. When data is changed, affected data pages are changed in the innodb_buffer_pool. Then, the change is written to the redo log, which in MySQL is the InnoDB logfile (ib_logfile0 and ib_logfile1). The pages are marked as "dirty", and eventually get flushed and written to disk.If MySQL crashes, t

Over the weekend, I worked on a client's two computers, trying to get a slave in sync with the master. It was during this time that I began thinking about:a) how this never should have happened in the first place.b) how "slave drift" could be kept from happening.c) how this is probably keeping some businesses from using MySQL.d) how MySQL DBAs must spend thousands of hours a year wasting time fixing replication issues.I'll be the first person to tell you that the replication under MySQL is pretty much dead-simple to set up. My only complaint is that it is annoying to type in the two-line "CHANGE MASTER" command to set up a new slave. Even so, it makes sense.It is also very easy, however, for a slave to end up with different data than the master server has. This can be caused by replication bugs, hardware problems, or by using non-deterministic functions. Without proper permissions, a user/developer/DBA can log  []

As I putter around the MySQL "INFORMATION_SCHEMA", I am finding lots of undocumented behavior for fields that should be straightforward. For example, the "VIEWS" table holds information about views, and the "VIEW_DEFINITION" field contains the view definition, right?Well, when I was looking at the "VIEW_DEFINITION" today, I noticed an odd thing. Even though I had permissions to see the view definition (as proven by the "SHOW CREATE VIEW

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!

If you work with databases long enough, you run into a certain problem. You have a mysqldump file* of a table or a database, and you need to import it into your new database. However, you need to change something in the file first. Maybe the "INSERT" statements need to be changed to "INSERT REPLACE". You fire up "vi "and load the file, but when you go to search and replace, "vi" runs out of memory and doesn't complete the operation. Or maybe the dump file is just so big it won't even load in the first place. What do you do in this situation?Well, one simple solution is to use the "sed" tool to modify the file. Sed actually stands for "stream editor". The "vi "editor would be considered a static editor in that it loads all of the file into memory at once. If you run out of memory, you are out of luck. With  []

Simple auditing, i.e., knowing what changed recently, can save you tons of time while troubleshooting.I know that, in the ideal world:\tEverything is supposed to be done through configuration management.\tEverything is documented and all changes are tracked through a VCS.\tEvery DDL or set global is trapped via MySQL Proxy and logged.But there are always ways to bypass the gatekeepers. Changes can go in unnoticed. An hour or so later, your database performance suddenly changes for the worse, and you get that phone call.First you check if anything caused an actual error. You look around at a few log files and nothing shows up. The next thing you ask yourself is, did someone change anything in the last little while. Of course, everybody says no. After a few hours of digging, comparing schemas, diff-ing old and current config files, you actually find what has changed, put it back the way it was and everything is back to normal.  []

Recently, a customer wondered if they should start using the "innodb_file_per_table" option, or if they should continue to use the large InnoDB tablespace files created by the "innodb_data_file_path" option in the "my.cnf" option file.Many people still use the older "innodb_data_file_path" option because it is the default for MySQL server. So, what are the benefits of using "innodb_file_per_table" instead?The "innodb_file_per_table" makes for easier-to-manage files. With this option each InnoDB table has its own data and index file under the database directory. As an example, if you had table "foo" located in database "xyz" the InnoDB data file for table "foo" would be "/var/lib/mysql/data/xyz/foo.idb". Each table would have its own idb table in the appropriate database directory. This is in contrast to using the  []

It's been a while since the MySQL Management Plug-in 0.42 was released. Since then, I quietly updated it to version 1.0. The changes were very few; the biggest news was that the plug-in was certified by Oracle and added to OTN Oracle 10g Grid Control Extensions Exchange (see at the bottom).I think the ne

One of the most frequently needed functionality in the MySQL Proxy is the need to know which server you are on. This is not given, on purpose, by the proxy, because the proxy is supposed to be transparent. It is not supposed to matter which back-end server you are on.However, for testing purposes we often want to know which back-end server we're on. Thus I developed functionality for "SHOW PROXY BACKEND [INDEX ADDRESS OTHER]"."SHOW PROXY BACKEND INDEX" -- gives t

Almost 2 years ago, in How Open Do You Have To Be To Be Open Source? I wrote:Google and Yahoo! are not rich because they have secrets. They are rich because they started with secrets, but I believe they could safely let their secrets out with very little loss of revenue.Matt Asay’s recent post Google’s slow transformation into an open, transparent company made me dig up that post, which by many standards is old in terms of time, but it’s only now that some of this change is actually happening.Matt ponders,It remains to be seen what, if anything, Google will actually open, but I trust its track record on living up to its word more than Microsoft’s, which also went through a flurry of “We’re now really open!” announcements lately that actually netted the industry…not much.In interesting news, at last night’s Boston Sun/MySQL event (more on that  []

According to the manual, FLUSH LOGS is supposed to:Closes and reopens all log files. If binary logging is enabled, the sequence number of the binary log file is incremented by one relative to the previous file. On Unix, this is the same thing as sending a SIGHUP signal to the mysqld server (except on some Mac OS X 10.3 versions where mysqld ignores SIGHUP and SIGQUIT).If the server is writing error output to a

I was asked today about the ANSI_QUOTES SQL mode.According to http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html, ANSI_QUOTES mode changes the functionality of double quotes (") to be like the backtick (`). Normally the functionality of double quotes is more like that of single quotes (').You might use this when you have a table with spaces or other special characters you would like to escape, without hav

So, how does one gather statistics on indexes? With InnoDB it's one thing, with MyISAM it's another thing. This post however, will focus on MyISAM. There are various ways, each having their own drawbacks and positive sides.What about "ANALYZE TABLE"? It's great -- very fast in InnoDB. But with MyISAM a lock occurs while scanning the table. "OPTIMIZE TABLE"? Still relatively quick with MyISAM, unless the table in question is huge. InnoDB requires an "ALTER TABLE" -- it might take forever, depending on the server configuration, as "OPTIMIZE TABLE" for InnoDB maps to a "ALTER TABLE tableName ENGINE=InnoDB"We all know how "ANALYZE TABLE", "REPAIR TABLE", and "OPTIMIZE TABLE" work with MyISAM. However, there's a less explored way, for MyISAM -- the "myisam_stats_method".

InnoDB is a storage engine that uses MVCC (described shortly) to provide ACID-compliant transactional data storage using row-level locking.  MVCC stands for Multi-Version Concurrency Control.  It is how InnoDB allows multiple transactions to look at a data set of one or more tables and have a consistent view of the data. MVCC keeps a virtual snapshot of the dataset for each transaction.  An example will make this clear. Let's assume you have two transactions (and only two transactions) running on a system. If transaction A starts at 10:45:56 and ends at 10:45:89, it gets a consistent view of the dataset during the time that the transaction runs.  If transaction B starts at 10:45:65, it would see exactly the same view of the dataset that transaction A saw when it began the transaction.  If transaction B started at 10:45:95, it would see the modified dataset after transaction A made modifications. During the duration of each  []

It's time to continue our series on the transactional storage engines for MySQL. Some might question why I even include Falcon because it is very much beta at this time. MySQL, however, has made quite an investment into Falcon, and while it is currently beta, the code is improving and it looks like that it will be production-worthy when MySQL server 6.0 hits GA.If this is the case, it is important to begin to understand what Falcon was designed for and how it differs from other transactional engines such as InnoDB. I am going to concentrate quite a bit on the Falcon/InnoDB comparison as that is what everyone wants to talk about. This is despite my having heard MySQL employees repeatedly make statements to the effect of, "Falcon is not going to replace InnoDB," or "Falcon is not competing with InnoDB." Well, take that with a grain of salt. It certainly seems to me that they are competing for the same  []