Expert MySQL, Second Edition (2012)

PART 2. Extending MySQL

CHAPTER 8. Extending MySQL High Availability

One advanced feature of MySQL is its ability to provide high-availability database solutions. The server component responsible for obtaining high availability is replication. Some would include other features, such as partitioning and a host of smaller features, but the most important feature that enables high availability is replication.

In this chapter, you will learn what replication is and the basics of its configuration through a brief tutorial on MySQL replication. With those basic skills and the skills we learned from previous chapters, you will journey through the replication source code and learn how to extend replication through example projects. First, let’s find out what replication is and how it works.

What is Replication?

MySQL replication is the ability to duplicate data changes that occur on one server to another. Rather than copying the data directly (which could be slow and complicated when it comes to applying UPDATE or DELETE statements), the changes are transferred in the form of metadata and commands, hence events, that are copied to the second server and executed there.

These events are written to an ordered, sequential file called a binary log on the original server, and the second server reads those events via a remote connection, storing them in a file of the same format called a relay log. Events are then read one at a time on the second server from the relay log and executed.

This provides the ability to duplicate exactly the data changes from the original server, because it preserves the order of the events and ensures the same path through the server is used. We therefore call the process replication, because it replicates the changes as they occur. We call the connection among the servers involved a replication topology.

There are several roles¹ a server can perform. The following briefly describes each.

· Master—this server is the original server to which all write and DML statements are sent.

· Slave—this server is the server that maintains a copy of the data through the events replicated.

· Relay slave—this server performs the role of a slave as well as being the master to one or more slaves.

Slaves are intended to be read-only by practice. This is to ensure there is only one location from which events (data changes) can originate. The master, therefore, is the location to which all of your writes should be directed. The slave uses two threads²: an input/output (IO) thread for reading events from the master and a SQL thread for executing events from its relay log. I will explain the details of how these threads work in a later section.

Note The latest release of MySQL includes a multithreaded slave that uses a single IO thread to read the masters binary log entries and multiple SQL threads to execute events. For replication installations that permit events to run in parallel (.e.g., isolated by database), a multithreaded slave can improve replication performance. See the online reference manual for more details on multithreaded slaves (MTS).

The following sections will explain why you would want to use replication, what capabilities it provides beyond making a duplicate of the date, and what is required to use replication. A complete explanation of every aspect and nuance of the replication system would require an entire volume itself. Rather than attempt to explain everything there is to know about replication, I present a broader view of replication that will enable you to get started quickly.

If you plan to experiment with replication, the coverage in this chapter should be sufficient. If you plan to use replication for advanced high availability solutions, you should read this chapter and bolster it with a careful study of the replication chapters in the online reference manual.

Why use Replication?

There are many reasons to use replication. What I have described above—one master and a single slave—is the most basic building block for constructing topologies. This simple master–slave topology provides a redundant copy of the data on the slave, enabling you to keep a copy in case something happens to the master or if you want to separate your writes and reads for better application performance.

But there is far more that you can do. You can use the slave as a hot standby in case the master fails, and you can use the slave to run backup and maintenance operations that would normally require taking the server offline. In this case, you temporarily stop the slave from processing events (once conditions permit), take the backup, and then restart the slave. The slave would start reading events, starting with the next event from the master’s binary log.

Applications with many clients can see a dramatic improvement in read operations by using multiple slaves to permit simultaneous reads. This process, called scale-out, is a building block for high-availability solutions.

These are just a few of the uses of replication. Table 8-1 summarizes the major capabilities that can be realized with replication.

Table 8-1. The Many Uses of Replication

As you can surmise, replication places a bit more demand on resources. Happily, replication can run on any machine that supports MySQL. In larger installations, employing multiple tiers of masters and many slaves, masters are typically installed on machines with increased memory, networking connections, and faster disk systems. This is because of the high number of writes that are performed on masters (writes take more time than reads in most cases).

Slaves, on the other hand, are typically installed on machines optimized for read operations. In cases in which you may want to use a slave as a hot standby for the master, you would use the same hardware as the master. This makes changing roles less likely to experience performance issues.

This section presented a brief look at MySQL replication in its barest, simplest terms. For more in-depth coverage of replication and all of its nuances, see the online reference manual.

How Does Replication Achieve High Availability?

You may be wondering how replication relates to high availability, given that most think of high availability as being a state with very little downtime (other than short, deliberately planned events). Replication enables high availability by providing the capability to switch the role of master from the master to another, capable slave. We say capable because not all slaves may be suitable to take on the role of master. If the hardware for the slave is vastly different (slower) from the master, you would not want to choose that slave as the new master. There are many ways to use a save as a standby for the master, and several ways to switch the role. Generally, we consider there to be two methods of changing the role: switchover and failover.

If the master is healthy but you need to change the role because you need to perform maintenance on the master or something has happened on the master but not completely disabled it, we call this switchover, because you are switching the role from a master to a slave.

If the master has crashed or is otherwise offline, we have to choose a slave to make the new master. In this case, we call this failover, because a failure is the impetus for changing the role.

This is where high availability comes into play. If you have several slaves capable of taking over for the master, especially if there is a failure on the master, you can avoid potentially long downtime and, more important, loss of data, by switching the role of the master quickly to one of the slaves.

Some effort of late has been put into trying to achieve near zero downtime even in the event of complete loss of the master. Oracle has added new features to replication that enable administrators to set up automatic failover. This is achieved with a combination of a feature in the server called Global Transaction Identifiers (GTID) and a script from the MySQL Utilities suite (see below) called mysqlfailover. While an in-depth look at GTIDs and automatic failover is beyond the scope of this book, see the sidebar “What’s a GTID?” for an overview of the process.

WHAT’S A GTID?

GTIDs enable servers to assign a unique identifier to each set or group of events, thereby making it possible to know which events have been applied on each slave. To perform failover with GTIDs, one takes the best slave (the one with the fewest missing events and the hardware that matches the master best) and makes it a slave of every other slave. We call this slave the candidate slave. The GTID mechanism will ensure that only those events that have not been executed on the candidate slave are applied. In this way, the candidate slave becomes the most up-to-date, and therefore a replacement for the master.

The mysqlfailover command-line tool monitors the original master and performs automatic failover by executing the above sequence of events and takes care of redirecting the remaining slaves to the new master.

Thus, GTIDs make replication even more capable of providing high-availability database solutions. For more information about GTIDs and using the automatic solution with MySQL Utilities, visit:

http://dev.mysql.com/doc/refman/5.6/en/replication-gtids.html

http://dev.mysql.com/doc/workbench/en/mysqlfailover.html

http://drcharlesbell.blogspot.com/2012/04/mysql-utilities-and-global-transaction.html

Before GTIDs, replication events executed on the slave and stored on the slave’s local binary log would be reapplied and, therefore, cause errors if a companion slave that had not read and executed those events were to attempt to set up replication. While this may sound a little strange, the online reference manual discusses this topic in much greater detail.

As you can tell, you can accomplish a lot with replication. What I have discussed thus far is really just the basics. You can find more in-depth information about replication in the online reference manual. If you would like to explore the advanced features of replication and configure your systems for maximum high availability, I recommend my book MySQL High Availability, published by O’Reilly Media.

In the next section, I demonstrate how to set up replication, and I discuss some of the general principles and commands needed to establish a replication topology.

Basic Replication Setup

You may be thinking that replication may be difficult to setup or complicated. Neither is the case. Replication is very easy to set up, and the commands are few. Furthermore, replication is very stable when set up correctly and very rarely has issues unrelated to data configuration, user-induced problems (e.g. an accidental DROP TABLE), or data corruption. Nevertheless, Oracle is constantly improving replication with additional features for more reliable replication, such as checking for data corruption during transmission using checksums.

In this section, I present the requirements for replication, explain the commands used in setting up replication, and present the standard method for establishing replication between a master and a slave.

Requirements for Replication

In order to set up replication, you need at least two servers: one to be the master for the original data where applications apply changes (the master), and one to be location where your applications read the data (the slave).

The most restrictive requirement is that each server in the topology must have a unique server identifier. This can be set in the option file with server-id=N, as a command line with --server-id=N or set with the SET GLOBAL server_id=N SQL command. You can also use an alternative form of the command; SET @@GLOBAL.server_id=N.

The master must have binary logging enabled. The easiest way to do this is to use the --log-bin option. This option allows you to specify a path and file name to use for the binary log. Include just the file name and no extension. The replication system will append an extension made up of six digits representing the sequence number of the binary log. This number is increased each time the binary logs are rotated.

Rotation of the binary log (and the relay log) can be accomplished using the FLUSH BINARY LOGS command. When this command is issued, the current file is closed (after transactions complete), and a new file is created with an incremented sequence number.

You can find an example of how to turn on binary logging in the standard my.cnf (my.ini for Windows) file. I include an example below that shows turning on binary logging and setting the binary log format.

# Uncomment the following if you want to log updates
log-bin=mysql-bin

# binary logging format - mixed recommended
binlog_format=mixed

Rotation means that the logs are flushed (cached events are written to disk) and closed, and a new log file is opened. Rotating the binary logs can be accomplished manually using the command:

mysql> FLUSH BINARY LOGS;

You also need to set up a special user that has the privilege to replicate events. More specifically, it is used to read events from the master’s binary log. You can do this using the following command on the master. You need only run this command once. This user and password are used in a special command on the slave to connect to the master.

mysql> GRANT REPLICATION SLAVE ON *.* TO 'rpl'@'%' IDENTIFIED BY 'secret';

You also need what are called the coordinates of the master’s binary log. These include the name of the current binary log and the position of the latest event, discovered through the use of the SHOW MASTER STATUS command, as shown below.

mysql> SHOW MASTER STATUS;
+−−----------------+−−--------+−−------------+−−----------------+−−------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+−−----------------+−−--------+−−------------+−−----------------+−−------------------+
| mysql-bin.000001 | 245 | | | |
+−−----------------+−−--------+−−------------+−−----------------+−−------------------+
1 row in set (0.00 sec)

Notice the columns displayed in this view. You can see the current binary log file and the current position. There are also two columns that display any binary log filters in action. The use of binary log filters is discouraged, because when activated, the events that do not pass through the filter are not written to the binary log. See the online reference manual for more information about binary log filters and replication filters on the slave.

Last, if you have any data on the master, copy the data in their current state to the slave; this is normally done via a backup and restore process. If binary logging is turned on, you need to lock the tables while you make a copy so that no events get written to the binary log while you are copying the data. If you use the InnoDB storage engine exclusively, you can use a consistent read lock to lock the tables but still permit reads. The basic process for copying data from a master that contains data to a slave is:.

1. Lock tables on the master with FLUSH TABLES WITH READ LOCK.

2. Copy the data. You can use any method you wish. For small amounts of data, you can use mysqldbexport (from MySQL Utilities) or the mysqldump client application.

3. Record the masters log file and position with SHOW MASTER STATUS.

4. Unlock the tables on the master with UNLOCK TABLES.

5. Import the data to the slave. For example, use mysqldbimport to import the file generated by msyqldbexport or read the mysqldump output via the source command in the mysql client.

6. Start replication using the values from (3).

Now that I have explained the requirements for setting up replication, let’s see how we can configure a master and slave. The following sections assume that you have two servers of compatible hardware with MySQL installed. The examples are not using GTIDs, but I make note of the differences below.

It is also assumed that you have copied any existing data from the server to be configured as the master to the server to be configured as a slave and that the master is not experiencing writes (changes to data) either by locking the tables or because there are no clients connected. This is important, because having writes occurring while you are establishing replication could mean you choose the wrong master log file and position (coordinates) for the slave.

Configuring the Master

If the server is running, check to see if it has binary logging turned on. Execute a SHOW VARIABLES LIKE ‘log_bin’. You should see a similar result as:

mysql> SHOW VARIABLES LIKE 'log_bin';
+−−---------------------------------------+−−--------------------+
| Variable_name | Value |
+−−---------------------------------------+−−--------------------+
| log_bin | ON |
+−−---------------------------------------+−−--------------------+
1 rows in set (0.00 sec)

Notice the value for the variable log_bin. In this example, it is turned on (ON). If this value is OFF for your server, shut down the server and turn binary logging on, either via the command line, if you started your server this way, or via the option file. It is best for servers that you intend to use for applications to place the setting in the configuration file.

To set the variable via the command line, add the following options when starting your server. The first option tells the server to use row format for the events (this is optional but recommended), the second is to turn on binary logging and to use mysql_bin as the file name (without extension), and the third is used to set a unique server_id. Be sure to check your slave to ensure it has a different value for server_id.

--binlog-format=row --log-bin=mysql_bin --server-id=5

Note You can set the server_id dynamically, but this will not save the value to the option file. Thus, when the server is restarted, the value reverts to the default or as read from the option file.

To set the variables in the configuration file, open the configuration file named my.cnf (or whatever you named your file) and add the following lines. For installations of MySQL that use predefined configuration files you may see these entries commented out. Just uncomment them in that case. Place these values in the [mysqld] section.

binlog_format=row
log_bin=mysql_bin
server_id = 5

Note Be sure to locate the option file that your server is using. This is normally found in /etc./my.cnf, /etc./mysql/my.cnf, /usr/local/mysql/etc./my.cnf, or ∼/.my.cnf for Mac, Linux, and Unix systems. Windows systems may name the file my.ini, and it is normally located in c:\windows\my.ini, c:\my.ini, <installation directory>\my.ini, or <application data for user>\.mylogin.cnf. Consult the online reference manual section “Using Option Files” for more information.

Once the changes are made to the configuration file, restart your server and check again to ensure that binary logging is turned on.

