For security reasons, mysql-server only allows local (localhost, 127.0.0.1) connection access, which is fine for Web site architectures where both web-server and mysql-server are on the same Server.

However, with the increase of website traffic, the Server architecture may put web-server and mysql-server on separate servers in the later stage, so as to get greater performance improvement. At this time, mysql-server should be modified to allow web-server to make remote connection.

Open the remote connection, the database management operation and maintenance does not need to be logged into the server every time, as long as the graphical interface (such as phpMyAdmin) can be remote management.

Enable mysql-server remote connection by authorized access IP address and Server port:

Mysql > connect to local Mysql Server (default: local connection only, phpMyAdmin remote access is equivalent to local connection)

[root@iZq2mvq6snkcniZ ~]# mysql -uroot -p123456      
Copy the code

123456 is the password, please use your database password. This section uses the CLI as an example or the GRAPHICAL user interface (GUI) as an example.

2. Modify user configuration of mysql-server:

MySQL [(none)]>use mysql; MySQL [MySQL]> SELECT User, Password, Host FROM User; +------+-------------------------------------------+-----------+ | User | Password | Host | +------+-------------------------------------------+-----------+ | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | Localhost | | root | * 6 bb4837eb74329105ee4568dda7dc67ed2ca2ad9 | 127.0.0.1 | + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + 2 rows in the set (0.00 SEC) # set to all IP access, more dangerous, it is not recommended. MySQL [mysql]> UPDATE user SET Host=@'%' where user='root' AND Host='localhost' LIMIT 1; MySQL [mysql]> flush privileges; MySQL [MySQL]> SELECT User, Password, Host FROM User; +------+-------------------------------------------+-----------+ | User | Password | Host | +------+-------------------------------------------+-----------+ | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | | root | * 6 bb4837eb74329105ee4568dda7dc67ed2ca2ad9 | 127.0.0.1 | +------+-------------------------------------------+-----------+Copy the code

3. Finally, note that on a Linux server, port 3306 is disabled by default and does not allow remote access. Open port 3306 for mysql remote connection

[root@iZq2mvq6snkcniZ ~]# iptables -I INPUT 4 -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT
[root@iZq2mvq6snkcniZ ~]# service iptables save
Copy the code

At this point, you can really remotely access the database, remote access example:

Other command reference:

Let’s configure the root user: the password is null, and only connections from 192.168.1.100 are allowed.

Mysql > GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.100' IDENTIFIED BY '' WITH GRANT OPTION; @'192.168.1.100' @' % '@'192.168.1.100' @' %' @' % '@'192.168.1.100' @' %' Mysql > UPDATE user SET Host='192.168.1.100' WHERE user ='root' AND Host='localhost' LIMIT 1;Copy the code

This article is originally published by Websoft9.