25

I'm pretty new to using Linux but am setting up my MySQL databases on an Amazon ec2 instance. I followed some directions I found about resetting the user login pass by using the --skip-grant-tables option of MySQL. Now I am trying to add a user and can't figure out how to turn that option off.

This is what I'm trying to do:

mysql> GRANT CREATE,SELECT,INSERT,UPDATE,DELETE ON ...my db username and pass

but I get this error:

ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

How do I turn this option off?

Michael Mrozek
  • 91,316
  • 38
  • 238
  • 232
user718229
  • 365
  • 1
  • 3
  • 6

5 Answers5

44

Login to mysql 

 mysql -u root -p

Then execute:

FLUSH PRIVILEGES;

http://dev.mysql.com/doc/refman/5.0/en/server-options.html http://dev.mysql.com/doc/refman/5.0/en/mysqladmin.html

Community
  • 1
wdkidd
  • 441
  • 4
  • 4
5

You can make this done by using below steps.

[root@backups1 mysql5.7]# bin/mysqld_safe --skip-grant-tables --user=mysql

connect to your mysql without password.

mysql> flush privileges;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '';
mysql> flush privileges;

switch to normal mode of mysql then connect without password.

This surely will work for you.

RalfFriedl
  • 8,816
  • 6
  • 23
  • 34
Mansur Ul Hasan
  • 277
  • 3
  • 4
4

Just stop and restart MySQL normally.

Michael Hampton
  • 8,658
  • 2
  • 31
  • 54
1

Yesterday I've encountered similar issue. The server was using Amazon Linux 2 as OS and the official yum repository (the el7 ones) as the installation means.

Especially when you are using the MySQL of the official yum repo, MySQL would be installed as a systemd service. In such case, you can check how MySQL process is launched by executing following command: sudo service mysql status -l. This results in description of the current status of the current mysql service. Of those descriptions, I could find following line:

  Process: 26474 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS

In my environment, it turned out that the MYSQLD_OPTS variable was set with value --skip-grant-tables option by the systemd process. To confirm the environment variables set by systemd, you can execute sudo systemctl show and look for line starting with Environment=.

To change this environment variable, I executed following command.

sudo systemctl set-environment MYSQLD_OPTS=""

After this operation, I restarted the mysqld service by sudo service mysql restart, and everything was working perfectly.

Yuki Inoue
  • 233
  • 2
  • 11
1

Slight modifications from @mansur ali

MYSQL 8, Ubuntu 18.04

[ubuntu@ip-172-31-39-173]$  sudo /usr/bin/mysqld_safe --skip-grant-tables --user=mysql

connect to your mysql without password in another shell by executing

$ mysql

then

mysql> flush privileges;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '';
mysql> flush privileges;
mysql> quit;

switch to normal mode of mysql then connect without password.

This surely will work for you.

Shobi
  • 111
  • 3