MySQL 8 : About GRANT / PRIVILEGES

In previous version of MySQL (before v8), we can grant/create mysql user one and grant it at once to get access by each host (location / 127.0.0.1) base on your mysql config.

In MySQL 8, you no longer can (implicitly) create a user using the GRANT command. You need to do by CREATE USER and also to manage each host you want to GRANT.

Some snippet to use:

Create User by Allowed Hosts

CREATE USER 'root'@'localhost' IDENTIFIED BY 'myrootify';

CREATE USER 'root'@'127.0.0.1' IDENTIFIED BY 'myrootify';

Example to grant all databases

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost';
FLUSH PRIVILEGES;

GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1';
FLUSH PRIVILEGES;

Example to grant a database to user

GRANT ALL PRIVILEGES ON `my-db`.* TO 'root'@'localhost';
FLUSH PRIVILEGES;

GRANT ALL PRIVILEGES ON `my-db`.* TO 'root'@'127.0.0.1';
FLUSH PRIVILEGES;

Login to your MySQL

You can do both as following:

mysql -h localhost -u root -p

mysql -h 127.0.0.1 -u root -p

Published by osify

Find us via: Twitter, Facebook

Leave a comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.