There are a number of high availability solutions possible with MySQL’s replication technology. Please contact us toll-free for details or implementation advice: +1-866-268-9448
Master-master in Active-Passive Mode
In this scenario two or more slaves also serve as masters. In the two node setup, the primary database propagates changes to the slave, which then behaves as a master propagating those changes back to node1. The first server then realizes those changes originated from itself, and doesn’t apply them. In such a setup, the initialization file can be exactly the same on both servers except for the read-only option on node2, and different server_id settings. In the event of a failure, node2 can be made read-write, and the application can be redirected to the node2 database, with very little downtime.
This switchover can be automated with various heartbeat solutions. It is the ideal configuration for many high availability requirements.
MySQL’s master-master configuration should not be confused with multi-master, where all master nodes can receive changes, and updates. These configurations require complex conflict resolution and are not typical. Even with Oracle’s own Real Application Clusters, there is some application partitioning seen in many typical environments.
Master-Master in Active-Active Mode
This is similar to the above master-master configuration except that both databases are read/write. MySQL can do this using the special auto_increment_increment, and auto_increment_offset options. These let you stagger updates in primary key based tables.
It is very likely that even in this configuration, your application will still have to be careful, and relatively smart about the database backend. However with enough care, this type of configuration can provide scaling out for heavy write-intensive applications.
This setup is probably the most common one we see at clients. The primary database serves as the master, and changes propagate in one direction to the slave. The slave can be used for read-only queries, reports, backups and so on. As with all MySQL replication, it must be monitored so you can verify it is kept in sync. Also the error logs must be monitored.
In the event of a failure on the primary, a few changes would be necessary in the initialization file, and the server restarted as the primary. Downtime would be minimal, but the failover would not be automatic.
Distribution Master Setup
If you have the need for more than one or two slaves, it may begin to load the primary database heavily. The IO threads which copy data to the slaves can load up the master database too much, impacting performance.
The solution is to build a distribution master. This master would then feed changes to subsequent slaves. Configure this node to be a slave of the master, and itself a master. Then point your various slave databases to that node instead of the production database.
Log Server Setup
A MySQL log server can be useful in cases where you want to do point-in-time recovery or other crash recovery. The server receives binary logs into it’s data directory. To startup the server, add log_bin, and log_bin_index to the my.cnf file, and make sure the log-bin.index file reflects the files that are currently in the directory. Then startup the server.
Keep in mind this log server would not have any data, it would just be there to help you replay SQL queries. It is basically a better version of mysqlbinlog that allows you to work with different logging formats, monitor progress, and filter replication events easily.
DRBD and Heartbeat
Solutions such as DRBD provide a filesystem layer solution to replication. The operating system controls this software, providing a mirrored copy of data across the network to a remote machine. That machine has MySQL installed and ready, but not up and running. Heartbeat controls all the components allowing them to work seemlessly together.
- Federated and Archive Storage Engines