Contact
Site: US UK AU |

What is MySQL Replication?

What is MySQL Replication?

Overview
MySQL replication is a built-in feature of MySQL, an open-source RDBMS that allows databases to be identically cloned in real-time between one or more servers using the traditional master and slave replication. 

Definition

MySQL replication is a built-in feature of MySQL, an open-source RDBMS that allows databases to be identically cloned in real-time between one or more servers using the traditional master and slave replication. A primary or master server is used for database writes and reads while a secondary server, the slave server, is then configured to read all changes that occur on the master and replicate them exactly. The slave server can be used for read queries only.

Attention: Currently, Nexcess uses a Percona 5.5 server, which is a fork of the MySQL relational database management system created by Percona. It is intended to retain close compatibility to the official MySQL releases, while focusing on performance and increased visibility into server operations. For the sake of clarity, Percona 5.5 server will be referred to as MySQL for the rest of this article.

Benefits

Performance

A slave server can be used to scale a site's performance horizontally to distribute database queries over multiple servers. All of the database writes will still have to go to the master, but database reads can be distributed among the master and multiple slaves.

Backup performance

The slave server can be used for backups instead of the master. Because R1Soft CDP application is scheduled for continuous disk-based online backups runs there is a performance hit. Busy sites with large amounts of database traffic can slow down while backups are run on the master, instead of running the backup on the slave which will take the load off of the master.

Disaster recovery

In the event a master database server completely fails, such as there is a serious issue that will take time to fix, the content management application, such as Magento, can be quickly reconfigured to begin using the MySQL slave server as a master. This will allow minimal site downtime as the master server is rebuilt and restored.

Disadvantages

Complexity

A site running master and slave replication is considerably more complex. We have tuned MySQL replication to be very reliable when used with an content management application like Magento, but the possibilities for issues do increase with replication.

Performance

Running replication requires binary logs to be written to disk. While minimal, this additional disk activity has to be considered in overall performance. For new clusters running replication we are going to put the binary logs on a separate partition to help limit disk io issues.

Requirements

Two identical database servers must be built and set up to the site and cluster. Each must be in the same location or at least across the same gigabit network segment. Percona 5.5 is the current version of MySQL Nexcess is using.

Function

Once both database servers are provisioned, then replication can be set up by Nexcess system administrators. Replication will then be handled automatically by MySQL. Nothing changes with the application using the database, it simply points to the master server. If replication is set up before databases are imported, no further work is necessary. Any databases created or imported on the master will automatically be created on the slave.

Limitations

  • MySQL replication is not a backup for application issues or user mistakes. If a user deletes an entry or drops a table from the master, because any changes that are made on the master are instantly replayed on the slave, it will also be deleted or dropped on the slave. If mistakes like this occur, database restores will still be necessary.

  • MySQL master and slave replication, with split reads and writes, does not increase site reliability, it decreases it. Because the database connections are being distributed over multiple servers, the risk of issues increases if any server fails, the site will become unresponsive. This risk can be mitigated with the use of applications like HAProxy which would allow graceful failover of slave reads to the master, if the slave were to fail.

How do we set it up?

Nexcess system administrators will be instructed to set up replication before releasing the environment to a client or a site migration.

What is our recommendation for MySQL master and slave replication?

Currently, we recommend running MySQL master and slave replication for any large size sites, using the slave server as a backup instance only.

In the case of Magento, the master database server will only be used, no split reads and writes will be used. The reasons for this are as follows:

  1. Requirements for performance

    • Nexcess has yet to see a Magento site large enough to warrant split read and writes.
    • Even our biggest sites perform fine when we place them on a single vertically scaled, high powered single database server.
  1. Complexity

    • Running split reads and writes with Magento on two or more database servers and utilizing HAProxy for failover, increases complexity considerably.
    • If split reads and writes are used unnecessarily, the chances of an issue affecting the site  increases.
  1. Backups: Running master and slave replication allows R1Soft CDP to run backups on the slave. This prevents the backups from degrading performance on the live site.

Many clients have requested the master and slave database server configuration to be set up in case the master should fail, the slave would automatically take over as the new master. While this is technically possible, we chose not to do it due to the complexity of the scenario.

If the master were to fail, the definition for fail is very complicated as the master could fail in hundreds of ways. Writing a script to monitor, interpret, and act correctly for every possible situation would be very difficult. If such a script were written, and if MySQL on the master were to fail, this script could make the wrong determination, resulting in significant data loss. Not only could the data on the master be compromised, but the data on the slave could also be corrupted or lost. For these reasons, Nexcess recommends if there is an issue with the MySQL, a human being should examine the problem to make a decision based on discovery.

Nexcess actively monitors MySQL on every database server we are responsible for, and are instantly alerted if an issue is detected. We recommend using master and slave replication with split reads and writes and HAProxy only, if traffic is large enough to warrant such a configuration.

 

For 24-hour assistance any day of the year, contact our Support Team by email or through the Client Portal.

Article Rating (1 Votes)
Rate this article
  • Icon PDFExport to PDF
  • Icon MS-WordExport to MS Word
 
Attachments Attachments
There are no attachments for this article.
Related Articles RSS Feed
How to create MySQL databases and users with SiteWorx
Added on Wed, Sep 11, 2013
How to create MySQL database user accounts in SiteWorx
Added on Mon, Aug 5, 2013
How to show running MySQL queries
Added on Fri, Nov 13, 2015
How to import and export MySQL databases
Added on Fri, Nov 13, 2015
How to change permissions for MySQL users
Added on Mon, Jul 29, 2013
Changing the password for a MySQL database user
Added on Tue, Jul 30, 2013
How to delete a MySQL database with SiteWorx
Added on Tue, Jul 30, 2013
How to repair MySQL tables
Added on Wed, Oct 28, 2015
What is phpMyAdmin?
Added on Fri, Sep 5, 2014
How to truncate MySQL tables
Added on Fri, Oct 30, 2015