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)