To permit a slave to connect to the master and read events, you must define a replication and issue the appropriate permissions. This user must have the REPLICATION SLAVE privilege and can be created with the GRANT statement as follows. Be sure to set the host name according to your domain or use IP addresses. Also, set the password according to your information-security policies.

mysql> GRANT REPLICATION SLAVE ON *.* TO 'rpl'@'%.mydomain.com' IDENTIFIED BY 'secret';

We will use these credentials later when we connect the slave to the master and start replication.

Tip If you have configured your server to not allow automatic creation of user accounts with the GRANT statement, you must issue a CREATE USER command before the GRANT command.

One more thing you need to do on the master is to discover the name of the binary log file and its current position. If you have not done so already or have not taken steps to ensure there are no writes occurring on the master, lock the tables as shown below.

mysql> FLUSH TABLES WITH READ LOCK;

Once you are sure there are no more writes occurring, you can use the SHOW MASTER STATUS command as shown below. We will use this information when we connect the slave to the master.

mysql> SHOW MASTER STATUS;
+−−----------------+−−--------+−−------------+−−----------------+−−------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+−−----------------+−−--------+−−------------+−−----------------+−−------------------+
| mysql-bin.000152 | 243 | | | |
+−−----------------+−−--------+−−------------+−−----------------+−−------------------+
1 row in set (0.00 sec)

Notice in this example that the binary log has been incremented a number of times. This is not uncommon for a server that has been running for an extended period. If you are following along and setting up a new master, you are likely to see a much lower value for the binary log number and possibly a smaller value for the position. This is perfectly normal. Just record the values for use later.

If you locked the tables before issuing the SHOW MASTER STATUS command, you can now unlock them with the command:

mysql> UNLOCK TABLES;

Note If you are using GTIDs, you do not need to know the coordinates of the master. The GTID feature will automatically resolve the starting location in the binary log for you.

Configuring the Slave

Configuring the slave is a bit easier. You only need to set the server_id for the slave. As with the master, you can set this value via the command line if you start your server that way (−−server-id=7), but the best method is to change the value in the configuration file. Open the option file for the slave and add or uncomment the following. Place these values in the [mysqld] section.

[mysqld]
...
server_id = 7

Connecting the Slave to the Master

Now that you have configured your master with binary logging and set the server_id for both servers to a unique value, you are ready to connect the slave to the master. This requires two commands issued in order. The first is used to instruct the slave as to how to connect to which master and the second is to initiate the startup of the IO and SQL threads on the slave.

The CHANGE MASTER command makes the connection from the slave to the master. You can specify several options and forms of connections, including SSL connections. For this example, we use the most basic and essential options, and use normal MySQL authentication to connect to the slave. For more information about SSL connections and the many options, see the online reference manual.

The following is an example of the CHANGE MASTER command that would be used to connect a slave to a master using the information from the SHOW MASTER STATUS above. the values from your master when you set up your own master and slave.

mysql> CHANGE MASTER TO MASTER_HOST='localhost', MASTER_USER='rpl',
MASTER_PASSWORD='pass', MASTER_LOG_FILE='mysql_bin.000152',
MASTER_LOG_POS=243;

If you are using GTIDs, you can omit the master binary log coordinates and use a special option, as follows. This tells the servers to begin negotiating the starting transactions to be executed on the slave.

mysql> CHANGE MASTER TO MASTER_HOST='localhost', MASTER_USER='rpl',
MASTER_PASSWORD='pass', MASTER_AUTO_POSITION = 1;

The next command is used to start the threads on the slave and begin replicating events from the master.

mysql> START SLAVE;

A companion command, STOP SLAVE, stops the slave threads, thereby stopping replication. There is also a command, RESET SLAVE, for removing all replication files on the slave and resetting the slave connection information for the master. The reset command is rarely used in cases in which it is necessary to purge the connection information from the slave. An equivalent RESET MASTER command removes all binary log files and clears the binary log index.

Caution Be sure you really want to destroy the replication information (RESET SLAVE) or the binary log information (RESET MASTER) and that there are no slaves connected to the master (RESET MASTER). Inadvertently issuing these commands can cause your replication topologies to incur errors and replication of data to cease.

You may see warnings, or in some cases an error ,when issuing the START SLAVE command. Unfortunately, this isn’t always that informative. Savvy database administrators use a special command—the SHOW SLAVE STATUS command—to check the status of a slave that is similar in syntax to the SHOW MASTER command. This command will generate a very long (wide) view of a single row with lots of information about exactly what is going on inside the slave concerning replication. The best way to view this information is to show it in a vertical format (use \G).Listing 8-1 shows a typical output for a slave without errors. I highlight the more important attributes to check in bold.

Listing 8-1. SHOW SLAVE STATUS report

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State:

Waiting for master to send event
Master_Host: localhost
Master_User: rpl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:

my_log.000152
Read_Master_Log_Pos:

243
Relay_Log_File: clone-relay-bin.000002
Relay_Log_Pos: 248
Relay_Master_Log_File: my_log.000152
Slave_IO_Running:

Yes
Slave_SQL_Running:

Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 243
Relay_Log_Space: 403
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)

Get into the habit of always issuing this command when setting up replication. It will provide a wealth of information, including more detail about any problems and errors.

Next Steps

If you have issued all of the commands above and there were no errors either in the output of SHOW SLAVE STATUS or the commands themselves, congratulations! You have just set up your first replication topology.

If you want to test your replication setup, create a new database and table on the master and check the slave to ensure that these events were repeated there. Check the slave with SHOW SLAVE STATUS until it reports that it is waiting on the master for new events. Otherwise, you could be checking the slave before it has finished reading events from the relay log. Rather than show you this by example, I leave testing your replication setup as an exercise.

In the next section, I delve a little deeper into the most important enabling feature for replication—the binary log.

BUT WAIT, ISN’T THERE A BETTER WAY?

If you’ve read over the previous sections and performed the steps on your own servers, you may think that while the process is simple there are a lot of steps to perform and things to check. You may wonder why there isn’t a simple command to “just do it” for replication.

I have good news. There is such a command, but it is in the form of a Python utility that is bundled with MySQL Workbench. Called MySQL Utilities, it includes two very useful commands: mysqlreplicate, which automates the setup of a master and slave; andmysqlrplcheck, which checks the prerequisites of a master and slave either prior to or after replication is set up. I explain these and MySQL Utilities in more detail below.

The Binary Log

No discussion about replication would be complete without a detailed look at the binary log. In this section, we discover more about how the binary log works and how to read the binary log and relay log using an external client as well as a special SHOW command.

Some may believe the binary log is replication, but that is not entirely true. Any server, including one performing the role of a slave, can have binary logging enabled. In the case of a slave, the server would contain both a relay log and a binary log. This makes it possible for a slave that has other servers as its slaves to perform the role of master and slave (sometimes called an intermediate slave).

The binary log originally had another purpose. It can be used for recovery of data in the event of loss. For example, if a server has the binary log enabled, it is possible to replay the binary logs to recover data up to the point of loss. This is called point in time recovery, and it is made possible because the mysql client can read the events and execute them. You can either source the file or copy and paste individual events (or a range of events) into a mysql client. See the online reference manual for more information about point in time recovery.

Tip To discover all the variables used for binary logging, issue the command SHOW VARIABLES LIKE '%binlog%'. Similarly, to discover the variables used for the relay log, issue the command SHOW VARIABLES LIKE '%relay%'.

As mentioned previously, the name of the binary log can be set by the user with the --log-bin option, with which you specify a file name to be used for all binary log files. When a binary log is rotated, a new file with an incremented six-digit number is appended as the file extension. There is also an index file with the name specified for the binary log and the extension .index. This file maintains the current binary-log file. The server uses this file at startup to know where to begin appending events. Thus, the binary log is a collection of files rather than a single file. Whenever you perform maintenance or wish to archive the binary log (or relay log), include all the files associated, including the index file.

Tip You can change the name of the relay log with the --relay-log startup option.

The binary log is the mechanism that makes replication possible and the place that administrators focus on when things go wonky. But first, let’s examine the format of the binary log.

Row Formats

The binary log is a sequential file in which events (changes to the data) are written to the end of the file. A file position is used to determine the offset into the file for the next binary-log event. The server therefore maintains the name of the current binary log and the next position pointer (as seen in SHOW MASTER STATUS above). In this way, the server can know where to write the next event.

While somewhat of a misnomer, the binary log can be considered one of two primary formats: statement based or row based. There is also a hybrid version called mixed format. While this may sound like the file is formatted differently, it is the events themselves that the format refers. The binary-log file itself is still a sequential file, regardless of the format of the events, and it is written and read using the simple concept of file name plus offset. The different formats are:

· Statement-based replication (SBR)—the events contain the actual SQL statements that were executed on the master. These are packaged without change, shipped to the slave, and executed there.

· Row-based replication (RBR)—the events contain the resulting binary row after the changes. This permits the slave to simply apply the event to the row rather than executing the statement via the SQL interface.

· Mixed format—this combination of SBR and RBR is governed by the storage engine and other elements of the server, as well as the type of command being executed. Mixed format uses SBR by default. For example, if the storage engine supports RBR, the event will be in RBR format. Similarly, if there is a reason to not use RBR, SBR will be used for that event instead.

See the online reference manual for a complete list of which commands force SBR versus RBR format for the event.

The mysqlbinlog Client

All MySQL installations include a special client for reading the binary log. The client is convincingly named mysqlbinlog. The purpose of the client is to display the contents of the binary log in a human-readable form. For SBR format, the actual query is included in the payload of the event, thereby making it easy to read. RBR format events are in binary form. For RBR events, the client will display any information that can be made human readable, displaying the row format in ASCII form.

The mysqlbinlog client has a number of options for controlling the output. You can display the output in hexadecimal format, skip the first N events, display events in a range of positions, or use many more options. To read a range of events in the binary log, you can specify a start datetime and end datetime or a start and a stop position. Perhaps the most powerful feature for the client is the ability to connect to a remote server and read its binary log. This feature makes managing multiple servers easier.

Listing 8-2 shows an example of running the mysqlbinlog client against a typical binary log file. In this case, the file is from a master that has had its logs rotated once. We can tell this by the sequence number used for the binary-log file extension. This value is incremented each time the log is rotated. Recall that this process is initiated by FLUSH LOGS and results in the existing binary-log file being closed, a new file opened, and a new header written to the new file.

Listing 8-2. The mysqlbinlog Client Example Output

$ mysqlbinlog /usr/local/mysql/data/mysql-bin.000002
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#121016 20:50:47 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.6.7-log created 121016 20:50:47 at startup
ROLLBACK/*!*/;
BINLOG '
5wB+UA8BAAAAZwAAAGsAAAABAAQANS41LjIzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADnAH5QEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#121016 20:54:23 server id 1 end_log_pos 198 Query thread_id=108 exec_time=0 error_code=0
SET TIMESTAMP=1350435263/*!*/;
SET @@session.pseudo_thread_id=108/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
CREATE DATABASE example1
/*!*/;
# at 198
#121016 20:54:42 server id 1 end_log_pos 303 Query thread_id=108 exec_time=0 error_code=0
SET TIMESTAMP=1350435282/*!*/;
CREATE TABLE example1.t1(a int, b varchar(20))
/*!*/;
# at 303
#121016 20:55:05 server id 1 end_log_pos 367 Query thread_id=108 exec_time=0 error_code=0
SET TIMESTAMP=1350435305/*!*/;
BEGIN
/*!*/;
# at 367
#121016 20:55:05 server id 1 end_log_pos 493 Query thread_id=108 exec_time=0 error_code=0
SET TIMESTAMP=1350435305/*!*/;
insert into example1.t1 values (1, 'one'), (2, 'two'), (3, 'three')
/*!*/;
# at 493
#121016 20:55:05 server id 1 end_log_pos 520 Xid = 141
COMMIT/*!*/;
# at 520
#121016 20:55:17 server id 1 end_log_pos 584 Query thread_id=108 exec_time=0 error_code=0
SET TIMESTAMP=1350435317/*!*/;
BEGIN
/*!*/;
# at 584
#121016 20:55:17 server id 1 end_log_pos 682 Query thread_id=108 exec_time=0 error_code=0
SET TIMESTAMP=1350435317/*!*/;
DELETE FROM example1.t1 where b = 'two'
/*!*/;
# at 682
#121016 20:55:17 server id 1 end_log_pos 709 Xid = 148
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

Notice that in the output we see a lot of metadata about each event as well as a header with information about the binary-log file. Also, notice that each event (SBR format in this example) shows the log position as well as the thread id and other pertinent information. The timestamp entries are a special form of event that the server uses to maintain timestamp data in the log (and hence on the slave).

Tip The binary log and the relay log have the same layout, and therefore both can be read by the mysqlbinlog client.

Reading events from the binary or relay log using the mysqlbinlog client is powerful, but there is also a convenient SHOW command that shows the events in a tabular form.

SHOW BINLOG EVENTS Command

The MySQL server contains a special SHOW command, SHOW BINLOG EVENTS, that permits you to see the latest events located in the binary log. Let’s see this command in action. Listing 8-3 shows the results of the SHOW BINLOG EVENTS run on the same server that was used for the mysqlbinlog example in the previous section. I use the vertical format to make it easier to read.

Listing 8-3. SHOW BINLOG EVENTS Example 1

