Remote Connections Mysql Ubuntu

Question

For some reason  I ve been unable to connect remotely to my MySQL server  I ve tried everything and I m still getting errors   root server1  home administrator  mysql -u monty -p -h www ganganadores cl Enter password  ERROR 1045  28000   Access denied for user  monty   server1 ganganadores cl   using password  YES    Now  I ve tried running    GRANT ALL ON     to monty localhost IDENTIFIED BY  XXXXX     GRANT ALL ON     to monty     IDENTIFIED BY  XXXXXX       and still nothing  What I m doing wrong   EDIT  my cnf has commented out the bind ip

User · Answer

MySQL only listens to localhost  if we want to enable the remote access to it  then we need to made some changes in my cnf file   sudo nano  etc mysql my cnf   We need to comment out the bind-address and skip-external-locking lines    bind-address   127 0 0 1   skip-external-locking   After making these changes  we need to restart the mysql service   sudo service mysql restart

User · Answer

To expose MySQL to anything other than localhost you will have to have the following line For mysql version 5 6 and below uncommented in  etc mysql my cnf and assigned to your computers IP address and not loopback For mysql version 5 7 and above uncommented in  etc mysql mysql conf d mysqld cnf and assigned to your computers IP address and not loopback  Replace xxx with your IP Address  bind-address          xxx xxx xxx xxx  Or add a bind-address         0 0 0 0 if you don t want to specify the IP Then stop and restart MySQL with the new my cnf entry  Once running go to the terminal and enter the following command  lsof -i -P   grep  3306  That should come back something like this with your actual IP in the xxx s mysqld  1046  mysql  10u  IPv4  5203  0t0  TCP  xxx xxx xxx xxx 3306  LISTEN   If the above statement returns correctly you will then be able to accept remote users  However for a remote user to connect with the correct priveleges you need to have that user created in both the localhost and     as in  CREATE USER  myuser   localhost  IDENTIFIED BY  mypass   CREATE USER  myuser      IDENTIFIED BY  mypass    then  GRANT ALL ON     TO  myuser   localhost   GRANT ALL ON     TO  myuser        and finally  FLUSH PRIVILEGES   EXIT   If you don t have the same user created as above  when you logon locally you may inherit base localhost privileges and have access issues  If you want to restrict the access myuser has then you would need to read up on the GRANT statement syntax HERE If you get through all this and still have issues post some additional error output and the my cnf appropriate lines  NOTE  If lsof does not return or is not found you can install it HERE based on your Linux distribution  You do not need lsof to make things work  but it is extremely handy when things are not working as expected  UPDATE  If even after adding changing the bind-address in my cnf did not work  then go and change it in the place it was originally declared   etc mysql mariadb conf d 50-server cnf

User · Answer

Add few points on top of apesa s excellent post   1  You can use command below to check the ip address mysql server is listening  netstat -nlt   grep 3306  sample result   tcp 0  0  xxx xxx xxx xxx 3306  0 0 0 0     LISTEN   2  Use FLUSH PRIVILEGES to force grant tables to be loaded if for some reason the changes not take effective immediately  GRANT ALL ON     TO  user   localhost  IDENTIFIED BY  passwd  WITH GRANT OPTION  GRANT ALL ON     TO  user      IDENTIFIED BY  passwd  WITH GRANT OPTION  FLUSH PRIVILEGES   EXIT       user    the user u use to connect to mysql ex root   passwd    the password u use to connect to mysql with   3  If netfilter firewall is enabled  sudo ufw enable  on mysql server machine  do the following to open port 3306 for remote access   sudo ufw allow 3306   check status using  sudo ufw status   4  Once a remote connection is established  it can be verified in either client or server machine using commands  netstat -an   grep 3306 netstat -an   grep -i established

User · Answer

If testing on Windows  don t forget to open port 3306

User · Answer

I was facing the same problem when I was trying to connect Mysql to a Remote Server  I had found out that I had to change the bind-address to the current private IP address of the DB server  But when I was trying to add the bind-address  0 0 0 0 line in my cnf file  it was not understanding the line when I tried to create a DB  Upon searching  I found out the original place where bind-address was declared  The actual declaration is in    etc mysql mariadb conf d 50-server cnf Therefore I changed the bind-address directly there and then all seems working

User · Answer

In my case I was using MySql Server version  8 0 22 I had to add bind-address          0 0 0 0  and change this line to be mysqlx-bind-address   0 0 0 0  in file at  etc mysql mysql conf d then restart MySQL by running sudo service mysql restart

User · Answer

You are using ubuntu 12  quite old one  First  Open the  etc mysql mysql conf d mysqld cnf file   etc mysql my cnf in Ubuntu 14 04 and earlier versions Under the  mysqld  Locate the Line  bind-address              127 0 0 1 And change it to  bind-address              0 0 0 0 or comment it Then  Restart the Ubuntu MysQL Server systemctl restart mysql service Now Ubuntu Server will allow remote access to the MySQL Server  But still you need to configure MySQL users to allow access from any host  User must be  username      with all the required grants To make sure that  MySQL server listens on all interfaces  run the netstat command as follows  netstat -tulnp   grep mysql Hope this works

[mysql] Remote Connections Mysql Ubuntu

Examples related to mysql

Examples related to ubuntu

Examples related to remoting