MySQL Database for remote connection

Let's take a look at several ways to add remote access to MySQL users from the Internet

A database on MySQL to back up, installed a MySQL GUI tool. Open the "MySQL Navicat" tool, fill in the user name and password but can not log in, always reported this error "error 1130:host ' lijuan-' isn't allowed to connect to this MySQL server". Online search, there are two ways to solve:

Workaround:

1. Change the table method. It may be that your account is not allowed to log on remotely, only on localhost. This time, as long as the computer on the localhost, log in to MySQL, change the "MySQL" Database in the "User" table "host", from "localhost" to "%"

Mysql>use MySQL;

Mysql>update User Set host = '% ' where user = ' root ';

Mysql>select host, user from user;

2. Authorization law. For example, if you want to myuser use MyPassword to connect to a MySQL server from any host.

GRANT all privileges on * * to ' myuser ' @ ' percent ' identified by ' MyPassword ' with GRANT OPTION;

If you want to allow users to connect to the MySQL server from a host myuser IP 192.168.1.3 and use MyPassword as the password

GRANT all privileges on * * to ' myuser ' @ ' 192.168.1.3 ' identified by ' MyPassword ' with GRANT OPTION;

My mysql.user root user's host is localhost, first with the change table method to localhost to "%", or not, still reported 1130 error, and press "from any host connected to MySQL server" method authorization, or reported the same mistake, Finally to their own IP authorization, finally logged on .... The ...

MySQL Error 1045 After the above situation as the customer segment 1045 can be performed on the server as follows

UPDATE user SET Password=password (' 123456 ') where user= ' myuser ';

Flush privileges;//When the user finishes paying permission to refresh the data! Otherwise, the MySQL database won't recognize it.

For example: (You must refer to the above to see the following example)

GRANT all on * * to [e-mail protected] '% ' identified by ' admin ' with GRANT OPTION;

MySQL "192.168.50.85" "admin" "admin";

GRANT all on * * to [e-mail protected] ' localhost ' identified by ' admin ' with GRANT OPTION;

GRANT all privileges on * * to ' myuser ' @ ' percent ' identified by ' 123456 ' with GRANT OPTION;

GRANT all privileges on * * to ' myuser ' @ ' 192.168.50.85 ' identified by ' 123456 ' with GRANT OPTION;

UPDATE user SET Password=password (' 123456 ') where user= ' myuser ';

FLUSH privileges;

MySQL default is no remote control is turned on, you must add remote access to the user.

MySQL default is no remote control is turned on, you must add remote access to the user. g&00 x$ Y

0VE}0 (FQ

If it is an installation version, enter it from the console provided by MySQL. 1s+< (0r/

Log in with the root user and then: Q k C jgS0

Grant all privileges the user name created by * to ("%" identified by "password"; O! q*) ' Xuq

Flush privileges; * Refresh the content just now *)/Z (5 3X

; (Q:ly \v

Format: Grant permission on the database name. Table name to user @ Login host identified by "User password";. =h J qi[

The IP address (or hostname) of the client that accesses MySQL is followed by any client, and if localhost is populated for local access (that user cannot remotely access the MySQL database). U <t 1O 6

LP 8 Z Rc

You can also set whether you have remote access permissions for existing users. As follows: \ch3 X x#

Use MySQL; o$ ' >: >n E

Update db Set host = '% ' where user = ' user name '; (If written as host=localhost, this user does not have remote access permission) B Gnl:r *

FLUSH privileges; Zn 6en;&?

3 pj_2b=

View results, execution: $4b 5-u

Use MySQL; ) {[' 66!

Select Host,user,password from user; J): H\b:j_m

I OZ 0 "H

+--------------+--------+---------------+ t75 JSDQ

| Host | User | Password | XKL di{U

+--------------+--------+-----------------+ b*1q< cx5b

| localhost | | | U 8RZq (f

| % | John | 123456 | R ' Al B

| localhost | Root | | H FD ZE

+--------------+--------+---------------+ pd7= 8 8

Arj~vs "+

Summarize:? Av L ' B A

In fact, MySQL default has two databases for MySQL and test, and the MySQL user information is placed in the database MySQL related table GB:HJ/

You can also use GUI tools such as Navicat (recommended) to manage users. HUD?} U BB5

Update user set host= '% ' where host= ' localhost '

Use of the Web server and MySQL server are not installed on the same machine. MySQL needs to be accessed via MySQL's remote account.

First, the syntax:

Grant [Permissions] on [database name]. []@[] to [' User lives ' IP address of ' Web server '] identified by [' Password '];

Instance:

Grant all on lportal.* to ' root ' @ ' 192.168.1.21 identified by ' root ';

Grant Select,update,insert,delete on lportal.* to ' root ' @ ' 192.168.1.21 identified by ' root ';

Reprint Address: Http://blog.163.com/[email protected]/blog/static/39026191200972745632454/

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

MySQL Database for remote connection