Skip to main content
Reiterate importance of not having lag in replication.
Source Link
q0rban
  • 1.1k
  • 6
  • 6

Despite all of the amazing work done on the database abstraction layer in Drupal 7, this is still surprisingly difficult to do with Drupal core out of the box. As others have mentioned, AutoSlave is an option, although not one I've attempted due to my stubborn refusal to believe that it should be that difficult to do this.

A simpler solution I have found is the following. To route all SELECTs to the slave server, you create a file titled select.inc inside of the core includes/database/mysql directory with the following contents:

<?php

/**
 * @file
 * Select builder for MySQL database engine, routing all SELECTs to the slave.
 */

/**
 * @addtogroup database
 * @{
 */

class SelectQuery_mysql extends SelectQuery {
  public function __construct($table, $alias = NULL, DatabaseConnection $connection, $options = array()) {
    $key = $connection->getKey();
    $connection = Database::getConnection('slave', $key);
    $options['target'] = 'slave';
    parent::__construct($table, $alias, $connection, $options);
  }
}

/**
 * @} End of "addtogroup database".
 */

There are some risks with this method:

  1. This method will hijack all SELECTs and direct them to the slave, which will undoubtedly cause issues if you have any lag in replication. Read that sentence again.
  2. When you upgrade Drupal core, it is possible that this file will get deleted.
  3. If Drupal core were ever to start shipping with its own includes/database/mysql/select.inc, your file would get overwritten during upgrade, and you would have to start maintaining your own patched version of the select.inc that ships with Drupal core.
  4. This method will hijack all SELECTs and direct them to the slave, which could cause issues if you have a significant lag in replication.

If you don't have any slave servers specified in settings.php, the above code will not cause a problem. It will still gracefully degrade to using the master server.

Despite all of the amazing work done on the database abstraction layer in Drupal 7, this is still surprisingly difficult to do with Drupal core out of the box. As others have mentioned, AutoSlave is an option, although not one I've attempted due to my stubborn refusal to believe that it should be that difficult to do this.

A simpler solution I have found is the following. To route all SELECTs to the slave server, you create a file titled select.inc inside of the core includes/database/mysql directory with the following contents:

<?php

/**
 * @file
 * Select builder for MySQL database engine, routing all SELECTs to the slave.
 */

/**
 * @addtogroup database
 * @{
 */

class SelectQuery_mysql extends SelectQuery {
  public function __construct($table, $alias = NULL, DatabaseConnection $connection, $options = array()) {
    $key = $connection->getKey();
    $connection = Database::getConnection('slave', $key);
    $options['target'] = 'slave';
    parent::__construct($table, $alias, $connection, $options);
  }
}

/**
 * @} End of "addtogroup database".
 */

There are some risks with this method:

  1. When you upgrade Drupal core, it is possible that this file will get deleted.
  2. If Drupal core were ever to start shipping with its own includes/database/mysql/select.inc, your file would get overwritten during upgrade, and you would have to start maintaining your own patched version of the select.inc that ships with Drupal core.
  3. This method will hijack all SELECTs and direct them to the slave, which could cause issues if you have a significant lag in replication.

If you don't have any slave servers specified in settings.php, the above code will not cause a problem. It will still gracefully degrade to using the master server.

Despite all of the amazing work done on the database abstraction layer in Drupal 7, this is still surprisingly difficult to do with Drupal core out of the box. As others have mentioned, AutoSlave is an option, although not one I've attempted due to my stubborn refusal to believe that it should be that difficult to do this.

A simpler solution I have found is the following. To route all SELECTs to the slave server, you create a file titled select.inc inside of the core includes/database/mysql directory with the following contents:

<?php

/**
 * @file
 * Select builder for MySQL database engine, routing all SELECTs to the slave.
 */

/**
 * @addtogroup database
 * @{
 */

class SelectQuery_mysql extends SelectQuery {
  public function __construct($table, $alias = NULL, DatabaseConnection $connection, $options = array()) {
    $key = $connection->getKey();
    $connection = Database::getConnection('slave', $key);
    $options['target'] = 'slave';
    parent::__construct($table, $alias, $connection, $options);
  }
}

/**
 * @} End of "addtogroup database".
 */

There are some risks with this method:

  1. This method will hijack all SELECTs and direct them to the slave, which will undoubtedly cause issues if you have any lag in replication. Read that sentence again.
  2. When you upgrade Drupal core, it is possible that this file will get deleted.
  3. If Drupal core were ever to start shipping with its own includes/database/mysql/select.inc, your file would get overwritten during upgrade, and you would have to start maintaining your own patched version of the select.inc that ships with Drupal core.

If you don't have any slave servers specified in settings.php, the above code will not cause a problem. It will still gracefully degrade to using the master server.

Source Link
q0rban
  • 1.1k
  • 6
  • 6

Despite all of the amazing work done on the database abstraction layer in Drupal 7, this is still surprisingly difficult to do with Drupal core out of the box. As others have mentioned, AutoSlave is an option, although not one I've attempted due to my stubborn refusal to believe that it should be that difficult to do this.

A simpler solution I have found is the following. To route all SELECTs to the slave server, you create a file titled select.inc inside of the core includes/database/mysql directory with the following contents:

<?php

/**
 * @file
 * Select builder for MySQL database engine, routing all SELECTs to the slave.
 */

/**
 * @addtogroup database
 * @{
 */

class SelectQuery_mysql extends SelectQuery {
  public function __construct($table, $alias = NULL, DatabaseConnection $connection, $options = array()) {
    $key = $connection->getKey();
    $connection = Database::getConnection('slave', $key);
    $options['target'] = 'slave';
    parent::__construct($table, $alias, $connection, $options);
  }
}

/**
 * @} End of "addtogroup database".
 */

There are some risks with this method:

  1. When you upgrade Drupal core, it is possible that this file will get deleted.
  2. If Drupal core were ever to start shipping with its own includes/database/mysql/select.inc, your file would get overwritten during upgrade, and you would have to start maintaining your own patched version of the select.inc that ships with Drupal core.
  3. This method will hijack all SELECTs and direct them to the slave, which could cause issues if you have a significant lag in replication.

If you don't have any slave servers specified in settings.php, the above code will not cause a problem. It will still gracefully degrade to using the master server.