Error 1130 ошибка

asked Oct 13, 2009 at 12:40

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.

Edit:

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

answered Oct 13, 2009 at 12:47

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

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.

My setup:

• Ubuntu 14.04 LTS
• MySQL v5.5.37

Solution

1. Open up the file under etc/mysql/my.cnf

2. Check for:

   • 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)

3. Now access the MySQL Database on your actual server (say your remote address is 123.123.123.123 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 root@'123.123.123.123' IDENTIFIED BY 'put-your-password';
   mysql>FLUSH PRIVILEGES;
   mysql>exit
   

4. sudo service mysqld restart

5. 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’

Just perform the following steps:

1. Connect to MySQL (via localhost)

   mysql -uroot -p
   

   • If the MySQL server is running in Kubernetes (K8s) and being accessed via a NodePort

     kubectl exec -it [pod-name] -- /bin/bash
     mysql -uroot -p
     

1. Create user

   CREATE USER 'user'@'%' IDENTIFIED BY 'password';
   

2. Grant permissions

   GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' WITH GRANT OPTION;
   

3. Flush privileges

   FLUSH PRIVILEGES;
   

You need to grant access to the user from any hostname.

This is how you add new privilege from phpmyadmin

Goto Privileges > Add a new User

Select Any Host for the desired username

answered Jun 21, 2013 at 11:42

Simple way:

Grant All Privileges ON *.* to 'USER_NAME'@'%' Identified By 'YOUR_PASSWORD'; 

then

FLUSH PRIVILEGES;

done!

answered Nov 24, 2018 at 8:05

The message *Host ''xxx.xx.xxx.xxx'' is not allowed to connect to this MySQL server is a reply from the MySQL server to the MySQL client. Notice how its returning the IP address and not the hostname.

If you’re trying to connect with mysql -h<hostname> -u<somebody> -p and it returns this message with the IP address, then the MySQL server isn’t able to do a reverse lookup on the client. This is critical because thats how it maps the MySQL client to the grants.

Make sure you can do an nslookup <mysqlclient> FROM the MySQL server. If that doesn’t work, then there’s no entry in the DNS server. Alternatively, you can put an entry in the MySQL server’s HOSTS file (<ipaddress> <fullyqualifiedhostname> <hostname> <- The order here might matter).

An entry in my server’s host file allowing a reverse lookup of the MySQL client solved this very problem.

answered Mar 13, 2012 at 13:37

This working for any future remote mysql connection !

    sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Navigate to the line that begins with the bind-address directive. It should look like this:

    bind-address            = 0.0.0.0

Login to your mysql as root terminal

    mysql -u root -p
    -- root password

    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;

    EXIT;

finally Grant that machine exclusive permission to connect to the database remotely with the following command.

    sudo ufw allow from remote_IP_address to any port 3306

answered Jun 3, 2020 at 13:14

If you modify the grant tables manually (using INSERT, UPDATE, etc.), you should execute
a 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.

answered Oct 13, 2009 at 12:56

Just use the interface provided by MySql’s GUI Tool (SQLyog):

Click on User manager:

Now, if you want to grant access FOR ANY OTHER REMOTE PC, just make sure that, just like in the underneath picture, the Host field value is % (which is the wildcard)

answered Sep 27, 2017 at 13:09

Erica KaneErica Kane

3,16226 silver badges36 bronze badges

BabaNewBabaNew

8841 gold badge13 silver badges27 bronze badges

Pratap SharmaPratap Sharma

2,6432 gold badges18 silver badges32 bronze badges

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

 FLUSH PRIVILEGES;

answered Feb 21, 2016 at 10:52

Alex RAlex R

11.4k15 gold badges100 silver badges181 bronze badges

Eran LeviEran Levi

8772 gold badges12 silver badges31 bronze badges

JanJan

3304 silver badges12 bronze badges

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

this error because no password to the root , and this Maybe occurred with you when you trying to connect from outside .

answered Mar 20, 2019 at 21:20

AdrianAdrian

2,2734 gold badges38 silver badges78 bronze badges

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.

this is real sol

answered Feb 4, 2014 at 7:31

SriniSrini

4465 silver badges11 bronze badges

user2060451user2060451

2,5763 gold badges24 silver badges31 bronze badges

Ali CAKILAli CAKIL

3835 silver badges21 bronze badges

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 VB.NET

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

answered Jul 21, 2015 at 5:14