MySQL How to allow remote connection to mysql

Question

I have installed MySQL Community Edition 5 5 on my local machine and I want to allow remote connections so that I can connect from external source   How can I do that

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

If you installed MySQL from brew it really does only listen on the local interface by default  To fix that you need to edit  usr local etc my cnf and change the bind-address from 127 0 0 1 to     Then run brew services restart mysql

User · Answer

And for OS X people out there be aware that the bind-address parameter is typically set in the launchd plist and not in the my ini file  So in my case  I removed  lt string gt --bind-address 127 0 0 1 lt  string gt  from  Library LaunchDaemons homebrew mxcl mariadb plist

User · Answer

some times need to use name of pc on windows  first step  put in config file of mysql       mysqld cnf SET bind-address  0 0 0 0    to let recibe connections over tcp ip   second step  make user in mysql  table users  with name of pc on windows propierties   NOT ip

User · Answer

Just F Y I I pulled my hair out with this problem for hours   finally I call my hosting provider and found that in my case using a cloud server that in the control panel for 1and1 they have a secondary firewall that you have to clone and add port 3306   Once added I got straight in

User · Answer

After doing all of above I still couldn t login as root remotely  but Telnetting to port 3306 confirmed that MySQL was accepting connections   I started looking at the users in MySQL and noticed there were multiple root users with different passwords    select user  host  password from mysql user    So in MySQL I set all the passwords for root again and I could finally log in remotely as root   use mysql  update user set password PASSWORD  NEWPASSWORD   where User  root   flush privileges

User · Answer

This blog How to setup a MySQL server on Local Area Network will be useful in setting up a MySQL from scratch

User · Answer

If your MySQL server process is listening on 127 0 0 1 or   1 only then you will not be able to connect remotely  If you have a bind-address setting in  etc my cnf this might be the source of the problem   You will also have to add privileges for a non-localhost user as well

User · Answer

Please follow the below mentioned steps inorder to set the wildcard remote access for MySQL User       1  Open cmd        2  navigate to path C  Program Files MySQL MySQL Server 5 X bin and   run this command       mysql -u root -p       3  Enter the root password        4  Execute the following command to provide the permission       GRANT ALL PRIVILEGES ON     TO  USERNAME   IP  IDENTIFIED BY    PASSWORD        USERNAME  Username you wish to connect to MySQL server           IP  Public IP address from where you wish to allow access to MySQL   server       PASSWORD  Password of the username used        IP can be replaced with   to allow user to connect from any IP   address        5  Flush the previleges by following command and exit       FLUSH PRIVILEGES        exit    or  q

User · Answer

I had to this challenge when working on a Java Project with MySQL server as the database  Here s how I did it  First  confirm that your MySQL server configuration to allow for remote connections  Use your preferred text editor to open the MySQL server configuration file  sudo nano  etc mysql mysql conf d mysqld cnf  Scroll down to the bind-address line and ensure that is either commented out or replaced with 0 0 0 0  to allow all remote connections  or replaced with Ip-Addresses that you want remote connections from  Once you make the necessary changes  save and exit the configuration file  Apply the changes made to the MySQL config file by restarting the MySQL service  sudo systemctl restart mysql  Next  log into the MySQL server console on the server it was installed  mysql -u root -p  Enter your mysql user password Check the hosts that the user you want has access to already   In my case the user is root  SELECT host FROM mysql user WHERE user    quot root quot    This gave me this output   -----------    host         -----------    localhost    -----------   Next  I ran the command below to grant the root user remote access to the database named my database  USE my database  GRANT ALL PRIVILEGES ON     TO  root      IDENTIFIED BY  my-password    Note    grants a user remote access from all hosts on a network  You can specify the Ip-Address of the individual hosts that you want to grant the user access from using the command - GRANT ALL PRIVILEGES ON     TO  root   Ip-Address  IDENTIFIED BY  my-password   Afterwhich I checked the hosts that the user now has access to   In my case the user is root  SELECT host FROM mysql user WHERE user    quot root quot    This gave me this output   -----------    host         -----------                  localhost    -----------   Finally  you can try connecting to the MySQL server from another server using the command  mysql -u username -h mysql-server-ip-address -p  Where u represents user  h represents mysql-server-ip-address and p represents password  So in my case it was  mysql -u root -h 34 69 261 158 -p  Enter your mysql user password You should get this output depending on your MySQL server version  Welcome to the MySQL monitor   Commands end with   or  g  Your MySQL connection id is 4 Server version  5 7 31 MySQL Community Server  GPL   Copyright  c  2000  2020  Oracle and or its affiliates  All rights reserved   Oracle is a registered trademark of Oracle Corporation and or its affiliates  Other names may be trademarks of their respective owners   Type  help   or   h  for help  Type   c  to clear the current input statement   mysql gt    Resources  How to Allow Remote Connections to MySQL That s all  I hope this helps