cbell$ mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 508
Server version: 5.6.7-m9 MySQL Community Server (GPL)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW BINLOG EVENTS \G
*************************** 1. row ***************************
Log_name: mysql-bin.000001
Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 107
Info: Server ver: 5.6.7-m9, Binlog ver: 4
*************************** 2. row ***************************
Log_name: mysql-bin.000001
Pos: 107
Event_type: Query
Server_id: 1
End_log_pos: 245
Info: SET PASSWORD FOR 'root'@'localhost'='*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B'
*************************** 3. row ***************************
Log_name: mysql-bin.000001
Pos: 245
Event_type: Stop
Server_id: 1
End_log_pos: 264
Info:
3 rows in set (0.00 sec)

You may be wondering what happened to the events from the previous example. This reveals an often mistaken assumption on the part of users new to binary logging. The SHOW BINLOG EVENTS command displays events from the first binary log by default. In the previous section, I used the second binary log. To see events from a binary log other than the first, use the IN clause, as shown below. I use the vertical format to make it easier to read.

Listing 8-4. SHOW BINLOG EVENTS Example 2

mysql> SHOW BINLOG EVENTS IN 'mysql-bin.000002' \G
*************************** 1. row ***************************
Log_name: mysql-bin.000002
Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 107
Info: Server ver: 5.6.7-m9, Binlog ver: 4
*************************** 2. row ***************************
Log_name: mysql-bin.000002
Pos: 107
Event_type: Query
Server_id: 1
End_log_pos: 198
Info: CREATE DATABASE example1
*************************** 3. row ***************************
Log_name: mysql-bin.000002
Pos: 198
Event_type: Query
Server_id: 1
End_log_pos: 303
Info: CREATE TABLE example1.t1(a int, b varchar(20))
*************************** 4. row ***************************
Log_name: mysql-bin.000002
Pos: 303
Event_type: Query
Server_id: 1
End_log_pos: 367
Info: BEGIN
*************************** 5. row ***************************
Log_name: mysql-bin.000002
Pos: 367
Event_type: Query
Server_id: 1
End_log_pos: 493
Info: insert into example1.t1 values (1, 'one'), (2, 'two'), (3, 'three')
*************************** 6. row ***************************
Log_name: mysql-bin.000002
Pos: 493
Event_type: Xid
Server_id: 1
End_log_pos: 520
Info: COMMIT /* xid=141 */
*************************** 7. row ***************************
Log_name: mysql-bin.000002
Pos: 520
Event_type: Query
Server_id: 1
End_log_pos: 584
Info: BEGIN
*************************** 8. row ***************************
Log_name: mysql-bin.000002
Pos: 584
Event_type: Query
Server_id: 1
End_log_pos: 682
Info: DELETE FROM example1.t1 where b = 'two'
*************************** 9. row ***************************
Log_name: mysql-bin.000002
Pos: 682
Event_type: Xid
Server_id: 1
End_log_pos: 709
Info: COMMIT /* xid=148 */
9 rows in set (0.00 sec)

mysql>

The command displays the log name, the starting and ending position of the event in the log (file offset), the event type, and the payload (the query for SBR events).

It may seem as if these tools—the msyqlbinlog client and the SHOW BINLOG EVENTS command—are like a limited set, but the truth is that they are the key to becoming a good administrator of servers that run binary logging and replication topologies. Make learning these tools a priority. The following lists several resources (in addition to the online reference manual) that you can use to learn more about binary logging and replication of events.

Additional Resources

Some rather obscure resources may be of interest to anyone wanting to know more about the details of the binary log and its format. Of course, the online reference manual has considerable documentation and should be your primary source, but the following contain some key information not found in other sources.

· http://dev.mysql.com/doc/internals/en/index.html

· http://dev.mysql.com/doc/internals/en/replication-protocol.html

· http://dev.mysql.com/doc/internals/en/row-based-replication.html

In the next sections, I dive into the finer points of replication, starting with an overview of the replication architecture, followed by a brief tour of the replication source code.

Replication Architecture

The replication source code is quite large, and indeed, it is one of the largest portions of the server code. Since replication permeates so many levels of the server code, and it has grown over the years, it is sometimes difficult to see how it fits into the other portions of the server.

It is helpful, therefore, to take a moment and look at a high-level view of the architecture itself to see how the parts work within replication. This is also a good way to introduce the major sections of the replication source code. Figure 8-1 shows a simplified block diagram of the replication architecture. The sections following the figure explain the components in more detail.

Figure 8-1. Replication Architecture

The numbered arrows in the figure above show the simplified sequence of events that describe the replication architecture. First, the master writes an event to the binary log. Second, the slave’s IO_THREAD reads the event from the master’s binary log via a dedicate network connection and writes it to its relay log. Third, the slave’s SQL_THREAD reads the event from the relay log and applies (executes) it to the database.

Lets slow that down a bit and examine the sequence in more detail. What follows is a generalization of the code sequence. Some events follow a slightly different path in the master, but generally, this is how events are shipped to the slave.

When a user or application issues a SQL statement on the master that is of the type that is written to the binary log (a SHOW command is one that is not recorded), the server code calls the code for writing to the binary log in binlog.cc, which in turn calls code in rpl_master.cc to create an instance of a log-event class. When written to the binary log, the pack_info() method of the log-event class is called to format a block of data for storing in the binary log. Of particular interest here is that the binary-log-class updates its log position so that the server and the user know the location of the next event in the binary-log file.

When the slave requests more events from the master, via the code in rpl_slave.cc, it connects to the master and issues a special command, COM_BINLOG_DUMP, which is executed via the big switch in sql_parse.cc and results in the code in the rpl_master.cc sending any events in the binary log since the last position read from the slave. As these events are read, they are written to the slave’s binary-log file via the code in rpl_slave.cc. This process is executed by the slave via the IO thread. Meanwhile, the slave has another thread, the SQL thread, which reads events from the relay log and executes them. This code is also inside rpl_slave.cc.

While this explanation leaves many of the details out, it is an accurate description of how the replication architecture achieves its goals. Many lines of code are involved in this process, so to generalize it as simple would be a gross understatement. The replication source code is far from simplistic, yet it has an elegance of execution that belies its complexity.

In the next section, I give you a brief tour of the replication source code. Because of the large number of elements that make up the replication feature, I concentrate on one of the key areas that will help you understand what makes replication work—the log events.

A Brief Tour of the Replication Source Code

As I mentioned earlier, the replication source touches many pieces of the server code. Consider for a moment all that is involved with recording an event in the binary log. Clearly, every command we wish to replicate must make calls to the replication code (in this case, the binary-logging code) to initiate the event and record it in the binary log. Shipping that event to the slave and executing it there introduces code that places the event in the server for execution—either by executing the query (SBR) or by applying the resulting row (RBR).

In the following sections, I take you on a tour of the replication source code, concentrating on the code surrounding the log events. Before we take that journey, let us take a look at the source-code files for replication.

Replication Source Code Files

Furthermore, more than 48 separate source-code files comprise the replication source code. As you can imagine, this is a huge amount of code, and as a result, it is difficult to remember (or learn) what all the files do and where each piece of code resides.

I recently interviewed a number of developers (who will remain nameless) and discovered an interesting trend. While some were knowledgeable about most of the source files and what they do, even the most experienced had to search their memories to recall what each file contains. It is safe to say that only the Oracle replication developers themselves can be considered experts in this area.

What I present here is a private tour of one of the most important aspects of the replication code. A complete tour of all of the code and what each class and method does would consume an entire book and would require the coordinated efforts of the author and the replication developers.

The good news here is that I have arranged this tour to provide you with a working knowledge of what log events are, how they are coded, where they are coded, and how they work to enable replication.

Before we journey down that thorny path, read through Table 8-2 for an overview of the 48 replication source files and what each contains. I am sure you will agree it is a formidable list. Most of these files are in the /sql folder of the source tree.

Table 8-2. List of Replication Source Files

The main files you will be working in are the log_event.h/.cc files. These contain all of the code for the log events. I explain the major log-event classes in the next section. Also of interest to study are the rpl_master.h/.cc files. This is where the code for the master role resides, including code to write events to the binary log. If you are working on a solution to extend replication for the master role, begin your research in these files. You may also want to examine the rpl_slave.h/.cc files. This is where the code resides for executing events on the slave. When working on extensions to the slave role, begin your research in these files. Now lets get on with the tour of the log events.

Log Events Explained

We begin our tour by examining the base class for all log events - Log_event. This class contains all of the methods and attributes needed to store, ship, and execute events via the binary log and relay log. Open the file /sql/log_event.h and scroll down to around line 962 to the start of the Log_event class. Listing 8-5 shows an excerpt of the log-event class in the log_event.h file.

I omit some details to make it easier to read. There is a lot of documentation inside the code from an explanation of the enumerations to a description of the byte layout for a log event. If you are interested in more details about these and similar details, see the excellent documentation in the source code. Take a moment to look through this code. Later in this chapter, I explain some of the details about the key methods one would use when creating your own log event class. As you can see from the listing above, this class is quite complex.

Note There are two log_event* header and class files. The log_event_old* files are those log-event classes that are for older formats of the RBR-event format. We focus on the newer log-event format in this chapter. You are free to examine the older format as an exercise.

Listing 8-5. Log_event Class Declaration

class Log_event
{
public:
enum enum_skip_reason {
EVENT_SKIP_NOT,
EVENT_SKIP_IGNORE,
EVENT_SKIP_COUNT
};

protected:
enum enum_event_cache_type
{
EVENT_INVALID_CACHE= 0,
EVENT_STMT_CACHE,
EVENT_TRANSACTIONAL_CACHE,
EVENT_NO_CACHE,
EVENT_CACHE_COUNT
};

enum enum_event_logging_type
{
EVENT_INVALID_LOGGING= 0,
EVENT_NORMAL_LOGGING,
EVENT_IMMEDIATE_LOGGING,
EVENT_CACHE_LOGGING_COUNT
};

public:
typedef unsigned char Byte;
my_off_t log_pos;
char *temp_buf;
struct timeval when;
ulong exec_time;
ulong data_written;
uint32 server_id;
uint32 unmasked_server_id;
uint16 flags;
ulong slave_exec_mode;
enum_event_cache_type event_cache_type;
enum_event_logging_type event_logging_type;
ha_checksum crc;
ulong mts_group_idx;
Relay_log_info *worker;
ulonglong future_event_relay_log_pos;

#ifdef MYSQL_SERVER
THD* thd;
db_worker_hash_entry *mts_assigned_partitions[MAX_DBS_IN_EVENT_MTS];
Log_event(enum_event_cache_type cache_type_arg= EVENT_INVALID_CACHE,
enum_event_logging_type logging_type_arg= EVENT_INVALID_LOGGING);
Log_event(THD* thd_arg, uint16 flags_arg,
enum_event_cache_type cache_type_arg,
enum_event_logging_type logging_type_arg);
static Log_event* read_log_event(IO_CACHE* file,
mysql_mutex_t* log_lock,
const Format_description_log_event
*description_event,
my_bool crc_check);
static int read_log_event(IO_CACHE* file, String* packet,
mysql_mutex_t* log_lock, uint8 checksum_alg_arg);

static void init_show_field_list(List<Item>* field_list);
#ifdef HAVE_REPLICATION
int net_send(Protocol *protocol, const char* log_name, my_off_t pos);

virtual int pack_info(Protocol *protocol);

#endif /* HAVE_REPLICATION */
virtual const char* get_db()
{
return thd ? thd->db : 0;
}
#else // ifdef MYSQL_SERVER
Log_event(enum_event_cache_type cache_type_arg= EVENT_INVALID_CACHE,
enum_event_logging_type logging_type_arg= EVENT_INVALID_LOGGING)
: temp_buf(0), event_cache_type(cache_type_arg),
event_logging_type(logging_type_arg)
{ }
/* avoid having to link mysqlbinlog against libpthread */
static Log_event* read_log_event(IO_CACHE* file,
const Format_description_log_event
*description_event, my_bool crc_check);
/* print*() functions are used by mysqlbinlog */
virtual void print(FILE* file, PRINT_EVENT_INFO* print_event_info) = 0;
void print_timestamp(IO_CACHE* file, time_t* ts);
void print_header(IO_CACHE* file, PRINT_EVENT_INFO* print_event_info,
bool is_more);
void print_base64(IO_CACHE* file, PRINT_EVENT_INFO* print_event_info,
bool is_more);
#endif // ifdef MYSQL_SERVER ... else
uint8 checksum_alg;

static void *operator new(size_t size)
{
return (void*) my_malloc((uint)size, MYF(MY_WME|MY_FAE));
}

static void operator delete(void *ptr, size_t)
{
my_free(ptr);
}

static void *operator new(size_t, void* ptr) { return ptr; }
static void operator delete(void*, void*) { }
bool wrapper_my_b_safe_write(IO_CACHE* file, const uchar* buf,
ulong data_length);

#ifdef MYSQL_SERVER
bool write_header(IO_CACHE* file, ulong data_length);
bool write_footer(IO_CACHE* file);
my_bool need_checksum();

virtual bool write(IO_CACHE* file)
{
return(write_header(file, get_data_size()) ||
write_data_header(file) ||
write_data_body(file) ||
write_footer(file));
}
virtual bool write_data_header(IO_CACHE* file)
{ return 0; }
virtual bool write_data_body(IO_CACHE* file __attribute__((unused)))
{ return 0; }
inline time_t get_time()
{
if (!when.tv_sec && !when.tv_usec) /* Not previously initialized */
{
THD *tmp_thd= thd ? thd : current_thd;
if (tmp_thd)
when= tmp_thd->start_time;
else
my_micro_time_to_timeval(my_micro_time(), &when);
}
return (time_t) when.tv_sec;
}
#endif
virtual Log_event_type get_type_code() = 0;
virtual bool is_valid() const = 0;
void set_artificial_event() { flags |= LOG_EVENT_ARTIFICIAL_F; }
void set_relay_log_event() { flags |= LOG_EVENT_RELAY_LOG_F; }
bool is_artificial_event() const { return flags & LOG_EVENT_ARTIFICIAL_F; }
bool is_relay_log_event() const { return flags & LOG_EVENT_RELAY_LOG_F; }
bool is_ignorable_event() const { return flags & LOG_EVENT_IGNORABLE_F; }
bool is_no_filter_event() const { return flags & LOG_EVENT_NO_FILTER_F; }
inline bool is_using_trans_cache() const
{
return (event_cache_type == EVENT_TRANSACTIONAL_CACHE);
}
inline bool is_using_stmt_cache() const
{
return(event_cache_type == EVENT_STMT_CACHE);
}
inline bool is_using_immediate_logging() const
{
return(event_logging_type == EVENT_IMMEDIATE_LOGGING);
}
Log_event(const char* buf, const Format_description_log_event
*description_event);
virtual ∼Log_event() { free_temp_buf();}
void register_temp_buf(char* buf) { temp_buf = buf; }
void free_temp_buf()
{
if (temp_buf)
{
my_free(temp_buf);
temp_buf = 0;
}
}
virtual int get_data_size() { return 0;}
static Log_event* read_log_event(const char* buf, uint event_len,
const char **error,
const Format_description_log_event
*description_event, my_bool crc_check);
static const char* get_type_str(Log_event_type type);
const char* get_type_str();

#if defined(MYSQL_SERVER) && defined(HAVE_REPLICATION)

private:

enum enum_mts_event_exec_mode
{
EVENT_EXEC_PARALLEL,
EVENT_EXEC_ASYNC,
EVENT_EXEC_SYNC,
EVENT_EXEC_CAN_NOT
};

bool is_mts_sequential_exec()
{
return
get_type_code() == START_EVENT_V3 ||
get_type_code() == STOP_EVENT ||
get_type_code() == ROTATE_EVENT ||
get_type_code() == LOAD_EVENT ||
get_type_code() == SLAVE_EVENT ||
get_type_code() == CREATE_FILE_EVENT ||
get_type_code() == DELETE_FILE_EVENT ||
get_type_code() == NEW_LOAD_EVENT ||
get_type_code() == EXEC_LOAD_EVENT ||
get_type_code() == FORMAT_DESCRIPTION_EVENT||

get_type_code() == INCIDENT_EVENT;
}

enum enum_mts_event_exec_mode get_mts_execution_mode(ulong slave_server_id,
bool mts_in_group)
{
if ((get_type_code() == FORMAT_DESCRIPTION_EVENT &&
((server_id == (uint32) ::server_id) || (log_pos == 0))) ||
(get_type_code() == ROTATE_EVENT &&
((server_id == (uint32) ::server_id) ||
(log_pos == 0 /* very first fake Rotate (R_f) */
&& mts_in_group /* ignored event turned into R_f at slave stop */))))
return EVENT_EXEC_ASYNC;
else if (is_mts_sequential_exec())
return EVENT_EXEC_SYNC;
else
return EVENT_EXEC_PARALLEL;
}

Slave_worker *get_slave_worker(Relay_log_info *rli);

virtual List<char>* get_mts_dbs(MEM_ROOT *mem_root)
{
List<char> *res= new List<char>;
res->push_back(strdup_root(mem_root, get_db()));
return res;
}

virtual void set_mts_isolate_group()
{
DBUG_ASSERT(ends_group() ||
get_type_code() == QUERY_EVENT ||
get_type_code() == EXEC_LOAD_EVENT ||
get_type_code() == EXECUTE_LOAD_QUERY_EVENT);
flags |= LOG_EVENT_MTS_ISOLATE_F;
}

public:

bool contains_partition_info(bool);
virtual uint8 mts_number_dbs() { return 1; }
bool is_mts_group_isolated() { return flags & LOG_EVENT_MTS_ISOLATE_F; }
virtual bool starts_group() { return FALSE; }
virtual bool ends_group() { return FALSE; }
int apply_event(Relay_log_info *rli);
int update_pos(Relay_log_info *rli)
{
return do_update_pos(rli);
}
enum_skip_reason shall_skip(Relay_log_info *rli)
{
return do_shall_skip(rli);
}
virtual int do_apply_event(Relay_log_info const *rli)
{
return 0; /* Default implementation does nothing */
}
virtual int do_apply_event_worker(Slave_worker *w);

protected:

enum_skip_reason continue_group(Relay_log_info *rli);
virtual int do_update_pos(Relay_log_info *rli);
virtual enum_skip_reason do_shall_skip(Relay_log_info *rli);
#endif
};

