Do we need a MySQL Cookbook?

The blog title says it all: Do we need a MySQL Cookbook? I tend to think so.

This seems to be something that is missing with current MySQL documentation. There is lots of information available but finding the appropriate bit can be quite tedious and it often requires looking in multiple places.

A lot of other software has such books, but for some reason MySQL seems to be missing one.

A recent example comes from a “documentation feature request” I posted today: http://bugs.mysql.com/bug.php?id=68171. MySQL 5.6 provides a way to “move InnoDB tables” from one server to another. There are many reasons why you may want to do it, but the documentation is currently rather sparse. A simple “example recipe” for this would be good, as would an equivalent recipe for other engines where you can do this such as MyISAM. This is just an isolated example.

As far as I know there are no “best practices” on how to setup or manage a MySQL server, whether that come from Oracle, or the community. A common community view would probably be better as it makes “all vendors” work to a common goal, even if they compete in other areas. Look at the LSB as an example, adopted to some extent by all Linux distributions. And again often there may be several ways to solve some problems so a Cookbook would be quite nice as it would suggest how to solve common tasks and let you use those as the basis for real implementations.

So what things could these Best Practices or Cook book contain? Things that come to mind are:

How to move tables from one instance to another (the example above)

How to clone an instance, whether to make a slave, or simply for copying to a development environment. It’s clear that here there are multiple solutions depending on factors such as database size, whether you are allowed to take the instance down or not, etc.

How to run multiple instances on the same server. This is something I used to do, but now try to avoid as often these instances interfere with each other. However, many people still do this so having common ways for setting up each instance and managing them (starting stopping, accessing each one) would be good. Here a common filesystem layout would be helpful too so that people do things a similar way but still have enough flexibility to adapt as needed.

How to run multiple versions of MySQL on the same server. Often could be useful in a development environment, or even in production, but at least on some versions of Linux the package managers make the assumption that only a single version of the “binaries” can be installed, thus preventing multiple versions running at once. Sometimes this is needed and to have to work outside of the package managers is rather a pain. A common agreement on how to resolve that would be nice.

Backup and restore procedures, whether simple full database dumps, to using some of the other commercial tools.

That includes doing full and incremental backups, where this is possible, and also partial restores, or restores to a certain point in time.

Replication. Plenty to talk about here.

How to set it up from scratch

How to make more slaves (see cloning above)

Different replication features such as replication specific databases, the benefits or otherwise of row or statement based replication

Differences between the different versions, especially as MySQL 5.6 brings lots of new features such as minimal logging of changes to reduce binlog sizes

How to diagnose and recover from common replication issues

Monitoring

Again there are lots of options here, and many do it yourself ways, but also both free and commercial solutions. Often you first need to determine what you want to check, some checks may be technical ( mysql server running, the number of connections is not too close to max_connections, the number of running connections is not over a certain threshold, the SQL thread’s usage is not too high, replication is working, replication delay is not too high, etc). For many of these problems there are already checks that do the right thing. For many others you have to write your own.

All of the above is great but really people care about the application working, so on top of these check you need to add business checks etc. Are the number of widgets we made in the last hour > 0?, …,

Grant management. If we had a single server then you only set things up once. If you have more than one server then it gets more complicated. MySQL allows you to specify network ranges or hostnames from where a clients can connect, so all of a sudden managing this can be hard. I’m not sure if there’s a best way but it would certainly be interesting knowing how different people solve this problem, especially as the environment grows. Replication can be used to help here and that works pretty well but you do have to be careful of gotchas with replicated grants if anything gets out of sync. So this is certainly a topic of interest to most DBAs, whatever the chosen solution.

Setting up a new server. Simple things like choice of OS, MySQL version, MySQL Engine to store the data, expected profiles of the queries to the server, and then basic settings for a couple of these common setups.

Configuration. A huge topic but in many ways there are general things you should do, and probably that you should not do. A cookbook is probably not the place to go into huge detail about anything, which is where existing books may well be better but often helps get you going and provides indications on how to approach the problem and see that things look right.

Upgrade procedures for MySQL, a critical procedure for any DBA. We never stay on the same version, so we are always going to upgrade to a newer version. There are things to do in order to make that upgrade process work smoothly and this can get more complicated in a replicated environment, so talking about this and how it should be best done would be very good. I have found the MySQL upgrade documentation to be sometimes rather incomplete or at least not written in a cookbook fashion of follow these steps to complete your upgrade, and hence you need to move all over the upgrade documentation trying to figure out if special steps are needed. A minor version upgrade may not be an issue, but when doing a major version upgrade as many people will be doing shortly to MySQL 5.6 is something if you have important data in your database that you do not want to go wrong, so a cookbook style guide would be good for this.