User · Answer

For whom it needs it  check firewall port 3306 is open too  if your firewall service is running

User · Answer

Enabling remote root access can be dangerous  It would be preferable if you were to set up user accounts with more restrictive permissions  The following three steps should do it    Ensure that the line starting with bind-address     is at least commented out in your my ini or my cnf file  If it doesn t exist  move on  You can find this file in C  ProgramData MySQL MySQL Server 8 0 on Windows  Afterwards  check that the user account you are establishing the connection with does not have localhost in the Limit to Hosts Matching field  While it isn t recommended  you can instead put   in that field for testing purposes  You can do this by opening a local connection to the server with MySQL Workbench  then going to Server Users and Privileges from the menu bar and finding the user account you want to connect with    The  Limit to Hosts Matching  field is what disallows you to connect non-locally  I e  it limits the accepted connections to a pattern of IP addresses  Ideally  you should be accessing the MySQL server from a static IP address or subnet  so that you can be as restrictive as possible    Obviously  your firewall should allow the MySQL Server application to communicate over the port you want  The physical networking equipment in between you and your server should allow communication on the port you want to connect with   port 3306 typically

User · Answer

All process for remote login  Remote login is off by default You need to open it manually for all ip  to give access all ip   GRANT ALL PRIVILEGES ON     TO  root      IDENTIFIED BY  password     Specific Ip  GRANT ALL PRIVILEGES ON     TO  root   your desire ip  IDENTIFIED BY  password     then   flush privileges    You can check your User Host  amp  Password  SELECT host user authentication string FROM mysql user    Now your duty is to change this   bind-address   127 0 0 1   You can find this on   sudo nano  etc mysql mysql conf d mysqld cnf    if you not find this on there then try this  sudo nano  etc mysql my cnf   comment in this    bind-address   127 0 0 1   Then restart Mysql  sudo service mysql restart   Now enjoy remote login

User · Answer

That is allowed by default on MySQL   What is disabled by default is remote root access   If you want to enable that  run this SQL command locally    GRANT ALL PRIVILEGES ON     TO  root      IDENTIFIED BY  password  WITH GRANT OPTION   FLUSH PRIVILEGES    And then find the following line and comment it out in your my cnf file  which usually lives on  etc mysql my cnf on Unix OSX systems  In some cases the location for the file is  etc mysql mysql conf d mysqld cnf    If it s a Windows system  you can find it in the MySQL installation directory  usually something like C  Program Files MySQL MySQL Server 5 5    and the filename will be my ini   Change line   bind-address   127 0 0 1   to    bind-address   127 0 0 1   And restart the MySQL server  Unix OSX  and Windows  for the changes to take effect

User · Answer

If mysqld has a bind address set to a loopback local address  e g  127 0 0 1   the server will not be reachable from remote hosts  because a loopback interface cannot be reached from any remote host   Set this option to 0 0 0 0     for IPv4 6  to accept connections from any host  or to another externally-reachable address if you want to only allow connections on one interface   Source

User · Answer

Just a note from my experience  you can find configuration file under this path  etc mysql mysql conf d mysqld cnf    I struggled for some time to find this path

[mysql] MySQL: How to allow remote connection to mysql

Examples related to mysql

Examples related to mysql-workbench