How to grant all privileges to root user in MySQL 8.0

  • 3,000
  • Tác giả: admin
  • Ngày đăng:
  • Lượt xem: 3
  • Tình trạng: Còn hàng

Just my 2 cents on the subject. I was having the exact same issue with trying lớn connect from MySQL Workbench. I'm running a bitnami-mysql virtual machine lớn phối up a local sandbox for development.

Bitnami's tutorial said lớn lập cập the 'Grant All Privileges' command:

/opt/bitnami/mysql/bin/mysql -u root -p -e "grant all privileges on *.* lớn 'root'@'%' identified by 'PASSWORD' with grant option";

This was clearly not working, I finally got it lớn work using Mike Lischke's answer.

What I think happened was that the root@% user had the wrong credentials associated lớn it. So if you've tried lớn modify the user's privileges and with no luck try:

  1. Dropping the user.
  2. Create the user again.
  3. Make sure you have the correct binding on your MySQL config tệp tin. In my case I've commented the line out since it's just for a sandbox environment.

1. Dropping the user.

From Mysql Console:

List Users (helpful lớn see all your users):

select user, host from mysql.user;

Drop Desired User:

drop user '{{ username }}'@'%';

2. Create the user again.

Create User and Grant Permissions:

CREATE USER '{{ username }}'@'%' IDENTIFIED BY '{{ password }}';
GRANT ALL PRIVILEGES ON *.* TO '{{ username }}'@'%' WITH GRANT OPTION;

Run this command:

FLUSH PRIVILEGES;

3. Make sure you have the correct binding on your MySQL config tệp tin.

Locate your MySQL config tệp tin (additional notes at the end). If you want lớn have MySQL listen for connections on more phàn nàn one network find the following line on the config file:

bind-address=127.0.0.1

and comment it using a '#':

#bind-address=127.0.0.1

For production environments you might want lớn use limit the network access (additional notes at the end).

Then restart your MySQL service.

Hope this helps someone having the same issue!


Binding: If you want lớn know more about this I suggest looking at the following solution How lớn bind MySQL server lớn more phàn nàn one IP address. It basically says you can leave MySQL open and limit connections by using a firewall, or natively if you have MySQL version 8.0.13 and above.

MySQL Config File The tệp tin could have different locations depending on your Linux distribution and installation. On my system it was located at '/etc/my.cnf'. Here are other suggested locations:

  • /etc/mysql/mysql.conf.d
  • /etc/mysql/my.cnf

You can also tìm kiếm for the config locations as shown in this website: How lớn find locations of MySQL config files.