Mysql remote connection: ERROR 1130 (HY000): Host & #39 ;*. *. *. * & #39; is not allowed to connect to this MySQL server solution, mysqlconnect

Source: Internet
Author: User

Mysql remote connection: ERROR 1130 (HY000): Host '*. *' is not allowed to connect to this MySQL server, mysqlconnect

After MySQL is installed, ERROR 1130 (HY000): host' 192 occurs when you remotely connect to the database. 168.0.1 'is not allowed to connect to this MySQL server prompts that the database cannot be remotely connected. The reason may be that the host in the user table in the mysql database is localhost. So, I tried to change this value to the ip address of my server, and it was really easy to use, however, if you use the mysql-u root-p command, you cannot connect to the database. You need to use the mysql-h Server ip address-u root-p because of the default host value connecting to the mysql database user table, the default host of this command is localhost, and it cannot be connected.

Procedure:

After connecting to mysql with localhost,
Use mysql;
Update user set host = '192. 456.789.254 '; (IP Address: the IP address of the Local Machine on which you want to remotely connect to the database)
\ Q;

Exit mysql and restart mysql.

Other solutions

2.ALTER TABLE. It may be that your account is not allowed to log on remotely, but only on localhost. At this time, you only need to log in to mysql on the computer of localhost, and change the "host" entry in the "user" table in the "mysql" database to "%" from "localhost"
Mysql-u root-pvmware
Mysql> use mysql;
Mysql> update user set host = '%' where user = 'root ';
Mysql> flush privileges;
Mysql> select host, user from user;

3.Authorization Law. For example, if you want myuser to use mypassword to connect to the mysql server from any host.
Grant all privileges on *. * TO 'myuser' @ '%' identified by 'mypassword' with grant option;
If you want to allow myuser to connect to the mysql server from a host whose ip address is 192.168.1.3, and use mypassword as the password
Grant all privileges on *. * TO 'myuser' @ '192. 168.0.1 'identified BY 'mypassword' with grant option;


**************************************** *******************************
PhpMyAdmin says it is a problem with the user name and password, which is strange. The root user name and password are certainly correct, and there is no problem with the command line connection. Check the configuration file carefully. So I searched and found the solution.

Log on to the MYSQL server as the root user and run
Mysql> setPasswordForThe username you want to use @ "localhost" = old_password ('password of this user ');

The reason is that the new password verification mechanism is used in the mysql server version you are using. This requires the client version to be later than 4.0. The original password function is changed to old_password ();, in this way, the password generated by Using password () can be solved in the old version.


Mysql ERROR 1130, detailed description: ERROR 1130 Host 'localhost' is not allowed to connect to this MySQL

Windows or linux?
Error Code 1045 Access denied for user 'root' @ 'localhost' (using password: YES)

The solution is to reset the root user password. The procedure is as follows on Windows:

1. log on to the system as a system administrator;

2. If the MySQL server is running, stop it.

If the server runs as a Windows Service, go to Service Manager: Start Menu-> Control Panel-> Administrative Tools-> services

If the server is not running as a service, you may need to use the task manager to force it to stop.

3. Create a text file and place the following commands in a single line:

Set password for 'root' @ 'localhost' = PASSWORD ('mynewpassword ');

Save the file with any name. In this example, the file is C: \ mysql-init.txt.

4. Enter the doscommand prompt: Start Menu-> Run-> cmd

Assume that you have installed MySQL to C: \ mysql. If you install MySQL in another location, adjust the following commands.

Run the following command at the doscommand prompt:

C: \> C: \ mysql \ bin \ mysqld-nt -- init-file = C: \ mysql-init.txt

When the server is started, run the "-- init-file" option (function: Read SQL commands from the specified file at startup) to change the root user password. When the server is successfully started, delete the C: \ mysql-init.txt.

5. Stop the MySQL server and restart it in normal mode. If you run the server as a service, you should start it from the Windows service window. If the server is started manually, the command can be used as usual.

Appendix:

Other methods

Method 1: #/etc/init. d/mysql stop # mysqld_safe -- user = mysql -- skip-grant-tables -- skip-networking & # mysql-u root mysql> UPDATE user SET Password = PASSWORD ('newpassword ') where USER = 'root'; mysql> flush privileges; mysql> quit #/etc/init. d/mysql restart # mysql-uroot-p Enter password: <Enter the new password newpassword> mysql> Method 2: directly use/etc/mysql/debian. the user name and password provided in the [client] section of the cnf file: # mysql-udebian-sys-maint-p Enter password: <enter the Password in the [client] section> mysql> UPDATE user set password = Password ('newpassword') where USER = 'root'; mysql> FLUSH pridrop ...... remaining full text>

That is why mysql cannot be connected.

There are several tables ending with "_ PRIVILEGES" in the Database "information_schema". They are for management permissions (including logon to the host, which is unclear ).
For example: USER_PRIVILEGES table:
| 'Root' @ 'localhost' | NULL | SELECT | YES |
In the first list, the root user is under localhost. Let's take a look at this table. If the table does not contain @ 'localhost', it means you cannot log on with localhost.
For details, refer to other

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

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: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.