5

I'm trying to make my MySQL Server running on CentOS to allow remote database connections. I'm reading this article that says I should comment out bind-address but bind address is not available in the my.conf, this line is available at /etc/my.cnf.

How should I enable remote connections now that bind-address is missing?

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
event_scheduler=ON
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
event_scheduler=ON
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
galoget
  • 107
  • 4

2 Answers2

8

The MySQL server listens on a single network socket for TCP/IP connections. This socket is bound to a single address, but it is possible for an address to map onto multiple network interfaces. The default address is 0.0.0.0.

  • If the address is 0.0.0.0, the server accepts TCP/IP connections on all server host IPv4 interfaces.
  • If the address is ::, the server accepts TCP/IP connections on all server host IPv4 and IPv6 interfaces. Use this address to permit both IPv4 and IPv6 connections on all server interfaces.

Source: http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_bind-address

You can check your current bind address by executing:

$ mysql -b -e "SHOW GLOBAL VARIABLES like 'bind_address'"
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| bind_address  | 127.0.0.1 |
+---------------+-----------+

on the command line, or SHOW GLOBAL VARIABLES like 'bind_address'; on a mysql client.

jynus
  • 13,932
  • 1
  • 29
  • 42
  • 2
    When i execute `SHOW GLOBAL VARIABLES like 'bind_address';` it returns an empty set yet i can connect from 127.0.0.1 –  Sep 15 '14 at 07:08
  • Strange, because with it commented it shows `bind_address | *` to me. What does `SELECT version();` show for you? Just set it to `0.0.0.0`, and make sure `skip-networking` is not set on my.cnf and it will listen on all interfaces (check it with `netstat -tl | grep mysql`) – jynus Sep 15 '14 at 07:38
  • `select version()` gives 5.1.7.3,also i have bind address set to 0.0.0.0. Skip networking is not present in my my.cnf file. –  Sep 15 '14 at 07:45
  • 1
    I get `ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)` when i run `mysql -b -e "SHOW GLOBAL VARIABLES like 'bind_address'"` –  Sep 15 '14 at 07:48
  • 1
    For connecting using the command line, you need to use a correct user and password `mysql -u user -p`. If you have `bind_address` set to `0.0.0.0` and you have rebooted, then it is listening on all interfaces already (unless a firewall is blocking it). BTW, 5.1 is unsupported. – jynus Sep 15 '14 at 08:33
  • what is unsupported in 5.1? –  Sep 15 '14 at 08:38
  • MySQL Sever 5.1 does not receive further security updates, upgrading to 5.5 or higher is highly recommended. – jynus May 26 '16 at 08:33
1

check for bind-address in 

/etc/mysql/mysql.conf.d/mysqld.cnf

if its not there in 

/etc/mysql/my.cnf
DuckPool
  • 11
  • 2