Mysql in ubuntu cannot connect to the database remotely.

Source: Internet
Author: User
Tags bind mysql in

MySQL remote access command

Format: mysql-h host address-u user name-p user password


Example:

Yanggang @ host :~ $ Mysql-h192.168.1.11-uroot-p123456
ERROR 2003 (HY000): Can't connect to MySQL server on '192. 168.1.11 '(192)
An error occurred! Unable to connect to the remote MySQL database


MySQL can connect to the database remotely in two ways:

Mysql. sock and TCP/IP, the former is faster than the latter, but only limited to the same local machine, see

The preceding error is caused by no remote access permission.

 


Solution:


1. On the target server (192.168.1.11), modify the mysql my. cnf file:

Sudo vi/etc/mysql/my. cnf


2. Comment out bind-address to shield it from listening only to the local device.

# Bind-address = 127.0.0.1


3. Start the MySQL service and make the modified configuration take effect. For details, see

Sudo restart mysql


After configuring the data access permissions on the server, you still cannot remotely access the MySQL database.

This is because you have not yet granted access permissions to the database or table on the server (GRANT)


4. Log on to the MySQL database on the server

Mysql-u root-p123456


5. Grant permissions to the database top800

Grant all privileges on top800. * to root@192.168.1.22 identified by '20140901 ';


Flush privileges;


6 Now you can remotely access MySQL on the server

Mysql-h192.168.1.11-uroot-p123456

 

By default, only information_schema and top800 can be accessed. top800 is the database that we grant permissions to in step 5.


Solution 1,

View/etc/mysql/my. after cnf finds bind-address, it finds that 127.0.0.1 (bind-address = 127.0.0.1) is configured. Change it to bind-address = 192.168.0.xxx (local ip address), and then check that Port 3306 is enabled, OK. The connection is normal. If it still does not work, refer to the following method.

The MySQL on Ubuntu10.04 is executed.
Root @ ubuntu :~ # Sudo apt-get install mysql
After installing mysql-server

Start mysql
Root @ ubuntu :~ #/Etc/init. d/mysql start

You can connect to the database locally.
Root @ ubuntu :~ # Mysql-uroot-p

Set the remote access permission:
Mysql> grant all PRIVILEGES on *. * to admin @ '%' identified by '123456 & prime ;;
Query OK, 0 rows affected (0.04 sec)

Mysql> use information_schema
Mysql> select * from user_privileges;
The following result is displayed: 'admin' @ '%', indicating that mysql has authorized remote connection.

Accessing the Ubuntu database in windows cannot be connected, but apache installed on Ubuntu can be accessed.
You cannot access port 3306 after using iptalbes.
Root @ ubuntu :~ # Iptables-a input-p tcp-dport 3306-j ACCEPT

View Mysql Network Connection on Ubuntu:
Root @ ubuntu :~ # Netstat-an | grep 3306
Tcp 0 0 127.0.0.1: 3306 0.0.0.0: * LISTEN
The local port is also listening.

Root @ ubuntu :~ # Ufw status
Firewall not loaded
The local firewall is not enabled.

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.