Use MySQL master / slave balancing in Drupal 8

Question

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 ?

Answer 1

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.

Answer 2

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');....