I'm working on a Drupal application that stores data in separate mysql databases, and syncs some of the data to CouchDB with node.js scripts.
The extra mysql dbs are 16+ GB and it's not practical nor necessary to keep them locally since I only want to read the latest data in local development.
Wouldn't it be cool if my local development Drupal sites can read from the remote database servers?
In some cases you can just use the connection you find in the remote site's settings.php:
'otherdb' => 'mysqli://username:[email protected]/dbname'
(note: it's a Drupal 6 site so that's why you don't see an array - I will give a Drupal 7 example below)
However, there's often a twist: I must create a SSH tunnel to connect to this particular db server.
First, you need to have configured and installed SSH keys on your local and remote machines.
Then fire up your terminal and create the SSH tunnel to forward the remote mysql port to a local port. This technique is based on information clearly presented by RevSys (quick example) and noah.org (more details).
ssh -L [local port]:[db host]:[remote port] [ssh-username]@[remote host] -N
NOTES:
- -N tells ssh that you don't want to run a remote command; you only want to forward ports.
- use a different port for your tunnel [local port] than the one you normally use for mysql; for example, if you connect to mysql locally on the default port 3306, use 3307 (or any other unused port) for your tunnel. You should use the correct [remote port] which is typically 3306, and you can see if it is different by looking at settings.php in the remote site.
- Keep this connection alive as long as you need to connect to the remote database.
ssh -L 3307:[db host]:3306 [ssh-username]@[remote host] -N
Then you can test your connection (in a different terminal instance):
mysql -u[dbuser] -p -P 3307 -h 127.0.0.1
Here is the connection in settings.php for Drupal 6:
What's cool is that you can mix local and remote databases. For example, I want to use a local copy of the Drupal database, which is smaller and easier to sync, and read the data from the second (and third, in my case) remote dbs.
$db_url = array(
);
You can also connect Drupal to the default remote database, but it makes sense to use a local instance for development.
And in Drupal 7:
$databases['default']['default'] = array(
'driver' => 'mysql',
'database' => 'local-dbname',
'username' => 'local-dbuser',
'password' => 'password',
'host' => 'localhost',
'prefix' => '',
);
$databases['otherdb']['default'] = array(
'driver' => 'mysql',
'database' => 'dbname',
'username' => 'username',
'password' => 'password',
'host' => '127.0.0.1',
'port' => '3307',
'prefix' => '',
);
If the db user for the remote db has all privileges, your application may alter the remote database.
Therefore, you should create a "read-only" user for the remote database which will prevent you from altering it.
THINK
Discover the power of Clicking-to-Give 
