Change normal user to admin in MySQL

Question

I have set up a MySQL database. My problem now is I can't add new users with the user I have created.

I try to grant access but I can't still add new users to my system. I use Workbench to add new users, so here is my screenshot:

Now I need to get access to my user account, but I can't remember how I can do this from MySQL command line.

Answer 1

Do you have access to the root account? Are you pointing to the right host? Sometimes it is useful to identify the host with the -h option to avoid DNS issues

mysql -u root -pSOMEPASSWORD -h127.0.0.1

If you still can't get into MySQL, you can reset the root password by starting MySQL with --skip-grant-tables option

Stop MySQL

In Linux, as root user or equivalent

/etc/init.d/mysqld stop

Start MySQL with --skip-grant-tables option, you can find the mysqld_safe binary file with the which command

which mysqld_safe

/usr/bin/mysqld_safe --skip-grant-tables &

Login to MySQL without password

mysql -u root

Login to MySQL database

use mysql;

Update the root user password

update user set password=PASSWORD("mynewpassword") where User='root';

This will reset the password for any account called root, regardless of the host.

Flush privliges

flush privileges;

quit

Stop and Start MySQL

/etc/init.d/mysql stop
/etc/init.d/mysql start

If you are using Windows, you can simply update the my.cnf file with this option under the [mysqld] section

[mysqld]
skip-grant-tables

If you are not sure where the my.cnf file is, you can find the path by going to Administrative Tools available in Control Panel

In this case, you would most likely find the my.cnf file in a subdirectory of C:\Program Files\MySQL\MySQL Server 5.5\

And then restart MySQL using the Windows Services control panel in Administrative Tools.

From there you would be in safe mode and the commands would be the same.

update user set password=PASSWORD("mynewpassword") where User='root'; flush privileges; quit

remove the skip-grant-tables from the my.cnf file and then restart MySQL

Conclusion

Now that you have root access again to MySQL, you can setup new users again