open mysql root remote

open mysql root remote

we can not remote login root default after install mysql.

now we want to user root to login mysql.

first localhost login use root.

mysql -uroot -p

Here need to put in password of root.

mysql> select user, authentication_string, host from user;

output like this:

+------------------+-------------------------------------------+-----------+
| user             | authentication_string                     | host      |
+------------------+-------------------------------------------+-----------+
| root             | *DC0F8E9026263BF211622F271A305C11569914D7 | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| debian-sys-maint | *89A7DE6180CE2CB0B4FD999719669BDCB08290F4 | localhost |
+------------------+-------------------------------------------+-----------+
4 rows in set (0.00 sec)

then add a new user:

mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'your_passwd';

output

Query OK, 0 rows affected, 1 warning (0.01 sec)

retry a query:

mysql> select user, authentication_string, host from user;

output

+------------------+-------------------------------------------+-----------+
| user             | authentication_string                     | host      |
+------------------+-------------------------------------------+-----------+
| root             | *DC0F8E9026263BF211622F271A305C11569914D7 | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| debian-sys-maint | *89A7DE6180CE2CB0B4FD999719669BDCB08290F4 | localhost |
| root             | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | %         |
+------------------+-------------------------------------------+-----------+

we can see a new user root %

then we make it work.

mysql> flush privileges;

output

Query OK, 0 rows affected (0.00 sec)

when we use a mysql client to connect a remote server, often get this error

ERROR 2003 (HY000): Can't connect to MySQL server on 'XXX.XXX.XXX.XXX' (111)

this is because a key named bind-address in the mysql config file.

the config file is under /etc/mysql

maybe in the file my.cnf. or maybe not.
you should find it out.
I find it under path

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

open the config file
you will find it like this:

#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address           = 127.0.0.1

we just add #

#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address           = 127.0.0.1

restart your mysql

service mysql restart

then you can try remote connect to your mysql server.

mysql -h xxx.xxx.xxx.xxx -uroot -p

It could work!
good luck！