mysql sets up multiple ways to access a database remotely
- 2020-06-03 08:35:52
Maybe IP is not set properly
Problem: The MySQL permissions are set correctly, but still cannot be accessed remotely. Through telnet it was found that port 3306 was not open.
Analysis: By default, MySQL is only bound to 127.0.0.1, that is, port 3306 is accessible only on the machine.
Solution: Find the MySQL configuration file, search "ES12en-ES13en" and find this 1 line:
bind-address = 127.0.0.1 Add 1 # in front, comment out the 1 line, save and restart MySQL. With remote access, telnet can see that the port is also open.
Example we configure
1. Ensure that ES24en-ES25en is deleted or blocked, otherwise TCP/IP access is not supported
2. Add line ES29en-ES30en = 188.8.131.52 to replace 184.108.40.206 as your server address
After modification, the configuration is:
[mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language = /usr/share/mysql/English bind-address = 220.127.116.11 # skip-networking .... ..
Save and close the configuration file
Restart mysql server: # / etc init d/mysql restart
If the above method does not work we can use mysql command mode to set it
1. Change table method.
Maybe your account is not allowed to log in remotely, only on localhost. At this time, just login to mysql on the COMPUTER of localhost and change the item "host" in the "user" table in the "mysql" database to "%" from "localhost"
CODE:mysql -u root -p123 mysql>use mysql; mysql>update user set host = '%' where user = 'root'; mysql>select host, user from user;
2. Authorization law.
For example, if you want kevin to use mypassword to connect to mysql server from any host.
GRANT ALL PRIVILEGES ON *.* TO 'kevin'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
If you want to allow user kevin to connect to the mysql server from HOST ip 192.168.101.234, use mypassword as password
GRANT ALL PRIVILEGES ON *.* TO 'kevin'@'192.168.101.234' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
Note that the linux firewall is also important
/* Turn off the firewall */ service iptables stop /* Open the firewall */ service iptables start /* Turn off firewall by default */ chkconfig iptables off