Managing & Using MySQL, 2nd Edition

Recovering from Password and Permission Problems

If you garble your GRANT commands or forget passwords and find that you don’t have access to the critical mysql table—even as the root user—don’t panic. Become the superuser on the operating system (we’re talking now about the Unix root, not the MySQL root) and kill the MySQL process. On a RedHat Linux or other SVR4-type systems, you might be able to end MySQL through the command:

/etc/rc.d/init.d/mysql stop

Otherwise, find all MySQL processes and kill them explicitly as root:

$ ps ax | grep mysql
  2498 pts/1    S      0:00 /bin/sh bin/safe_mysqld
  2514 pts/1    S      0:00 /usr/local/src/mysql/bin/mysqld --defaults-extra-file
  2516 pts/1    S      0:00 /usr/local/src/mysql/bin/mysqld --defaults-extra-file
  2517 pts/1    S      0:00 /usr/local/src/mysql/bin/mysqld --defaults-extra-file
$ kill 2498 2514 2516 2517

Now start up MySQL again, bypassing the grant tables:

$ safe_mysqld --skip-grant-tables

Make sure you can now get access to the mysql database:

 $ mysql mysql
 mysql> quit

Assign a password once again to the MySQL root user:

$ mysqladmin -u root password 'new_password'

Terminate the MySQL server and restart it in the usual way. Make any necessary changes to the privileges through GRANT commands, running mysql as the root user.