So eventually I end up screwing around with things long enough to screw them up. Today I managed to munge the root account on a MySQL installation and wasn’t able to log back in to fix it. It took me some time to figure my way this mess. There’s no easy to use guide (that I can find) so here’s one.
Recreating the MySQL ‘root’ account on Linux (Fedora Core 4):
Note: The command line prompt on my installation looks like this [root@hostname ~]& and commands that should be typed in are shown on bold. The MySQL client command line propmt looks like this mysql>
- Stop the MySQL server:
- Find the MySQL daemon, mysqld, process ID file.
[root@hostname ~]& locate mysql.pid - Kill the mysqld process.
[root@hostname ~]& kill `cat /var/run/mysql/mysql.pid`
- Find the MySQL daemon, mysqld, process ID file.
- Start the MySQL server without using the permission tables:
[root@hostname ~]& /usr/libexec/mysqld –user=root –skip-grant-tables –skip-networking
The “–skip-networking” option is a minor security precaution to prevent users connecting to the server whilst it’s running in an unsecure mode, ie. not checking permissions.
Check this for a more secure method of starting MySQL. - Start the mysql client:
[root@hostname ~]& mysql -u root -p - Correct the “broken” ‘root’ account as appropriate:
- Reset the password:
mysql> UPDATE user SET password=PASSWORD(‘new_password’) WHERE user=’root’;
OR - Change the allowed location(s):
mysql> UPDATE user SET host=’correct_host’ WHERE user=’root’;
OR - Create a new ‘root’ account:
mysql> GRANT ALL PRIVILEGES ON *.* TO ‘root’@’localhost’ IDENTIFIED BY ‘password’;
- Reset the password:
- Always remember to reload the provileges when you’re done:
mysql> FLUSH PRIVILEGES; - Stop the MySQL server:
- Restart the MySQL the normal way. I use Webmin (using system/boot up & shutdown/mysqld) but you can also use:
- Directly running the command
[root@hostname ~]& /usr/bin/safe_mysqld &
The ampersand (&) detaches the command from the current terminal, allowing the process to run on it’s own. - System init method (how it normally starts when Linux boots:
[root@hostname ~]& /etc/init.d/mysqld start
- Directly running the command
That’s it. You’re done. After this I go back to admin’ing MySQL with a combination of Webmin (minimal use) and PHPMyAdmin (most stuff).