How to remotely log on to MySQL _ MySQL

Source: Internet
Author: User
Solution 1:

Log on to the MySQL Server using the MySQL Adminstrator GUI Tool, but the Server returns the error message Host ' 'is not allowed to connect to this

MySQL server

This is because of permission issues. The solution is as follows:

Shell> mysql -- user = root-p

Enter password

Mysql> use mysqlmysql> grant select, INSERT, UPDATE, delete on [db_name]. * TO [username] @ [ipadd] identified by '[password]'; [username]: user code for remote login [db_name]: indicates the Name of the database to be opened to the user: [password]: user password for remote logon [ipadd]: IP address or DNS Name after IP lookup. In this example, enter ' ', enclose a letter with quotation marks (') (in fact, it is executed on the remote server, and fill in the IP address of the local host .)

If you want to open all permissions, execute:

Mysql> update user set select_priv = 'y', Insert_priv = 'y', Update_priv = 'y', delete_priv = 'y', Create_priv = 'y ', drop_priv = 'y', Reload_priv = 'y', shutdown_priv = 'y', Process_priv = 'y', File_priv = 'y', Grant_priv = 'y ', references_priv = 'y', Index_priv = 'y', Alter_priv = 'y', Show_db_priv = 'y', Super_priv = 'y', Create_tmp_table_priv = 'y ', lock_tables_priv = 'y', Execute_priv = 'y', Repl_slave_priv = 'y', Repl_client_priv = 'y' where user = '[username]';

Method 2:

How to solve the connection between the client and the server (mysql): xxx. xxx is not allowed to connect to this mysql serv

1. go to mysql and create a new user named xuys.

Format: grant permission on database name. table name user @ login host identified by "user password ";

Grant select, update, insert, delete on *. * to identified by "xuys1234 ";

2. view the result and execute

Use mysql; select host, user, password from user;
You can see that the created xuys user already exists in the user table. The host field indicates the host to be logged on. The value can be an IP address or a host name. changing the value of the host field to % indicates that the host can be logged on to the mysql server as an xuys user on any client, we recommend that you set it to % during development.

3../mysqld_safe -- user-root &

Remember: any modification to the authorization table must be reload again, that is, step 1.

If you still cannot connect from the client after the above three steps, perform the following operations to insert a record in the db table of the mysql database:

Use mysql; insert into db values ('2017. 168.88.234 ',' % ', 'xuys', 'y ', 'Y', 'y', 'y'); update db set host = '%' where user = 'xuys ';

Repeat steps 2nd and 3 above.

Method 3:

Add fbysss to connect phpMyAdmin to a remote user:

1. log on to the remote Mysql server with the root account

Grant select, update, insert, delete on *. * to sss@ identified by "sss"; update user set host = '%' where user = 'SS ';
Exit mysql and run the command in shell.

# Mysqladmin-u root-p [password] reload # mysqladmin-u root-p [password] shutdown #/etc/rc. d/init. d/mysqld start

2. modify the config. inc. php file in the phpMyAdmin directory and find

$ Cfg ['servers'] [$ I] ['host'] to the remote server address

$ Cfg ['servers'] [$ I] ['user'] to sss

$ Cfg ['servers'] [$ I] ['password'] change to sss password

Note that grant all privilege cannot grant the "grant" permission to the user. to add the permission, you can directly use mysql in mysql; update user set Grant_priv = 'y' to meet the requirements.

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: 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.