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.

> 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

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

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

In ./app/etc/local.xml

    <initStatements>SET NAMES utf8</initStatements>
  • Pingback: The Best Magento Server Set Up | Sonassi Makes Magento Ecommerce Websites()

  • SteveHoldoway

    Default mysql setup is to listen - bind-address - only on You'll need to modify this on the master ( + open firewalls accordingly ), and set up binary logging before this will work.

    And don't forget there's that gotcha that truncating a table on the master will screw stuff up if running older ( pre 5.5 I'm almost certain ) versions of MySQL.

    Finally, do you really want to replicate every database? information_ and performance_schema? Personally I'd recommend just replication of the production database. It'll take a bit more work, but the result is cleaner.

    Not worthy of a 5/5 review.

  • I have been through the whole content of this blog which is very informative and knowledgeable stuff, I would like to visit again.

  • wow! I reading and like they

  • urbanautomaton

    Useful article, thanks very much. Since this is the first google result for "Magento MySQL replication" though, it should be pointed out that performance is not the only reason to want to replicate your DB - even small stores will benefit by eliminating the DB as a single point of failure. And, as you say, there's also administrative load to take into account - more complex reports can be run on a slave without worrying about the effect on the storefront.

    • sonassi

      The key is understanding when to implement it as in the vast majority of cases, the perceived benefits do not exist.

      Small merchants won't be MySQL bound, so there will be no advantage of a slave server. Small merchants also aren't likely to have the budget to justify a second MySQL server (and the system administration time of managing a MySQL slave).

      To eliminate a SPOF - you would need to also have multiple firewalls, web servers, load balancers - and your complexity is suddenly quite vast; not to mention that MySQL Master/Master is no mean feat (or recommended).

      Personally, I've yet to encounter a Magento store that really warrants a dedicated slave server - and we host a few thousand large scale stores.


      • urbanautomaton

        Hi Ben,

        I'm not disputing the (lack of) performance benefits - I'm talking about using master/slave replication for improved availability and, most importantly, data safety.

        How recoverable you want your data to be in the event of a catastrophic failure? For example, if you take DB backups daily, a single server failure can lose you up to 24 hours of data. If you've got a slave running it takes two simultaneous failures to cause data loss. Single failures are pretty much inevitable - correlated failures are much less likely (particularly if you replicate off-site).

        You don't have to eliminate all your SPOFs - each has its own tradeoffs. If a loadbalancer or webserver fails, it only costs you downtime. But data loss is permanent, and master/slave replication really isn't that hard to set up or maintain if you're already managing your own MySQL instance. Budget certainly comes into it, but how much is your data worth?


        • sonassi

          Some good points, but its trying to apply those to businesses of all size where you see that replication doesn't have the advantages it appears to.

          On a large business, Magento more often than not, is just a small part of the whole commerce strategy, with order/category/customer information being synchronized with an external facility (ERP/EPOS etc.). Making even a 24 hour recovery point objective almost irrelevant, as the data can and will be sourced from a central location.

          On smaller businesses, a 24hr RPO will still pose some issues within lost orders - but its what cost that reflects in reality (ie. how long will it take to rekey orders) and whether that outweighs the total additional cost of another DB server.

          In 5 years, I've yet to see failure of an entire RAID array that lead to catastrophic unrecoverable loss of data forcing nothing else but the restoration of the previous backups. But what I have seen is repeated attempts at M/M or M/S that either failed completely with a total loss of data, or the slave stopped replicating due to an inconsequential error - giving the illusion of data security, but in reality there was none at all.

          If I was a small merchant, would my doubling up on server costs be justified to run another MySQL slave to take into account the possibility of a 1 in 2^17 risk of failure of 2 drives simultaneously. No I wouldn't.

          Data is much more probable to be lost due to user error (deletion or misconfiguration), which a slave won't protect you against - heck, I've seen total data loss from attempts at trying to deploy a MySQL slave.


          If you have the budget and knowledge, go for it - but don't be under any illusions the data is any safer.

          If you want to ensure data integrity and longevity, you would be far better investing in a web host that uses quality components and facilities to begin with - that reduces the risk of hardware failure to a fractional amount. You could rent 2 budget servers with desktop-grade drives with UBER's of 10^14 and very likely see drive failure, or invest the same money in a single quality server with enterprise-grade drives and battery backed RAID with drives in excess of 10^17 (for your reference, we use Intel DC S3700 drives in our servers @