10

I've setup two MySQL servers with Master/Slave replication for the drupal database and I've confirmed the databases are in sync and replicating.

I am now trying to point drupal to both databases basically for failover/redundancy purposes. That is if I need to reboot our primary database server I don't want our site to go down. (Going read-only is acceptable during failure scenarios)

Based on the following article. I've modified settings.php as follows:

$databases['default']['default'] = array(
  'driver' => 'mysql',
  'database' => 'sdrupal',
  'username' => 'drupal',
  'password' => 'topsecret',
  'host' => 'masterdb.ptp.local',
);
$databases['default']['slave'][] = array(
  'driver' => 'mysql',
  'database' => 'sdrupal',
  'username' => 'drupal',
  'password' => 'topsecret',
  'host' => 'slavedb.ptp.local',
);

The configuration is fine until I shutdown the master (service mysqld stop) - when I do that my site pukes:

PDOException: SQLSTATE[HY000] [2013] Lost connection to MySQL server at 'reading initial communication packet', system error: 111 in drupal_is_denied() (line 1895 of /www/includes/bootstrap.inc). Additional

PDOException: SQLSTATE[HY000] [2013] Lost connection to MySQL server at 'reading initial communication packet', system error: 111 in dblog_watchdog() (line 141 of /www/modules/dblog/dblog.module).

Whats the trick to make this work?

6

Regarding Master/Master (High availability) is possible to do load-balancing when no master is down.

The following will make all writes go to master1 and all reads go to master2. If master1 fails, all queries will go to master2. If master2 fails, all queries will go to master1.

'master' => array('master1', 'master2')
'slave' => array('master2', 'master1')

The following will make all queries go to master1. If master1 fails all queries will go to master2:

'master' => array('master1', 'master2')
'slave' => array('master1', 'master2')
  • Thank you for this answer. I understand this is quite old and might be outdated. Do you have any tips for implementing the same in the latest version of Drupal? – Gaurav Ojha Sep 25 at 3:47
4

For the benefit of others - as best as I've been able to find - out of the box Drupal 7 doesn't seem to have any database high availability capability built in.

You can setup two mysql servers in a master/slave configuration but the best it will do is send all writes to the master and all reads to the slave. That provides crude load distribution but not failover.

That is to say if the master mysql server goes down all bets are off – the site goes down with an ugly error message complaining that PHP can't reach the master database.

Traditionally, as I understand, the way to tackle this with other software is to either use mysql ndb clusters or mysql proxy – but after reading a bit - these technologies apparently don't play well with Drupal.

However I stumbled upon a Drupal module called auto-slave (http://drupal.org/project/autoslave). It’s not widely used (there are 12 sites actively using it according to the stats) but it does seem to be able to do what we want. It can be configured in a variety of ways:

Master/Slave

Writes go to Master
Reads go to Slave

Drawbacks:
If master is down, the durpal site displays a prominent message that the site is in "read only" mode.

Master/Master (High availability)

Read and Writes go to master1 (primary) unless its unavailable.
If master1 is unavailable, reads and writes go to master2 (secondary)

Drawbacks:
There is no load distribution. All load (reads and writes) go to either master1 or to master2

Master/Master/Slave (High availability/High performance)

Writes go to Master1 (primary) unless it’s down then they go to Master2
Reads go to the slave unless it’s down
Drawbacks:
Requires a minimum of three database servers (master/master/slave)

So far we've setup the first (Master/Slave) successfully. I'm going to be attempting to setup Master/Master/Slave next.

Hopefully this helps someone else out.

  • This did help alot. :) – esafwan Nov 30 '14 at 15:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.