First, notice the conditional compilation directives in this code. These are necessary because other portions of the greater MySQL source code use the log-event class. For example, the mysqlbinlog client application will compile against this code. Thus, there are portions marked specifically for use in a server (MYSQL_SERVER), portions marked for elimination if replication is not used (HAVE_REPLICATION).³ Those sections where these directives are not met (the #else) are used when compiling the external code such as mysqlbinlog.

The class includes many helper functions, such as methods for getting the database for the event, creating and destroying an instance (new, delete), and more. Some of the more interesting helper methods are the write_*() methods. These are used to write the event (also known as serialization) to the binary log in conjunction with the pack_info() method that is responsible for embedding the payload the event.

Similarly, there are methods for reading the event from the binary log file. Notice the reoccurrence of the read_event() method. Several of these are located in this class. This is because, depending on where or how you are reading the event, you may need different forms of this method. Thus, this method is overloaded, and which is called depends on the context of the parameters. The one we are most interested in is the version that returns an instance of the event once it is read from the binary log. We will see this in action a little later in this section.

Last, the server uses the do_apply_event() to execute the event. Each log-event type has a specific implementation of this method—hence the need to make it and a number of other methods virtual.

Notice the other methods that are marked as virtual. When you examine the different log events, you will see the class implementation of those events is a lot smaller and generally includes only those methods marked as virtual here.

In the next section, I describe some types of log events and what each is designed to perform. This is followed by a brief look at the execution path of log events.

Types of Log Events

There are more than 30 log events defined for replicating commands from the master to the slave. It is likely that if you examine your binary logs or relay logs, you will not find all of these events. Indeed, some classes are defined for a specific format (SBR or RBR), and unless you used mixed binary-log format, you will not find both classes of events.

The basic staple of SBR events is the Query_log_event. This contains the SQL statement issued by the user on the master. When this event is applied, the SQL statement is executed as written. Any variables used by the query—including user-defined variables, random number, or time-related values—are written to the binary log prior to the Query_log_event and therefore executed on the slave before the query itself. This is how replication keeps items such as timestamps, random numbers, incremental columns, and similar special events or codes the same on the master and slave. For example, in the case of random numbers, the seed that was used on the master is transmitted to the slave, resulting in the RAND function returning the same values on the slave as the master.

The events for RBR are a little different. These use a base class, Rows_log_event to encapsulate the base functionality for all RBR events. Because the RBR events contain only the row images or the results of applying a query on the master, there are unique log events for each type. There are log events for inserts, updates (with a before image event), and deletes.

Of special note is the Incident_log_event. This event stores any unusual condition or state encountered on the master, such as an error generating an event, or another error, or warnings, ranging from minor to serious. This event was created so that should something extraordinary occur on the master, the slave, when it applies (executes) the event, can decide if the incident is serious enough to warrant stopping replication. When troubleshooting replication, examine the incident events for more details.

Table 8-3 shows a list of the more frequently encountered log events. I include a description of each along with the binary log format used where these events are likely to appear.

Table 8-3. Important Log Event Types

In the upcoming sections, we will be working with a new log event designed to embed information in the binary log for diagnostic purposes. This event is not pertinent to the slave, so we will create an event similar to the Ignorable_log_event.

Now that we have seen some of the more important event types, let us examine how events are executed on the slave.

Execution of Log Events

Log events are executed by the slave by first reading them from the relay log and instantiating the class instance. This is where the type of log event is paramount. The slave must know what type of log event it is reading. As you will see when we explore extending replication, each log event is assigned a special enumeration (code). This is how the slave knows which class to instantiate. The code that reads log events and instantiates them is located in rpl_slave.cc in the Log_event::next_event() method.

The method contains an endless loop whose job is to read an event, check for prerequisites, errors, and special commands to manage the relay log (such as purge and rotate), and instantiate the event.

Events are instantiated via the Log_event::read_log_event() method. This method will also conduct error checking (like checksum validation) before creating the event instance. The code is located near line number 1386 in log_event.cc. If you open that file and scroll down through the method, you will see the switch statement used to instantiate the events. Listing 8-6 shows an excerpt of the method highlighting the switch statement

Listing 8-6. Log_event::read_log_event() Method

Log_event* Log_event::read_log_event(const char* buf, uint event_len,
const char **error,
const Format_description_log_event *description_event,
my_bool crc_check)
{
Log_event* ev;
...

if (alg != BINLOG_CHECKSUM_ALG_UNDEF &&
(event_type == FORMAT_DESCRIPTION_EVENT ||
alg != BINLOG_CHECKSUM_ALG_OFF))
event_len= event_len - BINLOG_CHECKSUM_LEN;

switch(event_type) {
case QUERY_EVENT:
ev = new Query_log_event(buf, event_len, description_event,
QUERY_EVENT);
break;
case LOAD_EVENT:
ev = new Load_log_event(buf, event_len, description_event);
break;
case NEW_LOAD_EVENT:
ev = new Load_log_event(buf, event_len, description_event);
break;
case ROTATE_EVENT:
ev = new Rotate_log_event(buf, event_len, description_event);
break;
case CREATE_FILE_EVENT:
ev = new Create_file_log_event(buf, event_len, description_event);
break;
case APPEND_BLOCK_EVENT:
ev = new Append_block_log_event(buf, event_len, description_event);
break;
case DELETE_FILE_EVENT:
ev = new Delete_file_log_event(buf, event_len, description_event);
break;
case EXEC_LOAD_EVENT:
ev = new Execute_load_log_event(buf, event_len, description_event);
break;
case START_EVENT_V3: /* this is sent only by MySQL <=4.x */
ev = new Start_log_event_v3(buf, description_event);
break;
case STOP_EVENT:
ev = new Stop_log_event(buf, description_event);
break;
case INTVAR_EVENT:
ev = new Intvar_log_event(buf, description_event);
break;
case XID_EVENT:
ev = new Xid_log_event(buf, description_event);
break;
case RAND_EVENT:
ev = new Rand_log_event(buf, description_event);
break;
case USER_VAR_EVENT:
ev = new User_var_log_event(buf, description_event);
break;
case FORMAT_DESCRIPTION_EVENT:
ev = new Format_description_log_event(buf, event_len, description_event);
break;
#if defined(HAVE_REPLICATION)
case PRE_GA_WRITE_ROWS_EVENT:
ev = new Write_rows_log_event_old(buf, event_len, description_event);
break;
case PRE_GA_UPDATE_ROWS_EVENT:
ev = new Update_rows_log_event_old(buf, event_len, description_event);
break;
case PRE_GA_DELETE_ROWS_EVENT:
ev = new Delete_rows_log_event_old(buf, event_len, description_event);
break;
case WRITE_ROWS_EVENT_V1:
ev = new Write_rows_log_event(buf, event_len, description_event);
break;
case UPDATE_ROWS_EVENT_V1:
ev = new Update_rows_log_event(buf, event_len, description_event);
break;
case DELETE_ROWS_EVENT_V1:
ev = new Delete_rows_log_event(buf, event_len, description_event);
break;
case TABLE_MAP_EVENT:
ev = new Table_map_log_event(buf, event_len, description_event);
break;
#endif
case BEGIN_LOAD_QUERY_EVENT:
ev = new Begin_load_query_log_event(buf, event_len, description_event);
break;
case EXECUTE_LOAD_QUERY_EVENT:
ev= new Execute_load_query_log_event(buf, event_len, description_event);
break;
case INCIDENT_EVENT:
ev = new Incident_log_event(buf, event_len, description_event);
break;
case ROWS_QUERY_LOG_EVENT:
ev= new Rows_query_log_event(buf, event_len, description_event);
break;
case SLAVE_CONNECT_LOG_EVENT:
ev= new Slave_connect_log_event(buf, event_len, description_event);
break;
case GTID_LOG_EVENT:
case ANONYMOUS_GTID_LOG_EVENT:
ev= new Gtid_log_event(buf, event_len, description_event);
break;
case PREVIOUS_GTIDS_LOG_EVENT:
ev= new Previous_gtids_log_event(buf, event_len, description_event);
break;
#if defined(HAVE_REPLICATION)
case WRITE_ROWS_EVENT:
ev = new Write_rows_log_event(buf, event_len, description_event);
break;
case UPDATE_ROWS_EVENT:
ev = new Update_rows_log_event(buf, event_len, description_event);
break;
case DELETE_ROWS_EVENT:
ev = new Delete_rows_log_event(buf, event_len, description_event);
break;
#endif
default:
/*
Create an object of Ignorable_log_event for unrecognized sub-class.
So that SLAVE SQL THREAD will only update the position and continue.
*/
if (uint2korr(buf + FLAGS_OFFSET) & LOG_EVENT_IGNORABLE_F)
{
ev= new Ignorable_log_event(buf, description_event);
}
else
{
DBUG_PRINT("error",("Unknown event code: %d",
(int) buf[EVENT_TYPE_OFFSET]));
ev= NULL;
}
break;
}
}
...
DBUG_RETURN(ev);
}

