MySQL MHA by Oracle ACE Director Yoshinori Matsunobu is an excellent open source tool to help in providing HA with native MySQL replication. The installation however is dependent on some Perl packages and to the untrained eye this may be an issue if you are using Percona Server as your choice of MySQL implementation.

The MHA Node page requires the perl-DBD-MySQL package to be installed. The installation on RedHat/CentOS/Oracle Linux look like this:

$ sudo yum install perl-DBD-MySQL
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: mirror.anl.gov
* extras: mirror.anl.gov
* updates: mirror.anl.gov
Setting up Install Process
Resolving Dependencies
--> Running transaction check …

This would have to be one of the most common MySQL error messages that is misleading to the end user developer. The MySQL Manual page confirms the broad range of possible conditions, but offers little to a PHP developer that does not speak MySQL Geek. I am commonly asked to help solve this issue from a developer.

The problem is that there are several conditions that can cause this error, and a more meaningful explanation to the end user would help in addressing the issue. In general terms, this actually means “Your SQL statement has failed because the connection to the database has been disconnected because of ???”.

Here are a few common situations and how to check for what “???” is.

1. Your MySQL server really did go away.

We can easily check this by looking at the server uptime and the server error log.

$ …

Being the end of the quarter, there has been some planning going on this month about goals for Q1 2013 as well as meeting our goals for Q4 2012. Our biggest goal was to stop using MySQL 5.0, which we have successfully done. We only have one server left on MySQL 5.0, and that has a compatible MySQL 5.1 server waiting for a few developers to get back from their well-deserved vacations to migrate off. In December, we finished upgrading 2 servers to MySQL 5.1.

- Looked at the top 30 Bugzilla queries and started to give optimization tips for MySQL.
- Did our regular purge/defrag of TinderBox PushLog.
- Worked on integrating our datazilla code with chart.io features.
- Helped change the data model …

I’m not a wizard with infographics, but I can do a few pie charts. I copied the data to the right of the pie charts for those that want to see the numbers. Overall, there are almost 400 databases at Mozilla, in 11 different categories. Here is how each category fares in number of databases:

Here is how each category measures up with regards to database size – clearly, our crash-stats database (which is on Postgres, not MySQL) is the largest:

So here is another pie chart with the relative sizes of the MySQL databases:

I’m sure I’ve …

You may have noticed that I stopped posting the “weekly news” from the Mozilla DB Team. After going through the Operations Report Card and applying it to DBAs in OurSQL Podcast numbers 111, 112, 114, 115 and 116, I started thinking that the updates were really more like metrics, and it would better serve my own purposes better to do the updates monthly.

The purposes of doing this type of blog post are:
0) Answering “So what does a DBA do, anyway?”
1) Answering “DBA? At Mozilla? Does Firefox have a database? Why does Mozilla have databases, and what …

Back in the summer, I heard about how Gene Babon of Boston’s PHP Meetup Group was brilliantly helping folks learn web programming through PHP Percolate, jQuery Jam and HTML5 Brunch. While he was explaining this, I thought to myself, “this needs to be done for MySQL!”

And so it will be. Starting in just over a month, January 1st, 2013, the first MySQL Marinate group will happen. We already have over 50 people signed up for the event itself, and as of right now there are 85 people in the meetup group itself, with over 30% of …

I am seeking the help of the community. I am working on an evaluation project about schema design in open source applications. While it’s possible for me to download the software of many popular apps, and install the software and then do a mysqldump, it takes time. Quite often there is no simple schema.sql file, but a process for creating the schema. If you are using an open source project, would you take a moment and run the following.

$ mysqldump -u[user] -p --skip-lock-tables --no-data --databases [schema]  > [schema].sql

This will only dump the table definitions, and should therefore contain nothing company specific. I have at this time:

• WordPress
• Drupal
• Mediawiki
• OS Commerce
• Joomla
• EzPublish
• PHPWiki

I am open to any projects, and it doesn’t matter if the version is not the most current, what I am seeking is …

It started with a tweet from a coworker asking if I can recommend reading for making a master/master MySQL server. There are plenty of caveats about writing to only one master at a time, and that master/master is not write scaling, but I think I tackled it pretty well in the MySQL Administrator’s Bible. It is not a very long topic, so I made a PDF of the relevant pages. High Performance MySQL also has a few pages that I would recommend reading, and the third edition has similar information as the Bible, although it goes into more detail about why you might use master/master replication and what might go …

I have a soft spot for hierarchical databases.  My first database-related job was programming in M/Mumps.  I know the standard history of databases says that hierarchical databases are a relic of the past, and that, thanks to Codd, relational databases solve many of the problems of hierarchical (and other kinds of) databases.  I like relational databases – I was an Oracle DBA, I’ve worked with DB2, Sybase, Postgres, mSQL, others, and now MySQL.  I really like InnoDB.  However, I am occasionally sad that hierachical databases seem a thing of the past.
Or are they?  Yesterday I had a thought that hierarchical databases are much more widely used than relational databases.  In fact, maybe every single computer has a hierarchical database that is used by every computer user, whether they have database software installed or not.  The file system!  Isn’t that a hierarchical database?  The idea …

Twice last week, a developer wanted to convert the existing datetime values in a database to UTC. The datetime values were the default for the server, which was the US/Pacific time zone, which is subject to Daylight Saving Time changes. Both developers for both applications wanted to convert all the times to UTC, so there would not be any changes due to Daylight Saving Time, and asked me for an easy query to know which times should be changed by adding 7 hours and which times should have 8 hours added to them.

The good news is that MySQL has some built-in functionality to make this easier. You may know about the CONVERT_TZ() function in MySQL, and that you can use it in a query to convert times like this:

mysql> SELECT CONVERT_TZ(NOW(),'-8:00','-0:00');
+-----------------------------------+
| …