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
    2. Kill the mysqld process.
      [root@hostname ~]& kill `cat /var/run/mysql/`
  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’;
    • Change the allowed location(s):
      mysql> UPDATE user SET host=’correct_host’ WHERE user=’root’;
    • 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:
  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).

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.