How to Use SQLyog to remotely connect to MySQL

How to Use SQLyog to remotely connect to MySQL

1. When the SQLyog client remotely connects to MySQL using the root user, the system prompts "Access Denied" and searches the internet for the reason.

It turns out that MySQL does not authorize its remote connection, so you can only link it in the client.

How can this problem be solved?

Original table data

Mysql> use mysql

Mysql> select Host, User, Password from user;
+ --------------- + --------- + ------------------------------------------- +
| Host | User | Password |
+ --------------- + --------- + ------------------------------------------- +
| Localhost | root | * DEA65ABECC50A1FEBD9C0D0D9045E0DDB312F38F |
| Www | root | * DEA65ABECC50A1FEBD9C0D0D9045E0DDB312F38F |
| 127.0.0.1 | root | * DEA65ABECC50A1FEBD9C0D0D9045E0DDB312F38F |
|: 1 | root | * DEA65ABECC50A1FEBD9C0D0D9045E0DDB312F38F |

Create a new user

Format: grant permission on database name. Table name to user @ login host identified by "User Password ";

Grant select, update, insert, delete on *. * to komiles@123.4.56.89 identified by "komiles1234 ";

After the preceding statement is executed, run

Mysql> select Host, User, Password from user;
+ --------------- + --------- + ------------------------------------------- +
| Host | User | Password |
+ --------------- + --------- + ------------------------------------------- +
| Localhost | root | * DEA65ABECC50A1FEBD9C0D0D9045E0DDB312F38F |
| Www | root | * DEA65ABECC50A1FEBD9C0D0D9045E0DDB312F38F |
| 127.0.0.1 | root | * DEA65ABECC50A1FEBD9C0D0D9045E0DDB312F38F |
|: 1 | root | * DEA65ABECC50A1FEBD9C0D0D9045E0DDB312F38F |
| 123.4.56.89 | komiles | * 50663F1ECEAB844897BC272EC1AE7E83F442F649 |
+ --------------- + --------- + ------------------------------------------- +

Now, you can use the user you just created for remote access.

The following is a reprinted http://www.cnblogs.com/smallstone/archive/2010/04/29/1723838.html

2. implement remote connection (authorization)

Changing the value of the host field to % indicates that you can log on to the mysql server as a root user on any client machine. We recommend that you set the value to % during development.
Update user set host = '%' where user = 'root ';

Change the permission to all privileges.

Mysql> use mysql;
Database changed
Mysql> grant all privileges on *. * to root @ '%' identified by "root ";
Query OK, 0 rows affected (0.00 sec)

Mysql> select host, user, password from user;
+ -------------- + ------ + --------------------------------------------- +
| Host | user | password |
+ -------------- + ------ + --------------------------------------------- +
| Localhost | root | * 81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| 192.168.1.12 | root | * 81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| % | Root | * 81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
+ -------------- + ------ + --------------------------------------------- +
3 rows in set (0.00 sec)

In this way, the machine can remotely access MySql on the machine with the username root Password root.

3. remote connection (Table change)

Use mysql;

Update user set host = '%' where user = 'root ';

In this way, you can access Mysql through the root user at the remote end.

Case study of SQLyog client unable to connect to MySQL Server

Install MySQL and SQLyog in CentOS 6.3

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

This article permanently updates the link address: