Fixing MySQL and phpMyAdmin After Upgrading to OS X Lion

By Dragan Simonovic

I was impressed how Apple managed upgrade process from OS X Leopard to Lion. Everything was smooth and unreal. All settings, personalization, files, everything was there. Everything was there until I tried to access my local installation of phpMyAdmin. Something was broken..

First of all, my MySQL root password doesn’t work. That’s a huge problem. If root password doesn’t work, I can’t access any of my local databases. So the first thing I needed to do is to reset MySQL root password. Here is the tip how to do that:

  1. Stop MySQL service. You can do that using the preference pane if you have that installed or you can stop it using Terminal sudo /Library/StartupItems/MySQLCOM/MySQLCOM stop
  2. Skipping Access Tables. If you have installed MySQL5, fire up Terminal window and execute /usr/local/mysql/bin/mysqld_safe --skip-grant-tables For older versions of MySQL, execute the following command /usr/local/mysql/bin/safe_mysqld --skip-grant-tables
  3. Reset MySQL root password. Now when safe_mysqld running in one Terminal window, open up another Terminal window and execute /usr/local/mysql/bin/mysql mysql. This opening up the MySQL console and opening the mysql table so we can update MySQL root user. Write the reset query into the console as follows UPDATE user SET Password=PASSWORD(‘YOUR_PASSWORD’) WHERE Host=’localhost’ AND User=’root’;. Replace “YOUR_PASSWORD” with your desired password. Once you’ve done that just exit the console “exit;” close the safe_mysqld execution and restart your MySQL server.

This fixed MySQL root user but after logging into phpMyAdmin I still can’t access any database. The problem was in MySQL Socket because scripting language (PHP, in my case) can’t find the location of mysql.sock. To fix this type the following commands into Terminal:

cd /var
sudo mkdir mysql
cd mysql
sudo ln -s /private/tmp/mysql.sock mysql.sock

This move the sock file to a spot where a scripting language (specifically PHP) looks for it. Restart MySQL server and now you should be able to login into phpMyAdmin. But after login, I found a new problem. phpMyAdmin advanced features are not set correctly. To fix this follow the instructions:

  1. Find the location where phpMyAdmin is installed and open file.
  2. Comment out all $cfg['Servers'][$i] rows below “Advanced phpMyAdmin features” title.
  3. Add $cfg['Servers'][$i]['tracking'] = 'pma_tracking'; and $cfg['Servers'][$i]['tracking_default_statements'] = 'CREATE TABLE,ALTER TABLE,DROP TABLE,RENAME TABLE,CREATE INDEX,DROP INDEX,INSERT,UPDATE,DELETE,TRUNCATE,REPLACE,CREATE VIEW,ALTER VIEW,DROP VIEW,CREATE DATABASE,ALTER DATABASE,DROP DATABASE'; $cfg['Servers'][$i]['tracking_version_auto_create'] = TRUE; $cfg['Servers'][$i]['tracking_version_drop_view'] = TRUE; $cfg['Servers'][$i]['tracking_version_drop_table'] = TRUE; $cfg['Servers'][$i]['tracking_version_drop_database'] = TRUE;.
  4. Execute the file create_tables.sql, found in the scripts/ folder of your phpMyAdmin installation. The easiest way is to use phpMyadmin; click the Import button on the main page, and import the .sql file.
  5. After that, remove the code we added in step #3 and uncomment “Advanced phpMyAdmin features”. They should look like this:
    • /* Advanced phpMyAdmin features */
      $cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';
      $cfg['Servers'][$i]['bookmarktable'] = 'pma_bookmark';
      $cfg['Servers'][$i]['relation'] = 'pma_relation';
      $cfg['Servers'][$i]['table_info'] = 'pma_table_info';
      $cfg['Servers'][$i]['table_coords'] = 'pma_table_coords';
      $cfg['Servers'][$i]['pdf_pages'] = 'pma_pdf_pages';
      $cfg['Servers'][$i]['column_info'] = 'pma_column_info';
      $cfg['Servers'][$i]['history'] = 'pma_history';
      $cfg['Servers'][$i]['tracking'] = 'pma_tracking';
      $cfg['Servers'][$i]['designer_coords'] = 'pma_designer_coords';

That’s it, your MySQL server and phpMyAdmin should be up and running!