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
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.

  • 1
    When i execute SHOW GLOBAL VARIABLES like 'bind_address'; it returns an empty set yet i can connect from 127.0.0.1 – user46420 Sep 15 '14 at 7: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 7: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. – user46420 Sep 15 '14 at 7: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'" – user46420 Sep 15 '14 at 7: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 8:33
1

check for bind-address in

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

if its not there in

/etc/mysql/my.cnf

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy