How to enable MySQL remote connection

For security reasons, MySql-Server only allows the local machine (localhost, 127.0.0.1) to connect and access. This is not a problem for the website architecture where the Web-Server and MySql-Server are on the same server.

However, as website traffic increases, the later server architecture may place the Web-Server and MySql-Server on separate servers in order to achieve greater performance improvements. At this time, MySql-Server must be modified to allow the Web-Server to connect remotely.

With the remote connection enabled, you don't have to log in to the server every time for the subsequent management and maintenance of the database. You can use a graphical interface (such as phpMyAdmin) for remote management.

To open a remote connection to MySql-Server, you need to authorize access to the IP address and server port:

1. Log in to Mysql-Server on the server and connect to the local mysql (only local connections are allowed by default, and remote access to phpMyAdmin is actually equivalent to local connections):

[root@iZq2mvq6snkcniZ ~]# mysql -uroot -p123456

123456 is the password, please use your database password. Here we use command line login as an example, but you can also use a graphical interface.

2. Modify the Mysql-Server user configuration:

MySQL [(none)]>use mysql;
#View existing users, passwords and hosts allowed to connect MySQL [mysql]> SELECT User, Password, Host FROM user;  
+------+-------------------------------------------+-----------+
| User | Password | Host |
+------+-------------------------------------------+-----------+
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | localhost |
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 127.0.0.1 |
+------+-------------------------------------------+-----------+
2 rows in set (0.00 sec)

#Setting it to be accessible to all IPs is dangerous and not recommended.
MySQL [mysql]> UPDATE user SET Host=@'%' where user='root' AND Host='localhost' LIMIT 1;  
MySQL [mysql]> flush privileges;
#View the existing users, passwords and hosts allowed to connect again MySQL [mysql]> SELECT User, Password, Host FROM user;  
+------+-------------------------------------------+-----------+
| User | Password | Host |
+------+-------------------------------------------+-----------+
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | |
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 127.0.0.1 |
+------+-------------------------------------------+-----------+

3. Finally, please note that on Linux servers, port 3306 is closed by default and remote access is not allowed. Therefore, you need to open port 3306 for MySQL remote connection. The port opening method is:

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

At this point, you can actually access the database remotely. An example of remote access is:

Other command references:

Next we configure the root user: the password is empty, and only connections from 192.168.1.100 are allowed.

The above is the detailed content of how to enable MySQL remote connection. For more information about enabling MySQL remote connection, please pay attention to other related articles on 123WORDPRESS.COM!