upgrading SL/RHEL mysql5.{0,1} to mysql 5.5
Background:
Attention SL 5 MySQL users: Upstream will not issue any more security advisories for the MySQL 5.0 packages (mysql-5.0.* and related packages). In order to ensure your systems are fully up to date with security errata, you must migrate to the newly provided MySQL 5.5 packages. Future security advisories will be provided only for MySQL 5.5. The only trusted way to upgrade from MySQL 5.0 to MySQL 5.5 is by using MySQL 5.1 as an intermediate step. This is why the mysql51* Software Collection packages are provided. Note that the MySQL 5.1 packages are not supported and are provided only for the purposes of migrating to MySQL 5.5. You should not use the mysql51* packages on any of your production systems. Because the mysql51 and mysql55 Software Collections do not conflict with each other, or any mysql packages, users can install mysql51 and mysql55 Software Collections together with mysql packages. We have placed the relevant mysql packages within the Scientific Linux 5 security tree to facilitate this upgrade in advance of any actual security errata. Giving you the time to test this migration should result in you being better prepared for a time when you must perform it to maintain your system security.Dmitry noted this change in email in December, including these excerpts considering the effects:
This is applied to both RHEL 5 (mysql 5.0) and RHEL 6 (mysql 5.1) used at STAR. I already tested MySQL 5.5 at my laptop to some extent, and we should not experience any drastic changes anywhere, except for the client library installation. MySQL libraries: - MySQL 5.5 has libmysqlclient.so.18 <-- new library - MySQL 5.1 has libmysqlclient.so.16 <-- currently, via mysql-libs-5.1 - MySQL 5.0 has libmysqlclient.so.15 <-- mysql-sl5-compat So, mysql-sl5-compat will have to include both libmysqlclient.so.15 and libmysqlclient.so.16, because mysql55 package from Scientific Linux does not contain mysql-libs-compat package.The mysql-sl5-compat package referred to above is not part of RHEL or SL distributions, but instead a package created for use at the RCF on SL 6 machines, which includes these files:
- /usr/lib/mysql/libmysqlclient.so.15.0.0
- /usr/lib/mysql/libmysqlclient_r.so.15.0.0
- /usr/lib64/mysql/libmysqlclient.so.15.0.0
- /usr/lib64/mysql/libmysqlclient_r.so.15.0.0
[Jerome] We created a custom RPM for the compat MySQL for the RCF SL6 upgrade (so this issue is known). The package is named mysql-sl5-compat-5.0.45-7.x86_64 and will be indicated later for a possible update online (although I am not sure why it would be needed there). The so.16 will not disappear upon the update and is not related to SL5 (so would not be bundled into it).
[Dmitry] When we upgrade to mysql-5.5, then system mysql package will contain only so.18 (current so.16 disappears as it is replaced by newer lib), and we will need so.16 (for deployed libraries) to be added to mysql-sl5-compat package. Correct?
[Jerome} I am not sure if he [sic] .16 would disappear. .16 is the SL6 initial package (same on the farm). .15 is the SL5 version, we have a custom RPM for that. In brief: if the .16 disappears, then we would need to create a mysql-sl6-compat.
What to do
To see what is involved, I am going to try an “in place” upgrade of mysql-5.0 to mysql-5.5 on staruser01. The test node has Sc.Linux 5 and small, loseable databases (old, early OCS database for instance). It also has the STAR_mysqld_init_shuffle rpm installed, like (almost?) all STAR database servers at BNL. This might complicate things a bit, introducing non-default init script files and triggers that won't work trigger on mysql55 package actions. I will try to follow the Red Hat guidance given in this reference as closely as possible. This will also introduce an additional Red Hat feature called "Software Collections" (reference), which we have not previously used. "With Software Collections, you can build and concurrently install multiple versions of the same software components on your system. Software Collections have no impact on the system versions of the packages installed by any of the conventional RPM package management utilities." (Not sure if it is replacing the "alternatives" system, but it seems to be addressing a similar need.) Initial state of the test machine:
[root@staruser01 ~]# more /etc/redhat-release Scientific Linux release 5.9 (Boron) [root@staruser01 ~]# rpm -qa |grep mysql mysql-server-5.0.95-1.el5_7.1 mysql-5.0.95-5.el5_9 STAR_mysqld_init_shuffle-1.2-0 php-mysql-5.1.6-43.el5_10 mysql-server-5.0.95-5.el5_9 [root@staruser01 ~]# service mysqld_multi status Reporting MySQL servers MySQL server from group: mysqld3306 is runningInstall the new packages:
[root@staruser01 ~]# yum install mysql51-mysql-server mysql55-mysql-server Loaded plugins: downloadonly, kernel-module Setting up Install Process Resolving Dependencies --> Running transaction check ---> Package mysql51-mysql-server.i386 0:5.1.70-1.el5 set to be updated --> Processing Dependency: mysql51-mysql-libs = 5.1.70-1.el5 for package: mysql51-mysql-server --> Processing Dependency: mysql51-mysql = 5.1.70-1.el5 for package: mysql51-mysql-server --> Processing Dependency: mysql51-runtime for package: mysql51-mysql-server --> Processing Dependency: libmysqlclient_r.so.1016(libmysqlclient_1016) for package: mysql51-mysql-server --> Processing Dependency: libmysqlclient_r.so.1016 for package: mysql51-mysql-server --> Processing Dependency: libmysqlclient.so.1016(libmysqlclient_1016) for package: mysql51-mysql-server --> Processing Dependency: libmysqlclient.so.1016 for package: mysql51-mysql-server ---> Package mysql55-mysql-server.i386 0:5.5.36-2.el5 set to be updated --> Processing Dependency: mysql55-mysql-libs = 5.5.36-2.el5 for package: mysql55-mysql-server --> Processing Dependency: mysql55-mysql = 5.5.36-2.el5 for package: mysql55-mysql-server --> Processing Dependency: mysql55-runtime for package: mysql55-mysql-server --> Running transaction check ---> Package mysql51-mysql.i386 0:5.1.70-1.el5 set to be updated ---> Package mysql51-mysql-libs.i386 0:5.1.70-1.el5 set to be updated ---> Package mysql51-runtime.i386 0:1-9.el5 set to be updated ---> Package mysql55-mysql.i386 0:5.5.36-2.el5 set to be updated ---> Package mysql55-mysql-libs.i386 0:5.5.36-2.el5 set to be updated ---> Package mysql55-runtime.i386 0:1-12.el5 set to be updated --> Finished Dependency Resolution Beginning Kernel Module Plugin Finished Kernel Module Plugin Dependencies Resolved ========================================================================================================================================================== Package Arch Version Repository Size ========================================================================================================================================================== Installing: mysql51-mysql-server i386 5.1.70-1.el5 sl-security 11 M mysql55-mysql-server i386 5.5.36-2.el5 sl-security 13 M Installing for dependencies: mysql51-mysql i386 5.1.70-1.el5 sl-security 1.1 M mysql51-mysql-libs i386 5.1.70-1.el5 sl-security 1.7 M mysql51-runtime i386 1-9.el5 sl-security 14 k mysql55-mysql i386 5.5.36-2.el5 sl-security 7.6 M mysql55-mysql-libs i386 5.5.36-2.el5 sl-security 441 k mysql55-runtime i386 1-12.el5 sl-security 14 k Transaction Summary ========================================================================================================================================================== Install 8 Package(s) Upgrade 0 Package(s) Total download size: 35 M Is this ok [y/N]: y <snip>
[root@staruser01 ~]# service mysqld_multi stopI know not why, but the above command did not stop the running mysql server, nor did a few half-hearted attempts to stop it by other means work, so rebooted the machine at this point. I hope this is peculiar to this test machine...
[root@staruser01 ~]# rm -rf /opt/rh/mysql51/root/var/lib/mysql/ [root@staruser01 ~]# cp -r --preserve=all /var/lib/mysql/ /opt/rh/mysql51/root/var/lib/
The database files are copied to a new location. Points to note here:
- I am deviating ever so slightly from Red Hat's recipe by using the -a (archive) option in the copy to preserve ownership, modes, timestamps, SEL contexts, etc.
- This could be a lot of disk space required in some cases - check first that enough space is available, and clean up original files afterwards
- The original files are still present in case something goes wrong later in the upgrade
- STAR usually uses data paths under /db01 rather than the default /var/lib/mysql
[root@staruser01 ~]# service mysql51-mysqld start Starting mysql51-mysqld: [ OK ] [root@staruser01 ~]# scl enable mysql51 'mysql_upgrade' Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck Running 'mysqlcheck with default connection arguments Running 'mysqlcheck with default connection arguments mysql.columns_priv OK mysql.db OK mysql.func OK mysql.help_category error : Table upgrade required. Please do "REPAIR TABLE `help_category`" or dump/reload to fix it! mysql.help_keyword error : Table upgrade required. Please do "REPAIR TABLE `help_keyword`" or dump/reload to fix it! mysql.help_relation OK mysql.help_topic error : Table upgrade required. Please do "REPAIR TABLE `help_topic`" or dump/reload to fix it! mysql.host OK <snip> ocsweb.accesslog error : Table upgrade required. Please do "REPAIR TABLE `accesslog`" or dump/reload to fix it! ocsweb.accountinfo error : Table upgrade required. Please do "REPAIR TABLE `accountinfo`" or dump/reload to fix it! ocsweb.accountinfo_config OK ocsweb.bios error : Table upgrade required. Please do "REPAIR TABLE `bios`" or dump/reload to fix it! ocsweb.blacklist_macaddresses error : Table upgrade required. Please do "REPAIR TABLE `blacklist_macaddresses`" or dump/reload to fix it! <snip> Repairing tables mysql.help_category OK mysql.help_keyword OK mysql.help_topic OK <snip> ocsweb.accesslog note : The storage engine for the table doesn't support repair ocsweb.accountinfo note : The storage engine for the table doesn't support repair ocsweb.bios note : The storage engine for the table doesn't support repair ocsweb.blacklist_macaddresses OK <snip>
In this case, during the initical check, 40 "errors" were noted with the suggestion to --Please do "REPAIR TABLE `xyz`" or dujmp/reload to fix it--
Then when it attempted to repair tables, it apparently succeeded for 25 of them (including all of the of the tables in the mysql db), leaving 15 others with the note: The storage engine for the table doesn't support repair.
Note that at this point mysql-5.1 is running, and is running with the master process running as root. Have to come back to this, but proceeding for now...
First try dumping and reloading one of those remaining 15 tables, which to this non MySQL expert, it seems to have repaired things:
mysql> use ocsweb; Database changed mysql> check table accesslog for upgrade; +------------------+-------+----------+----------------------------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +------------------+-------+----------+----------------------------------------------------------------------------------------+ | ocsweb.accesslog | check | error | Table upgrade required. Please do "REPAIR TABLE `accesslog`" or dump/reload to fix it! | +------------------+-------+----------+----------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) [root@staruser01 ~]# scl enable mysql51 "mysqldump ocsweb accesslog> accesslog.dump.sql" [root@staruser01 ~]# mysql ocsweb < accesslog.dump.sql mysql> check table accesslog for upgrade; +------------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +------------------+-------+----------+----------+ | ocsweb.accesslog | check | status | OK | +------------------+-------+----------+----------+ 1 row in set (0.00 sec)
Other than causing indexes to be rebuilt, I don't know what this process is doing or what dangers it might pose, if any. Then to try to short cut the process, dump all the tables (including those that need no repair):
[root@staruser01 ~]# scl enable mysql51 "mysqldump --all-databases > alldb.sql" -- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly. [root@staruser01 ~]# mysql < alldb.sql
Then I checked the reminaing 14 tables for upgrade status and all were ok. Had I started with the "Dump and Restore Upgrade" instead of the "In Place Upgrade", I expect it has the same effect and thus probably is somewhat preferable, except if the database(s) are huge, in which case selective table dumping might be preferable to dumping and reloading everything.
Well, that got things up to MySQL 5.1. Now proceed to 5.5...
[root@staruser01 ~]# service mysql51-mysqld stop Stopping mysql51-mysqld: [ OK ] [root@staruser01 ~]# service mysql55-mysqld stop Stopping mysql55-mysqld: [ OK ] [root@staruser01 ~]# service mysql55-mysqld status mysql55-mysqld is stopped [root@staruser01 ~]# [root@staruser01 ~]# rm -rf /opt/rh/mysql55/root/var/lib/mysql/ [root@staruser01 ~]# cp -r --preserve=all /opt/rh/mysql51/root/var/lib/mysql/ /opt/rh/mysql55/root/var/lib/ [root@staruser01 ~]# service mysql55-mysqld start Starting mysql55-mysqld: [ OK ] [root@staruser01 ~]# scl enable mysql55 'mysql_upgrade' Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck Running 'mysqlcheck with default connection arguments Running 'mysqlcheck with default connection arguments mysql.columns_priv OK mysql.db OK mysql.event OK mysql.func OK <snip> ocsweb.tags OK ocsweb.temp_files OK ocsweb.videos OK ocsweb.virtualmachines OK Running 'mysql_fix_privilege_tables'... OK
Everything came up OK in this upgrade.
Mysql's top level process is still running as root, so still have to get the init scripts straightened out...
Open questions:
- For slave nodes, are we better off foregoing the upgrade steps, and instead just install mysql55 and treat it as new slave, in whatever manner that is usually handled? (Need Dmitry's thoughts on this one)
- Will slaves at 5.5 work with masters at 5.1? Will masters at 5.5 work with slaves at 5.1?
- Will 5.0 and 5.1 clients work with 5.5 servers? (eg. RCF connecting to our upgraded offline servers) For what its worth, the 5.0 mysql client on staruser01 was used above, connected to the 5.1 and 5.5 server versions, although I didn't exercise it much beyond what is shown above.
- what about sc, which is also running a mysql-5.0 server?
References:
Redhat's migration documentation specifically for RHEL 5.x (mysql-5.0):
https://access.redhat.com/site/documentation/en-US/Red_Hat_Enterprise_Linux/5/html/Deployment_Guide/ch-Migrating_from_MySQL_5.0_to_MySQL_5.5.html
https://access.redhat.com/site/documentation/en-US/Red_Hat_Developer_Toolset/1/html/Software_Collections_Guide/
list of early Software Collection Components:
https://access.redhat.com/site/documentation/en-US/Red_Hat_Software_Collections/1/html/1.0_Release_Notes/chap-RHSCL.html
Groups:
- wbetts's blog
- Login or register to post comments