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

I locked my root user out from our database. I need to get all privileges back to the root user. I have m开发者_运维百科y password and I can log in to MySQL. But the root user has no all privileges.

I had the same problem as the title of this question, so incase anyone else googles upon this question and wants to start MySql in 'skip-grant-tables' mode on Windows, here is what I did.

Stop the MySQL service through Administrator tools, Services.

Modify the my.ini configuration file (assuming default paths)

C:\Program Files\MySQL\MySQL Server 5.5\my.ini

or for MySQL version >= 5.6

C:\ProgramData\MySQL\MySQL Server 5.6\my.ini 

In the SERVER SECTION, under [mysqld], add the following line:

skip-grant-tables

so that you have

# SERVER SECTION
# ----------------------------------------------------------------------
#
# The following options will be read by the MySQL Server. Make sure that
# you have installed the server correctly (see above) so it reads this 
# file.
#
[mysqld]

skip-grant-tables

Start the service again and you should be able to log into your database without a password.

How to re-take control of the root user in MySQL.

DANGER: RISKY OPERATTION

• Start session ssh (using root if possible).

• Edit my.cnf file using.

  sudo vi /etc/my.cnf
  

• Add line to mysqld block.*

  skip-grant-tables
  

• Save and exit.

• Restart MySQL service.

  service mysql restart
  

• Check service status.

  service mysql status
  

• Connect to mysql.

  mysql
  

• Using main database.

  use mysql;
  

• Redefine user root password.

  UPDATE user SET `authentication_string` = PASSWORD('myNuevoPassword') WHERE `User` = 'root'; 
  

• Edit file my.cnf.

  sudo vi /etc/my.cnf
  

• Erase line.

  skip-grant-tables
  

• Save and exit.

• Restart MySQL service.

  service mysqld restart
  

• Check service status.

  service mysql status
  

• Connect to database.

  mysql -u root -p
  

• Type new password when prompted.

This action is very dangerous, it allows anyone to connect to all databases with no restriction without a user and password. It must be used carefully and must be reverted quickly to avoid risks.

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!

On the Linux system you can do following (Should be similar for other OS)

Check if mysql process is running:

sudo service mysql status

If runnning then stop the process: (Make sure you close all mysql tool)

sudo service mysql stop

If you have issue stopping then do following

Search for process: ps aux | grep mysqld Kill the process: kill -9 process_id

Now start mysql in safe mode with skip grant

sudo mysqld_safe --skip-grant-tables &

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.

If you use mysql 5.6 server and have problems with C:\Program Files\MySQL\MySQL Server 5.6\my.ini:

You should go to C:\ProgramData\MySQL\MySQL Server 5.6\my.ini.

You should add skip-grant-tables and then you do not need a password.

# SERVER SECTION
# ----------------------------------------------------------------------
#
# The following options will be read by the MySQL Server. Make sure that
# you have installed the server correctly (see above) so it reads this 
# file.
#
# server_type=3
[mysqld]
skip-grant-tables

Note: after you are done with your work on skip-grant-tables, you should restore your file of C:\ProgramData\MySQL\MySQL Server 5.6\my.ini.

Use the following command (notice the "d"): mysqld --skip-grant-tables

if you are running on Apple MacBook OSX then:

1. Stop your MySQL server (if it is already running).
2. Find your MySQL configuration file, my.cnf. (For me it was placed @ /Applications/XAMPP/xamppfiles/etc. You can just search if you can't find it).
3. Open my.cnf file in any text editor.
4. Add "skip-grant-tables" (without quotes) at the end of [mysqld] section and save the file.
5. Now start your MySQL server. It'll start with skip-grant-tables option.

Do what you want now!!

PS: Please remove skip-grant-tables from my.cnf file once you are done with whatsoever you want to do ELSE MySQL server will always run without access grants.

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 

if this is a windows box, the simplest thing to do is to stop the servers, add skip-grant-tables to the mysql configuration file, and restart the server.

once you've fixed your permission problems, repeat the above but remove the skip-grant-tables option.

if you don't know where your configuration file is, then log in to mysql send SHOW VARIABLES LIKE '%config%' and one of the rows returned will tell you where your configuration file is.

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.

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