How to grant remote access permissions to mysql se

问题:

If I do SHOW GRANTS in my mysql database I get

GRANT ALL PRIVILEGES ON *.* TO \'root\'@\'localhost\' 
    IDENTIFIED BY PASSWORD \'some_characters\' 
    WITH GRANT OPTION

If I am not mistaken, root@localhost means that user root can access the server only from localhost. How do I tell MySQL to grant root the permission to access this mysql server from every other machine (in the same network), too?

回答1:

This grants root access with the same password from any machine in *.example.com:

GRANT ALL PRIVILEGES ON *.* TO \'root\'@\'%.example.com\' 
    IDENTIFIED BY \'some_characters\' 
    WITH GRANT OPTION;
FLUSH PRIVILEGES;

If name resolution is not going to work, you may also grant access by IP or subnet:

GRANT ALL PRIVILEGES ON *.* TO \'root\'@\'192.168.1.%\'
    IDENTIFIED BY \'some_characters\'  
    WITH GRANT OPTION;
FLUSH PRIVILEGES;

MySQL GRANT syntax docs.

回答2:

Try:

GRANT ALL PRIVILEGES ON *.* TO \'root\'@\'%\' IDENTIFIED BY \'Pa55w0rd\' WITH GRANT OPTION;

回答3:

You need to take some steps to make sure first mysql and then root user is accessible from outside:

1. Disable skip-networking in my.cnf (i.e: /etc/mysql/my.cnf)

2. Check value of bind-address in my.cnf, if it\'s set to 127.0.0.1, you can change it to 0.0.0.0 to allow access from all IPs or whatever ip that you want to connect from.

3. Grant remote access the root user from any ip (or specify your ip instead of %)

   GRANT ALL PRIVILEGES ON *.* TO \'root\'@\'%\'
       IDENTIFIED BY \'your_root_password\'
       WITH GRANT OPTION;
   FLUSH PRIVILEGES;`
   

4. Restart mysql service:

   sudo service mysql restart
   

回答4:

GRANT ALL PRIVILEGES ON *.* TO \'root\'@\'%\' 
    IDENTIFIED BY \'YOUR_PASS\' 
    WITH GRANT OPTION;
FLUSH PRIVILEGES;  

*.* = DB.TABLE you can restrict user to specific database and specific table.

\'root\'@\'%\' you can change root with any user you created and % is to allow all IP. You can restrict it by changing %.168.1.1 etc too.

回答5:

Those SQL grants the others are sharing do work. If you\'re still unable to access the database, it\'s possible that you just have a firewall restriction for the port. It depends on your server type (and any routers in between) as to how to open up the connection. Open TCP port 3306 inbound, and give it a similar access rule for external machines (all/subnet/single IP/etc.).

回答6:

In my case I was trying to connect to a remote mysql server on cent OS. After going through a lot of solutions (granting all privileges, removing ip bindings,enabling networking) problem was still not getting solved.

As it turned out, while looking into various solutions,I came across iptables, which made me realize mysql port 3306 was not accepting connections.

Here is a small note on how I checked and resolved this issue.

• Checking if port is accepting connections:

telnet (mysql server ip) [portNo]

-Adding ip table rule to allow connections on the port:

iptables -A INPUT -i eth0 -p tcp -m tcp --dport 3306 -j ACCEPT

-Would not recommend this for production environment, but if your iptables are not configured properly, adding the rules might not still solve the issue. In that case following should be done:

service iptables stop

Hope this helps.

回答7:

Open the /etc/mysql/mysql.conf.d/mysqld.cnf file and comment the following line:

#bind-address = 127.0.0.1

回答8:

This worked for me. But there was a strange problem that even I tryed first those it didnt affect. I updated phpmyadmin page and got it somehow working.

If you need access to local-xampp-mysql. You can go to xampp-shell -> opening command prompt.

Then mysql -uroot -p --port=3306 or mysql -uroot -p (if there is password set). After that you can grant those acces from mysql shell page (also can work from localhost/phpmyadmin).

Just adding these if somebody find this topic and having beginner problems.

回答9:

Two steps:

1. set up user with wildcard:
   create user \'root\'@\'%\' identified by \'some_characters\'; GRANT ALL PRIVILEGES ON *.* TO \'root\'@\'%\' IDENTIFIED BY PASSWORD \'some_characters\' WITH GRANT OPTION

2. vim /etc/my.cnf
   add the following:
   bind-address=0.0.0.0

restart server, you should not have any problem connecting to it.