How to make mysql accept connections externally

Question

I have a VPS and I want to make mysql DB accept connection externally (from my PC for instance). I have Debian Linux installed on the server. I checked some tutorials online and they said to comment out:

bind-address          = 127.0.0.1

But this didn't seem to help! is there anything specific for VPSs? or Am I missing something else? The command that runs mysql is:

/usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock

Answer 1

You have to set bind-address to the value of your machine's external IP address instead of the localhost IP address. Don't forget to restart the MySQL service afterwards.

Check these other answers out for more details.

Answer 2

You also have to change the bind-address to the external ip of your VPS machine (ipconfig would help).

And grant access from your PC. Connect to the database from localhost with root privilege and do:

grant all privilege on *.* to `username`@`your-pc-id` identified by 'your-password'

For more information look through MySQL grant documentation.

Answer 3

The MySQL server must be configured to accept connections externally (binding to the correct network interface as appropriate), and its firewall must be configured to allow incoming connections on that port (TCP port 3306). This may or may not already be set up when you installed MySQL (see iptables if you're on *nix).

You must also account for this in the MySQL permissions as follows.

Often, when setting up your MySQL permissions, you'll set user access rights only for @'localhost'. You'll need to make sure that both the user account and its granted permissions are set for the appropriate hostname or IP address you will be connecting from. For example, you could create a new authorised user with:

GRANT ALL PRIVILEGES ON somedatabase.* TO someuser@'somehostname' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

You have to do all of this before you can connect to that server remotely, using something like this (this example uses PHP):

mysql_connect('mysqlservername', 'someuser', 'password');

Answer 4

BlockquoteI tried that by the server couldn't start after doing that Starting MySQL database server: mysqld . . . . . . . . . . . . . . failed! Is there anything else I have to do such as opening port or such?

I was having the same problem too after adding the binding address , mysqld wouldn't start :(

any workaround around this or is it a not-required step ?