How to grant remote access permissions to mysql server for user?

Question

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?

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

Answer 2

Try:

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

Answer 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
   

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

---

If that doesn't resolve, then also modify my.cnf or my.ini and comment these lines

bind-address = 127.0.0.1 to #bind-address = 127.0.0.1
and
skip-networking to #skip-networking

• Restart MySQL and repeat above steps again.

Raspberry Pi, I found bind-address configuration under \etc\mysql\mariadb.conf.d\50-server.cnf

Answer 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.).

Answer 6

By mysql 8 and later version, you cannot add a user by granting privileges. it means with this query:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' 
    IDENTIFIED BY 'type-root-password-here' 
    WITH GRANT OPTION;
FLUSH PRIVILEGES;

mysql will return this error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'written password' at line 1

this means you don't have a root user for % domain. so you need to first insert the user and then grant privileges like this:

mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'your password';
Query OK, 0 rows affected (0.11 sec)

mysql> GRANT ALL ON *.* TO 'root'@'%';
Query OK, 0 rows affected (0.15 sec)

mysql> FLUSH PRIVILEGES;

Dont forget to replace passwords with your specific passwords.

Answer 7

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.

Answer 8

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

#bind-address = 127.0.0.1

Answer 9

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.

Answer 10

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.

Answer 11

Ubuntu 18.04

Install and ensure mysqld us running..

Go into database and setup root user:

sudo mysql -u root
SELECT User,Host FROM mysql.user;
DROP USER 'root'@'localhost';
CREATE USER 'root'@'%' IDENTIFIED BY 'obamathelongleggedmacdaddy';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
exit;

Edit mysqld permissions and restart:

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

# edit the line to be this:
bind-address=0.0.0.0

sudo systemctl stop mysql
sudo systemctl start mysql

From another machine, test.. Obvs port (3306) on mysqld machine must allow connection from test machine.

mysql -u root -p -h 123.456.789.666

All the additional "security" of MySql doesn't help security at all, it just complicates and obfuscates, it is now actually easier to screw it up than in the old days, where you just used a really long password.