[HOWTO] Grant and Revoke Remote root Access to MySQL

To grant root access from all hosts (except for localhost):

GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY 'topsecret';

To revoke root access from all hosts (except for localhost):

DELETE FROM mysql.user WHERE User = 'root' AND Host = '%';
FLUSH PRIVILEGES;

To enable MySQL service to accept connections from all hosts change the following line in file mysql.conf:
bind-address=127.0.0.1
to
bind-address=0.0.0.0
or better just comment out:
#bind-address=127.0.0.1
and restart the MySQL service.

Notes:
*) The percent symbol ("%") in the notation root@"%" means “any host”, but it doesn’t imply localhost. You need to repeat the commands above with root@localhost in order to grant/revoke permissions for localhost.

Advertisements
This entry was posted in console and tagged . Bookmark the permalink.

4 Responses to [HOWTO] Grant and Revoke Remote root Access to MySQL

  1. anonymous says:

    Thanks it worked, it solved my problem
    i also had to add the /etc/my.cnf the following line:

    #listen to any IP
    bind-address=0.0.0.0

    And i also added to myiptables : -A INPUT -i eth0 -p tcp –destination-port 3306 -j ACCEPT

    then..: sudo service mysqld restart
    and sudo service iptables restart

    thanks !

  2. mlucasg says:

    Worked!
    Thank you!

  3. Manu Ganji says:

    Thank you so much!


    To enable MySQL service to accept connections from all hosts change the following line in file mysql.conf:
    bind-address=127.0.0.1
    to
    bind-address=0.0.0.0
    or better just comment out:
    #bind-address=127.0.0.1
    and restart the MySQL service.

    This was a life saver. I was looking for a way to enable remote access from anywhere. But I didn’t find that any where on the internet so I was pretty frustrated.

  4. Pingback: Habilitando acesso remoto ao servidor Mysql | Mairon Mendes

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s