General Preventive Maintenance

Updating MySQL

The first public release of MySQL was for version 3.11.1. Releases currently appear in the 3.23, 4.0, 4.1, and 5.0 series. MySQL 3.23 is quite old now, and releases appear only for critical bugs, such as security problems. The 4.0 series probably is the most widely used series at the moment, but the balance now is shifting rabidly toward the 4.1 series, which has more features than 4.0 and is more stable than 5.0. The current development series is 5.0.

Because MySQL is under active development, updates appear fairly often. The pace of ongoing changes raises the question for the administrator as to whether to upgrade your existing MySQL installation when new releases appear. This section provides some guidelines to help you make this decision.

The first thing you should do when a new release appears is to determine how it differs from previous releases. To make sure you're aware of new releases, subscribe to the [e-mail] mailing list. (Visit http://lists.mysql.com/ to find out how to subscribe.) Each announcement includes the new change notes, so this is a good way to remain apprised of new developments. (Alternatively, check the "Change Notes" appendix in the MySQL Reference Manual to familiarize yourself with what's new.) Also, you should read the section on upgrading in the MySQL Reference Manual for the relevant release series. This section will indicate any important issues you should consider and note any special steps you must take when upgrading. This is particularly important if the new release introduces behaviors that are incompatible with earlier releases.

After checking the change notes and upgrading sections in the manual, ask yourself these questions:

• Are you experiencing problems with your current version that the new version fixes?

• Does the new version have additional features that you want or need?

• Is performance improved for certain types of operations that you use?

If the answer to all these questions is no, you have no compelling reason to upgrade. If the answer to any question is yes, you might want to go ahead. However, at this point, it's often useful to wait a few days and watch the MySQL mailing list to see what other people report about the release. Was the upgrade helpful? Were bugs or other problems found?

Some other factors to consider that may help you make your decision are as follows:

• Releases in a stable series are most often for bug fixes, not new features. There is generally less risk for upgrades within a stable series than within a development series.

• If you upgrade MySQL, you might need to upgrade other programs that are built with the MySQL C client library linked in. For example, after a MySQL upgrade, other libraries or applications that depend on the MySQL C client library might need to be rebuilt to link the new client library. (Examples include the Perl DBD::mysql module and PHP. An obvious symptom that you need to rebuild them is that all your MySQL-related DBI and PHP scripts start dumping core after you upgrade MySQL.) If you prefer to avoid these rebuilds, you might be better off not upgrading MySQL. If you use statically linked rather than dynamically linked programs, the likelihood of this problem is much reduced, but then your system memory requirements increase.

If you're still not sure whether to upgrade, you can test the new server independently of your current server. You can do this either by running it in parallel with your production server, or by installing it on a different machine. It's easier to maintain independence between servers if you use a different machine because you have greater freedom to configure it as you choose. If you elect to run the new server in parallel with an existing server on the same host, be sure to configure it with unique values for parameters such as the installation location, the data directory, and the network interfaces on which the server listens for connections. For details, see the section "Running Multiple Servers" elsewhere in this chapter.

In either case, you'll probably want to test the new server using a copy of the data in your existing databases. See the section "Making Database Backups," in Chapter 13 for instructions on copying databases.

If you upgrade to a version that it not backward compatible with older versions and then decide to revert to the earlier version, it may not be so easy to downgrade. For example, if you upgrade from MySQL 4.0.x to 4.1.x and convert your tables to 4.1 format, they'll be incompatible with 4.0.x servers. A downgrade will be easier if you have not yet begun to use features available only in the newer release. One useful strategy is to dump your databases using the --compatible option of mysqldump to generate dump files for loading into the older server.

Don't Be Afraid to Try Development Releases

It's not a wise idea to use a development release for production purposes, such as managing your business assets. On the other hand, I do encourage you to test new releases, perhaps with a copy of your production data. The greater the number of people that try new releases, the more thoroughly they are exercised. This improves the likelihood of finding any bugs that may exist, which is a good thing. Bug reports are a significant factor in helping MySQL development move forward, because the developers actually fix problems that the user community reports.

If you want an ongoing source of statements to be executed by a test server, consider using a production server as a replication master and setting up the test server as a replication slave. That way, updates executed by the master server will be sent to the slave server, providing it with a continual stream of input. The master will not send any retrievals to the slave, but you can point client programs at the slave and issue SELECT statements to see how it processes them.