MySQL Database Remote Access How to Open of Two Methods

Download GPL version for installation

MySQL Community Edition(GPL)

Community (GPL) Downloads »

When we use the mysql database, there are times when our program is not on the same machine as the database and we need to access the database remotely. mysql users do not have remote access by default.

Here are two ways to solve this problem.

1, change the table method

Maybe your account is not allowed to log in remotely, only on localhost. At this time, just log into mysql and change the entry "host" in the table "user" in "mysql" database from "localhost" to "%"

mysql -u root -p 
mysql>use mysql; 
mysql>update user set host = '%' where user = 'root'; 
mysql>select host, user from user; 

2. Authorization Law

Run on the machine on which mysql is installed:

1, d: \ mysql \ bin \ > mysql -h localhost -u root

// This should allow access to the MySQL server

2, mysql > GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'WITH GRANT OPTION

// Give any host access to the data

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

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

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

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

3, mysql > FLUSH PRIVILEGES

// The modification takes effect

4, mysql > EXIT

Log out of the MySQL server so that you can log in as root on any other host