Upgrading from MySQL 5.1 to 5.5 on Ubuntu Lucid Lynx

I decided to try Percona’s MySQL server, and went for the most recent version (5.5). Unfortunately, the version of MySQL included with Ubuntu Lucid Lynx is 5.1 – so installing Percona’s 5.5 server means an upgrade.

This upgrade requires some additional steps beyond the usual.

First, the new MySQL 5.5 makes InnoDB the default storage engine – so anything that relies on that default will have to be changed. To revert this “change” you may need to add the following to your my.cnf:

[mysqld]
default-storage-engine=MyISAM


Secondly, the mysql database will need updating. Run this command:

# su - mysql -c "mysqld_safe --init-file=/usr/share/mysql/mysql_system_tables.sql --verbose"

This runs the command as the mysql user; if you are using Ubuntu, the mysql user has a shell of /bin/false which causes this to fail. You can either give the mysql user a new shell (such as /bin/bash) by editing with the command vipw or you can run the mysql_safe command as root, like this:

# mysqld_safe --init-file=/usr/share/mysql/mysql_system_tables.sql --verbose

Once this command runs, make sure that the MySQL instance stops by running service mysql stop or mysqladmin shutdown.

This should fix things. One thing to watch out for is the innodb_buffer_pool_size – this is increased from a default of 128Mb to 132Mb; if you’ve tight memory you’ll want to plan for this.

Percona has a complete guide for migrating from 5.1 to 5.5; MySQL also has a thorough description of upgrading to 5.5. MariaDB 5.5 is not out yet, but there is instructions on upgrading to 5.3 (equivalent to MySQL 5.1).