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, [email protected] 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?

-------------Problems Reply------------

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.

Try:

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

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

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

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.

Category:mysql Views:1 Time:2011-06-04

Related post

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.161 (s). 11 q(s)