Shulou(Shulou.com)06/01 Report--

By default, mysql accounts do not allow remote login and can only log in from localhost. This article provides two ways to set up mysql to connect through a remote host.

Modify the data of the user table

After logging in to mysql, change the host entry in the user table in the mysql database and rename localhost%

Mysql > update user set host ='% 'where user =' root';mysql > select host, user from user

Modify authorization

For example, if you want myuser to connect to the mysql server from any host using mypassword (password).

Mysql > GRANT ALL PRIVILEGES ON. TO 'myuser'@'%'IDENTIFIED BY' mypassword' WITH GRANT OPTION; mysql > FLUSH PRIVILEGES

For example, if you want to allow the user myuser to connect to the mysql server from the host with ip 192.168.1.6, and use mypassword as the password

Mysql > GRANT ALL PRIVILEGES ON *. * TO 'myuser'@'192.168.1.3'IDENTIFIED BY' mypassword' WITH GRANT OPTION;mysql > FLUSH PRIVILEGES

Check the firewall

If you can't connect, it could be the pot of the firewall.

# check firewalld status via systemctl status firewalld # FirewallD is not runningsystemctl unmask firewalld systemctl start firewalld# check firewalld status via systemctl status firewalld again, and running is enabled. # execute firewall-cmd-- permanent-- zone=public-- add-port=3306/tcp again, prompting success, indicating that the setting is successful, so you can continue with the following settings. Firewall-cmd-- reloadsystemctl stop firewalld.service # turn off the firewall

Modify mysql configuration file

If the firewall still doesn't work, use the last resort to modify the configuration file (the blogger used the last resort to solve the problem of remote access)

The location of the my.cnf configuration file, usually in / etc/my.cnf, and some versions in / etc/mysql/my.cnf

In the configuration file, add 2 lines of code

[mysqld] bind-address = 0.0.0.0

Restart the service, remote access, and find that it can be accessed normally.

P.s.

Mysql8.0 version configuration method is different. After configuring according to the above method, Navicat can connect normally, but nodejs code connection error is reported.

Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client

Solution (change the encryption rule to normal mode, the default is strict encryption mode):

Mysql > ALTER USER 'root'@'%' IDENTIFIED BY' xxxxxxxx' PASSWORD EXPIRE NEVERmysql > ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY' xxxxxxxx';mysql > FLUSH PRIVILEGES

Test the database connection with nodejs, and you should be ready to use it

Mysql5.5 allows remote access

1. Start the cmd command for mysql

two。 Use the "use mysql" command, select the database to use, modify the basic information of the remote connection, and save it in the mysql database, so use the mysql database.

3. Use the "GRANT ALL PRIVILEGES ON *. * TO 'root'@'%' IDENTIFIED BY' root' WITH GRANT OPTION;" command to change the settings for a remote connection.

4. Use the "flush privileges;" command to refresh the permissions you just modified to take effect.

5. Use "select host,user from user;" to see if the modification was successful.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.