Windows under Navicat Remote link virtual machine linux under MySQL database

Today, I want to use Navicat remote connection to the MySQL database in the virtual machine, has not been connected, searched the internet for a while, found that the reason is the MySQL remote user authorization issue. Here's a little bit about my solution. (I small white)

First, I used navicat to remotely link MySQL in my virtual machine, and the link test failed.

Then view the network port information in the virtual machine:

#netstat-NTPL

　　

After reviewing the status of the firewall, we found that the 3306-port packets were discarded.

#iptables-VNL

　　

　　

Here to clear the rules in the chain in the firewall

#iptables-F

After this, I also remote link mysql, the following error occurred, checked, because MySQL did not allow remote login, so to authorize the remote login MySQL.

　　

　　

Here, let's talk about MySQL login:

Format: mysql-h host address-u user name-P user Password

1. Example 1: Connect to MySQL on this machine
Find the MySQL installation directory, you can generally directly type the command mysql-uroot-p, enter after the prompt you to lose the password, entered the password, directly enter into the MySQL, MySQL prompt is:mysql>

2. Connect to MySQL on the remote host
Assume that the remote host IP is: 10.0.0.1, the user name is root, the password is 123. Type the following command:
Mysql-h10.0.0.1-uroot-p123
(Note: P and 123 (password) between the best without space, the other is the same)

3. Quit MySQL Command
Exit (Enter)

Finally authorize MySQL to allow remote users to log in to access MySQL

Manually increase the number of users who can access the database remotely.

Method one, log in to MySQL locally, change the "host" entry in the "User" table in the "MySQL" Database, and replace "localhost" with "%"

#mysql-U root-proot
Mysql>use MySQL;
Mysql>update User Set host = '% ' where user = ' root ';
Mysql>select host, user from user;

Method two, direct authorization (recommended)

To use the root user from any host, password: youpassword (your root password) to connect to the MySQL server:
# mysql-u Root-proot
Mysql>grant all privileges on * * to ' root ' @ '% ' identified by ' Youpassword ' with GRANT OPTION;

　　After the operation, remember to perform the following command refresh permissions
FLUSH Privileges

at this time again through the Navicat remote link mysql, link test, such as, success.

　　

Windows under Navicat Remote link virtual machine linux under MySQL database