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?