Free Mysql remote access 8

Asked

Viewed 4,362 times

6

I’m testing the new version 8 of Mysql, and encountered some difficulty in releasing remote access (specifically, using the Workbench).

What I found was same question on Soen, and only one commenting with a link, but nothing enlightening.

Even so, I tried some details, but without success:

[mysqld]
default_authentication_plugin=mysql_native_password


ALTER USER 'root'@'localhost'
IDENTIFIED WITH mysql_native_password
BY 'password';

A little more in research, I found some release details for Ipv6, but there is something about Ipv4 in the Official Documentation 8.0 (link), and tried various ways:

[mysqld]
bind_address = *

[mysqld]
bind_address = 0.0.0.0

[mysqld]
bind_address = 192.168.0.0/255.255.255.0

However, so far I have not been able to make the release.

mysql

  • See help: https://www.variphy.com/kb/how-to-enable-connections-to-mysql-from-remote-machine

  • @Reginaldorigo I have tried... in various ways! rs... then I started searching specifically for Mysql 8, that’s where I found these new questions.

1 answer

5


The bind_address can be removed or replaced by * and you must create a user with global access (using wildcard %) or specify the host that can connect to it.

If you use Mysql < 8 or Mariadb 10.X: 

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

If you use Mysql >= 8

CREATE USER 'root'@'%' IDENTIFIED BY 'password';
GRANT ALTER ROUTINE, ALTER, SHOW VIEW, SHOW DATABASES, SELECT, PROCESS, EXECUTE ON . TO 'root'@'%';

  • Funny, it wasn’t supposed to happen. Try installing some mysql client on the server like Heidisql or phpMyAdmin and add it there

  • In the Mysql 8 documentation itself it talks to use this command. This is probably an error by https://dev.mysql.com/doc/refman/8.0/en/ipv6-remote-connections.html

  • The bind need not necessarily be *, it may be omitted as well. Actually, in mysql 8 seems to have to create CREATE, in previous versions and mariadb can be done with Grant, so the code would be: CREATE USER 'root'@'%' IDENTIFIED BY 'password';

  • You can have multiple users with the same name and different hostnames, % is a chord, after the @ comes the hostname it belongs to, you can have root with different hostnames and different passwords. The answer is correct, it is probably some error on your side, including I did the tests.

  • I just re-installed mysql 8 and it worked properly. I removed the bind and surrounded the following queries: CREATE USER 'test'@'%' IDENTIFIED BY '123'; GRANT ALTER ROUTINE, ALTER, SHOW VIEW, SHOW DATABASES, SELECT, PROCESS, EXECUTE ON . TO'test@'%'; As I said earlier, the answer is correct, check if the firewall is disabled or with the correct rules.

  • 1

    I found the error. The problem was not having created the user "root", which is necessary. Creating the user "root", I ran GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';, yours is wrong because it has IDENTIFIED BY 'password'; and Mysql does not accept this direct user creation by GRANT, thus eliminates the IDENTIFIED BY of the sequence. Correct your answer I will mark as solved. But when posting, it is interesting to detail as much as possible, because this way is a good content for research and assistance. Thanks!

  • The answer was corrected, could mark as correct?

Show 2 more comments

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.