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:- Unable to use the init.d script to stop MySQL, I manually stopped the process:
killall mysqld - Started the MySQL daemon, ignoring the permissions/grant tables:
/usr/bin/mysqld_safe --skip-grant-tables & - Reset the root user's password:
UPDATE mysql.user SET Password=PASSWORD('new-password-here') WHERE User='root'; - 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 - Attempted a repair of the "information_schema" database:
mysqlcheck --repair information_schema - Ran mysqlcheck again against the "information_schema" database; errors were still produced by the same tables as before.
- 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:- 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 ''; - Now restarting MySQL generated different error messages:
Cannot proceed because system tables used by Event Scheduler were found damaged at server start - 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".
