Mysql 8 remote access

mysql authentication mysql-8.0
sHaDeoNeR picture sHaDeoNeR · May 28, 2018 · Viewed 40.2k times · Source

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

Answer

rbz picture rbz · Apr 25, 2019
  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.

Related questions

Node.js can't authenticate to MySQL 8.0

I have a Node.js program that connects to a local MySQL database with the root account (this is not a production setup). This is the code that creates the connection: const mysql = require('mysql'); const dbConn = mysql.createConnection({ host: …

Read More
MySQL root access from all hosts

I've installed MySQL server on a remote Ubuntu machine. The root user is defined in the mysql.user table this way: mysql> SELECT host, user, password FROM user WHERE user = 'root'; +------------------+------+-------------------------------------------+ | host | user | password | +------------------+------+…

Read More
Cannot connect to MySQL 4.1+ using old authentication

I'm trying to connect to a mySQL database at http://bluesql.net, but when I try to connect, it gives this error: Connect Error (2000) mysqlnd cannot connect to MySQL 4.1+ using old authentication I've looked into this, and it has to …

Read More