MySQL Problems

Reset MySQL root password in MacPorts

First of all you will need to ensure that your database is stopped:

$ sudo /opt/local/share/mysql5/mysql/mysql.server stop

Now you should start up the database in the background, via the mysqld_safe command:

$ sudo /opt/local/lib/mysql5/bin/mysqld_safe –skip-grant-tables &

Now that the server is running with the –skip-grant-tables flag you can connect to it without a password and complete the job:

$ sudo mysql5 –user=root mysql
Enter password:
mysql> update user set Password=PASSWORD(‘new-password-here’) WHERE User=’root’;
Query OK, 2 rows affected (0.04 sec)
Rows matched: 2  Changed: 2  Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)
mysql> exit
Bye

Now that you’ve done that you just need to stop the server, so that you can go back to running a secure MySQL server with password restrictions in place. First of all bring the server you started into the foreground by typing “fg“, then kill it by pressing “Ctrl+c” afterwards.

This will now allow you to start the server:

$ sudo /opt/local/share/mysql5/mysql/mysql.server start