Detailed process sharing of setting Mysql5.6 to allow external network access

A recent deployment of mysql 5.6 found that the default mysql only allows services to be provided locally, as noted below if several configurations are required.

1. Set MySQL service to allow external network access

Modify the configuration files for mysql, either my. ini (windows) or my. cnf (linux),

Add to the configuration file

[mysqld]
port=3306
bind-address=0.0.0.0

Then restart the mysql service and execute service mysql restart.

2. Set mysql users to support external network access

You need to log in to mysql with root permission, update mysql. user table, and set the Host field of the specified user to%, and the default 1 is generally 127.0. 0.1 or localhost.

1. Log in to the database

mysql -u root -p

Enter password

mysql> use mysql;

2. Query host

mysql> select user,host from user;

3. Create host

If there is no host value of "%", execute the following two sentences:

mysql> update user set host='%' where user='root';
mysql> flush privileges;

4. Authorized users

(1) Any host connects to the mysql server with user root and password mypwd

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'mypwd' WITH GRANT OPTION;
mysql> flush privileges;

(2) Hosts with IP 192.168. 133.128 connect to the mysql server with user myuser and password mypwd

mysql> GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.133.128' IDENTIFIED BY 'mypwd' WITH GRANT OPTION; 
mysql> flush privileges;

"host Field Description"

%  Allow access from any ip Login  
x.x.x.x  Allows access from the specified ip Visit