MySQL’ed Out

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>

1. Stop the MySQL server:
   1. Find the MySQL daemon, mysqld, process ID file.
      [root@hostname ~]& locate mysql.pid
   2. Kill the mysqld process.
      [root@hostname ~]& kill `cat /var/run/mysql/mysql.pid`
2. 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.
3. Start the mysql client:
   [root@hostname ~]& mysql -u root -p
4. 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’;
5. Always remember to reload the provileges when you’re done:
   mysql> FLUSH PRIVILEGES;
6. Stop the MySQL server:
7. 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

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).