CODEWITHSUNDEEP
mysqlconnectionsql-grant
Aufwind
Aufwind

Reputation: 26288

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

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?

Upvotes: 137

Views: 518684

Answers (11)

studio1057
studio1057

Reputation: 157

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

vim /etc/my.cnf

add the following:

bind-address=0.0.0.0

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

Upvotes: 5

Khashayar
Khashayar

Reputation: 1351

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

Upvotes: 51

Mehdi Golzadeh
Mehdi Golzadeh

Reputation: 2583

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.

Upvotes: 13

Peter Prographo
Peter Prographo

Reputation: 1331

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.

Upvotes: 1

Wasim A.
Wasim A.

Reputation: 9866

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

Upvotes: 38

Vik2696
Vik2696

Reputation: 367

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

#bind-address = 127.0.0.1

Upvotes: 4

icemanzzz
icemanzzz

Reputation: 9

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.

Upvotes: 0

Kushal
Kushal

Reputation: 423

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.

Upvotes: 3

moshe beeri
moshe beeri

Reputation: 2037

Try:

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

Upvotes: 101

Michael Berkowski
Michael Berkowski

Reputation: 270775

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.

Upvotes: 153

user171212
user171212

Reputation: 101

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

Upvotes: 8

Related Questions