Once the event is read, the slave code calls the Log_event::apply_event() method, which in turn calls the *_log_event::do_apply_event() for the log-event-class instance. This method is responsible for executing the event. As seen in a previous section, all log event classes have an *_log_event::apply_event() method. An example implementation of this event is shown in Listing 8-7 below. This shows the code that is executed when an Intvar_log_event is executed.

Listing 8-7. Example do_apply_event() Method

/*
Intvar_log_event::do_apply_event()
*/

int Intvar_log_event::do_apply_event(Relay_log_info const *rli)
{
/*
We are now in a statement until the associated query log event has
been processed.
*/
const_cast<Relay_log_info*>(rli)->set_flag(Relay_log_info::IN_STMT);

if (rli->deferred_events_collecting)
return rli->deferred_events->add(this);

switch (type) {
case LAST_INSERT_ID_EVENT:
thd->stmt_depends_on_first_successful_insert_id_in_prev_stmt= 1;
thd->first_successful_insert_id_in_prev_stmt= val;
break;
case INSERT_ID_EVENT:
thd->force_one_auto_inc_interval(val);
break;
}
return 0;
}

Notice how the code is designed to manipulate attributes for auto-increment values. This is how the slave ensures that it sets auto-increment values correctly for the queries executed. This is primarily used for SBR because RBR contains an image of the resulting row from the master.

Now that we understand how log events are executed on the slave, now is a good time to roll up our sleeves and once again dive into the source code to conduct experiments on modifying the replication source code. The next sections show you some basic ways that you can extend replication to meet your unique high-availability needs.

Extending Replication

This section presents a number of example projects you can use to explore the MySQL replication source code. While some may consider the examples academic in nature, you may find them informative for using as templates for practical applications for your environment.

Caution Modifying the replication code should be taken very seriously. It is one of the most complex subsystems, as well as one of the most robust and reliable. Be sure that your modifications are sound. If your code introduces side effects—or, worse, causes the server to crash—the replicated data could become out of date or corrupt. It is best to plan your modifications carefully and test them extensively before attempting to use them in any production environment.

If the above caution scares, you that is good, because the replication subsystem is well designed, with a long history of solid stability. Clearly, if you want to use replication or are relying on replication for standby, backups, or high availability, it is reasonable to be cautious.

Do not let that stop you from exploring these examples. Indeed, sometimes the best way to learn about something is to break it first. If you have worked through the examples from the earlier chapters, you have probably already experienced this.

Now that the requisite admonishments have been stated, let’s modify some code!

Global Slave Stop Command

I will start with a less complicated extension. Suppose that you have a replication topology with many slaves, and there arises a time when you need to stop replication. In this case, you must visit each slave and issue the STOP SLAVE command. Wouldn’t it be a lot easier to have a command on the master that you can use to tell all of the slaves to stop replication?

That is not to say there isn’t a way to do this—there are several. You could lock all of the tables on the master, thereby stopping the flow of events. You could also turn off the binary log, but this could cause the slaves to throw an error.

Visiting each slave still may not be enough, however. Consider for a moment an active master that is continuously receiving updates from clients. Consider also that it is unlikely one could issue the STOP SLAVE command to every slave at the same time. You would still need to stop the replication of events on the master before visiting each slave.

If there were a command on the master that is replicated to each slave at the same time, it would ensure that the slaves are all stopped at the same point in the replication process.

Thus, if there were a SQL command—say, STOP ALL SLAVES—that replicated the STOP SLAVE command to all slaves, you could be sure that all slaves are stopped at the same time. Let’s see how we can go about making such a command.

Code Modifications

This extension requires modifying the parser and adding the case statement for the big switch in sql_parse.cc. Table 8-4 includes a list of the files that need to be modified.

Table 8-4. Files Changed for STOP ALL SLAVES Command

Now that we know what files need to change, let’s dive into the modifications. First, open the sql/lex.h file and add the following code. We are adding the new symbol for the command. This file contains the symbol array stored in alphabetical order. Thus, we will add the SLAVESsymbol near line number 523. Listing 8-8 depicts the modifications in context.

Listing 8-8. Adding SLAVES Symbol to sql/lex.h

{ "SIGNED", SYM(SIGNED_SYM)},
{ "SIMPLE", SYM(SIMPLE_SYM)},
{ "SLAVE", SYM(SLAVE)},
/* BEGIN CAB MODIFICATION */
/* Reason for Modification: */
/* Add SLAVES keyword */
{ "SLAVES", SYM(SLAVES)},
/* END CAB MODIFICATION */
{ "SLOW", SYM(SLOW)},
{ "SNAPSHOT", SYM(SNAPSHOT_SYM)},
{ "SMALLINT", SYM(SMALLINT)},

Next, we need to modify the sql_cmd.h file to add a new enumeration for the big switch. Open the sql_cmd.h file and locate the enum enum_sql_command definition near the top of the file. Listing 8-9 shows the code to add a new enumeration for the new command.

Listing 8-9. Adding the Enumeration for the STOP ALL SLAVES Command

SQLCOM_FLUSH, SQLCOM_KILL, SQLCOM_ANALYZE,
SQLCOM_ROLLBACK, SQLCOM_ROLLBACK_TO_SAVEPOINT,
SQLCOM_COMMIT, SQLCOM_SAVEPOINT, SQLCOM_RELEASE_SAVEPOINT,
/* BEGIN CAB MODIFICATION */
/* Reason for Modification: */
/* Add SQLCOM_STOP_SLAVES enum */
SQLCOM_SLAVE_START, SQLCOM_SLAVE_STOP, SQLCOM_STOP_SLAVES,
/* END CAB MODIFICATION */
SQLCOM_BEGIN, SQLCOM_CHANGE_MASTER,
SQLCOM_RENAME_TABLE,
SQLCOM_RESET, SQLCOM_PURGE, SQLCOM_PURGE_BEFORE, SQLCOM_SHOW_BINLOGS,

Next we need to add a new token to be used in the new rule. Once again, the list of tokens is arranged in alphabetical order. Open the sql_yacc.yy file and locate the section where new tokens are defined. In this case, we need to add a definition for a token for the new command. We will name it SLAVES. Listing 8-10 shows the code in context to be added. You can find this code near line number 1497.

Listing 8-10. Adding the Tokens

%token SIGNED_SYM
%token SIMPLE_SYM /* SQL-2003-N */
%token SLAVE
/* BEGIN CAB MODIFICATION */
/* Reason for Modification: */
/* Add SLAVES token */
%token SLAVES
/* END CAB MODIFICATION */
%token SLOW
%token SMALLINT /* SQL-2003-R */
%token SNAPSHOT_SYM

Next, modify the section where the %type <NONE> definition resides. We need to add the new token to this definition. You can find this section near line number 1813. Listing 8-11 shows the code in context.

Listing 8-11. Adding the Token to the Type None Definition

/* BEGIN CAB MODIFICATION */
/* Reason for Modification: */
/* Add stop to list of NONE types */
repair analyze check start stop checksum
/* END CAB MODIFICATION */
field_list field_list_item field_spec kill column_def key_def
keycache_list keycache_list_or_parts assign_to_keycache
assign_to_keycache_parts

...

We’re almost done. Next, we add a new command definition to the list of commands so that the parser can direct control to the new rule. Once again, this list is in alphabetical order. You can find the location to be modified around line number 2035. Listing 8-12 shows the modification. Notice we add a new ‘or’ condition mapping to a new rule to be evaluated. In this case, we name the rule stop.

Listing 8-12. Adding a New Rule Definition

| show
| slave
| start
/* BEGIN CAB MODIFICATION */
/* Reason for Modification: */
/* Add stop to list of statement targets */
| stop
/* END CAB MODIFICATION */
| truncate
| uninstall
| unlock

Last, we will add the new rule to process the STOP ALL SLAVES command. I place this code near the existing start rule around line 8027. Listing 8-13 shows the new rule. The rule simply saves the new enumeration to the lex->sql_command attribute. This is how the code maps the result of the rule (and the processing of the command) to the big switch to a case equal to the enumeration value.

Listing 8-13. Adding the STOP ALL SLAVES Rule to the Parser

}
;

/* BEGIN CAB MODIFICATION */
/* Reason for Modification: */
/* Add rule for STOP ALL SLAVES command */
stop:
STOP_SYM ALL SLAVES
{
LEX *lex= Lex;
lex->sql_command= SQLCOM_STOP_SLAVES;
}
;
/* END CAB MODIFICATION */

start:
START_SYM TRANSACTION_SYM opt_start_transaction_option_list
{

With the changes to the YACC file complete, we can add a new case for the big switch to ensure the command, once captured by the parser, is directed to code to written the event to the binary log. Normally, the STOP SLAVE command is not replicated. Our code would also need to override this restriction. Let us add that case statement. Open the sql_parse.cc file and locate the section with the replication statements. This is near line number 3054. Listing 8-14 shows the new case statement.

Listing 8-14. Adding the Case for the Big Switch

mysql_mutex_unlock(&LOCK_active_mi);
break;
}

/* BEGIN CAB MODIFICATION */
/* Reason for Modification: */
/* Add case statement for STOP ALL SLAVES command */
case SQLCOM_STOP_SLAVES:
{
if (!lex->no_write_to_binlog)
res= write_bin_log(thd, TRUE, "STOP SLAVE IO_THREAD", 20);
break;
}
/* END CAB MODIFICATION */

#endif /* HAVE_REPLICATION */

case SQLCOM_RENAME_TABLE:

Take a moment to examine this code. The first statement is designed to check to see if the server is able to write to the binary log. If so, we add a new log event, passing it the STOP SLAVE SQL command. Notice that we use a specific version of the STOP SLAVE command. In this case, we are stopping only the IO thread. This is for two reasons. First, we need only stop the IO thread to stop replicating events to the slave. This would still permit the slave to process any events read from the master to this point (as done by the SQL thread). Second, the STOP SLAVE command is more complicated in that it stops both threads, and in doing so, it has several critical sections that need to be protected by mutexes. Executing a STOP SLAVE command in the middle of executing a log event (the log event itself is the STOP SLAVE) will lead to conflicts with the mutexes. Thus, stopping only the IO thread is the only way to successfully execute the command on the slave.

Compiling the Code

To compile the new code, simply execute make from the root of the source tree. Since there are no new files or changes to the cmake files, we only need to rebuild the executables. If there are errors, go back and fix them until the server code compiles successfully.

Example Execution

Execution of the global slave-stop command requires a replication topology with at least one slave. To illustrate how well the command works, I will create a topology that uses three slaves.

The first step is to set up a simple replication topology. Rather than go through all the steps outlined above to set up the master and slave, I use the MySQL Utilities commands to quickly set up my test conditions.

I begin by cloning an instance of a running server once for the master and once for each slave. The utility, mysqlserverclone, is designed to create a new instance of either a downed or a running slave. We simply connect to the server using the server option, pass in any options for the new server (mysqld), and define a new data directory, port, and server_id. Listing 8-15 shows the results of these steps.

Listing 8-15. Setting up a Simple Replication Topology

cbell@ubuntu:$ mysqlserverclone.py --basedir=/source/mysql-5.6 \
--mysqld="--log-bin=mysql-bin" --new-port=3310 –new-data=/source/temp_3310 \
--new-id=100 --delete-data
# WARNING: Root password for new instance has not been set.
# Cloning the MySQL server located at /source/mysql-5.6.
# Creating new data directory...
# Configuring new instance...
# Locating mysql tools...
# Setting up empty database and mysql tables...
# Starting new instance of the server...
# Testing connection to new instance...
# Success!
# Connection Information:
# -uroot --socket=/source/temp_3310/mysql.sock
#...done.

cbell@ubuntu:$ mysqlserverclone.py --basedir=/source/mysql-5.6 \
--mysqld="--log-bin=mysql-bin --report-port=3311 --report-host=localhost" \
--new-port=3311 –new-data=/source/temp_3311 \
--new-id=101 --delete-data
# WARNING: Root password for new instance has not been set.
# Cloning the MySQL server located at /source/mysql-5.6.
# Creating new data directory...
# Configuring new instance...
# Locating mysql tools...
# Setting up empty database and mysql tables...
# Starting new instance of the server...
# Testing connection to new instance...
# Success!
# Connection Information:
# -uroot --socket=/home/cbell/source/temp_3311/mysql.sock
#...done.

cbell@ubuntu:$ mysqlserverclone.py --basedir=/source/mysql-5.6 \
--mysqld="--log-bin=mysql-bin --report-port=3312 --report-host=localhost" \
--new-port=3312 –new-data=/source/temp_3312 \
--new-id=102 --delete-data
# WARNING: Root password for new instance has not been set.
# Cloning the MySQL server located at /source/mysql-5.6.
# Creating new data directory...
# Configuring new instance...
# Locating mysql tools...
# Setting up empty database and mysql tables...
# Starting new instance of the server...
# Testing connection to new instance...
# Success!
# Connection Information:
# -uroot --socket=/home/cbell/source/temp_3311/mysql.sock
#...done.

cbell@ubuntu:$ mysqlserverclone.py --basedir=/source/mysql-5.6 \
--mysqld="--log-bin=mysql-bin --report-port=3313 --report-host=localhost" \
--new-port=3313 –new-data=/source/temp_3313 \
--new-id=103 --delete-data
# WARNING: Root password for new instance has not been set.
# Cloning the MySQL server located at /source/mysql-5.6.
# Creating new data directory...
# Configuring new instance...
# Locating mysql tools...
# Setting up empty database and mysql tables...
# Starting new instance of the server...
# Testing connection to new instance...
# Success!
# Connection Information:
# -uroot --socket=/home/cbell/source/temp_3311/mysql.sock
#...done.

