Turn off --skip-grant-tables in MySQL
Solution 1
Just stop and restart MySQL normally.
Solution 2
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
Solution 3
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.
Solution 4
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.
Solution 5
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.
Related videos on Youtube
user718229
Updated on September 18, 2022Comments
-
user718229 over 1 year
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?
-
Khom Nazid over 3 yearsDoesn't work with mariadb.