Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

enter image description here

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

share|improve this question
1  
You might start by checking the output you get from running SHOW GRANTS; from the console and the same thing from Workbench, compare the results, and see if that gives you any ideas. –  Michael - sqlbot Oct 17 '13 at 10:46
    
in console its show me this: "GRANT ALL PRIVILEGES ON . TO 'root'@'localhost' IDENTIFIED BY PASSWORD '{password}' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION" and on workbranc its show me that "GRANT USAGE ON . TO ''@'master.mysql.domain.com'" –  ParisNakitaKejser Oct 17 '13 at 11:36
    
You're not logging in as the same user with the command line and with workbench. The command line, you're logging in as root, and workbench you're logging in anonymously. The first thing to try is disconnect and verify your credentials in Workbench, then reconnect. –  Michael - sqlbot Oct 17 '13 at 11:47
    
i know, thats the problem i can't remember how i can make my "reparer_nu" user to "admin" user. –  ParisNakitaKejser Oct 17 '13 at 11:48

1 Answer 1

up vote 1 down vote accepted

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

enter image description here

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

share|improve this answer
    
GREAT! :D Thanks a lot. now its working perfect! now i can go to the next step! :) –  ParisNakitaKejser Oct 17 '13 at 13:59
1  
@ParisNakitaKejser Your welcome, wish all the questions were that easy. –  Craig Efrein Oct 17 '13 at 14:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.