Once we have the master and slaves running, we then set up replication between the master and each slave. The utility, mysqlreplicate, makes connecting a slave to a master one step. Listing 8-16 shows the results. Notice that the utility simply requires a connection to the master and a connection to the slave.

Listing 8-16. Setting up Replication

cbell@ubuntu:$ mysqlreplicate.py --master=root@localhost:3310 \
--slave=root@localhost:3311
# master on localhost: ... connected.
# slave on localhost: ... connected.
# Checking for binary logging on master...
# Setting up replication...
# ...done.

cbell@ubuntu:$ mysqlreplicate.py --master=root@localhost:3310 \
--slave=root@localhost:3312
# master on localhost: ... connected.
# slave on localhost: ... connected.
# Checking for binary logging on master...
# Setting up replication...
# ...done.

cbell@ubuntu:$ mysqlreplicate.py --master=root@localhost:3310 \
--slave=root@localhost:3313
# master on localhost: ... connected.
# slave on localhost: ... connected.
# Checking for binary logging on master...
# Setting up replication...
# ...done.

WHAT ARE MYSQL UTILITIES?

MySQL Utilities is a subproject of the MySQL Workbench tool. MySQL Utilities contains a number of helpful command-line tools for managing MySQL servers with emphasis on replication.

When you download Workbench, you will also get MySQL Utilities. You can access the utilities via the plugin in Workbench. You can also download MySQL Utilities directly from Launchpad.

MySQL Workbench download: http://dev.mysql.com/downloads/workbench/5.2.html

MySQL Utilities download from Launchpad: https://launchpad.net/mysql-utilities

For more information about MySQL Utilities, see the online documentation:

http://dev.mysql.com/doc/workbench/en/mysql-utilities.html

Let us review the setup thus far. The commands above allow me to create a running instance of a server from a source-code tree (specified by --basedir), passing it parameters such as the port to use and the location of its data directory. The mysqlserverclone utility will do all of the work for me and then tell me how to connect to the server. I do this twice: once for the master and once for the slave, using different ports and data directories. I then use the automated-replication setup utility, mysqlreplicate, to set up replication between the master and three slaves. Notice that I set the --report-port and --report-host for each slave in the --mysqld option. This option allows you to specify options for server startup.

As you can see, this is very easy and very quick to set up. You can even put these commands in a script so that you can create the test topology at any time.

Now that we have our test topology, let’s check the status of each slave’s IO thread and then issue the command and check the status again. The first step is to see a list of the slaves attached to the master. For this, I use the mysqlrplshow command. Listing 8-17 shows the output of this command; it prints a nice graph of our topology. While we are at it, we also show the slave status for each slave to ensure that each is actively replicating data from the master. I include excerpts of the output for brevity.

Listing 8-17. Checking the Topology

cbell@ubuntu:$ mysqlrplshow.py --master=root@localhost:3310
# master on localhost: ... connected.
# Finding slaves for master: localhost:3310

# Replication Topology Graph
localhost:3310 (MASTER)
|
+−−- localhost:3311 - (SLAVE)
|
+−−- localhost:3312 - (SLAVE)
|
+−−- localhost:3313 - (SLAVE)

cbell@ubuntu: $ mysql -uroot -h 127.0.0.1 --port=3311
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.6-m9-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: rpl
Master_Port: 3310
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 325
Relay_Log_File: clone-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
1 row in set (0.00 sec)

cbell@ubuntu: $ mysql -uroot -h 127.0.0.1 --port=3312
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.6-m9-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: rpl
Master_Port: 3310
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 325
Relay_Log_File: clone-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
1 row in set (0.00 sec)

cbell@ubuntu: $ mysql -uroot -h 127.0.0.1 --port=3313
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.6-m9-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: rpl
Master_Port: 3310
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 325
Relay_Log_File: clone-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
1 row in set (0.00 sec)

Now that we have set up the new topology and verified that all is well, we can test the new STOP ALL SLAVES command. Listing 8-18 shows the results.

Listing 8-18. Demonstration of the STOP ALL SLAVES Command

cbell@ubuntu$ mysql -uroot -h 127.0.0.1 --port=3310
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.6.6-m9-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> STOP ALL SLAVES;
Query OK, 0 rows affected (0.00 sec)

You may be thinking, “Is that it?” For the master, it is. Nothing happens, because ( recall the code for the big switch) we only write the command to the binary log. Nothing else is done on the master, and it keeps executing without interruption.

The desired effect, of course, is for the slaves to stop. Let us check the slave status on the slaves to see if this indeed has occurred. Remember, we are stopping the IO thread, so we should look for that in the slave-status output. Listing 8-19 shows the output of each slave status excerpted for brevity. Notice that in each case the slaves IO thread has indeed stopped (Slave_IO_Running = No).

Listing 8-19. Result of the STOP ALL SLAVES Command on the Slaves

cbell@ubuntu: $ mysql -uroot -h 127.0.0.1 --port=3311
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.6-m9-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: localhost
Master_User: rpl
Master_Port: 3310
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 408
Relay_Log_File: clone-relay-bin.000002
Relay_Log_Pos: 366
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: Yes
...
1 row in set (0.00 sec)

cbell@ubuntu: $ mysql -uroot -h 127.0.0.1 --port=3312
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.6-m9-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: localhost
Master_User: rpl
Master_Port: 3310
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 408
Relay_Log_File: clone-relay-bin.000002
Relay_Log_Pos: 366
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: Yes
...
1 row in set (0.00 sec)

cbell@ubuntu: $ mysql -uroot -h 127.0.0.1 --port=3313
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.6-m9-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: localhost
Master_User: rpl
Master_Port: 3310
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 408
Relay_Log_File: clone-relay-bin.000002
Relay_Log_Pos: 366
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: Yes
...
1 row in set (0.00 sec)

This example demonstrates how to create a unique replication command that can be executed on the master and sent to all of the slaves for the purpose of stopping the flow of replication events. As you can see, this extension is easy to add and shows the power of the binary log events. If you use your imagination, you can think of other useful commands that you may want to send to all of your slaves.

In the next example, the difficultly level increases considerably. I show you how to create a new log event that records information in the binary log. In this case, we create a new log event that documents when a slave connects to the master. This could be useful if you need to determine when or if a new slave joined the topology.

Slave Connect Logging

Savvy administrators know to check replication topologies periodically for errors. This can range from checking the result of SHOW SLAVE STATUS and examining the values for errors, slave lag, and similar events. Administrators who encounter errors or who have to diagnose replication problems are missing a key data point. Replication in large topologies with many slaves that are brought online for scale out may be taken offline. This process may occur several times a week, and in some cases, it may occur multiple times a day. Administrators of smaller or even medium replication topologies might keep a log (at least mentally) when slaves are connected and disconnected, but this may not be possible for larger replication topologies. In that case, it may be impossible to determine when a slave was connected to a master, just that a slave is connected to the master.

In the case of diagnosing and repairing replication, it helps to know when a slave was connected in order to map the timing of that event to other events that occurred at the same time. Perhaps the events are related. If you don’t know when the slave connected to the master, you may never know.

In this section, we create a new log event that records when a slave connects to a master. This is written to the binary log as a permanent entry. While it is also sent to the slaves, we make the event an ignorable event that permits the slaves to skip the event. The slaves, therefore, will take no action and will not write the event to its relay log, nor will they write the event to their binary logs, if binary logging is engaged.

Let us begin by reviewing the source files that need to be modified. While the event itself is simplistic in design and purpose, the code needed to create the new event is extensive and dispersed throughout the replication source files. As you will see, there are a number of places where an event gets processed in both the master and slave.

Code Modifications

This extension will require modifying a number of the replication source files. Table 8-5 includes a list of the files that need to be modified. All these files are located in the /sql folder.

Table 8-5. Files Changed for Slave Connect Event

The list of modifications may seem daunting at first glance. Fortunately, most source files require small changes. The major effort is creating the new log-event class and connecting it to the register_slave() method for the master. To make it easier, and to keep the same general isolation as the server code (the main source files for the server), do not create log events directly; instead use a method defined in the MYSQL_BIN_LOG class. I will create a new method to do just that.

There are several places in the source code where we need to discard the new event. We mirror the Rows_query_log_event action so that we can be sure to cover all cases in which the event needs to be ignored and deleted.

Now that we know what files need to change, let’s dive into the modifications. We start with the new log event. Open the log_event.h file located in the ./sql folder. First, add a new enumeration for the new event to the enum Log_event_type list. We need to add the enumeration to the end of the list prior to the ENUM_END_EVENT marker. Name the new entry SLAVE_CONNECT_LOG_EVENT and assign it the next value in the list. Listing 8-20 shows the modification in context.

Tip This is a standard mechanism seen in most enumerated lists in the source code. The use of an end marker allows for loop constricts as well as out of bounds checking.

Listing 8-20. Adding a New Enumeration for the New Event in log_event.h

enum Log_event_type
{
/*
Every time you update this enum (when you add a type), you have to
fix Format_description_log_event::Format_description_log_event().
*/
UNKNOWN_EVENT= 0,
START_EVENT_V3= 1,
QUERY_EVENT= 2,
STOP_EVENT= 3,
ROTATE_EVENT= 4,
INTVAR_EVENT= 5,
LOAD_EVENT= 6,

...

/* BEGIN CAB MODIFICATION */
/* Reason for Modification: */
/* Add new log event enumeration */
SLAVE_CONNECT_LOG_EVENT= 36,
/* END CAB MODIFICATION */
ENUM_END_EVENT /* end marker */
};

Also in the log_event.h file, we need to add a new class declaration for the new event. Name the event Slave_connect_log_event and derive it from the Log_event base class. Add this code to the section of the file that contains declarations for the other log-event classes.

You can copy any of the other log-event declarations and change the name according. Be careful if you use search and replace, because you don’t want to change the original log event. Listing 8-21 shows the completed class declaration. I describe each element in more detail later in this section.

Listing 8-21. The Slave_connect_log_event Class Declaration in log_event.h

/* BEGIN CAB MODIFICATION */
/* Reason for Modification: */
/* Add new log event class declaration */
class Slave_connect_log_event : public Log_event {
public:
#ifndef MYSQL_CLIENT
Slave_connect_log_event(THD *thd_arg, const char * query, ulong query_len)
: Log_event(thd_arg, LOG_EVENT_IGNORABLE_F,
Log_event::EVENT_STMT_CACHE,
Log_event::EVENT_IMMEDIATE_LOGGING)
{
DBUG_ENTER("Slave_connect_log_event::Slave_connect_log_event");
if (!(m_slave_connect= (char*) my_malloc(query_len + 1, MYF(MY_WME))))
return;
my_snprintf(m_slave_connect, query_len + 1, "%s", query);
DBUG_PRINT("enter", ("%s", m_slave_connect));
DBUG_VOID_RETURN;
}
#endif

#ifndef MYSQL_CLIENT
int pack_info(Protocol*);
#endif

Slave_connect_log_event(const char *buf, uint event_len,
const Format_description_log_event *descr_event);

virtual ∼Slave_connect_log_event();
bool is_valid() const { return 1; }

#ifdef MYSQL_CLIENT
virtual void print(FILE *file, PRINT_EVENT_INFO *print_event_info);
#endif
virtual bool write_data_body(IO_CACHE *file);

virtual Log_event_type get_type_code() { return SLAVE_CONNECT_LOG_EVENT; }

virtual int get_data_size()
{
return IGNORABLE_HEADER_LEN + 1 + (uint) strlen(m_slave_connect);
}
#if defined(MYSQL_SERVER) && defined(HAVE_REPLICATION)
virtual int do_apply_event(Relay_log_info const *rli);
#endif

private:

char *m_slave_connect;
};
/* END CAB MODIFICATION */

Notice that some portions are protected with conditional compilation flags. This is because this code is shared with other parts of the server source code. For example, it is used in the mysqlbinlog client tool. Clearly, some of the code is not needed for that application. Thus, we mask out the portions that are not needed with conditional compilation.

I want to call your attention to a very important, but obscure, flag. Notice the LOG_EVENT_IGNORABLE_F flag is the second argument to the base-class constructor. As evident by its name, this flag is what tells the server to ignore the event. The binary-log and relay-log code are designed to ignore any event with this flag. This means that we need only handle the normal methods for writing to the binary log (and printing events). In a number of places, we must also add code to skip destroying the event instance, but fortunately, we have a model to follow. I describe these later in this section.

At the bottom of the class declaration, I placed a pointer to contain the message that will be added to the log event as the payload. The key methods that need to be defined in the class include the constructor and destructor, pack_info(), print(), write_body(), andapply_event(). Most remaining methods are self-explanatory. Notice the get_type_code() and get_data_size() methods. I will explain each of these when we add the code to the log_event.cc file.

Now that we have the class declaration, open the log_event.cc file located in the ./sql folder. First, locate the Log_event::get_type_str() method and add a new case statement for the Slave_connect_log_event. The case statement uses the enumeration we created earlier. This method is used in various places to describe the event in views such as SHOW BINLOG EVENTS. Listing 8-22 shows the change in context. This code is located near line number 686.

Listing 8-22. Case Statement for Log_event::get_type_str() in log_event.cc

case INCIDENT_EVENT: return "Incident";
case IGNORABLE_LOG_EVENT: return "Ignorable";
case ROWS_QUERY_LOG_EVENT: return "Rows_query";
/* BEGIN CAB MODIFICATION */
/* Reason for Modification: */
/* Add case to return name of new log event */
case SLAVE_CONNECT_LOG_EVENT: return "Slave_connect";
/* END CAB MODIFICATION */
case WRITE_ROWS_EVENT: return "Write_rows";
case UPDATE_ROWS_EVENT: return "Update_rows";
case DELETE_ROWS_EVENT: return "Delete_rows";

