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

User · Answer

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

Disable skip-networking in my.cnf (i.e: /etc/mysql/my.cnf)
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.

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;`

Restart mysql service:
```
sudo service mysql restart
```

User · Answer

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 gt  CREATE USER  root      IDENTIFIED BY  your password   Query OK  0 rows affected  0 11 sec   mysql gt  GRANT ALL ON     TO  root       Query OK  0 rows affected  0 15 sec   mysql gt  FLUSH PRIVILEGES    Dont forget to replace passwords with your specific passwords

User · Answer

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

User · Answer

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

User · Answer

Try   GRANT ALL PRIVILEGES ON     TO  root      IDENTIFIED BY  Pa55w0rd  WITH GRANT OPTION

User · Answer

Open the  etc mysql mysql conf d mysqld cnf file and comment the  following line    bind-address   127 0 0 1

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Two steps    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

User · Answer

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

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

Examples related to mysql

Examples related to connection

Examples related to grant