Forgot MySQL Password? Here is how to get back in and add a new user

Posted on by Terry Pearson

If you program for the web, you probably have been locked out of MySQL at some point. If you find yourself in a situation where you cannot login, here is how you regain access. The following example will give your user complete control over the database.

 

1. Login as a sudo-er or a root user.

2. Stop mysql:

  • /etc/init.d/mysqld stop

3. Start mysql in safe mode:

  • mysqld_safe –skip-grant-tables

4. Login to mysql as root

  • mysql -u root

5. Select the mysql database.

  • use mysql;

6. Insert a new username or update the existing password for a user (The following gives universal access, adjust accordingly):

  • INSERT INTO user VALUES(‘%’,’INSERT_USERNAME_HERE’,PASSWORD(‘INSERT_PASSWORD_HERE’), ‘Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,”,”,”,”,0,0,0,0);
  • Note that the exact number of columns will differ depending on mysql version. Use the ‘desc user’ command to see the corresponding column names. This was for version 5.0.45)

Leave a Reply