MySQL

Quite often we need to perform a so-called “MySQL instance audit”. This common DBA procedure should give you a general view of the MySQL environment. You may be interested in a basic understanding of what kind of operation MySQL performs, how much memory does it use, or how well does it look from the performance point of view. There is no easy out-of-the-box way to do such an audit on a MySQL server. Fortunately there are several tools to make this process easier. Among most popular are mysqlreport and MySQLTuner. In this post I’d like to give a brief overview of MySQLTuner.

Do you have a master-slave MySQL set up? Ever do DDL changes on the master? You may be hit with a serious data integrity bug. Read on.

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…If you follow the common practice of symlinking /usr/local/mysql to your local tarball installation of mysql, this symptom would not exist. But, what would still exist is the issue of DBD trying to load from /usr/local/mysql even though nothing tells it to do so.

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.If the master crashes for whatever reason (say, a hosting company accidentally punches the power button on a master server) it will often cause corruption of the binary log. When the master comes back up, the slave cries about a non-existent binary log position. Possible solutions

The unsung heroes of InnoDB are the logfiles. They are what makes InnoDB automatic crash recovery possible. 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. On crash recovery, MySQL can read the redo log (InnoDB log files) and apply any changes that were not written to disk. That is the basic functionality of the InnoDB log files. Given this, let’s look at some of the different parameters and their ramifications.

As I putter around the MySQL INFORMATION_SCHEMA, I am finding lots of undocumented behavior for fields that should be straightforward. when I was looking at the VIEW_DEFINITION today, I noticed an odd thing. Even though I had permissions to see the view definition, the INFORMATION_SCHEMA.VIEWS table sometimes came up blank for the VIEW_DEFINITION. I had to figure out why, and now that I know, I’m not sure if it’s a bug or a feature…..can you figure it out?

A while ago, MySQL developed a Community Contribution Agreement for community contributions to the MySQL source code. While browsing the MySQL Forge Wiki I came across a page about how the Community Contribution Agreement has changed

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…

Over the last few weeks I’ve been looking at several customers’ slow query logs, and I found in many of them an odd type of query. These are SELECT statements that contain an IN clause that includes dozens, sometimes hundreds of values. These statements often end in the slow query log. I’m not sure if these queries are this way by design or if they are generated by a specific database development tool. What’s your experience with these kind of expressions? I’d love to learn where do these gigantic IN clauses come from and hear some use-cases.