This detailed guide is mainly for FreeBSD, however the idea coincides for Linux. Every once a while, when I upgrade my FreeBSD box, the system prefers to shutdown my MySQL web server. Therefore, I need to begin it again after the upgrade is done. Sadly, the update process isn’t smooth every time. Sometimes it will toss me some mistake..
/usr/local/etc/rc.d/mysql.server start
Oh well, I got the following error messages:
Starting MySQL..... ERROR! The server quit without updating PID file.
Sometimes, the message will tell you the exact location of which PID file:
Starting MySQL..... ERROR! The server quit without updating PID file (/var/db/mysql/www.icesquare.com.pid).
There are several solutions to troubleshoot these problems. I will go over each one by one.
Solution 1: Reboot The Computer
Although it sounds simple, but it truly works. Throughout the system update, the OS may disable some of your daemons. Rather than fixing each one at a time, the easiest way is to begin everything over. For example, I skilled this problem today after updating the Apache and Ruby (Yes, MySQL isn’t component of the update), and I obtained this mistake message later. After rebooting the computer system, the mistake message is gone.
Solution 2: Remove Your MySQL Config File
If you have actually customized your MySQL setup submit, MySQL might not like it couple of variations after (MySQL isn’t backward compatibility pleasant). It could be the issue of utilizing an unsupported variable, or something comparable. The simplest method is to eliminate your setup submit, and attempt to begin the MySQL web server once more:
Backup your MySQL configuration first.
mv /etc/my.cnf /etc/my.cnf.backup
And restart the MySQL server again:
/usr/local/share/mysql/mysql.server start
Hopefully you will see the following message:
Starting MySQL. SUCCESS!
Solution 3: Upgrade Your Database File
Sometimes, the newer MySQL doesn’t like the database created in earlier version. I discovered this when I upgrade to MySQL 5.5.7:
Starting MySQL..... ERROR! The server quit without updating PID file (/var/db/mysql/www.icesquare.com.pid).
Since MySQL tells me which PID file causes the problem, I open the file and take a look what’s going on:
sudo tail /var/db/mysql/www.icesquare.com.err
And I saw something interesting: tables: Table ‘mysql.proxies_priv’ doesn’t exist:
101112 10:49:16 InnoDB: Initializing buffer pool, size = 128.0M
101112 10:49:16 InnoDB: Completed initialization of buffer pool
101112 10:49:16 InnoDB: highest supported file format is Barracuda.
101112 10:49:17 InnoDB: 1.1.3 started; log sequence number 1589404
101112 10:49:17 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.proxies_priv' doesn't exist
101112 10:49:17 mysqld_safe mysqld from pid file /var/db/mysql/www.icesquare.com.pid ended
The reason is very simple. MySQL could not open a table created in the earlier version (< 5.7.7) because it is not compatible with the current version. So, we can try to start the MySQL in safe mode through rc.d. First, you can edit the /etc/rc.conf and put the following into the file:
mysql_enable="YES"
mysql_args="--skip-grant-tables --skip-networking"
Restart MySQL through rc.d:
/usr/local/etc/rc.d/mysql-server start
If you did it right, you should see something like the following:
Starting MySQL.. SUCCESS!
Now, MySQL is already running the safe-mode. We want to perform a MySQL upgrade on all tables:
sudo mysql_upgrade
You should see something like this:
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/tmp/mysql.sock'
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/tmp/mysql.sock'
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.servers OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
Running 'mysql_fix_privilege_tables'...
OK
Now, we want to switch the MySQL back to normal mode by commenting the extra options in /etc/rc.conf:
mysql_enable="YES"
#mysql_args="--skip-grant-tables --skip-networking"
And restart MySQL through /etc/rc.d:
/usr/local/etc/rc.d/mysql-server restart
Now the MySQL is up and running again!