title: Upgrading MySQL databases link: https://onemoretech.wordpress.com/2013/01/11/upgrading-mysql-databases/ author: lembobro description: post_id: 4010 created: 2013/01/11 15:17:47 created_gmt: 2013/01/11 19:17:47 comment_status: closed post_name: upgrading-mysql-databases status: publish post_type: post

Upgrading MySQL databases

You'll find another post here that talks about how to upgrade your MySQL server binaries using a third-party rpm repository. This article concerns what to do about all those databases that might have been created using an older version. I stumbled on this problem when in the midst of creating and permissioning a new database on my desktop at work. Awhile back I had used the procedure described in the above link to upgrade it to MySQL 5.5 from the shipping RHEL version, 5.1.6, but really hadn't used it since. After creating the db and setting my perms, I ran the obligatory "flush privileges" and received a message back from MySQL server that the "mysql.server" table didn't exist. A quick bit of googling revealed this helpful post that explained this can happen when upgrading a MySQL server installation that has existing databases. The fix was fairly simple. First to run the mysql_upgrade utility, which on RHEL systems is under /usr/bin:

mysql_upgrade -T -debug_check -u root -p

Here's the man page on what this does in the background:

mysql_upgrade executes the following commands to check and repair tables and to upgrade the system tables: mysqlcheck --all-databases --check-upgrade --auto-repair mysql < fix_priv_tables mysqlcheck --all-databases --check-upgrade --fix-db-names --fix-table-names

Check the output carefully for any recommended actions (you might want to invoke the "script" command before starting to pipe the console session to a file for later review). In my case the tool advised me to dump and then reload two databases in particular in order to properly fix them.

Copyright 2004-2019 Phil Lembo