CODEWITHSUNDEEP
mysqlauthenticationmysql-8.0
sHaDeoNeR
sHaDeoNeR

Reputation: 647

Mysql 8 remote access

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

Upvotes: 35

Views: 97166

Answers (5)

Patrick S
Patrick S

Reputation: 510

Mysql 8: Following solution worked for me:

  • Change bind-address from 127.0.0.1 to 0.0.0.0
    vim /etc/mysql/mysql.conf.d/mysqld.cnf
  • Sign in to your mysql using root and then:

    CREATE USER 'docker'@'%' IDENTIFIED BY 'your_password_here'; 
    
    GRANT ALL PRIVILEGES ON *.* TO 'docker'@'%';

Upvotes: 1

Shadi Alnamrouti
Shadi Alnamrouti

Reputation: 13248

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

Upvotes: 28

Siddharth G
Siddharth G

Reputation: 199

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

Upvotes: 16

rbz
rbz

Reputation: 1197

  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.

Upvotes: 64

Milan
Milan

Reputation: 759

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

Upvotes: 4

Related Questions