Chapter 24. Migrating from MySQL 5.0 to MySQL 5.5

Before migrating from MySQL 5.0 to MySQL 5.5, back up all your data, including any MySQL databases. For more information about MySQL 5.1 and MySQL 5.5, refer to the release notes available at http://dev.mysql.com/doc/relnotes/mysql/5.1/en/ and http://dev.mysql.com/doc/relnotes/mysql/5.5/en/.

Note

Red Hat will not issue any more security advisories for the MySQL 5.0 packages (mysql-5.0.* and related packages). Security advisories will be provided only for MySQL 5.5.

24.1. Upgrading from MySQL 5.0 to MySQL 5.5

The only supported 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. It is also possible to run all versions of MySQL at the same time. However, the port and socket number must be changed in the my.cnf configuration files to prevent specific resources from conflicting.
There are two ways of upgrading from MySQL 5.0 to MySQL 5.5:
  • Using the mysqldump and mysql utilities — the dump and restore upgrade generates an entirely new dump of all databases from one database. Next, the MySQL command line interface is run with the dump file as an input (alternatively, use the mysqlimport utility or the LOAD DATA INFILE SQL command) within the other database. Appropriate daemons have to be running during both dumping and restoring. Use the --all-databases option when executing the mysqldump command to include all databases in the resulting dump. The --routines, --triggers, and --events (valid only for MySQL 5.1 and above) options can be used if needed.
    Example 24.1, “Dump and Restore Upgrade” shows the specific commands used to upgrade using the dump and restore method.
  • In-place upgrade — consists of copying data files from one database directory to another database directory while both MySQL daemons are stopped. Appropriate permissions and SELinux context must be set for the copied files. The in-place upgrade is usually faster and easier for large databases, but there are some risks and known problems. For more information, refer to the release notes for MySQL 5.1 and MySQL 5.5, linked to at the beginning of this chapter.
    Example 24.2, “In-place Upgrade” shows the specific commands used to perform an in-place upgrade.
After upgrading, either using the dump and restore method or performing an in-place upgrade, start the MySQL server and run the mysql_upgrade command. Running the mysql_upgrade is necessary to check and repair internal tables. Note that all scripts that interact with a MySQL server from a Software Collection, especially the mysql_upgrade script, should be run inside an scl enable environment.

Note

While running the mysql_upgrade command, you may encounter the following errors: 
You can't use locks with log tables.
This is a known issue (reported at http://bugs.mysql.com/bug.php?id=30487), which has no effect on the upgrade process.
Except when data upgrading, consider changing appropriate settings in your my.cnf configuration file to reflect the new environment.

Example 24.1. Dump and Restore Upgrade

Example of dump and restore upgrading from MySQL 5.0 to MySQL 5.5 Software Collection: 
~]# service mysqld start
Starting mysqld:                                           [  OK  ]
~]# mysqldump --all-databases --routines > dump.sql
~]# service mysqld stop
Stopping mysqld:                                           [  OK  ]
~]# service mysql51-mysqld start
Starting mysql51-mysqld:                                   [  OK  ]
~]# scl enable mysql51 'mysql' < dump.sql
~]# 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
a.t1                                               OK
mysql.columns_priv                                 OK
⋮
mysql.user                                         OK
Running 'mysql_fix_privilege_tables'...
OK
~]# scl enable mysql51 'mysqldump --all-databases --routines --events' > dump2.sql
~]# service mysql51-mysqld stop
Stopping mysqld:                                           [  OK  ]
~]# service mysql55-mysqld start
Starting mysql55-mysqld:                                   [  OK  ]
~]# scl enable mysql55 'mysql' < dump2.sql
~]# 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
a.t1                                               OK
mysql.columns_priv                                 OK
⋮
mysql.user                                         OK
Running 'mysql_fix_privilege_tables'...
OK

Example 24.2. In-place Upgrade

Example of an in-place upgrade from MySQL 5.0 to MySQL 5.5 Software Collection: 
~]# service mysqld stop
Stopping mysqld:                                           [  OK  ]
~]# service mysql51-mysqld stop
Stopping mysql51-mysqld:                                   [  OK  ]
~]# rm -rf /opt/rh/mysql51/root/var/lib/mysql/
~]# cp -r /var/lib/mysql/ /opt/rh/mysql51/root/var/lib/mysql/
~]# chown -R mysql:mysql /opt/rh/mysql51/root/var/lib/mysql/
~]# restorecon -R /opt/rh/mysql51/root/var/lib/mysql/
~]# service mysql51-mysqld start
Starting mysql51-mysqld:                                   [  OK  ]
~]# 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
a.t1                                               OK
mysql.columns_priv                                 OK
⋮
mysql.user                                         OK
Running 'mysql_fix_privilege_tables'...
OK
~]# service mysql51-mysqld stop
Stopping mysql51-mysqld:                                   [  OK  ]
~]# service mysql55-mysqld stop
Stopping mysql55-mysqld:                                   [  OK  ]
~]# rm -rf /opt/rh/mysql55/root/var/lib/mysql/
~]# cp -r /opt/rh/mysql51/root/var/lib/mysql/ /opt/rh/mysql55/root/var/lib/mysql/
~]# chown -R mysql:mysql /opt/rh/mysql55/root/var/lib/mysql/
~]# restorecon -R /opt/rh/mysql55/root/var/lib/mysql/
~]# service mysql55-mysqld start
Starting mysql55-mysqld:                                   [  OK  ]
~]# 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
a.t1                                               OK
mysql.columns_priv                                 OK
⋮
mysql.user                                         OK
Running 'mysql_fix_privilege_tables'...
OK