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.
This question is related to
mysql
privileges
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 &
if you are running on Apple MacBook OSX
then:
my.cnf
. (For me it was placed @
/Applications/XAMPP/xamppfiles/etc
. You can just search if you
can't find it).my.cnf
file in any text editor."skip-grant-tables"
(without quotes) at the end of [mysqld]
section and save the file.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.
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:
my.ini
in a text editor, searched for [mysqld]
'skip-grant-tables'
at the end of the [mysqld]
section (but within it)skip-grant-tables
optionALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'newpassword';
my.ini
file, erase the 'skip-grant-tables'
line, save the fileNow you can enter with the new password. Thanks to all answers here.
How to re-take control of the root user in MySQL.
DANGER: RISKY OPERATTION
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.
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.
Use the following command (notice the "d"): 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.
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
.
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
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';
So 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!
Source: Stackoverflow.com