How to enable remote connections to MySQL server

When you install MySQL, remote connections are disabled by default for security reasons.

You can enable remote connections by changing configuration options in config file.

Edit my.cnf configuration file. On Debian based distributions (also includes Raspbian, which is what I’m using), config file is located in /etc/mysql/my.cnf.

sudo pico /etc/mysql/my.cnf

Once config file is open, find section [mysqld]. Under that section you’ll need to set bind address (which by default should be set to 127.0.0.1) to your server’s IP address. You can also bind to 0.0.0.0 if you don’t wish to specify IP address – useful if machine on which MySQL is hosted has dynamically allocated IP address. You’ll also need to comment out or remove skip-networking line if its in the config file.

So if the machine on which MySQL is hosted has an IP address of 192.168.1.115, configuration options need to look like:

[mysqld]
bind-address 192.168.1.115
#skip-networking

Or if you don’t want to specify IP address:

[mysqld]
bind-address 0.0.0.0
#skip-networking

Leave rest of the configuration file as is and save your changes. Restart MySQL for changes to take effect.

Once this is done, you’ll need to enable users to connect remotely – this is done by creating user account(s) with “From Host” option set to specific IP address from which user is going to connect, or setting “From Host” to “%” value which will accept connection from any host for that username.

For more information on user administration, look at MySQL Manual – Users and Privileges or MySQL Manual – Adding User Accounts.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s