MySQL database allows remote connections

1. Verify that the initial remote connection is allowed

Since this virtual machine IP is 192.168.2.120, we do

Mysql-h 192.168.20.120-p 3306-u root-proot (Note:-proot,root refers to the password of the root account),

Can get results connected not on.

If we do not use remote connection, can be connected, the command is: Mysql-u root-proot.

2. Connect to the database

Execute commands with MySQL;

Use the MySQL database.

and view the user table information, the execution command is: Select Host,user from user.

The execution results are as shown

3. Updating or adding user records

The above steps can be used to get the values in the data table user, the following we update the table to allow remote access,

The execution commands are:

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

(This command means to update the host value of the first record in the user table to root) after the update is complete, perform the previous query command to verify

If you do not want to modify the original record, then you can insert a new record, but if you use INSERT into user (Host,user) VALUES (' 111,222,3,4 ', ' root ');

That's not the way it is,

Tip: Rror 1364 (HY000): Field ' ssl_cipher ' doesn ' t has a default value

The correct method is as follows:

GRANT USAGE on *. User01 ' @ ' localhost ' identified by ' 123456 ' with GRANT OPTION;

User: User01, Password: 123456, this adds a new user, will not make the above error.

In this way, you can skip step 5.

4. Perform a forced refresh

command flush privileges;

When the execution is complete, close the database connection.

5. Change the my.cnf file in the MySQL installation directory.

General default path under/etc/mysql/, find bind-address = 127.0.0.1 This line, you can delete it, note or change 127.0.0.1 to 0.0.0.0, save after the modification is complete.

6. Restart MySQL

The command is for service MySQL restart. For a remote login test, you can see that the remote connection has been allowed.

MySQL database allows remote connections