Tuesday, April 27, 2010

Ubuntu MySQL Upgrade Issue

I recently had to assist a friend who is hosting sites on an Ubuntu VPS; he found himself unable to connect to local MySQL instance as root.
Obviously, my first suggestion was to simply reset the password using the --skip-grant-tables trick that's well documented elsewhere online. However, attempting to stop the MySQL instance using the command /etc/init.d/mysql stop was not successful. So, after backing up the /var/lib/mysql directory, I began to research the issue a bit further:

  1. Unable to use the init.d script to stop MySQL, I manually stopped the process: killall mysqld

  2. Started the MySQL daemon, ignoring the permissions/grant tables: /usr/bin/mysqld_safe --skip-grant-tables &

  3. Reset the root user's password: UPDATE mysql.user SET Password=PASSWORD('new-password-here') WHERE User='root';

  4. Ran mysqlcheck to look for any corrupted tables (especially in the "mysql" database containing the accounts and permissions) and noticed that errors were being thrown in relation to the "information_schema" database: mysqlcheck --all-databases

  5. Attempted a repair of the "information_schema" database: mysqlcheck --repair information_schema

  6. Ran mysqlcheck again against the "information_schema" database; errors were still produced by the same tables as before.

  7. Restarted mysqld and noticed errors being output in relation to the "debian-sys-maint" user.

This particular error was what helped to identify the root cause of the issue. The server had recently been updated using the apt-get tool and this had included an update to MySQL. Trawling the web unearthed a bug filed on the Ubuntu project bug tracker that suggested that the post-upgrade scripts had not been executed. There is a dedicated MySQL account that exists on a Debian-based system specifically for this (and other DB maintenance) tasks: debian-sys-maint
The credentials for this user can be found in the file /etc/mysql/debian.cnf and should already be present in the MySQL database itself; allowing the system to perform maintenance tasks. Realising this user must be missing from the system, I set about recreating the user:

  1. Logged into MySQL as root and, using the credentials specified in /etc/mysql/debian.cnf, created the "debian-sys-maint" user: GRANT ALL ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY '';

  2. Now restarting MySQL generated different error messages: Cannot proceed because system tables used by Event Scheduler were found damaged at server start

  3. Knowing that the MySQL package installed on the system had been upgraded recently (and judging by the number of people in similar situations), I forced the execution of the mysql upgrade scripts: mysql_upgrade -u root -p --verbose --force

After these steps were carried out, restarting the MySQL instance occurred without any errors being produced as well as it being possible to authenticate to the system as "root".