Troubleshooting MySQL does not allow remote access from the method

Source: Internet
Author: User

There are two major strides in opening MySQL's remote login account:

1. Determine that the firewall on the server does not block port 3306.

MySQL default port is 3306, need to determine the firewall does not block 3306 port, otherwise remote is unable to connect via 3306 port to MySQL.

If you specified a different port when you installed MySQL, open the port number that you specified for MySQL in the firewall.

If you don't know how to set up a firewall on your server, check with your server administrator.

2, add to allow remote connection to MySQL users and authorized.

1) First log in to MySQL with the root account

In the Windows Host, click Start menu, Run, enter "cmd", go to the console, MySQL Bin directory, and enter the following command.

In the Linux host, enter the following command at the command prompt line.

      CODE: [COPY]

    • > MySQL - Uroot - p123456

123456 is the root user's password.

2) Create a remote login user and authorize

      Code: [copy]

    • >  grant all privileges  on discuz .*  to ted @ ' 123.123.123.123 '   identified by   ' 123456 ' ;

The above statement means that all permissions to the Discuz database are granted to the TED user, allowing the TED user to remotely log on to the 123.123.123.123 IP and set the TED user's password to 123456.

All parameters are analyzed below:

All privileges means giving all permissions to the specified user, which can also be replaced by assigning a specific permission, such as: Select,insert,update,delete,create,drop, and so on, with the "," half-width comma separated by the specific permissions.

Discuz.* means that the above permissions are for which table, discuz refers to the database, followed by the * for all tables, it can be inferred that: for all of the database is authorized as "*. *" For all tables, the entire table for a database is authorized as "database name. *", A table for a database is authorized as the database name. Table name.

Ted indicates which user you want to authorize, and this user can be either an existing user or a non-existent user.

123.123.123.123 represents the IP address that allows remote connections, or "%" if you want to limit the IP of the link.

123456 is the user's password.

After executing the above statement, execute the following statement before it can take effect immediately.

CODE: [COPY]

>  flush privileges ;

--------------------------------------------------------------------------------------------------------------- --

Workaround:

1, Change the table method:

It may be that your account is not allowed to log in remotely, only inlocalhost. At this time, justlocalhostcomputer, log in.MySQLafter the change"MySQL"in the database."User"of the table"Host"items, from"localhost"renamed"%"

X:\>mysql-u Root-pvmware

mysql> use MySQL;
mysql> Update user Set host = '% ' where user = ' root ';
Mysql> Select Host, user from user;

Mysql> flush Privileges;

note:mysql> flush privileges; make the changes effective.

2. Authorization Law:

For example, you want to MyUser Use MyPassword Connect from any host to MySQL server.

mysql> Grant all privileges on * * to ' myuser ' @ '% ' identified by ' MyPassword ' with GRANT OPTION;

mysql> GRANT All privileges on * * to ' myuser ' @ ' 192.168.1.3 ' identified by ' MyPassword ' with GRANT OPTION;

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.