- 不允许主机'xxx.xx.xxx.xxx'连接到此 MySQL 服务器
This should be dead simple, but I cannot get it to work for the life of me.
I'm just trying to connect remotely to my MySQL server.
mysql -u root -h localhost -p
works fine, but trying
mysql -u root -h 'any ip address here' -p
fails with the error
ERROR 1130 (00000): Host ''xxx.xx.xxx.xxx'' is not allowed to connect to this MySQL server
mysql.usertable, there is exactly the same entry for user 'root' with host 'localhost' as another with host '%'.
I'm at my wits' end, and have no idea how to proceed. Any ideas are welcome.
Possibly a security precaution. You could try adding a new administrator account:
mysql> CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass'; mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' -> WITH GRANT OPTION; mysql> CREATE USER 'monty'@'%' IDENTIFIED BY 'some_pass'; mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' -> WITH GRANT OPTION;
Although as Pascal and others have noted it's not a great idea to have a user with this kind of access open to any IP. If you need an administrative user, use root, and leave it on localhost. For any other action specify exactly the privileges you need and limit the accessibility of the user as Pascal has suggest below.
From the MySQL FAQ:
If you cannot figure out why you get Access denied, remove from the user table all entries that have Host values containing wildcards (entries that contain '%' or '_' characters). A very common error is to insert a new entry with Host='%' and User='some_user', thinking that this allows you to specify localhost to connect from the same machine. The reason that this does not work is that the default privileges include an entry with Host='localhost' and User=''. Because that entry has a Host value 'localhost' that is more specific than '%', it is used in preference to the new entry when connecting from localhost! The correct procedure is to insert a second entry with Host='localhost' and User='some_user', or to delete the entry with Host='localhost' and User=''. After deleting the entry, remember to issue a FLUSH PRIVILEGES statement to reload the grant tables. See also Section 5.4.4, “Access Control, Stage 1: Connection Verification”.
One has to create a
new MySQL User and assign privileges as below in
Query prompt via phpMyAdmin or command prompt:
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' WITH GRANT OPTION; CREATE USER 'username'@'%' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION; FLUSH PRIVILEGES;
Once done with all four queries, it should connect with
username / password
If this is a recent mysql install, then before changing anything else, try simply to execute this command and then try again:
This alone fixes the issue for me on Ubuntu 16.04, mysql 5.7.20. YMMV.
simple way is to login to phpmyadmin with root account , there goto mysql database and select user table , there edit root account and in host field add % wild card . and then through ssh flush privileges
Well what you can do is just open mysql.cfg file and you have to change Bind-address to this
bind-address = 127.0.0.1
and then Restart mysql and you will able to connect that server to this.
Look this you can have idea form that.
If you modify the grant tables manually (using INSERT, UPDATE, etc.), you should execute
FLUSH PRIVILEGES statement to tell the server to reload the grant tables.
PS: I wouldn't recommend to allow any host to connect for any user (especially not the
root use). If you are using mysql for a client/server application, prefer a subnet address. If you are using mysql with a web server or application server, use specific IPs.
If you happen to be running on Windows; A simple solution is to run the MySQL server instance configuration wizard. It is in your MYSQL group in the start menu. On the second from last screen click the box that says "allow root access from remote machines".
Most of the answers here show you creating users with two host values: one for
localhost, and one for
Please note that except for a built-in localhost user like root, you don't need to do this. If you simply want to make a new user that can log in from anywhere, you can use
CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypassword'; GRANT <whatever privileges are appropriate> ON <relevant tables> TO myuser;
and it will work just fine. (As others have mentioned, it's a terrible idea to grant administrative privileges to a user from any domain.)
- 下载 计算机组成原理实验教程
- 博客 Java 最常见的 200+ 面试题：面试必备
- 下载 winfrom中嵌套html，跟html的交互
- 学院 玩转Python-Python3基础入门
- 学院 程序员的兼职技能课
- 学院 HoloLens2开发入门教程
- 下载 基于VHDL的16位ALU简易设计
- 下载 利用Verilog实现数字秒表（基本逻辑设计分频器练习）
- 学院 Python可以这样学（第一季：Python内功修炼）
- 下载 计算机操作系统 第三版.pdf
- 博客 技术大佬：我去，你写的 switch 语句也太老土了吧
- 学院 Vue.js 2.0之全家桶系列视频课程
- 学院 微信公众平台开发入门
- 学院 150讲轻松搞定Python网络爬虫
- 下载 SEIR课程设计源码与相关城市数据.rar
- 学院 Python数据挖掘简易入门
- 学院 2019 AI开发者大会
- 博客 Java面试题大全（2020版）
- 下载 定量遥感中文版 梁顺林著 范闻捷译
- 下载 GIS程序设计教程 基于ArcGIS Engine的C#开发实例
- 学院 三个项目玩转深度学习（附1G源码）
- 学院 微信小程序开发实战之番茄时钟开发
- 博客 面试了一个 31 岁程序员，让我有所触动，30岁以上的程序员该何去何从？
- 下载 去除异常值matlab程序
- 下载 用verilog HDL语言编写的秒表
- 下载 [透视java——反编译、修补和逆向工程技术]源代码