We also need to add a case statement for the new event to the Log_event::read_log_event(). This method is responsible for creating a new event. It also uses the new enumeration defined earlier. Add a new case statement to create a new instance of theSlave_connect_log_event class. Listing 8-23 shows the changes in context. This code is located near line number 1579. Notice that except for the class name, it is the same code as other events.

Listing 8-23. Case Statement for Log_event::read_log_event() in log_event.cc

case ROWS_QUERY_LOG_EVENT:
ev= new Rows_query_log_event(buf, event_len, description_event);
break;
/* BEGIN CAB MODIFICATION */
/* Reason for Modification: */
/* Add case to create new log event */
case SLAVE_CONNECT_LOG_EVENT:
ev= new Slave_connect_log_event(buf, event_len, description_event);
break;
/* END CAB MODIFICATION */
case GTID_LOG_EVENT:
case ANONYMOUS_GTID_LOG_EVENT:

One more method needs modification. The Format_description_log_event() returns the header length for each log event. In this case, we need to return the header length for the new log event. Listing 8-24 shows this code in context. This code is located near line number 5183. In this case, we return the length of the ignorable log event header already defined in the code.

Listing 8-24. New lookup for Format_description_log_event() in log_event.cc

post_header_len[HEARTBEAT_LOG_EVENT-1]= 0;
post_header_len[IGNORABLE_LOG_EVENT-1]= IGNORABLE_HEADER_LEN;
post_header_len[ROWS_QUERY_LOG_EVENT-1]= IGNORABLE_HEADER_LEN;
/* BEGIN CAB MODIFICATION */
/* Reason for Modification: */
/* Return header length for the new log event */
post_header_len[SLAVE_CONNECT_LOG_EVENT-1]= IGNORABLE_HEADER_LEN;
/* END CAB MODIFICATION */
post_header_len[WRITE_ROWS_EVENT-1]= ROWS_HEADER_LEN_V2;
post_header_len[UPDATE_ROWS_EVENT-1]= ROWS_HEADER_LEN_V2;
post_header_len[DELETE_ROWS_EVENT-1]= ROWS_HEADER_LEN_V2;

Now we can add the code for the class methods themselves. I list all of the code in Listing 8-25 and then explain each method.

Listing 8-25. The Slave_connect_log_event Methods in log_event.cc

/* BEGIN CAB MODIFICATION */
/* Reason for Modification: */
/* Class method definitions for the new log event */
Slave_connect_log_event::Slave_connect_log_event(const char *buf,
uint event_len,
const Format_description_log_event *descr_event)
: Log_event(buf, descr_event)
{
DBUG_ENTER("Slave_connect_log_event::Slave_connect_log_event");
uint8 const common_header_len= descr_event->common_header_len;
uint8 const post_header_len=
descr_event->post_header_len[SLAVE_CONNECT_LOG_EVENT-1];

DBUG_PRINT("info",
("event_len: %u; common_header_len: %d; post_header_len: %d",
event_len, common_header_len, post_header_len));

/*
m_slave_connect length is stored using only one byte, but that length is
ignored and the complete query is read.
*/
int offset= common_header_len post_header_len 1;
int len= event_len - offset;
if (!(m_slave_connect= (char*) my_malloc(len1, MYF(MY_WME))))
return;
strmake(m_slave_connect, buf offset, len);
DBUG_PRINT("info", ("m_slave_connect: %s", m_slave_connect));
DBUG_VOID_RETURN;
}

Slave_connect_log_event::∼Slave_connect_log_event()
{
my_free(m_slave_connect);
}

#ifndef MYSQL_CLIENT
int Slave_connect_log_event::pack_info(Protocol *protocol)
{
char *buf;
size_t bytes;
ulong len= sizeof("# SLAVE_CONNECT = ") (ulong) strlen(m_slave_connect);
if (!(buf= (char*) my_malloc(len, MYF(MY_WME))))
return 1;
bytes= my_snprintf(buf, len, "# SLAVE_CONNECT = %s", m_slave_connect);
protocol->store(buf, bytes, &my_charset_bin);
my_free(buf);
return 0;
}
#endif


#ifdef MYSQL_CLIENT
void
Slave_connect_log_event::print(FILE *file,
PRINT_EVENT_INFO *print_event_info)
{
IO_CACHE *const head= &print_event_info->head_cache;
IO_CACHE *const body= &print_event_info->body_cache;
char *slave_connect_copy= NULL;
if (!(slave_connect_copy= my_strdup(m_slave_connect, MYF(MY_WME))))
return;

my_b_printf(head, "# Slave Connect:\n# %s\n", slave_connect_copy);
print_header(head, print_event_info, FALSE);
my_free(slave_connect_copy);
print_base64(body, print_event_info, true);
}
#endif

bool
Slave_connect_log_event::write_data_body(IO_CACHE *file)
{
DBUG_ENTER("Slave_connect_log_event::write_data_body");
/*
m_slave_connect length will be stored using only one byte, but on read
that length will be ignored and the complete query will be read.
*/
DBUG_RETURN(write_str_at_most_255_bytes(file, m_slave_connect,
(uint) strlen(m_slave_connect)));
}

#if defined(MYSQL_SERVER) && defined(HAVE_REPLICATION)
int Slave_connect_log_event::do_apply_event(Relay_log_info const *rli)
{
DBUG_ENTER("Slave_connect_log_event::do_apply_event");
DBUG_ASSERT(rli->info_thd == thd);
/* Set query for writing Slave_connect log event into binlog later.*/
thd->set_query(m_slave_connect, (uint32) strlen(m_slave_connect));

DBUG_ASSERT(rli->slave_connect_ev == NULL);

const_cast<Relay_log_info*>(rli)->slave_connect_ev= this;

DBUG_RETURN(0);
}
#endif

The following sections describe each method in more detail. I explain why we need the method and how the method is used, along with any particulars implemented in the code.

Slave_connect_log_event::Slave_connect_log_event()

This is the constructor for the class instance. Of particular note here is that we allocate memory for the message or payload for the event when written to the binary log. It also has code to set up the header length.

Slave_connect_log_event::∼Slave_connect_log_event()

This is the destructor for the class instance. Here, we simply free the string that we allocated in the constructor.

Slave_connect_log_event::pack_info()

This method is used to store the event data for writing to the binary log. We format a string to include a label that describes the event along with a string that contains the hostname, port, and server_id of the slave that connected to the master.

Slave_connect_log_event::print()

This method is used by clients to print the event in a human-readable form. Notice the conditional compilation directives that ensure the code is compiled only with clients. This tells us this method is very different from the pack_info() event.

In this method, the header is printed, followed by a similar format for the event data containing the hostname, port, and server_id. In this method, we also duplicate the string from the class so that it can be used outside of the class by the client application.

Slave_connect_log_event::write_data_body()

This method is used in conjunction with the pack_info() method. It will write the event data to the binary log.

Slave_connect_log_event::do_apply_event()

This method is used to write the event to the relay log. Since this event is ignored, it won’t be written to the relay log. Rather than leave this method empty, I complete it as an example in case you wish to create custom events. In that case, you can use the code here as an example.

Now that we have the class defined, we can add a method to the binary log class that can be called from the main code more easily. We begin by adding a new method declaration to the class. Open the binlog.h file and locate write_incident() event near line number 541. Add a new method named write_slave_connect() with parameters for the current thread instance, hostname, port, and server_id. We will use the last three parameters to for the informational message or payload of the Slave_connect_log_event class. Listing 8-26 shows the new method declaration.

Listing 8-26. Method Declaration for write_slave_connect() in binlog.h

bool write_incident(Incident_log_event *ev, bool need_lock_log,
bool do_flush_and_sync= true);

/* BEGIN CAB MODIFICATION */
/* Reason for Modification: */
/* Method declaration for writing the slave connect event to the binlog */
bool write_slave_connect(THD *thd, char *host, int port, int server_id);
/* END CAB MODIFICATION */

void start_union_events(THD *thd, query_id_t query_id_param);
void stop_union_events(THD *thd);
bool is_query_in_union(THD *thd, query_id_t query_id_param);

Now we can add the method definition to the binlog.cc file. As with the header file, we will place the new method near the write_incident() method. Open this file and location this method near line number 5154. Listing 8-27 shows the completed method.

Listing 8-27. The write_slave_connect() Method Definition in binlog.cc

/* BEGIN CAB MODIFICATION */
/* Reason for Modification: */
/* Add new method definition to write slave connect event to binlog */
bool MYSQL_BIN_LOG::write_slave_connect(THD *thd, char *host, int port, int server_id)
{
char buffer[255];
binlog_cache_data* cache_data= NULL;
DBUG_ENTER("MYSQL_BIN_LOG::write_slave_connect");

/* Record slave connection in the binary log */
sprintf(buffer, "Host: %s Port: %d Server_Id: %d", host, port, server_id);
Slave_connect_log_event ev(thd, buffer, (int)strlen(buffer));

if (thd->binlog_setup_trx_data())
DBUG_RETURN(1);
cache_data= &thd_get_cache_mngr(thd)->trx_cache;
if (cache_data->write_event(thd, &ev))
DBUG_RETURN(1);
cache_data->finalize(thd, NULL);
ordered_commit(thd, true);

DBUG_RETURN(0);
}
/* END CAB MODIFICATION */

In the method, we first create a new instance of the new event and then call methods to set up a transaction, retrieve the cache manager (the device used to write events to the cache for the binary log), and write the event. The finalize() method ensures that the correct flags are set for the event and that the ordered_commit() method completes the transaction, ensuring that the event is written to the binary log when the cache is flushed to disk.

Next, we will change the code for the master that will call the new binary-log method. The rpl_master.cc file contains the code for the master, including the code used whenever a slave connects. The register_slave() method is called when the slave connects. We will use this method as our starting point for initiating the Slave_connect_log_event via the write_slave_connect() method.

Open the rpl_master.cc file and locate the register_slave() method near line number 147. Add the code shown in Listing 8-28. This code should appear right after the mutex lock call and before the unregister_slave() call.

Listing 8-28. Changes to register_slave() in rpl_master.cc

mysql_mutex_lock(&LOCK_slave_list);
/* BEGIN CAB MODIFICATION */
/* Reason for Modification: */
/* Write a new Slave_connect_log_event to binary log when slave connects */
/* If this is a new slave registration, log the slave connect message. */
if (my_hash_search(&slave_list, (uchar*)&thd->server_id, 4) == NULL)
{
DBUG_PRINT("info", ("Logging slave connect for host: %s", si->host));
mysql_bin_log.write_slave_connect(thd, si->host, si->port, si->server_id);
}
/* END CAB MODIFICATION */
unregister_slave(thd, false, false/*need_lock_slave_list=false*/);
res= my_hash_insert(&slave_list, (uchar*) si);
mysql_mutex_unlock(&LOCK_slave_list);

You might notice something odd about this code. If you are like me, when I first looked at the register_slave() method, I concluded that the method was called once each time the slave connects to the master. What I didn’t realize, however, is that this method is called every time the slave requests data from the master. Thus, the register_slave() methodcan be called many times. If we want to record the slave-connect event only when the slave connects the first time, we must search the slave hash first. If we don’t find it, we can callwrite_slave_connect().

Now that we have the new event class and a new method in the binary-log class to write the event, and have linked it to the master code, we can work on the minor parts of the extension to complete the feature.

We begin with the relay-log code. Open the rpl_rli.h file and add a new member variable to contain an instance of the class. While the event is ignored, it is still read by the slave’s IO thread and interrogated by the relay log code. Thus, the relay log code will need to create an instance in order to determine if it is ignorable. Listing 8-29 shows the code to add the new variable. This is located near line number 480, but anywhere in the Relay_log_info class is fine.

Listing 8-29. Add New Variable for the Slave_connect_log_event class in rpl_rli.h

bool deferred_events_collecting;

/* BEGIN CAB MODIFICATION */
/* Reason for Modification: */
/* Add a new variable for a Slave_connect_log_event instance */

Slave_connect_log_event* slave_connect_ev;

/* END CAB MODIFICATION */
/*****************************************************************************

Next, we add code to initialize the new variable when the event is read by the IO thread. Open the rpl_rli.cc file and locate the constructor Relay_log_info::Relay_log_info(). Add an initialization of the new variable and set it to NULL. We do this to protect against calling methods or attributes on a class instance that has not been initialized (instantiated). This works well but requires that you always check the variable for NULL prior to calling any method or attribute. Listing 8-30 shows the code to add to the constructor.

Listing 8-30. Initialization of slave_connect_ev Variable in rpl_rli.cc

retried_trans(0),
tables_to_lock(0), tables_to_lock_count(0),
rows_query_ev(NULL), last_event_start_time(0), deferred_events(NULL),
/* BEGIN CAB MODIFICATION */
/* Reason for Modification: */
/* Initialize the slave_connect_ev variable */
slave_connect_ev(NULL), slave_parallel_workers(0),
/* END CAB MODIFICATION */
recovery_parallel_workers(0), checkpoint_seqno(0),
checkpoint_group(opt_mts_checkpoint_group),
recovery_groups_inited(false), mts_recovery_group_cnt(0),

The next few files to be changed are mostly to add the new event to existing destroy instance and error handling code. I chose to place this code in the same locations as Rows_query_log_event. This is a good choice because the Rows_query_log_event is also an ignorable event.

Also in the rpl_rli.cc file, we need to add code to destroy the instance if it is was instantiated earlier. We do this in the cleanup_context() method. Listing 8-31 shows the code to delete (destroy) the instance. Notice we first check to see that the instance has been instantiated. This avoids a particularly nasty error when attempting to destroy a class instance that does not exist.

