How can I change user password under MySQL server when I lost my password?
Here’s I’ll show two examples. The first example is about how to change password for normal user using Linux command line option, and the second example is on how to reset MySQL DBA password (By default, this user is called root).
Reset user password in MySQL
Let’s assume the normal user’s name is fatman, follow these steps to start:
1), Login to MySQL server, type following command at shell prompt to login as root
$ mysql -u root -p
2), Choose the right database mysql database, it’s mysql:
mysql> use mysql;
3), Change password for user fatman:
mysql> update user set password=PASSWORD("NEW-PASSWORD-HERE") where User='fatman';
4), Finally,dont forget to flush privileges:
mysql> flush privileges;
You can also use grant to reset a user’s password, the command is like this:
mysql> grant select,update,delete,insert on somedb.* to fatman@'somehost' identified by 'NEW-PASSWORD-HERE';
Reset DBA password in MySQL
When the DBA’s password lost, things become a little difficult, a restart might needed which is not so good for productive servers. There still have some possibilites to retrieval it without restart mysql service:
1), Is there any user has insert permission on the database “mysql”?
If so, he can insert into mysql.user and create another DBA account.
2), Is there any user has super user permissions?
For example, when the user was create like “GRANT ALL ON *.* TO user@’localhost’ WITH GRANT OPTION“, then this use is actually another DBA, you can ask him to reset root’s password.
If you got no luck till now, we have a common way here to fix the password. We can ask mysql server to bypass its grant tables when starting it. Thus it will not verify user passwords and permissions when they logon. That means all users will have full access to all databases as a database administrator.
$ /usr/sbin/mysqld --verbose --help | grep skip-grant-tables -A1 --skip-grant-tables Start without grant tables. This gives all users FULL ACCESS to all tables! -- skip-grant-tables FALSE skip-slave-start FALSE
Before making this changes, you need to make sure there’s no queries running on mysql. Then shutdown mysql instance first (mysql’s control script will help do that. In RPM based systems, it’s /etc/init.d/mysql) , and apply this option in the script, and start the service again.
When the DBA password got reset, do forget to remove the changes in mysql’s control script. If there any issue occures, please dont hesitate to create a topic in our support forum!