name - mysql error 1130 host is not allowed to connect windows
Host 'xxx.xx.xxx.xxx' is not allowed to connect to this MySQL server (14)
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.user table, 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.
All of the answers here didn't work in my case so I guest this may help other users in the future. This can also be a problem in our code, not just in MySQL alone.
If you are using
Instead of this code:
Dim server As String = My.Settings.DB_Server Dim username As String = My.Settings.DB_Username Dim password As String = My.Settings.DB_Password Dim database As String = My.Settings.DB_Database MysqlConn.ConnectionString = "server=" & server & ";" _ & "user id=" & username & ";" _ & "password=" & password & ";" _ & "database=" & database MysqlConn = New MySqlConnection()
You need to move
MysqlConn = New MySqlConnection() on the first line. So it would be like this
MysqlConn = New MySqlConnection() Dim server As String = My.Settings.DB_Server Dim username As String = My.Settings.DB_Username Dim password As String = My.Settings.DB_Password Dim database As String = My.Settings.DB_Database MysqlConn.ConnectionString = "server=" & server & ";" _ & "user id=" & username & ";" _ & "password=" & password & ";" _ & "database=" & database
I was also facing the same issue. I resolved it in 2 min for me I just white list ip through cpanel
Suppose you are trying to connect database of server B from server A. Go to Server B Cpanel->Remote MySQL-> enter Server A IP Address and That's it.
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".
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.
Just perform the following steps:
1) Connect to mysql
mysql -uroot -p
2) Create user
CREATE USER 'user'@'%' IDENTIFIED BY 'password';
3) Grant permissions
GRANT ALL PRIVILEGES ON \*.\* TO 'user'@'%' WITH GRANT OPTION;
4) Flush priviledges
My error message was similar and said 'Host XXX is not allowed to connect to this MySQL server' even though I was using root. Here's how to make sure that root has the correct permissions.
- Ubuntu 14.04 LTS
- MySQL v5.5.37
- Open up the file under 'etc/mysql/my.cnf'
- port (by default this is 'port = 3306')
- bind-address (by default this is 'bind-address = 127.0.0.1'; if you want to open to all then just comment out this line. For my example, I'll say the actual server is on 10.1.1.7)
Now access the MySQL Database on your actual server (say your remote address is 18.104.22.168 at port 3306 as user 'root' and I want to change permissions on database 'dataentry'. Remember to change the IP Address, Port, and database name to your settings)
mysql -u root -p Enter password: <enter password> mysql>GRANT ALL ON *.* to [email protected]'22.214.171.124' IDENTIFIED BY 'put-your-password'; mysql>FLUSH PRIVILEGES; mysql>exit
sudo service mysqld restart
- You should now be able to remote connect to your database. For example, I'm using MySQL Workbench and putting in 'Hostname:10.1.1.7', 'Port:3306', 'Username:root'
On the off chance that someone facing this issue is experiencing it from within SQLyog, this happened:
I had connected to the remote database (from within SQLyog) and worked for some hours. Afterwards I left the system for some minutes, then came back to continue my work - ERROR 1130 ! Nothing I tried worked; Restarting SQLyog didn't fix it. Then I restarted the system - it still didn't work.
So I tried connecting from the terminal - it worked. Then retried it on SQLyog ... and it worked. I can't explain it other than 'random computer quirkiness', but I think it might help someone.
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”.
Problem: [email protected] is unable to connect to a fresh installation of mysql-community-server on openSUSE 42.2-1.150.x86_64. Mysql refuses connections - period.
$ ls -l /var/lib/mysql/mysql/user.* -rw-rw---- 1 mysql mysql 0 Apr 29 19:44 /var/lib/mysql/mysql/user.MYD -rw-rw---- 1 mysql mysql 1024 Apr 29 19:44 /var/lib/mysql/mysql/user.MYI -rw-rw---- 1 mysql mysql 10684 Apr 29 19:44 /var/lib/mysql/mysql/user.frm
File user.MYD has 0 size (really ?!). I copied all 3 files from another working system.
$ /usr/sbin/rcmysql stop $ cd /var/lib/mysql/mysql/ $ scp [email protected]:/var/lib/mysql/mysql/user.* ./ $ /usr/sbin/rcmysql start $ cd - $ mysql -u root -p
I was able to log in. Then, it was just a matter of re-applying all schema privileges. Also, if you disabled IPv6, re-enable it temporary so that [email protected]::1 account can also work.
This answer might help someone...
All these answers didnt help, then I realised I forgot to check one crucial thing.. The port :)
I have mysql running in a docker container running on a different port. I was pointing to my host machine on port 3306, which I have a mysql server running on. My container exposes the server on port 33060. So all this time, i was looking at the wrong server! doh!
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 are trying to execute mysql query withouth defining connectionstring, you will get this error.
Probably you forgat to define connection string before execution. have you check this out? (sorry for bad english)
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