How to grant all privileges to root user in MySQL

Question

Tried

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;

Getting

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'root' WITH GRANT OPTION' at line 1.

Note: The same is working when tried in MySQL 5.7

Also tried

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

Getting

ERROR 1410 (42000): You are not allowed to create a user with GRANT

MySQL (8.0.11.0) username/password is root/root.

Answer 1

This worked for me

mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'password';

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

Answer 2

1) This worked for me. First, create a new user. Example: as 'foo' with pw 'bar.enter code here

> mysql> CREATE USER 'foo'@'localhost' IDENTIFIED WITH mysql_native_password BY 'bar';

2) Replace the below code with a username with 'foo'.

> mysql> GRANT ALL PRIVILEGES ON database_name.* TO'foo'@'localhost';

Note: database_name is the database that you want to have privileges, . means all on all

3) Login as user foo

mysql> mysql -u foo -p

Password: bar

4) Make sure your initial connection from Sequelize is set to foo with pw bar.

Answer 3

I heard the same problem on centOS and this worked for me (version: 8.0.11)

mysql> GRANT ALL PRIVILEGES ON . TO 'root'@'%'

Answer 4

Starting with MySQL 8 you no longer can (implicitly) create a user using the GRANT command. Use CREATE USER instead, followed by the GRANT statement:

mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'root';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;