32

I usualy setup correctly MySQL for having remote access.

And currently I got stuck with MySQL 8.

The first thing is that on the mysql.conf.d/mysqld.cnf , I don't have any bind-address line, so I added it by hand (bind-address 0.0.0.0) And I granted access to the user on '%'

When I connected I got the message "Authentication failed"

But it works well on localhost/command line

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
sHaDeoNeR
  • 617
  • 2
  • 8
  • 14
  • I think you have to comment the bind address and give the permission on '%' from root. –  May 28 '18 at 17:56
  • You might be having trouble because MySQL 8 changed the default authentication plugin, and some clients need to be upgraded. An alternative is to configure your MySQL 8 instance to use the traditional mysql_native_password plugin. It's hard to know for sure because you have given no details about how you're connecting (client, language, etc.) Read https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password – Bill Karwin May 28 '18 at 18:19
  • I gave permission on root with Host = '%' I tried to connect with dbForge Studio (a database client) from my computer. I also read the upgrate updates to MySQL 8 I do not see anything dealing with remote access updgrade – sHaDeoNeR May 28 '18 at 18:44
  • ok I updated to old autentication system, and now I got as error : "This server version 8.0.11 is not currently supported." Im checking with Dbforge – sHaDeoNeR May 28 '18 at 18:49

4 Answers4

57
  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/)

  1. Restart the service.

  2. 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';

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

rbz
  • 1,097
  • 13
  • 20
  • 1
    This works just fine, thanks for sharing your response. – jpruiz114 Sep 25 '19 at 15:04
  • 1
    Thanks it worked. But the file name is different depend on the OS. On Linux `my.ini` is actually `my.cnf` located in directory `/etc/mysql/`. – realhu Oct 18 '19 at 17:41
  • @EvanHu It's true. I had forgotten that. I will include in the answer to be more complete and help others. Thanks! – rbz Oct 18 '19 at 18:13
  • 1
    Authentication plugin 'caching_sha2_password' cannot be loaded: The specified module could not be found. https://stackoverflow.com/questions/50169576/mysql-8-0-11-error-connect-to-caching-sha2-password-the-specified-module-could-n – Pažout Nov 22 '19 at 09:15
  • 1
    I think there's a mistake in the answer. The `WITH GRANT OPTION` clause is not applicable to `CREATE USER` statement, but to `GRANT` statement – malloc4k Jul 11 '20 at 21:06
  • @malloc4k Yes, it's at the end of the answer. :) – rbz Jul 13 '20 at 10:17
  • 1
    If you see any error such as ```ERROR 1396```, then just change ```root``` to any other ```remoteuser```, it will work perfect! – LoveCoding Dec 18 '20 at 01:47
  • 1
    perfect answer ..... – Umair Ayub Feb 08 '22 at 10:28
  • thanks for the tip that root users differ from host to host in mysql v8 ... thanks u saved my day – nonap2k Dec 23 '22 at 18:55
24

Remote Access in MySQL 8:

1) Allow access from any host

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

bind-address            = 0.0.0.0

2) Allow the user to access from anywhere:

mysql

GRANT 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';
Shadi Alnamrouti
  • 11,796
  • 4
  • 56
  • 54
14

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, MySQL Workbench 8.0.20

Siddharth G
  • 179
  • 1
  • 8
3

For MySQL 8 open the mysqld.cnf file

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

And modify or add the bind-address option:

[mysqld]
bind-address = 0.0.0.0

Restart the mysql server

sudo service mysql restart
Milan
  • 558
  • 6
  • 13
  • 3
    In manual 8.0, change *bind-address* to *bind_address* [link](https://dev.mysql.com/doc/refman/8.0/en/ipv6-remote-connections.html) – rbz Apr 23 '19 at 14:51
  • 3
    Forget about adding anything additional to the cnf file, don't add "bind-address" or "bind_address". The correct solution is to grant the root user (or another user) remote access since root comes only with localhost access: sudo mysql -u root -p # CREATE USER 'root'@'%' IDENTIFIED BY '1234567890'; # GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'; sudo systemctl restart mysql – jpruiz114 Sep 25 '19 at 15:04
  • GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'; – jonincanada Apr 14 '20 at 22:05