Set up optional database replication

Setting up database replication provides the following benefits:

  • Provides data backup
  • Enables data analysis without affecting the master database
  • Scalability

MySQL databases replicate asynchronously, which means slaves do not need to be connected permanently to receive updates from the master.

Configure database replication

An in-depth discussion of database replication is beyond the scope of this guide. To set it up, you can consult a resource like:

Magento provides sample MySQL configurations for your slave databases. A simple configuration is provided with the ResourceConnections class README.md.

The following is more advanced and is provided for your information only:

	return array (
   //...
  'db' =>
  array (
    'connection' =>
    array (
      'indexer' =>
      array (
        'host' => 'default-master-host',
        'dbname' => 'magento',
        'username' => 'magento',
        'password' => 'magento',
        'active' => '1',
        'persistent' => NULL,
      ),
      'default' =>
      array (
        'host' => 'default-master-host',
        'dbname' => 'magento',
        'username' => 'magento',
        'password' => 'magento',
        'active' => '1',
      ),
      'checkout' =>
      array (
        'host' => 'checkout-master-host',
        'dbname' => 'checkout',
        'username' => 'magento',
        'password' => 'magento',
        'model' => 'mysql4',
        'engine' => 'innodb',
        'initStatements' => 'SET NAMES utf8;',
        'active' => '1',
      ),
      'sales' =>
      array (
        'host' => 'sales-master-host',
        'dbname' => 'sales',
        'username' => 'magento',
        'password' => 'magento',
        'model' => 'mysql4',
        'engine' => 'innodb',
        'initStatements' => 'SET NAMES utf8;',
        'active' => '1',
      ),
    ),
    'slave_connection' =>
    array (
      'default' =>
      array (
        'host' => 'default-slave-host',
        'dbname' => 'magento',
        'username' => 'read_only',
        'password' => 'password',
        'active' => '1',
      ),
      'checkout' =>
      array (
        'host' => 'checkout-slave-host',
        'dbname' => 'checkout',
        'username' => 'read_only',
        'password' => 'password',
        'model' => 'mysql4',
        'engine' => 'innodb',
        'initStatements' => 'SET NAMES utf8;',
        'active' => '1',
      ),
      'sales' =>
      array (
        'host' => 'sales-slave-host',
        'dbname' => 'sales',
        'username' => 'read_only',
        'password' => 'password',
        'model' => 'mysql4',
        'engine' => 'innodb',
        'initStatements' => 'SET NAMES utf8;',
        'active' => '1',
      ),
    ),
    'table_prefix' => '',
  ),
//.......

Performance improvement

To improve the performance of master-slave replication, you can filter some tables on slave instances. We recommend filtering all temporary tables with name pattern search\_tmp\_% that are used for catalog search.

To do this, add the following line to your my.cnf file on your slave instances:

replicate-wild-ignore-table=%.search\_tmp\_%

For more information about this setting, see MySQL documentation.