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

Question

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.

Answer 1

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 &

Answer 2

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.

Answer 3

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.

Answer 4

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 

Answer 5

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.

Answer 6

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.