Solve the Problem of remote connection to the MySQL database.
Problem description
Out of interest, I have installed MySQL servers and clients on Windows and Ubuntu systems. There are some preparation information:
- Windows IP Address: 192.168.5.196
- IP address of Ubuntu: 192.168.5.228
Now I am trying to remotely connect to the Ubuntu terminal host on Windows.
First, configure the MySQL access permission on the remote Ubuntu host. The procedure is as follows:
Mysql> grant all privileges on.To 'longlong' @ '%' identified by '201312 ';
Now, query the MySQL permission information on the Ubuntu host as follows:
Now, remotely log on to the MySQL database of the Ubuntu host on Windows. The result is:
This error is reported all the time!
Solution
After csdn ask raised this question, I tried some suggestions from some netizens, for example, you still cannot solve this problem by disabling mysql-u xx-p xxxx-h xxxx or Windows Firewall and the ufw disable command.
Later, I learned that MySQL on Ubuntu configures the entire mysql through the/etc/MySQL/my. cnf configuration file. Excerpt 2:
[Mysqld]
Port = 3306
Basedir =/usr
Datadir =/var/lib/mysql
Tmpdir =/tmp
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
Note that there is a configuration command in the preceding configuration file:
Bind-address = 127.0.0.1
This command indicates that the Ubuntu host listens locally and only processes local connection requests.
You can also run the netstat command to view the port listening information:
Shell> netstat-anp | grep 3306
Output> tcp 127.0.0.1: 3306 LISTEN
Now, comment out the bind-address statement of my. cnf and restart the MySQL service:
Shell> sudo service mysql restart
Output> mysql stop/waiting mysql start/running, process 25765
Now, if we remotely connect to the MySQL database on Ubuntu on Windows, no error will be reported.
Run the netstat command again:
Shell> netstat-anp | grep 3306
Output> tcp 0.0.0.0: 3306 LISTEN
At this time, we found that port 3306 no longer only listens locally, but also supports remote connection requests.