How to grant all privileges to root user in MySQL 8.0

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 'PASSWORD';mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;mysql> FLUSH PRIVILEGES;

Caution about the security risks about WITH GRANT OPTION, see:

• Grant all privileges on database

I see a lot of (wrong) answers, it is just as simple as this:

USE mysql;CREATE USER 'user'@'localhost' IDENTIFIED BY 'P@ssW0rd';GRANT ALL ON *.* TO 'user'@'localhost';FLUSH PRIVILEGES;

Note: instead of a self-created user you can use root to connect to the database. However, using the default root account to let an application connect to the database is not the preferred way.

Alternative privileges (be careful and remember the least-privilege principle):

-- Grant user permissions to all tables in my_database from localhost --GRANT ALL ON my_database.* TO 'user'@'localhost';-- Grant user permissions to my_table in my_database from localhost --GRANT ALL ON my_database.my_table TO 'user'@'localhost';-- Grant user permissions to all tables and databases from all hosts --GRANT ALL ON *.* TO 'user'@'*';

If you would somehow run into the following error:

ERROR 1130 (HY000): Host ‘1.2.3.4’ is not allowed to connect to thisMySQL server

You need add/change the following two lines in /etc/mysql/my.cnf and restart mysql:

bind-address           = 0.0.0.0skip-networking

1) This worked for me. First, create a new user. Example: User foo with password bar

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