14

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?

3

Just stop and restart MySQL normally.

|improve this answer|||||
28

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

|improve this answer|||||
2

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.

|improve this answer|||||
0

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.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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