Mysql Server Login and Permission Deny Error

Question

I have a problem with MySQL server. After upgrading MySQL server from 5.3 to 5.4, I am not able to log-in using my MySQL root password (Yes, I am able to log-in without password!).

Also not able to grant permissions to users as well. Not able to change in databases using root account, while taking backup of database due to same.

When I looked into MySQL databases using root account, it doesn't show anything. While on other server, I am able to find all entries.

Kindly please help me to resolve this issue as it is critical.

Log of mysql is as below.

130906 18:46:13 [Warning] Found invalid password for user: '[email protected]'; Ignoring user
1843 130906 18:46:13 [ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it
1844 130906 18:46:14 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
1845 130906 18:46:14 [ERROR] Native table 'performance_schema'.'events_waits_current' has the wrong structure
1846 130906 18:46:14 [ERROR] Native table 'performance_schema'.'events_waits_history' has the wrong structure
1847 130906 18:46:14 [ERROR] Native table 'performance_schema'.'events_waits_history_long' has the wrong structure
1848 130906 18:46:14 [ERROR] Native table 'performance_schema'.'setup_consumers' has the wrong structure
1849 130906 18:46:14 [ERROR] Native table 'performance_schema'.'setup_instruments' has the wrong structure
1850 130906 18:46:14 [ERROR] Native table 'performance_schema'.'setup_timers' has the wrong structure
1851 130906 18:46:14 [ERROR] Native table 'performance_schema'.'performance_timers' has the wrong structure
1852 130906 18:46:14 [ERROR] Native table 'performance_schema'.'threads' has the wrong structure
1853 130906 18:46:14 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_thread_by_event_name' has the wrong structure
1854 130906 18:46:14 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_instance' has the wrong structure
1855 130906 18:46:14 [ERROR] Native table 'performance_schema'.'events_waits_summary_global_by_event_name' has the wrong structure
1856 130906 18:46:14 [ERROR] Native table 'performance_schema'.'file_summary_by_event_name' has the wrong structure
1857 130906 18:46:14 [ERROR] Native table 'performance_schema'.'file_summary_by_instance' has the wrong structure
1858 130906 18:46:14 [ERROR] Native table 'performance_schema'.'mutex_instances' has the wrong structure
1859 130906 18:46:14 [ERROR] Native table 'performance_schema'.'rwlock_instances' has the wrong structure
1860 130906 18:46:14 [ERROR] Native table 'performance_schema'.'cond_instances' has the wrong structure
1861 130906 18:46:14 [ERROR] Native table 'performance_schema'.'file_instances' has the wrong structure
1861 130906 18:46:14 [ERROR] Native table 'performance_schema'.'file_instances' has the wrong structure
1862 130906 18:46:14 [ERROR] Column count of mysql.db is wrong. Expected 22, found 13. Created with MySQL 50045, now running 50532. Please use mysql_upgrade to fix this error.
1863 130906 18:46:14 [ERROR] mysql.user has no `Event_priv` column at position 29
1864 130906 18:46:14 [ERROR] Cannot open mysql.event
1865 130906 18:46:14 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
1866 130906 18:46:14 [Note] /usr/libexec/mysqld: ready for connections.
1867 Version: '5.5.32-cll-lve' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) by Atomicorp

Answer 1

Besides not running mysql_upgrade after upgrading MySQL from major subversion to major subversion (E.g 5.1 to 5.5), having the wrong permissions/ownership on the performance_schema folder and files within the data directory can also cause this error.

For example, a fresh install of MySQL 5.5's RPM will set the ownership of that folder and files within it to root:root. To fix this, you will need to change the ownership to mysql:mysql.

E.g.

chown -R mysql:mysql /var/lib/mysql/performance_schema

ls -al /var/lib/mysql

drwx------. 2 mysql mysql     4096 Jun 10 10:29 performance_schema

Answer 2

First of all before performing an upgrade you should have taken a backup of all important databases (or simply all databases) using the mysqldump command.

Anyhow, to fix this please run:

shell> mysql_upgrade 

To understand more what mysql_upgrade does see here.

If you get permissions errors:

mysql_upgrade -u root -p (give the root password here)

If still it fails, rename /etc/my.cnf to /etc/my.cnfbku and then rerun the command.

This will fix all the issues regarding the upgrade.