Blog/ How to use Drupal 7 with MySQL master-slave setup

Drupal 7 has made it very easy to use it with MySQL master-slave setup. Assume that we have 3 MySQL servers configured in master-slave mode. In this article, we'll show you how to configure Drupal's settings.php so that it uses the three MySQL servers.

In settings.php, start with the MySQL master configuration. Replace mysql-master-db, mysql-master-username, mysql-master-password and mysql-master-IP with the appropriate details of your MySQL master.

$databases['default']['default'] = array(
  'driver' => 'mysql',
  'database' => 'mysql-master-db',
  'username' => 'mysql-master-username',
  'password' => 'mysql-master-password',
  'host' => 'mysql-master-IP',
);

Now below this, add the following code to configure the slaves. Replace mysql-slave1-db, mysql-slave1-user, mysql-slave1-password, mysql-slave1-IP, mysql-slave2-db, mysql-slave2-user, mysql-slave2-password, mysql-slave2-IP with the appropriate details of your MySQL slaves.

$databases['default']['slave'][] = array(
  'driver' => 'mysql',
  'database' => 'mysql-slave1-db',
  'username' => 'mysql-slave1-user',
  'password' => 'mysql-slave1-password',
  'host' => 'mysql-slave1-IP',
);
$databases['default']['slave'][] = array(
  'driver' => 'mysql',
  'database' => 'mysql-slave2-db',
  'username' => 'mysql-slave2-user',
  'password' => 'mysql-slave2-password',
  'host' => 'mysql-slave2-IP',
);

Now if you want any of the select queries to use the slave server, then use the following syntax:

$query = db_select('node', 'n', array('target' => 'slave'));

By providing array('target' => 'slave') argument, the query will run against one of the slave databases. If there are multiple such queries for the same page, then all will run on the same slave server. If no slave is available, then that option will be ignored and the query will run against the master.

If you don't want to change your code, then try the Autoslave module. It automatically uses the slave servers for applicable queries.