Listing 8-31. Code to Destroy the Variable in cleanup_context() in rpl_rli..cc

rows_query_ev= NULL;
info_thd->set_query(NULL, 0);
}
/* BEGIN CAB MODIFICATION */
/* Reason for Modification: */
/* Destroy the Slave_connect_log_event instance */
if (slave_connect_ev)
{
delete slave_connect_ev;
slave_connect_ev= NULL;
info_thd->set_query(NULL, 0);
}
/* END CAB MODIFICATION */
m_table_map.clear_tables();
slave_close_thread_tables(thd);

The next file to change is rpl_rli_pdb.cc. Open the file and locate the last method in the file, slave_worker_exec_job(). We need to add another exclusion in that method’s error-handling code. Listing 8-32 shows the new changes. Notice that we add another condition in the same manner as the Rows_query_log_event event.

Listing 8-32. Add Condition to Delete Slave_connect_log_event in rpl_rli_pdb.cc

// todo: simulate delay in delete
/* BEGIN CAB MODIFICATION */
/* Reason for Modification: */
/* Destroy the Slave_connect_log_event instance */
if (ev && ev->worker && ev->get_type_code() != ROWS_QUERY_LOG_EVENT &&
ev->get_type_code() != SLAVE_CONNECT_LOG_EVENT)
/* END CAB MODIFICATION */
{
delete ev;
}

Next, we add the new event to another exclusion in the rpl_slave.cc code. Open the file and locate the condition that destroys the event after execution in the exec_relay_log_event() method. This is located near line number 3654. Listing 8-33 shows the changes needed for this condition. Notice that we add another condition to the if statement.

Listing 8-33. Exclude Slave_connect_log_event from Destroy Condition in rpl_slave.cc

clean-up routine.
*/
/* BEGIN CAB MODIFICATION */
/* Reason for Modification: */
/* Exclude the Slave_connect_log_event from destruction */
if (ev->get_type_code() != FORMAT_DESCRIPTION_EVENT &&
ev->get_type_code() != ROWS_QUERY_LOG_EVENT &&
ev->get_type_code() != SLAVE_CONNECT_LOG_EVENT)
/* END CAB MODIFICATION */
{
DBUG_PRINT("info", ("Deleting the event after it has been executed"));
delete ev;

The last file to change is sql_binlog.cc. There are two places where we need to change the code. First, we must add the SLAVE_CONNECT_LOG_EVENT to the exclusion for deleting an event that has been executed. Second, we must add code to destroy the new event instance in the case of an error.

Open the sql_binlog.cc file, locate the error condition for the Rows_query_log_event around line number 292, and make the changes shown in Listing 8-34. Notice that we simply add another condition to the if statement.

Listing 8-34. Exclude Slave_connect_log_event from Destroy Condition in sql_binlog.cc

of the event.
*/
/* BEGIN CAB MODIFICATION */
/* Reason for Modification: */
/* Exclude the Slave_connect_log_event from destruction */
if (ev->get_type_code() != FORMAT_DESCRIPTION_EVENT &&
ev->get_type_code() != ROWS_QUERY_LOG_EVENT &&
ev->get_type_code() != SLAVE_CONNECT_LOG_EVENT)
/* END CAB MODIFICATION */
{
delete ev;
ev= NULL;

We must add a condition to the error condition that destroys the new event in case there is an error. As with previous cases, we will mimic the code for the Rows_query_log_event. This is located near line number 320. Listing 8-35 shows the new code. We check for an error, and if there is an error and the new event exists (the pointer is not NULL or 0), we delete the instance.

Listing 8-35. Destroy Slave_connect_log_event if Error Condition in sql_binlog.cc

delete rli->rows_query_ev;
rli->rows_query_ev= NULL;
}
/* BEGIN CAB MODIFICATION */
/* Reason for Modification: */
/* Destroy the Slave_connect_log_event instance if there is an error */
if ((error || err) && rli->slave_connect_ev)
{
delete rli->slave_connect_ev;
rli->slave_connect_ev= NULL;
}
/* END CAB MODIFICATION */
rli->slave_close_thread_tables(thd);
}
thd->variables.option_bits= thd_options;

This completes the modifications to the code to add a new ignorable event to the list of events in the binary log. Clearly, this is not a trivial change, and log events touch several source-code files.

You are now ready to compile the code. Check all the files you modified for correctness. You should also compile the server from the root of the source tree so that all components are built with the changes. This is necessary so that the mysqlbinlog client application can decipher the new event.

Compiling the Code

Now, compile the server code. If all modifications were made as shown in the listings, the code should compile. If it does not, go back and check the modifications. The next section demonstrates the new event in action.

Example Execution

In this section, we see a demonstration of the new log event in action. There isn’t much to see in the form of output on either the master or the slave, because log events live under the hood of replication. There is, however, a SQL command, SHOW BINLOG EVENTS, that can be used to display an excerpt of the binary-log events on a server. A client tool, mysqlbinlog, which we previously discussed, can be used to examine the contents of the binary or relay log.

We being by setting up a test environment. Instead of using the utilities, like I did with the first example in this chapter, I show you an interesting trick in setting up a master and slave quickly using the existing server test environment.

The test environment described in Chapter 4, mysql-test-run, can be used to setup a master and slave. We do this by using two key options;: --start-and-exit and --suite. The --start-and-exit option tells mysql-test-run to start a new server and exit. This has the same effect as the mysqlserverclone utility, but unlike the utility, mysql-test-run uses predefined options for the mysqld process. In most cases, this is fine, but the utility allows you to create specialized server instances.

We also want to start a slave at the same time. If you provide a test suite, specifically the replication suite, mysql-test-run will create additional servers. For --suite=rpl, mysql-test-run will create two servers that are preconfigured for replication.

Clearly, this is easier than starting a new instance for the master and slave. Rather than two commands—which is already easier than launching mysqld instance manually—for the case of needing a master and a single slave, we can use one command. Listing 8-36 shows an example of using mysql-test-run to launch a master and slave.

Listing 8-36. Starting a new Master and Slave Using MTR

cbell@ubuntu:$ ./mysql-test-run.pl --start-and-exit --suite=rpl
Logging: ./mysql-test-run.pl --start-and-exit --suite=rpl
121003 18:26:01 [Note] Plugin 'FEDERATED' is disabled.
121003 18:26:01 [Note] Binlog end
121003 18:26:01 [Note] Shutting down plugin 'CSV'
121003 18:26:01 [Note] Shutting down plugin 'MyISAM'
MySQL Version 5.6.6
Checking supported features...
- skipping ndbcluster
- SSL connections supported
- binaries are debug compiled
Using suites: rpl
Collecting tests...
Checking leftover processes...
Removing old var directory...
Creating var directory '/mysql-test/var'...
Installing system database...
Using server port 54781

==============================================================================

TEST RESULT TIME (ms) or COMMENT
------------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
worker[1]
Started [mysqld.1 - pid: 2300, winpid: 2300] [mysqld.2 - pid: 2330, winpid: 2330]
worker[1] Using config for test rpl.rpl_000010
worker[1] Port and socket path for server(s):
worker[1] mysqld.1 13000 /mysql-test/var/tmp/mysqld.1.sock
worker[1] mysqld.2 13001 /mysql-test/var/tmp/mysqld.2.sock
worker[1] Server(s) started, not waiting for them to finish

We can now use the mysqlreplicate utility to quickly attach the slave to the master and begin replicating data. Listing 8-37 shows the output of running this utility.

Listing 8-37. Setting up Replication

cbell@ubuntu:$ python ./scripts/mysqlreplicate.py --master=root@localhost:13000 --slave=root@localhost:13001
# master on localhost: ... connected.
# slave on localhost: ... connected.
# Checking for binary logging on master...
# Setting up replication...
# ...done.

Were you expecting something? Perhaps a message or the ubiquitous “bing” sound? None of those are supposed to happen. The code behind replication is extensive but stable, and for the most part completely silent. To find out what is going on with replication, and in this case what has just transpired, you must either query the master or the slave for status.

If you recall, the extension is supposed to write a single log event to the binary log on the master recording when the slave connects. Since we set up replication with mysqlreplicate, nothing was displayed or otherwise presented there. We must go to the master to see if the event actually occurred.

Tip If you started the servers in console mode, you would have seen any messages and errors in the console. This includes errors and warnings from replication. For instance, the slave would display messages pertaining to connecting to the master and an error if there is a problem reading or executing events from the master. You can start a server in console mode on Linux and Mac systems by starting the mysqld executable directly. On Windows, you must use the --console option.

It might be a good idea to check the slave for errors. Execute SHOW SLAVE STATUS on the slave and check for errors. I leave this to you as an exercise. What you should see is a normal, healthy slave.

Let’s return to the master. The SHOW BINLOG EVENTS command will display the most recent events written to the binary log. If the Slave_connect_log_event worked correctly, you should see it in the view. Listing 8-38 shows the results of the SHOW BINLOG EVENTS run on the master. Remember, this command only shows the events from the first binary log. Use the IN clause to specify a particular binary log.

Note Running SHOW BINLOG EVENTS on any server that does not have binary logging enabled will result in an empty result set. If you see this when running your test on this code, check to be sure that you are not running on the slave.

Listing 8-38. Binary Log Events on the Master

cbell@ubuntu:$ mysql -uroot -h 127.0.0.1 --port=13000
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.6-m9-debug-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show binlog events \G
*************************** 1. row ***************************
Log_name: master-bin.000001
Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 121
Info: Server ver: 5.7.0-m10-debug-log, Binlog ver: 4
*************************** 2. row ***************************
Log_name: master-bin.000001
Pos: 121
Event_type: Query
Server_id: 1
End_log_pos: 326
Info: GRANT REPLICATION SLAVE ON *.* TO 'rpl'@'localhost'
*************************** 3. row ***************************
Log_name: master-bin.000001
Pos: 326
Event_type: Slave_connect
Server_id: 2
End_log_pos: 390
Info: # SLAVE_CONNECT = Host: 127.0.0.1 Port: 13001 Server_Id: 2
3 rows in set (0.00 sec)

mysql>

Notice that the listing above shows the new log event in the output. We see that the payload (Info field) of the event includes the hostname, port, and server_id of the slave. It is showing the loopback address because the replication topology (master and slave) runs on the local machine (localhost = 127.0.0.1).

Now, let us examine the output of the mysqlbinlog client application. Run the application with only the binary log of the master as the only argument. Since I set up the servers using mysql-test-run, I find the files under the /mysql-test/var folder. In this case, I am looking for the binary file for the master, which is the first server and, therefore, the folder is named mysqld.1, and the data directory, named data. Thus, the relative path from mysql-test is ./var/mysqld.1/data.

Listing 8-39 shows the output of the mysqlbinlog client application dumping the binary log of the master in the test topology.

Listing 8-39. Examining the Binary Log on the Master with mysqlbinlog

cbell@ubuntu:$ ../client/mysqlbinlog ./var/mysqld.1/data/master-bin.000001
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#121003 16:03:59 server id 1 end_log_pos 121 CRC32 0x83c06bae Start: binlog v 4, server v 5.6.6-m9-debug-log created 121003 16:03:59 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
X8RsUA8BAAAAdQAAAHkAAAABAAQANS43LjAtbTEwLWRlYnVnLWxvZwAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABfxGxQEzgNAAgAEgAEBAQEEgAAXQAEGggAAAAICAgCAAAACgoKGRkAAAGu
a8CD
'/*!*/;
# at 121
#121003 16:05:23 server id 1 end_log_pos 326 CRC32 0x559a612a Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1349305523/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1073741824/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
GRANT REPLICATION SLAVE ON *.* TO 'rpl'@'localhost'
/*!*/;
# at 326
# Slave Connect:
# Host: 127.0.0.1 Port: 13001 Server_Id: 2
#121003 16:05:23 server id 2 end_log_pos 390 CRC32 0x262bb144 DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

The output may seem like a bunch of random, magical strings and bits, but trust me, it all makes sense. Near the bottom of the list you can see the result of the Slave_connect_log_event. Remember, the Slave_connect_log_event::print() method is used to display binary-log events by the mysqlbinlog client application. The print() method was designed to print two lines for the event: a title and the payload displaying the hostname, port, and server_id of the slave.

If this binary log was from a master with many slaves, some of which have been added, removed, and added again, the output of running the mysqlbinlog client application would show all of the slave-connection events. In fact, you can pipe the output to a program such as grep to find all of the locations in the file for the ‘# Slave Connect:’ lines.

Aside from this utility, this extension of the replication features has demonstrated the location of the log-event code and how log events are written to and read from the binary log. I hope that this exercise has given you some ideas for solving your own unique and advanced replication challenges.

Summary

In this chapter, I’ve presented a brief tutorial of MySQL replication, including why you would use it, a look at its architecture, and a tour of the replication source code. You learned how to setup replication as well as several ways to extend the MySQL replication feature set.

In the next chapter, I present another powerful feature of MySQL – the pluggable architecture. I explore the architecture through an exploration of another advanced feature - pluggable authentication. I present a short introduction into the pluggable facility in MySQL and present a sample authentication plugin that uses RFID tags for user validation.

¹ Some feel that the names of these roles are derogatory and have suggested they may be offensive in some cultures. Thus, in the future, these names may change, but the role or job of the server is not likely to change.

² MySQL has a multithreaded slave feature that implements multiple threads to read the relay log, thereby improving performance of the slave for certain use cases. A deeper discussion of this feature is beyond the scope of this work but can be found in the online reference manual.

³ The HAVE_REPLICATION conditional compilation is used primarily for the embedded server.