Use MySQL master / slave balancing in Drupal 8

3
votes

I want my Drupal8 installation to be more reliable in case of database failure, so I want to enable Master / Slave replication in MySQL, and make it work with Drupal 8.

I setted up two MySQL servers (one master, one slave), and replication is working.

Now, I want to setup Drupal to balance SELECT query to the slave, and INSERT/UPDATE/DELETE queries to the master. It will balance the load between the two servers, and I will have a (litle) HA concept with it.

To test this architecture, I installed a brand new Drupal 8 instance, no custom module at all. Only pure vanilla Drupal 8 if possible.

I change the settings.php file as follow :

$databases['default']['default'] = array (
   'database'=>'drupaldb',
   'username'=>'masteruser',
   'host'=>'database-master.com'
   ...
);
$databases['default']['slave'] = array (
   'database'=>'drupaldb',
   'username'=>'slaveuser',
   'host'=>'database-slave.com'
   ...
);

"slaveuser" is a MySQL user with only READ access ; "masteruser" have RW access on database.

With this settings, the application is working fine, but when I look at the Slave metrics, there is no connection. Even when I'm reloading page of the front-end.

If I switch off the master, or if I change the master settings (bad password, wrong database) in config php file, I expected the application, for the front-end pages, to use the Slave settings. But front pages are in error : "The website encountered an unexpected error. Please try again later". Error log indicate a SQL connection failure.

What I see here, is that there is no way to balance queries onto master (for writes) and slave (for reads). I expected Drupal8 to manage this without any extra plugin.

Do I have to use the mysql_nd_ms PHP extension to do M/S balancing ?

3
mysqlamazon-rdsdrupal-8database-replication
What metrics are you looking at? How do you expect the code to determine which connection to use? - symcbean
I watch at "queries / sec" on both Master and Slave : slave is 0, master is > 0. I use ApacheBench to force reload of a front page (in "not logged" mode). - JayMore
As I read the documentation (drupal.org/docs/8/api/database-api/database-configuration) , I expected the native CMS part of Drupal to work out of the box, but it seems supported only by AutoSlave driver (drupal.org/node/1952964). I dont know if it works with D8. - JayMore

3 Answers

2
votes

The syntax for the replica database configuration can be found in settings.php

https://api.drupal.org/api/drupal/sites%21default%21default.settings.php/8.7.x

The syntax for the default db replica is: 

// $info_array is the db connection details
$databases['default']['replica'][] = $info_array;

Note: D8 uses replica not slave for the terminology.

1
votes

Note: so far as I can tell, adding 'replica' servers does nothing unless you are using the db_* functional calls (which are deprecated), or if you manually instantiate the database.replica connection in any of your custom queries e.g. /** @var \Drupal\Core\Database\Connection $database_replica */ $database_replica = \Drupal::service('database.replica'); $query = $database_replica->select('node', 'n');....

0
votes
$database_replica = \Drupal::service('database.replica');
or
$database_replica = Database::getConnection('replica', 'default');