Mysql 8 remote access

1. Delete or comment the bind_address parameter from the my.ini file.

(The file name is different depend on the OS. On Linux my.ini is actually my.cnf located in directory /etc/mysql/)

2. Restart the service.

3. Create the root user (yes, a new user because what exists is 'root@localhost' which is local access only):

   CREATE USER 'root'@'%' IDENTIFIED BY '123';

4. Give the privileges:

   GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';

For DBA user, add WITH GRANT OPTION at the end.

e.g. CREATE USER 'root'@'%' IDENTIFIED BY '123' WITH GRANT OPTION;

Because it does not work CREATE with GRANT?

MySQL 8 can no longer create a user with GRANT, so there is an error in IDENTIFIED BY '123' if you try to use it with GRANT, which is the most common error.

Remote Access in MySQL 8:

1) Allow access from any host

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnfbind-address            = 0.0.0.0

2) Allow the user to access from anywhere:

mysqlGRANT ALL PRIVILEGES ON *.* TO 'root'@'%';UPDATE mysql.user SET host='%' WHERE user='root';

3) Change authentication to password

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'ThePassword';

Had the same problem, Connected to MySQL Workbench and updated the User privilege.

MySQL version : 8.0.20 Community.OS : Windows 10.

1. Open MySQL Workbench --> Server --> Users and Privileges
2. Select the user
3. Change the Limit to Hosts Matching to "%" for accessing from any host.
4. Apply changes.
5. Restart MySQL Service if required. It worked for me with out restarting.

Screen shot,