Correct MySQL remote connection Configuration

This article mainly describes the actual operation steps of MySQL remote connection configuration and the relevant matters worth attention in its actual operations. The following describes the specific solution, I hope this will help you in your future studies.

MySQL Remote Configuration

 
 

  
  
GRANT ALL PRIVILEGES ON *.* TO root@'%' IDENTIFIED BY 'your paaaword'; 
 
 

% Indicates that many machines exist.

Open Port 3306, set exceptions for the firewall, and release port 3306.

The following are online selections

1. ALTER TABLE

It may be that your account is not allowed to log on remotely, but only on localhost. At this time, you only need to log on to the computer of localhost. After logging on to MySQL, change the "host" item in the "user" table in the "MySQL" database to "%" from "localhost"

 
 

  
  
MySQL -u root -pvmwareMySQL>use MySQL;  
  
  
MySQL>update user set host = ‘%’ where user = ‘root’;  
  
  
MySQL>select host, user from user; 
 
 

2. Authorization Law

For example, if you want myuser to use mypassword to connect to the MySQL server from any host.

 
 

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

If you want to allow myuser to connect to the MySQL server from a host whose ip address is 192.168.1.6, and use mypassword as the password

 
 

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

I used the first method. I first found that it could not work. I checked it online and executed one less statement MySQL> FLUSH PRIVILEGES.
Make the modification take effect.

Enable the remote connection permission for the MySQL database:

 
 

  
  
grant all privileges on *.* to 'root' @'%' identified by 'wrx123';  
  
  
flush privileges; 
 
 

When you enable MySQL remote connection, change your HOST field to %, indicating that any address can be logged on with this account, or you can set an IP address.

 
 

  
  
MySQL>GRANT ALL on *.* to 123@192.168.0.% identified by “456″; 
 
 

Note: The firewall of the MySQL host is faulty. Port 3306.

Reference http://hi.baidu.com/zham/blog/item/3a6a8018c0d283b34bedbc40.html

Original article title: MySQL remote connection Configuration

Connection: http://www.cnblogs.com/qqian/archive/2010/03/05/1679119.html