The mysqlroot user cannot log on remotely.

Source: Internet
Author: User
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 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 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:
Mysql> grant all privileges on *. * TO 'root' @ '%' with grant option;
Mysql> flush privileges;

('Root' @ '%' specifies that the root user can connect to the 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:
+ ----------------------- + ------ +
| 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:
# 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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.