MySQL won’t start after upgrading to PHP 5.3

MySQL

Just upgraded to PHP 5.3 with the latest version of mysql, but now mysql won't start. Below is mysqld.log

110516 22:24:46 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
110516 22:24:46 [ERROR] Error message file '/usr/share/mysql/english/errmsg.sys' had only 480 error messages, but it should contain at least 709 error messages. Check that the above file is the right version for this program!
110516 22:24:46 [Note] Plugin 'FEDERATED' is disabled.
/usr/libexec/mysqld: Unknown error 1146
110516 22:24:46 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
110516 22:24:46 InnoDB: The InnoDB memory heap is disabled
110516 22:24:46 InnoDB: Mutexes and rw_locks use GCC atomic builtins
110516 22:24:46 InnoDB: Compressed tables use zlib 1.2.3
110516 22:24:46 InnoDB: Using Linux native AIO
110516 22:24:46 InnoDB: Initializing buffer pool, size = 128.0M
110516 22:24:46 InnoDB: Completed initialization of buffer pool
110516 22:24:46 InnoDB: highest supported file format is Barracuda.
110516 22:24:46  InnoDB: Waiting for the background threads to start
110516 22:24:47 InnoDB: 1.1.6 started; log sequence number 10818302
110516 22:24:47 [ERROR] Aborting

110516 22:24:47  InnoDB: Starting shutdown...
110516 22:24:48  InnoDB: Shutdown completed; log sequence number 10818302
110516 22:24:48 [Note]
110516 22:24:48 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

Any clues?

Thanks. Help Appreciated!

Best Answer

I think the problem stems from the fact that the upgrade was done trying to use the mysql schema folder of the previous version of MySQL, since the message "Can't open the mysql.plugin table" appears.

MySQL 5.1/5.5 uses a table called mysql.plugin

Prior versions (MySQL 5.1/4.x/3.x) do not use mysql.plugin.

I also noticed another oddity. The error message also said the highest file format is Barracida. The innodb_file_format of earlier versions of mysql is Antelope. You may want to export the InnoDB data in the previous version of mysql you had running. Otherwise, you may not have proper access to the InnoDB files.

Here is what you should do:

Step 1) Make a backup copy of that mysql folder. (/var/lib/mysql/mysql and /var/lib/mysql)

Step 2) You should reinstall the MySQL 5.0 version that was there before.

Step 3) Once mysql 5.0 is back up, perform a mysqldump of all databases except the mysql schema to /root/OldData.sql. Make sure you get triggers, stored procedures and views as follows:

For example, if you have 5 databases, (information_schema, db1, db2, db3, and mysql), do not include information_schema and mysql. Just mysqldump all the others.

mysqldump -hhostaddr -umyuserid -pmypassword --routines --triggers --databases db1 db2 db3 > /root/OldData.sql

Step 4) Capture the mysql grants from MySQL 5.0 as follows:

mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A | sed 's/$/;/g' > /root/MySQLGrants.sql

Step 5) service mysql stop

Step 6) mv /etc/my.cnf /etc/my.cnf1

Step 7) Clear away /var/lib/mysql (Make sure you did Step 1)

Step 8) Uninstall mysql 5.0

Step 9) Install mysql 5.5 (MySQL 5.5 does not auto-install anymore)

Step 10) mv /etc/my.cnf1 /etc/my.cnf

Step 11) service mysql start

Step 12) Login to mysql and from the mysql client run the scripts

Step 13) Load the two SQL scripts

mysql> source /root/MySQLGrants.sql
mysql> source /root/OldData.sql

You should fine from here.

Give it a Try !!!

Related Question