MySQL 5.7.9, released by Oracle Corporation in October 2015, offers users various updates and new features, our favorite of which is replication.
Replication is a key feature that enables users to develop a high-availability database. Most database administrators are familiar with the most standard type of replication: master-slave. The administrator copies the data on the primary server to another server. In the event anything happens to the primary server, the data can be found and easily recovered.
MySQL supports numerous types of replication. These include single master-slave replication and master-master replication as well as multi-source replication. This includes replication from multiple different masters to a single server. Another improvement is enhanced, semi-sync replication.
Before we look at replication in more detail, we need to know how MySQL works. Within the database, there is a great deal of traffic making operations possible. You may be writing something, deleting, or inserting data. A logging mechanism in MySQL logs all these transactions, the changes that happen to the database.
This binary logging system would, in a standard master-slave replication configuration, send logs to your secondary server over the network. A thread continuously dumps this data into the binary log and sends the data to the secondary slave. The secondary slave server receives the data and dumps it into a separate log file on the target slave. We call this the relay log. It captures all the information received from the master. The log file is used to make certain all changes are applied to the target server.
As previously mentioned, MySQL has many different possible replication topologies available. You can, for example, have all the reads directed to your system’s slaves and writes directed to the master alone, which helps with load balancing across the network.
The greatest advantage to having these different replication methods available is the reliability of MySQL replication they afford.
Problems With Replication
If we look back at MySQL releases prior to MySQL 5.5, the data was stored using a statement-based default logging metrology. This means that operations were executed like a basic scale command. Because these were logged on the file and applied on the target, they would re-execute although the operation had already been executed on the master. This introduced overhead and could create latency when all the data had to be re-processed on the slave server again. Since the slave server was a single-thread operation, latency occurred.
A second approach, which became available starting in MySQL 5.1, offers Row based replication in which only the modified events or row blocks are replicated to the target server and eliminates the overhead of re-executing the entire statement.
When you have hundreds of servers in a multi-tiered replication environment, what happens if you use one of the intermediate slaves? This introduces a new layer of complexity to the replication process. You might have to troubleshoot the events occurring in the process, including looking into the bin log and how the replication failed. The process may include some manual reconfiguration of the slaves and master servers.
This process may work well enough in environments with a few servers, but with a large server farm, it becomes impossible to manually manage any glitches in the replication process.
Simplifying Replication in MySQL
Replication should be simple.
That’s why there are so many different improvements within this version of MySQL. These improvements enhance availability and performance alike.
One new feature enables the slave options to be changed while the server is running. This allows users to change or modify their replication filtering rules without needing to bring the server to a full stop and restart. The master can also be changed without pausing the replication process.
A new feature called Global Transaction Identifier maintains data integrity across all nodes in the replication chain. Thus, based on a unique identifier attached to the data, all the nodes involved in replication are responsible for acknowledging when they have received that transaction ID. This enhances transaction consistency and helps make replication even more synchronized in comparison to traditional log-based replication approaches. Simple, eh?
A fuller list of all the various replication features, both new and improved, is available online.
This is one of the many resources available for those new to MySQL as well as existing users. Another one of these is our white paper exploring the primary benefits related to using MySQL, which includes how to optimize MySQL for high availability, how to configure it for scalability, and its performance analysis tools.
If you are using a previous version of MySQL and want to migrate to the newest release, Datavail can help.
Contact Datavail to discuss a custom MySQL solution designed for your enterprise. Find more solutions to common and advanced MySQL questions as well as many other database administration-related questions on our frequently updated blog.
The “ORA-12154: TNS:could not resolve the connect identifier specified” Oracle error is a commonly seen message for database administrators.
Which RAID should you use with SQL Server? Learn the differences between RAID 0, RAID 1, RAID 5, and RAID 10, along with best practices.
This blog reviews how you can generate scripts for SQL server logins, role assignments, and server permissions for a smooth migration.