Remote access to MySQL database in Ubuntu _ MySQL

Remote access to MySQL database in Ubuntu

BitsCN.com

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.

Knowledge Development:

1. delete the user's access permissions to the database on the server:

Revoke all privileges on top800. * from root@192.168.1.22 identified by '20140901 ';

2. delete the user root on the server:

Delete from user where user = 'hangzhou ';

3. the modification takes effect:

Flush privileges;

4. query users on the server:

From Sunboy_2050

BitsCN.com