Troubleshooting. I have had problems with the mysql servers I manage and some of these problems come up again and again, recognising them and having solutions to the common problems might be good, though recognising which problems are the most common is something that I guess can only be done by reading mail list archives, though Oracle/SkySQL/Percona support probably have a good idea here.

What other things am I missing for a cook book?

I’m sure this list could include many other things and maybe there’s a need to avoid too many details, referring to existing documentation where appropriate for details, but just provide the recipes and the explication of why, how etc would be good for a large number of people. I for one have found from time to time that I could not find such a recipe to do a particular task, and I am sure there must be others like me with the same problem. Existing books are very good, but the focus seems to be slightly different and may go into a lot of detail when in some cases that detail may not be needed initially.

So would a cook book like this solve a need for you, and if so what might be missing from the suggestions I have made above?

Published by

Simon J Mudd

Born in England, I now live in Spain, but spent a few years living in the Netherlands.
I previously worked in banking (financial markets) both in IT and as a broker, but IT has always had a stronger influence. Now working at booking.com as a Senior Database Administrator.
Other interests include photography, and travel.
Simon is married, with two children and lives in Madrid.
View all posts by Simon J Mudd

Hmm. I must have missed this book, though as pointed out by Andy it does look rather old. “The new edition covers MySQL 5.0 and its powerful new features…”. A lot has happened since then, so an update would be most welcome. I also think that MySQL 5.6 is going to change things quite a lot as there’s more visibility into how it is working, it works on the ever increasing larger systems and so it will be attractive to audiences that may not in the past even have considered MySQL as a possible candidate database server.

Thanks to both Andy and Dave for pointing out this book I had missed.

I use Percona Toolkit heavily and agree it’s a great toolset. MySQL Sandbox is also very good, though I’ve only played with it a little, but from when I used it it did not seem designed to fit in with “normal usage patterns”, such as that used on a production server. Perhaps things have changed.

To be honest I’ve not ever played with the MySQL Utilities. Perhaps I need to try it out.

Sure you can replace these OS provided packages with those provided by Oracle or others (that’s good), but you can’t as easily (if using a package manager) have multiple versions of MySQL installed at once.

Dave suggests you can do this by downloading the tarball binaries. That works but also works around the whole point of a package manager: providing easy installation, verification and removal of software packages.

Again the package manager is not at fault per se here, as it’s quite possible to install multiple versions of the linux kernel at once (even if only 1 is running), but this does require the install locations to not conflict and other parts of the packaging to know how to make the “system” see the right version.

good points about MySQL Utilties and the sandbox, I had not heard about those before.

From my experience – as others have noted here – most books cover pre 5.5 versions. Which is kind of understandable with 5.6 just around the corner. Hopefully things will change when 5.6 GA has been out for a couple of months.

What I’m currently really looking for is best practices or a good script for shutting down a mysql server.

Currently our internal best practices for a scheduled restart are:

– run “set global innodb_max_dirty_pages_pct = 0; + mysqladmin -proot ext -i2 | grep dirty” and wait until numbers are ok
– run pt-config-diff to see which changes will be effective after restart (just saved me yesterday when I mixed up innodb_buffer_pool_size with innodb_buffer_pool_instances..)
– check slave distances, make sure slaves are up-to-date
– stop slaves, just in case

I guess every DBA has some more checks in mind that may make restart safer (e.g. warn if log innodb_log_file_size has changed/is too high, do not shutdown with long transactions/rollbacks running, check pt-table-checksum results…).

Or did I miss the golden shutdown script utility? I’d be able spend some ressources contributing.

As did the MySQL forge, traffic was low so nobody used it. That had a wiki, snkippets, etc.

So I’d say go for it, but I don’t think folks will use it…at least not the folks that need it. I’d also caution that one of the reasons it might not get used is that there’s a LONG list of things you have there. And as the post I just linked to notes, with the advent of powerful searching, people just do a web search for what they want. e.g. “how to upgrade from MySQL 5.0 to 5.1”. Things like configurations are very version-dependent as awell as use-specific.