Upgrading MySQL Server is a very interesting task as you can approach it with so much different “depth”. For some this is 15 minutes job for others it is many month projects. Why is that ?
Performing MySQL upgrade two things should normally worry you. It is Regressions – functionality regressions when what you’ve been using before does not work any more or works with different result and performance regressions when performance (in a broad sense) is negatively affected.
In general even minor MySQL version upgrade can have both of these issues. It gets larger as you have a larger leap in minor version – Upgrade of MySQL 5.0.30 to 5.0.32 is generally likely to expose less issues than 5.0.30 to 5.0.86. The largest amount of potential issues happens when you upgrade to different major version, especially if you skip over one. MySQL 5.0 to 5.1 upgrade is a lot safer upgrade path than 4.1 to 5.1 just because so much more people have followed that road.
So how do people approach the upgrade process ? I will describe couple of processes which I’ll call “reckless” and “safe” with no negative meaning implied 🙂
Reckless approach is typically used by small simple systems or by hosting providers. This simply means stopping old MySQL version and starting new one. MySQL always was very good maintaining on disk binary compatibility between version and so you can run MySQL 5.0 database with MySQL 5.1 with no problems in most cases. When storage type changes, such as DECIMAL field got whole another meaning in MySQL 5.0 MySQL generally supports legacy storage format for a while, even if it is not clearly visible. mysqlcheck -A –check-upgrade is a good quick way to check for known incompatibilities and fix them.
The data storage is however only small part of the problem – much bigger is potential behavior changes which may impact your application only subtly – some features can stop working or work differently after change. For example you may get queries returning different result or getting different execution plan, taking minutes or hours even though they were previously sub second. The changes which got a lot of publicity over years is change of TIMESTAMP output format in MySQL 4.1 or changing JOIN evaluation in 5.0 (which could both cause different result set or make query not runnable in MySQL 5.0)
In case you’re using Reckless approach you just hope you’re lucky – you hope you’re using simple enough functionality not to be affected or if you are you are capable of implementing fixes quickly, and what is most important your users will forgive you bugs or potential data loss you may run into 🙂
Reckless approach is safest if you’re running application which uses simple MySQL functionality which is unlikely to be affected. If you’re something like SELECT name FROM users WHERE id=5 you can probably go to the earliest MySQL version and still get same result. The more exotic or complicated functionality is the more likely it to be changed. It is also a lot safer to use this approach if you’re using some widely used application rather than your site which exists in single installation – WordPress, vBulletin etc. With such software, if you’re not upgrading to the most bleeding edge software, chances are someone else has done it before you, and so if there are some problems they have been worked around. The more widely used and well maintained software is the more likely it is to run on wide set of MySQL versions.
Safe Approach So what if you actually care about your user experience or at least about your data ? In this case you may need to take it much slower checking your progress along the way. There is no limit as Safe you can get so I would just outline some reasonable safety for example
Upgrade QA Upgrade needs to start with development/QA boxes. If you’re using safe approach you probably do not do development against your production. Note as soon as you upgrade QA envinronment the code which runs on it may not run well on production until it is upgraded. In case fixing code to run on different MySQL version is taking a lot of time you may wish to create second QA/dev environment just for this project.
Query Validation It is often hard to ensure queries work same way with different MySQL version, unless you have very detailed automated QA. So what you can do is get the backup of the database and get query log (you can use mk-query-digest with tcpdump to sniff out queries if you can’t enable log for any reason). As you’ve gotten database backup and set of queries which is representative enough you should set it up on 2 same boxes and use mk-upgrade tool to run comparison. The tool will run SELECT queries on both new and old MySQL installations and check result set, explain plan and execution time, reporting you on all the differences. When you can analyze the differences and see if there is anything you need to deal with.
Stress Testing Running single stream of queries with good speed is not enough. You also need to perform stress testing to ensure both MySQL does not crash under your load as well s it scales well. There have been number of scalability bugs in MySQL history when issues would only happen at high load. You can do this in test/stress test envinronment or you can do it later when you setup some slaves with this version (or selecting to use version only for new shard and measuring its capacity)
Setting up the Slave(s) If you want to minimize downtime it is best to use MySQL Replication for upgrade. Once you have setup slave with new version and made it to caught up you can use mk-table-checksum to ensure data is the same. It is possible some update statements worked differently in the new version or you run into some replication bug – in any case you need to take care about these if you’re to be safe. Adding the slave with new version for testing is also a safe way to test things out – you can move read traffic to such slave to ensure new version stability and performance while you still have the slave running the old version in case you need to fail back quickly. This especially makes sense for cloud environments as Amazon EC2 where it is easy and inexpensive to temporary get extra boxes for time of upgrade.
Swapping master. Finally as you have your slave running on new MySQL version you want to promote it to the master and upgrade Master too. I’d take the final backup before upgrading and make sure to keep binary logs for some time, in case you run into some serious issues with Master. In many cases you can leave the ex-Master as a slave so you can fail back to the old version easily if you need to. In other cases however it is not possible as MySQL only fully support slaves newer than the master. Older slaves may have issues unable to interpret new replication stream correctly. If this is the problem for you can try Tungsten Replicator which claims to work in both directions (though I have not tried it myself)
Note in case you’re having Sharded envinronment you often approach things a bit differently – upgrading one shard with full validation and when doing reckless upgrades for other shards after you’re sure your application works well with them. You can often stage upgrades in time a bit so if something happens (like MySQL crashes) you have only one/few shard to deal with.
P.S Happy New Year to all of you 🙂
Happy New Year to you too!
Thanks for the explanation on the two upgrade paths. I never thought of setting up a slave server, to help minimize downtime.
Two notes from the manual:
“VARCHAR values are not padded when they are stored. Handling of trailing spaces is version-dependent. As of MySQL 5.0.3, trailing spaces are retained when values are stored and retrieved, in conformance with standard SQL. Before MySQL 5.0.3, trailing spaces are removed from values when they are stored into a VARCHAR column; this means that the spaces also are absent from retrieved values. ”
http://dev.mysql.com/doc/refman/5.0/en/char.html
I have observed this cause significant problems, particularly with regards to unique indexes.
“If you perform a binary (in-place) upgrade without dumping and reloading tables, you cannot upgrade directly from MySQL 4.1 to 5.1. This occurs due to an incompatible change in the MyISAM table index format in MySQL 5.0. Upgrade from MySQL 4.1 to 5.0 and repair all MyISAM tables (see Section 2.4.4, “Rebuilding or Repairing Tables or Indexesâ€). Then upgrade from MySQL 5.0 to 5.1 and check and repair your tables.”
http://dev.mysql.com/doc/refman/5.1/en/upgrade.html
Overall I wonder if in-place upgrades are wise. It seems to me that a dump and reload is overall a safer approach. Comments?
Yes, the “Safe” approach sounds very close to what I have planned when we upgrade our system from 4.1.
I am quite pleased to see that there are some mk- tools to run the queries in parallel and compare results – I had assumed I would have to roll my own for that.
One of the additional difficulties in upgrading is that development will be happening concurrently with the upgrade project (expected to take several months), which means that the other developers are going to have to be careful about not doing anything which breaks in the interim. Sampling queries from production will only check for code already in production, not that which is “in the pipeline”.
Also other developers will probably only upgrade their databases later in testing.
Happy new year.
Good Post.
Any points for architectural upgrade? I mean from 32-bit to 64-bit or the reverse!?
I suppose it only concerned with “performance regressions” as far as we have same version!!
Yes,
VARCHAR changes and DECIMAL type changes can also cause issues for some cases.
In place upgrades are generally more dangerous (especially between major versions) but if you’re checking your tables “for upgrade” it normally works quite well. At the same time if you have a time (and can create proper rollback plan) dumping and reloading can work better because it also will use latest features in tables. For example if you do dump and reload upgrade for MySQL 4.1 to 5.0 with Innodb tables you will have your tables created in the COMPACT Innodb Format instead of Fixed which gives smaller tables.
Kedar,
I spoke mainly about version upgrades however it applies to other changes as well, though with modifications – using different storage engine or different hardware/OS can follow similar cycle.
upgrade to 32bit to 64bit rarely causes problems (unless you’re using Windows which had it fair share of issues specific to 64bit version) though as with any change there are risks.
Mark,
Yes – upgrade and concurrent development add more trickery and they need to synchronized. As I mentioned if there is no major roll out planned during production upgrade I’d upgrade development first – this way whatever developers do should work on new version as it is placed in production. There is of course risk such changes will not work with old version if you happen to need to deploy something before upgrade is completed.
But in fact it is very remarkable how compatible MySQL is – I’ve seen many instances when Production is running 5.0 and development is going on 5.1 for months without any issues. I would not recommend it though.
“Upgrade development first” doesn’t really cut it; we’ve got multiple teams working on different branches. If I was to work on upgrading MySQL, I would create a branch and commit the new mysql installation to that branch, as well as any fixes to make the app work. Other teams would not.
Hey Peter,
One approach I’ve had success with is to *start* with mk-upgrade (before any dev/test/stress/qa environment is upgraded). This way, problem queries can be identified and (if I’m lucky) the queries can be rewritten such that they will return the same results on both the original MySQL instance as well as the upgraded one. This allows development to continue and new code to be rolled out before the new MySQL version is. After that, I usually follow your basic steps:)
/me is always wary of simultaneous MySQL upgrade + code deployment … if there’s a problem, we don’t necessarily know where to attribute.
Mark,
If you have multiple teams working on different branches you can have them using different development environments, or you can hope for best and catch the problems on QA stage. Version on QA should match production of course.
Ryan,
I meant mainly to upgrade development before upgrading production. Identifying potential issues as early as possible, being it with mk-upgrade or any other means is best.
Regarding upgrade and development at same time – surely it is not the best choice but situations can be very different. I’ve seen cases when thousands of boxes need to be upgraded with petabytes of data – with careful verification it can take a lot of time while development needs to make its progress.
Also at least minor upgrades can be hopefully only operations team issue with dev team involved only if issues are identified.
Speaking of upgrading 5.0 vs 5.1, which Percona build do you recommend be used in production, 5.1.34-5 or 5.0.87-b20?
The web application doesn’t use anything exotic, just simple selects/joins/group by/order, but it does serve a large number of concurrent users.
Any suggestions would be greatly appreciated!
Thanks,
John
Of course different teams are using different development environments (in most cases each developer has their own one).
It is simply not an option to suspend all development work for the duration of the upgrade project; this would go down like a lead balloon with management. The trick will be to try to ensure that when the different projects get merged on to the release, that bugs don’t appear.
Mark
Peter,
I’m given a very short time frame to perform the upgrade MySQL server from 5.0.27 to 5.0.45. I think I go with the option to setup the slave first and try to minimize downtime.
I’ve checked Change history on http://dev.mysql.com/doc/refman/5.0/en/news-5-0-x.html and it seems that this upgrade should be seamless.
Before I proceed with this, are there any major incompatibility issues between these two minor versions that I have to be aware of?
Any advice is more than welcome.
Hey Peter, do you remember the regression where on duplicate key update stopped working? I think it was like 5.0.30 or so. That was fun! And lead me to be even more skeptical about MySQL upgrades than normal; the reckless way really is reckless if you care about your data at all. Anyway, hopefully this will all be improved once Oracle takes over; their QA process on the big database is pretty thorough. 😉
Tatjana,
When upgrading between any two versions, there is always a possibility you will encounter incompatibilities.
I highly recommend testing the upgrade in a QA environment, it’s always good to see how your specific application will work with new versions of MySQL.
Tatjana,
Anytime you upgrade, it is recommended to test your code against the new version of MySQL in a QA environment.
could be noted that in modern versions of mysql (5.0.40+ ?), the majority of data incompatibilities arose from charset related sorting problems. mysql_upgrade or check table for upgrade in modern versions of 5.1 should catch these cases and rebuild tables, which can make the reckless upgrade take hours on a large installation..
see http://dev.mysql.com/doc/refman/5.1/en/checking-table-incompatibilities.html
could be mentioned that in modern versions of 5.0 and 5.1 the incompatibilities in data formats arose from charset related sorting changes that were made. In these cases, all recent 5.1 ‘check table for upgrade’ should flag the table as needing a rebuild, which could make the 15 minutes ‘reckless’ approach table hours on a large installation.
read http://dev.mysql.com/doc/refman/5.1/en/checking-table-incompatibilities.html for some examples.
From my experience upgrading MySQL can be trivial to rather a pain. Here I’m talking about major version changes such as 5.0 to 5.1.
This also depends on various issues one of which is the amount of downtime you can take and also how you are using MySQL.
MySQL often recommends (if possible) doing an upgrade by doing a MySQL dump of the databases and then loading them clean into a the new version. This is the best idea as it ensures that you have a clean dump and a clean load but is mainly impractical for many people, especially if the database is sufficiently large, as this takes too long.
In that case often the alternative is to take a copy of a running slave (if you are working in a replicated environment) and then upgrade to the newer binaries and run mysql_check followed by mysql_upgrade. That works pretty well as long as you don’t have stored procedures or triggers which the 5.1 upgrade may not cope with properly.
I had some nasty issues on some instances with this problem, but luckily I was able to remove the triggers and all of a sudden the upgrades became a lot less painless.
Then there’s the issue which is still “in theory” an issue with 5.0 to 5.1 upgrades that when you run mysql_upgrade it tries to check and upgrade all tables which need to be changed but fails miserably with Innodb tables. Recommendation by the mysql_upgrade binary is to dump the tables and then load them back. Of course to run mysql_upgrade you are running the new 5.1 binaries and to do the dump you need to downgrade again to 5.0…. If the table is large you are back to the problem that the dump/load takes ages. Then again none of this is really necessary: ALTER TABLE ENGINE=INNODB rebuilds the table under 5.1 and MySQL is happy… (why this is not done as part of the mysql_upgrade I don’t know though I suspect that this will be fixed in the future).
Apart from these issues the upgrade can be trivially easy. For most people it is, but it would be nice to have a complete, simple, step list of the tasks that are required, all in the right order so that nothing gets forgotten.
Working in a replicated environment also does not help as it is often impractical to upgrade all boxes at the same time, especially if you want to avoid down time. So you follow the “procedure” of upgrading slaves one by one until you only have the master to do. Be aware that if you upgrade the master by running something like mysql_upgrade it will potentially write to the bin logs commands used to update the server itself. This of course gets picked up by the slaves and breaks replication as it fails on the slaves which have already been upgraded. To avoid this it’s often best to build a new master (originally just a slave which has been upgraded), and then put all existing slaves underneath it. Then all that’s required to complete the upgrade is to make the applications talk to the new master.
So I for one would vote for better documentation, that covers the case of the single small mysql server, but also at least mentions the issues that need to be taken into account when the server size gets too large for dump/load to be used, or what extra steps or things need to be taken into account if the server is working in a replicated environment.
It would also be nice if mysql_upgrade finally works for all engine types (within reason), and also if somehow mysql_check (5.1) could be run on an old version and give some sort of idea of which tables may need to be rebuilt BEFORE you change the version you are running. This type of thing is typical in Oracle or Sybase so should not be so hard to implement in MySQL. Perhaps it is not needed by everyone but it does make our life easier and as such should gain more users.
Can someone post the step by step document for the mysql 5.0 to 5.1 upgrade?….what are the various ways i can do this?…i am kind of new to the mysql upgrade procedure
Thanks a lot
How to upgrade mysql from 5.1.47 to 5.1.53 only, not later 5.1.53 or latest version using mysql_upgrade command …
hi all:
I tried to upgrade mysql community 5.0 to percona 5.1, after I run mysql_upgrade, its errored out on all the innodb tables, saying:
error : Table upgrade required. Please do “REPAIR TABLE
table_1” or dump/reload to fix it!…..
well, “repair table” is not supported on innodb table so I either have to do a dump/reload or alter engine=innodb,
we have so many large tables and it takes a week just to upgrade a single server.
So is this error messege a known issue or is it normal? am I doing something wrong?
I also gets warnings like :
Warning : Triggers for table
schema1.trigger_!have no creation context, which requires me to recreate triggers, is this normal for upgrade too?mysql upgrade simple 17 staps :—————
cp mysql-5.5.11-linux2.6-i686.tar.gz /usr/local/
cd /usr/local/
tar -xf mysql-5.5.11-linux2.6-i686.tar.gz
mv mysql-5.5.11-linux2.6-i686 mysql
cd mysql
chmod root:mysql data/
chown root:mysql data
cd /var/lib/mysql/
rm -rf ibdata1 ib_logfile*
/usr/local/mysql/
cd /usr/local/mysql/
ps aux|grep mysql
kill -9 18230 18266
ps aux|grep mysql
bin/mysqld_safe –user=mysql &
bin/mysql_upgrade –socket=/var/lib/mysql/mysql.sock
mysql