Today, when I remotely log on to my mysql database using php, I cannot log on, and I am using a root user. Now I have checked some information on the Internet and finally got it done, let's take a look at the process and hope to help you.
Today, when I used mysql workbench to connect to mysql on vmware, I was unable to connect. After reading the relevant information on the Internet, I collected some solutions:
1. Authorization method: the root user of mysql can only log on locally by default, but cannot be remotely. If you connect remotely, You need to authorize:
The Code is as follows: |
Copy code |
Mysql> grant all privileges on *. * TO 'root' @ '%' with grant option; Mysql> flush privileges; |
('Root' @ '%' specifies that the root user can connect to the database server on any computer. to specify a specific computer, replace % with a specific IP address)
2. Change table method: similar to the authorization method, the result of the authorization method is to add a record to the user table of the database mysql. The host column value is '%', and the user column value is 'root ', see the following table:
The Code is as follows: |
Copy code |
+ ----------------------- + ------ + | Host | user | + ----------------------- + ------ + | % | Root | | 127.0.0.1 | root | | Localhost | | Localhost | root | | Localhost. localdomain | | Localhost. localdomain | root | + ----------------------- + ------ + |
3. Open Port 3306: the root user's authorization method is emphasized on the Internet. However, if only the root user is authorized and port 3306 is not opened, the connection is still unavailable.
The method for enabling a port is as follows:
Open the/etc/sysconfig/iptables file (the file path varies with the operating system). The file content is as follows:
The Code is as follows: |
Copy code |
# 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-m state -- state NEW-m tcp-p tcp -- dport 22-j ACCEPT -A input-m state -- state NEW-m tcp-p tcp -- dport 3306-j ACCEPT -A input-j REJECT -- reject-with icmp-host-prohibited -A forward-j REJECT -- reject-with icmp-host-prohibited COMMIT |
Add the content in bold Chinese to open port 3306 and restart iptables:
# Service iptables restrat
4. Another method is to modify the mysql configuration file my. the bind-address option in cnf. This option is not available in my configuration file. After this option is added, it does not work and I don't know why.
After repeated tests in the above four areas, it is necessary to authorize root and open port 3306.