Workarounds for MySQL failure on a remote connection to a Linux virtual machine

Today in the virtual machine Ubuntu on the night of the MySQL, and then try to connect with Java, did not succeed for a long time, but the students with a good Debian on the connection is successful, that is, I have a problem with the configuration.

Tossing for a long time, and finally by understanding the anomaly information to roughly guess.

A remote connection is the IP and port of the host where MySQL is entered to determine the logical address of the host, and then the user and password to determine which user to log into. Like what

String url = "Jdbc:mysql://192.168.183.134:3306/mysql";

is to first in the network layer connected to the IP 192.168.183.134 host, and then to connect the host's specific port 3306 (transport Layer), and then the transport layer reached a connection, in the application layer with the account password login, access to the MySQL database

Connection conn = drivermanager.getconnection (URL, user, password);

At first I doubt whether the port Setup problem, and then add the port=3306 on the/etc/mysql/my.cnf, the result is still not connected, the display port denied access.

Then use the command $NETSTAT-APN to find a message for Port 3306

TCP 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN

Look carefully, the IP here is 127.0.0.1, combined with the experience of socket programming, generally to receive messages sent by any host, the IP will be set to 0.0.0.0, and 127.0.0.1 (loopback address) means that only local access? Then open the classmate's Debian to view his MySQL occupied port, as I thought.

TCP 0 0 0.0.0.0:21 0.0.0.0:* LISTEN

After a search, it came to the conclusion that MySQL is provided for local access by default, and additional settings are required to turn on remote access. From a safety point of view it is also reasonable.

Set the method or modify the configuration file/etc/mysql/my.cnf

[Client]default-character-set=utf8[mysqld]default-storage-engine=innodbcharacter- set-server=utf8collation-server=utf8_general_ciport=3306bind-address=  0.0. 0.0

UTF8 related to the previous local input of Chinese character data when the insertion failed, man and varchar is not compatible, in MySQL mode when entering status View state

Server characterset:latin1
Db characterset:latin1
Client Characterset:utf8
Conn. Characterset:utf8

After this modification, the first 2 character sets are all UTF8 and support Chinese. Other modifications as I said, the binding IP is 0.0.0.0, which is the connection to the host receiving any address, port binding 3306.

But still can not access, but the error message has changed, this time is very clear, is to show that the user does not have connection rights, that is, users can only connect locally, still search data after the solution is as follows

1. New user remote connection MySQL database
Mysql> Grant All on * * to [e-mail protected] '% ' identified by ' java123 ' with GRANT option;
Query OK, 0 rows affected, 1 Warning (0.00 sec)
mysql> flush Privileges;
Query OK, 0 rows Affected (0.00 sec)
2. Support for root user allows remote connection to MySQL database
Mysql> Grant All on * * to ' root ' @ '% ' identified by ' cplusplus ' with GRANT option;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush Privileges;
Query OK, 0 rows Affected (0.00 sec)

Reference Blog http://www.cnblogs.com/linjiqin/p/5270938.html

In fact, the blog also solved my previous problems.

Workarounds for MySQL failure on a remote connection to a Linux virtual machine