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:
- 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
- Skipping Access Tables. If you have installed MySQL5, fire up Terminal window and execute
/usr/local/mysql/bin/mysqld_safe --skip-grant-tablesFor older versions of MySQL, execute the following command
- Reset MySQL root password. Now when
safe_mysqldrunning 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_mysqldexecution 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:
- Find the location where phpMyAdmin is installed and open
- Comment out all
$cfg['Servers'][$i]rows below “Advanced phpMyAdmin features” title.
$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;.
- 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.
- 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!