MySQL Remote Access fails.

Source: Internet
Author: User

How can I not connect to MySQL on this server when I configure a server today. Remote logon is normal, and access to MySQL on this machine is normal. Error prompt SQL
Error (2003): Can't connect to MySQL server on '**' (IP) (10061) (** at that time, it refers to the remote IP address that initiates the access request ). The solution is as follows:

Note: I use the ubuntu10.04 operating system and use the sudo apt-GET command to install MySQL by default.

1. First, try to grant the account the permission to access from the remote IP address in MySQL.

After logging in, run the following command to grant remote access to the client ..

grant all on *.* to 'remote'@'172.16.21.39' identified by 'password';

Here, remote indicates the user name. '192. 16.21.39 'is the IIP address that requires access permission. Password indicates the remote login password.

The command is successfully executed but MySQL cannot be accessed remotely. Perform the following steps:

You may need to log on as root to execute this command.

2. Cancel MySQL local binding

That is to say, you can cancel the default local access restriction for MySQL.

Edit/etc/MySQL/My. CNF (MySQL is my. CNF by default in Ubuntu, otherwise it may not be the file name). Find

# Instead of skip-networking the default is now to listen only on# localhost which is more compatible and is not less secure.bind-address = 127.0.0.1

Comment out "bind-address = 127.0.0.1" (add # above). If necessary, you can change 127.0.0.1 to a specific IP address or 0.0.0.0.

On the [mysqld] side, add

Skip-name-resolve

After the modification, You need to restart the MySQL service to take effect. Command:

Linux:

Sudo/etc/init. d/MySQL restart

Or service mysqld restart

Windows:

Net stop MySQL

Net start MySQL

At this point, you can remotely access MySQL, but you may find that you can access but cannot see the database. This is because you have not yet granted access to a specific database.

3. Grant existing database authorization

If user test often accesses the webdb database on the client with the remote IP address 173.20.213.208, the Command executed on the server side should be as follows ):

mysql> use mysqlmysql> update db set Host='173.255.213.208' where Db='webdb';mysql> update user set Host='173.255.213.208' where user='test';

 
grant all privileges on rendezvous.* to 'username'@'%' identified by 'password'; flush privileges;

So far, if there is no accident, you should be able to remotely access the database.

There is a small detail that requires attention. If your server is installed with a firewall, You need to enable the 3306 access. Otherwise, the connection will fail.

Related Article

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.