2

Ok I'm asking this question since I tried looking for an answer for the last 4 hours.
I don't even know what I did in the server that it won't allow me to connect from the local host. but even after installing anything related to the mysql installation and reinstalling only the server it won't let me connect.

when I try to configure the server after I just installed it on the apply security settings it fails and give me that error.

My server is installed on a regular PC and not on any website, the PC runs a win 7 and I tried the skip-grant-tables option as well as the removing the registry keys after uninstalling the server.

Please if anyone has any other option for me to use or if you think I did something wrong in these other options please tell me how can I fix that doing the right thing and how to do that.

Sagi Rokach
  • 147
  • 1
  • 2
  • 13

3 Answers3

5

I also had this same problem when installing a new version of mysql5.5, but i solved it.

If you go through the path mysql was installed in, in windows your windows system, "c:\mysqlinstalllocation\bin" you can run the mysql query from this directory, but if you have already added the mysql command to the path option in windows, in environment variables, which will help you run custom commands form the command line, you can run it directly from any directory in the command prompt.

Just add the "skip-grant-tables" command under the [mysqld] heading in the mysql.ini file, and make sure to restart your server after doing this, and run mysql, it will work. At this point you will login without any password, so it's advisable to delete the password at this point, so you can login easily after you've uncomment the skip-grant-tables" command

And after you've removed the password from the root user, make sure to remove the "skip-grant-tables" command, or comment it out, with #, then restart your server. It will work.

Now you can login without any password, and run the grant command, and set any password you like.

Samo Adx
  • 61
  • 7
3

You may solve the problem like bellow:

  1. Open my.ini file
  2. add skip-grant-tables under the [mysqld]

  3. [mysqld] is look like

    # The MySQL server
[mysqld]
skip-grant-tables
port= 3306
socket = "C:/xampp/mysql/mysql.sock"
basedir = "C:/xampp/mysql" 
tmpdir = "C:/xampp/tmp" 
datadir = "C:/xampp/mysql/data"
pid_file = "mysql.pid"
Enamul Haque
  • 4,789
  • 1
  • 37
  • 50
0

You need to add the relevant permissions and use the skip-grant-tables option to bypass the inbuilt security.

GRANT ALL ON *.* TO 'root'@'localhost' IDENTIFIED by 'password';

Restart as normal without the skip-grant-tables flag.

ChrisBint
  • 12,773
  • 6
  • 40
  • 62
  • ok just to understand. how exactly do I do that? because when I tried to login to the server after starting the service with the skip-grant-tables I couldn't login with the command line shell. and couldn't create the user through the workbench. thanks – Sagi Rokach Feb 26 '13 at 11:26
  • what error do you get when connecting from command line? do you connect using: mysql -h localhost -u YourUser -p ? – Alejandro Feb 26 '13 at 16:56
  • Following your instructions I still get the error: `ERROR 1290 (HY000): The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement`. The statement was: `GRANT ALL ON *.* TO 'root'@'localhost' IDENTIFIED by 'xxxxxxx';`. – Stephane Mar 22 '17 at 09:50
  • Doing a `flush privileges;` beforehand allowed me to run the `grant all...` command. – Stephane Mar 22 '17 at 09:53