MySQL root access from all hosts

Question

I ve installed MySQL server on a remote Ubuntu machine  The root user is defined in the mysql user table this way   mysql gt  SELECT host  user  password FROM user WHERE user    root    ------------------ ------ -------------------------------------------    host               user   password                                     ------------------ ------ -------------------------------------------    localhost          root    xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx     ip-10-48-110-188   root    xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx     127 0 0 1          root    xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx       1                root    xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx    ------------------ ------ -------------------------------------------    I can access with user root from the same remote machine command-line interface using the standard mysql client  Now I want to allow root access from every host on the internet  so I tried adding following row  it s an exact duplicate of the first row from previous dump  except for the host column    mysql gt  SELECT host  user  password FROM user WHERE host         ------------------ ------ -------------------------------------------    host               user   password                                     ------------------ ------ -------------------------------------------                       root    xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx    ------------------ ------ -------------------------------------------    But my client on my personal PC continues to tell me  I obscured the server IP       SQL Error  2003   Can t connect to MySQL server on  46 x x x   10061    I can t tell if it s a authentication error or a network error  On the server firewall I enabled port 3306 TCP for 0 0 0 0 0  and that s ok for me

User · Answer

Run the following query  use mysql  update user set host     where host  localhost   NOTE  Not recommended for production use

User · Answer

In my case the  bind-address  setting was the problem  Commenting this setting in my cnf did not help  because in my case mysql set the default to 127 0 0 1 for some reason   To verify what setting MySql is currently using  open the command line on your local box   mysql -h localhost -u myname -pmypass mydb   Read out the current setting   Show variables where variable name like  bind     You should see 0 0 0 0 here if you want to allow access from all hosts  If this is not the case  edit your  etc mysql my cnf and set bind-address under the  mysqld  section   bind-address 0 0 0 0   Finally restart your MySql server to pick up the new setting   sudo service mysql restart   Try again and check if the new setting has been picked up

User · Answer

Update the bind-address   0 0 0 0 in the  etc mysql mysql conf d mysqld cnf and from the mysql command line allow the root user to connect from any Ip  Below was the only commands worked for mysql-8 0 as other were failing with error syntax  check the manual that corresponds to your MySQL server version for the right syntax to use near  IDENTIFIED BY  abcd   at line 1 GRANT ALL PRIVILEGES ON     TO  root   localhost   UPDATE mysql user SET host     WHERE user  root    Restart the mysql client sudo service mysql restart

User · Answer

There s two steps in that process   a  Grant privileges  As root user execute with this substituting  password  with your current root password    GRANT ALL PRIVILEGES ON     TO  root      IDENTIFIED BY  password     b  bind to all addresses   The easiest way is to comment out the line in your my cnf file    bind-address   127 0 0 1    and restart mysql  service mysql restart   By default it binds only to localhost  but if you comment the line it binds to all interfaces it finds  Commenting out the line is equivalent to bind-address     To check where mysql service has binded execute as root   netstat -tupan   grep mysql   Update For Ubuntu 16   Config file is  now     etc mysql mysql conf d mysqld cnf     at least on standard Ubuntu 16

User · Answer

if you have many networks attached to you OS  yo must especify one of this network in the bind-addres from my conf file   an example    mysqld  bind-address   127 100 10 234   this ip is from a ethX configuration

User · Answer

Sometimes     bind-address   127 0 0 1    should be      bind-address

User · Answer

MYSQL 8 0 - open mysql command line client GRANT ALL PRIVILEGES ON      TO  root   localhost      use mysql UPDATE mysql user SET host     WHERE user  root      Restart mysql service

User · Answer

I m using AWS LightSail and for my instance to work  I had to change  bind-address   127 0 0 1  to bind-address    lt Private IP Assigned by Amazon gt   Then I was able to connect remotely

User · Answer

MariaDB running on Raspbian - the file containing bind-address is hard to pinpoint  MariaDB have some not-very-helpful-info on the subject   I used     sudo grep -R bind-address  etc    to locate where the damn thing is   I also had to set the privileges and hosts in the mysql like everyone above pointed out    And also had some fun time opening the 3306 port for remote connections to my Raspberry Pi - finally used iptables-persistent   All works great now

User · Answer

mysql update is what you need   I don t know why anyone would follow the more complex ways of correcting this issue  when MySql graciously built a tool that already does this

[mysql] MySQL root access from all hosts

Examples related to mysql

Examples related to authentication