Drupal 7 MySQL master/slave replication not working

Question

I am having trouble getting Drupal 7.7 to use a MySQL slave database.

My settings.php is as follows:

$databases['default']['default'] = array(
  'driver' => 'mysql',
  'database' => 'my_db',
  'username' => 'dbuser',
  'password' => 'dbpw',
  'host' => 'db-ip-address'
);
$databases['default']['slave'][] = array(
  'driver' => 'mysql',
  'database' => 'my_db',
  'username' => 'dbuser',
  'password' => 'dbpw',
  'host' => '127.0.0.1'
);

Replication itself is working great. When I add new content to the site it quickly is replicated on the slave.

Looking at tcpdump though, I never see a call to the local database.

Is there anything I'm missing to enable Drupal to use the slave?

score 1 · Accepted Answer · answered Aug 23 '11 at 18:30

1

Using slave databases is barely implemented in Drupal core. If you are developing your own modules then calls to db_query need to specify that they want to use the slave database using the $options array. See DatabaseConnection::defaultOptions for how to set this array.

answered Aug 23 '11 at 18:30

JamesK

475
4
9

Thanks James, This was also answered for me on drupal.org. http://drupal.org/node/1253352 Looks like only 'Search' and 'Aggregator' can make use of a slave. – Brian Aug 25 '11 at 19:11

score 1 · Answer 2 · edited Apr 13 '17 at 12:46

1

This issue has a really good answer to this same question: https://drupal.stackexchange.com/questions/10806/how-to-get-core-to-leverage-a-mysql-master-slave-configuration

It is indeed possible to rather easily make most of core and contrib use slave server for selects, without patching core :)

edited Apr 13 '17 at 12:46

Community

1
1

answered Feb 01 '12 at 17:23

Ilmari

177
1
5

score 0 · Answer 3 · answered Dec 13 '12 at 16:47

The AutoSlave module redirects SELECT queries to read-only replicant databases, and it takes into account replication lag.

According to the module docs, it only uses the read-only replicant when all of the following conditions are true:

The query is a select query

The tables in the select query have not been written to during the request and within the assumed replication lag

A transaction has not been started

The tables in the select query are not specified in the 'tables' option in the driver settings

A lock has not been started (core db-lock and memcache-lock supported)

Drupal 7 MySQL master/slave replication not working

3 Answers3