How to authorize remote connections in MySQL in Linux

Note: Other machines (IP) cannot connect to the MySQL database through the client without authorization. If you need to remotely connect to MySQL on the Linux system, you must authorize the IP and specific user. Generally the root user is not available to developers. For example, if you want to use the SQLyog graphical management tool on Windows to connect to a MySQL database on Linux, you must first authorize it.

1. Log in to the MySQL database using the root user in the virtual machine

mysql -u root -p

Note: The root user password is usually set to root

2. Use the mysql command to authorize the mysql remote connection service for the root user

mysql> grant all privileges on *.* to 'root'@'%' identified by 'root' with grant option;

Description: This command is used to authorize the root user whose password is root and IP (%) is arbitrary. (%: fuzzy query, all IPs are acceptable, other host IPs can be specified; the 'root' after BY is the password)

3. Write the configuration into the mysql authorization table

mysql> flush privileges;

Modify the user table of the mysql database and change the host item from localhost to %. %This means that any host is allowed to access. If only a certain IP is allowed to access, you can change it to the corresponding IP. For example, you can change localhost to 192.168.1.123, which means that only the IP 192.168.1.123 in the local area network is allowed to remotely access MySQL.

mysql>use mysql; 
mysql>update user set host = '%' where user = 'root';
mysql>select host,user from user;
mysql> flush privileges;

Supplement: Open port 3306 in the firewall

1. Open the firewall configuration file

vi /etc/sysconfig/iptables

2. Add the following line

-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT

3. Restart the firewall

service iptables restart

Note: The statement to open port 3306 must be placed before icmp-host-prohibited

Attachment: Personal configuration

# Firewall configuration written by system-config-firewall
# Manual customization of this file is not recommended.
*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [0:0]
-A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
-A INPUT -p icmp -j ACCEPT
-A INPUT -i lo -j ACCEPT
-A INPUT -i eth0 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 80 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
-A FORWARD -m state --state ESTABLISHED,RELATED -j ACCEPT
-A FORWARD -p icmp -j ACCEPT
-A FORWARD -i lo -j ACCEPT
-A FORWARD -i eth0 -j ACCEPT
-A INPUT -j REJECT --reject-with icmp-host-prohibited
-A FORWARD -j REJECT --reject-with icmp-host-prohibited
COMMIT

The above is the full content of this article. I hope that the content of this article can bring some help to your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.