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.
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.
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:
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.
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.