How to start MySQL with --skip-grant-tables?

Viewed 299509

I locked my root user out from our database. I need to get all privileges back to the root user. I have my password and I can log in to MySQL. But the root user has no all privileges.

13 Answers

After trying lots of things, this is what worked for me:

sudo mysql -u root
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'newpassword'; 

What that does is first we use sudo to log in mysql as root without needing a password. Then we just update root's password.

After that, I restarted mysqld:

sudo service mysql restart

And the newpassword logged root in!

I'm in windows 10, using WAMP64 server. Searched for my.cnf and my.ini. Found my.ini in C:\wamp64\bin\mariadb\mariadb10.2.14.

Following the instructions from the colleagues:

  1. Opened the quick start menu from Wampserver, selected 'Stop All Services'
  2. Opened my.ini in a text editor, searched for [mysqld]
  3. Added 'skip-grant-tables' at the end of the [mysqld] section (but within it)
  4. Save the file, leave the editor open
  5. In the Wampserver menu, select "Restart Services'. There will be a warning about the skip-grant-tables option
  6. In the Wampserver menu select MySQL to open the prompt
  7. It asked for a password, just press enter
  8. Paste the command ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'newpassword';
  9. It must report that the operation was successful (no tables affected)
  10. In the my.ini file, erase the 'skip-grant-tables' line, save the file
  11. In the WampServer menu, select once more Restart Service

Now you can enter with the new password. Thanks to all answers here.

Please run this below command from the console to skip the user table verification while launching mysql database from command prompt

mysqld -skip-grant-tables

Edit my.ini file and add skip-grant-tables and restart your mysql server :

[mysqld]
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"
# enable-named-pipe
key_buffer = 16M
max_allowed_packet = 1M
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
log_error = "mysql_error.log"
skip-grant-tables
# Change here for bind listening
# bind-address="127.0.0.1" 
# bind-address = ::1 

I see that the question is old, but maybe my configuration will help someone. I use this configuration in scripts:

sed -i 's/^#skip-grant-tables.*/skip-grant-tables/g' /etc/my.cnf

service mysql restart

mysql -e "UPDATE mysql.user SET authentication_string='' WHERE user='root';"

sed -i 's/^skip-grant-tables.*/#skip-grant-tables/g' /etc/my.cnf

service mysql restart

This is how to do it on Ubuntu 20.4. This worked for me.
Go to /etc/mysql/mysql.conf.d/
You can write into terminal
cd /etc/mysql/mysql.conf.d/,
then you need to edit the file which is named mysqld.cnf.
On my PC, that file was a read-only file, so I needed to first change the permissions.
I wrote sudo chmod +rw mysqld.cnf in the terminal.
After that, I edited the file by typing sudo gedit mysqld.cnf in the terminal.
In the file, you will see [mysqld] somewhere, below [mysqld] add skip-grant-tables in a new line, so that it looks like this

[mysqld]
skip-grant-tables

Restart the mysql service by writting sudo service mysql restart in terminal.
If your server wasn't running then write sudo service mysql start in terminal.
Another thing worth mentioning here is that
I also had another problem which I fixed in almost the exact same manner.
My server wasn't listening at the port 3306,
so I also had to add port = 3306 in that mysqld.cnf file.
Now I have

[mysqld]
skip-grant-tables
port = 3306

in the mysqld.cnf file.

Related