Magento MySQL Replication

We've got a fairly vast experience of MySQL clusters - and Percona have worked with us on a number of occasions when pushing the boundaries of complex configurations.

Can Magento natively handle read-only slaves

Magento is natively capable of splitting off reads/writes to different database servers (with the exception of a few broken releases, eg. EE 1.11) - allowing you to offset select load to an additional (or more) server(s); and forwarding all the update/write queries to a single master.

When should I do it

This is a more appropriate question. With dedicated Magento operating systems like MageStack - it is becoming more common for in-built server side advanced caching techniques to be available and easily used (such as Varnish front end caching and Redis back end caching).

Historically, Magento has never been bound by MySQL - but rather PHP. But as Varnish and Full Page Caching (FPC) are used more frequently, the burden of repeated tasks (category/product loads, frequent searches) is suddenly absorbed and PHP becomes less of a burden. In fact, it only really comes into play to generate the content initially, or complete non-cachable scenarios (add to cart, order completion etc.); for the purpose of explanation we're deliberately ignoring administrative load.

We have always stood by the fact that MySQL isn't an areas of concern for most retailers, as seen both here and here. But if your in the region of processing hundreds of orders per hour, not single or double digits - it will soon become an areas for optimisation.

Ultimately for smaller stores (<25k daily unique visitors)

Your efforts would be far better focused on simply finding an appropriate host who can suggest the right hardware to be on from the offset and that has configured the machine in the most optimal fashion for your store. Don't waste your time pursuing Master/Slave or Master/Master configurations - which will yield no performance benefit and will ultimately require continual attention and advanced MySQL knowledge.

Ultimately hardware sizing and selection will have a bigger part to play than MySQL optimisation.

But for larger stores

As your store starts to grow, converting or transactional load becomes more of a burden with the repeated task of completing complex inserts and updates. The addition of each new order will trigger the decrement of catalogue stock, callbacks from payment gateways and updates from EPOS/ERP systems. Combine this with the associated cache purge of the respective products/categories and you'll soon see MySQL load disproportionately increase.

Multi-master is never a solution we recommend or consider as a viable option, but Master/Slave can yield benefits (we stress, on Enterprise-size stores) by offsetting read load to secondary/tertiary nodes.

But I still want to do it

First configure your slaves. We're big advocates of the Percona utilities and MySQL branches - they have an ideal tool for taking hot backups of your existing DB - innobackupex. There is a good write up here.

On the master

Replace $TIMESTAMP or tab complete.

mysql
> GRANT REPLICATION SLAVE ON *.*  TO 'repl'@'$slaveip' IDENTIFIED BY '$slavepass';
> quit;
innobackupex --user=username --password=password /path/to/backupdir
innobackupex --user=username --password=password /
       --apply-log /path/to/backupdir/$TIMESTAMP/

rsync -avprP -e ssh /path/to/backupdir/$TIMESTAMP TheSlave:/path/to/mysql/
scp /etc/mysql/my.cnf TheSlave:/etc/mysql/my.cnf

On the slave

/etc/init.d/mysql stop
mv /path/to/mysql/datadir /path/to/mysql/datadir_bak
mv /path/to/mysql/$TIMESTAMP /path/to/mysql/datadir
chown -R mysql:mysql /path/to/mysql/datadir
sed -i 's#server-id=1#server-id=2#g' /etc/mysql/my.cnf
/etc/init.d/mysql start
cat /var/lib/mysql/xtrabackup_binlog_info
> TheMaster-bin.000001     481

mysql
> CHANGE MASTER TO MASTER_HOST='$masterip', MASTER_USER='repl', MASTER_PASSWORD='$slavepass', MASTER_LOG_FILE='TheMaster-bin.000001', MASTER_LOG_POS=481;
> START SLAVE;

Then once your slave is operational, in practice, it only takes a few additional lines of code to achieve.

In ./app/etc/local.xml

<default_read>
  <connection>
    <use/>
    <host><![CDATA[host]]></host>
    <username><![CDATA[username]]></username>
    <password><![CDATA[password]]></password>
    <dbname><![CDATA[dbname]]></dbname>
    <type>pdo_mysql</type>
    <model>mysql4</model>
    <initStatements>SET NAMES utf8</initStatements>
    <active>1</active>
  </connection>
</default_read>