Productive, Not Busy - Work smarter and get more done.

How to enable remote access to a MySQL database server

16 Jun 2009

By default, remote access to the MySQL database service is disabled. If you need to enable remote access, follow the steps below:

Modify the MySQL configuration file

Log into the server via SSH if you don't have direct access to the machine (replacing USERNAME and SERVER-ADDRESS with your username and Server IP Address or Hostname):

ssh {USERNAME}@{SERVER-ADDRESS}

... then locate the MySQL configuration file:

mysql -? | grep ".cnf"

The above command will list configuration files in the order that they will be read, the file that you should edit will usually be located at "/etc/my.cnf".

Open the configuration file for editing:

sudo nano /etc/my.cnf

... and locate the section starting with "[mysqld]" (if you're using the nano editor as in the command above, press control-w to search for text).

In this section, make sure that "skip-networking" is disabled:

#skip-networking

... then edit (or add) the "bind-address" line anywhere in the section to read as follows (replacing YOUR-SERVER-IP with your Server IP Address):

bind-address={YOUR-SERVER-IP}

Save and close the file (if you're using the nano editor as in the command above, press control-x to do this), then restart the "mysql" service to enable the change:

sudo /etc/init.d/mysql restart

Modify the MySQL user accounts

Depending on the way your MySQL User Accounts were created, you might need to grant access to your Remote IP Address. Log into the MySQL command line client with the MySQL Root User using the "mysql" database:

mysql -u root -p mysql

... then check the Host settings for your MySQL User Accounts:

select Host, User from user;

In MySQL, the "%" (percentage) sign indicates a wildcard, so any User Accounts that have "%" as their Host setting will be able to connect remotely.

You can change a User Account's Host setting using a query similar to the one below (replacing MYSQL-USERNAME with the MySQL Username you want to grant access to):

update User set Host='%' where user='{MYSQL-USERNAME}';

... or you can create a new User Account with remote access using a command similar to the one below:

grant all on {DATABASE-NAME}.* to {NEW-MYSQL-USERNAME}@% identified by '{NEW-MYSQL-USERNAME-PASSWORD}';

The above command will grant full access to all tables on the specified database; if you need to assign specific Permissions (such as CREATE, UPDATE, DELETE, DROP, INSERT, etc.) you should replace "all" with the specific privileges you need.

For more information on granting privileges, see the GRANT Syntax page from the MySQL 5.1 Reference Manual.

Test the new configuration

After you've finished granting Permissions or modifying User Accounts, exit the MySQL command line client by entering:

exit

You can test your new configuration by logging in via the MySQL command line client from a remote machine:

mysql -u {MYSQL-USERNAME} -p {MYSQL-DATABASE-NAME} -h
  {MYSQL-SERVER-IP-ADDRESS}
Do you like this? Share it:
.

Copyright © Geekology 2012. All Rights Reserved.