Replication in MySQL 8

In the previous chapter, we dived deep into MySQL 8 indexing. Indexes are an important entity for any database management system. They help improve SQL query performance by limiting the number of records to be visited. Database administrators working on performance improvement must be aware of this important technique. This chapter explains in detail the types of indexes and their advantages. This chapter also explains how indexing works in MySQL 8. It's going to be a pretty informative chapter!

Moving further along the same line, in this chapter, we will discuss database replication. How much are we already aware about database replication? It doesn't actually matter. This chapter covers insightful details about database replication. If you have prior knowledge of database replication, this chapter will add to it. If you have only just heard about...

Overview of replication

We will walk through the basics of database replication in this section. We will understand what replication is, the advantages it provides, and the scenarios in which replication can be beneficial.

What is MySQL replication?

It is assumed that you are reading this for two reasons. You're familiar with MySQL replication and are willing to gain more knowledge, and perhaps you're unfamiliar with MySQL replication and want to learn.

MySQL replication is useful for serving lots of different purposes. Usually, people start thinking about MySQL replication when they start having more queries than a single database server can handle. Based on this, do you have any guesses on what MySQL replication...

Configuring replication

In this section, we will learn configuration for different types of MySQL 8 replication methods. It includes step by step instructions for setting up and configuring replication.

Binary log file based replication

One of the most common traditional methods of MySQL database replication is the binary log file position method. This section focuses on configuration of the binary log file position method of replication. Before we jump into the configuration section, it would be good to revise and understand the basics of binary log position based replication.

As described earlier, one of the MySQL database servers acts as master and the rest of the MySQL database servers become slaves. The master database...

Implementing replication

The basis for replication is that the master database server keeps track of all the changes taking place on the master database. The changes are tracked in the binary log files in the form of events since the server was started. SELECT operations are not recorded as they modify neither the database nor the contents. Each of the REPLICATION SLAVE pull a copy of the binary log file from master instead of a master database pushing the log file to the slave. The slave in turn executes the events as it is read from the master's binary log file. This maintains the consistency between master and slave servers. In MySQL replication, each slave functions independently from master and other slave servers. So, it is up to the slave to request the master's binary log file at a convenient time without impacting the master or slave functioning.

The focus for...

Group replication

This section of the chapter explains what group replication is, setting up group replication, configure and monitor group replication. Basically, MySQL group replication is a plugin that enables us to create elastic, highly-available, fault-tolerant replication topologies.

The purpose of the group replication is to create a fault tolerant system. To create a fault tolerant system, the components should be made redundant. The component should be removed without impacting the way system operates. There are challenges in setting up such a system. The complexity of such a system is of a different level. Replicated databases require maintenance and administration of several servers instead of just one. The servers cooperate together to create a group, which raises the problems related to network partitioning and split-brain scenarios. So, the ultimate challenge is...

Replication solutions

MySQL replication is useful in many different scenarios to fulfill a range of purposes. This section focuses on specific use cases and provides general information on how to use the replication.

One of the major use cases is to use replication for backup purposes. The data from the master can be replicated on the slave database server and then the data on the slave can be backed up. The slave database server can be shut down without affecting the operations running on the master database server.

Another use case is to handle unexpected halt of the REPLICATION SLAVE. To accomplish this, once the slave restarts, the I/O thread must be able to recover information about the transactions received and the transactions that are executed by the SQL thread. This information is stored in the InnoDB tables. As the InnoDB storage engine is transactional, it is always...

Summary

In this chapter, we learned insightful details about MySQL 8 replication, what replication is, and how it helps solve specific problems. We also learned how to set up statement-based and row-based replication types. Along the way, we also learned about the system variables and server start up options for replication. In the later part of the chapter, we dived deep into group replication and how it is different from the traditional method of MySQL replication. We also learned logging and replication formats. Last but not least, we learned different replication solutions in brief. We covered a lot of stuff, huh?

It's now time to move on to our next chapter, where we will be setting up several types of partitioning, and exploring the selection of partitioning, and pruning of partitioning. It also explains how to cope with restrictions and limitations while partitioning...