Mysql remote connection

Most often we work on our local database for Drupal projects and thus mysql host in settings.php would likely be 'localhost' as in the line:

$db_url = 'mysql://db_user:db_password@localhost/db_name';

However, occasionally we may want to connect to a remote mysql server directly (eg. on a dev server). If you have full control over the remote mysql server, here's what you can do to achieve that:

1) Log onto the remote server and edit mysql server configuration file

Suppose the remote server is Fedora with IP address 192.168.1.150. vim /etc/my.cnf

Comment or remove the line 'skip-networking' if it exists and add a new 'bind-address' line like the following:

#skip-networking bind-address=192.168.1.150 Then restart mysql server. service mysqld restart

2) Grant relevant user and database permissions

Suppose you're connecting to the mysql server from IP 192.168.1.100. You can do the following in mysql prompt on server 192.168.1.150:

GRANT ALL ON db_name.* TO 'db_user'@'192.168.1.100' IDENTIFIED BY 'db_password';

or if you want to allow connection from anywhere:

GRANT ALL ON db_name.* TO 'db_user'@'%' IDENTIFIED BY 'db_password';

3) Check and configure firewall on remote server

Check current firewall rules:

iptables --list

You may need to add a new rule to iptables (suppose mysql server uses the default port 3306).

iptables -A INPUT -s 192.168.1.100 -p tcp --dport 3306 -j ACCEPT

Save the iptables rules.

service iptables save

4) Edit local settings.php file in Drupal

The relevant line in settings.php should look like:

$db_url = 'mysql://db_user:db_password@192.168.1.150/db_name';

5) Test to see if you can view the web pages in Drupal