MySQL High Availability (2014)
Appendix B. A GTID Implementation
With MySQL 5.6, global transaction identifiers were introduced to provide a way to uniquely identify a transaction regardless of what server it is executed on. Handling failovers with a 5.6 server is very easy, and you can find a description of that in Failover Using GTIDs. But if you do not have a 5.6 server and still need to perform failover, you can use the methods described in this appendix, basically by implementing your own global transaction identifiers and writing them to the binary log. The goal is to implement the features necessary to perform slave promotion.
Adding GTIDs to a Server
To match the latest transaction on each of the slaves with the corresponding event in the binary log of the promoted slave, you need to tag each transaction. The content and structure of the tags don’t matter; they just need to be uniquely identifiable no matter who executed the transaction so each transaction on the master can be found in the promoted slave’s binary log. These tags are the global transaction identifiers that we are going to implement in this appendix.
The easiest way to accomplish this is to insert a statement at the end of each transaction that updates a special table and use that to keep track of where each slave is. Just before committing each transaction, a statement updates the table with a number that is unique for the transaction.
Tagging can be handled in two main ways:
§ Extending the application code to perform the necessary statements
§ Calling a stored procedure to perform each commit and writing the tag in the procedure
Because the first approach is easier to follow, it will be demonstrated here. If you are interested in the second approach, see Stored Procedures to Commit Transactions later in this appendix.
To implement global transaction identifiers (GTIDs), create two tables as in Example B-1: one table named Global_Trans_ID to generate sequence numbers and a separate table named Last_Exec_Trans to record the GTID.
The server identifier is added to the definition of Last_Exec_Trans to distinguish transactions committed on different servers. If, for example, the promoted slave fails before all the slaves have managed to connect, it is very important to distinguish between the transaction identifier of the original master and the transaction identifier of the promoted slave. Otherwise, the slaves that didn’t manage to connect to the promoted slave might start to execute from a position that is wrong when they are redirected to the second promoted slave. This example uses MyISAM to define the counter table, but it is possible to use InnoDB for this as well.
In this section, to keep code simple and familiar, we show the operations as plain SQL. In later sections, we show sample code that illustrates how to automate the operations as part of your applications.
Example B-1. Tables used for generating and tracking global transaction identifiers
CREATE TABLE Global_Trans_ID (
number INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
) ENGINE = MyISAM;
CREATE TABLE Last_Exec_Trans (
server_id INT UNSIGNED,
trans_id INT UNSIGNED
) ENGINE = InnoDB;
-- Insert a single row with NULLs to be updated.
INSERT INTO Last_Exec_Trans() VALUES ();
The next step is to construct a procedure for adding a global transaction identifier to the binary log so that a program promoting a slave can read the identifier from the log. The following procedure is suitable for our purposes:
1. Insert an item into the transaction counter table, making sure to turn off the binary log before doing so, because the insert should not be replicated to the slaves:
2. master> SET SQL_LOG_BIN = 0;
3. Query OK, 0 rows affected (0.00 sec)
4.
5. master> INSERT INTO Global_Trans_ID() VALUES ();
Query OK, 1 row affected (0.00 sec)
6. Fetch the global transaction identifier using the function LAST_INSERT_ID. To simplify the logic, the server identifier is fetched from the server variable server_id at the same time:
7. master> SELECT @@server_id as server_id, LAST_INSERT_ID() as trans_id;
8. +-----------+----------+
9. | server_id | trans_id |
10.+-----------+----------+
11.| 0 | 235 |
12.+-----------+----------+
1 row in set (0.00 sec)
13.Before inserting the global transaction identifier into the Last_Exec_Trans tracking table, you can remove its row from the transaction counter table to save space. This optional step works only for a MyISAM table, because it internally tracks the latest autoincrement value and keeps autoincrementing from the value of the removed row. If you use InnoDB, you have to be careful about leaving the last used global transaction identifier in the table. InnoDB determines the next number from the maximum value in the autoincrement column currently in the table.
14.master> DELETE FROM Global_Trans_ID WHERE number < 235;
Query OK, 1 row affected (0.00 sec)
15.Turn on the binary log:
16.master> SET SQL_LOG_BIN = 1;
Query OK, 0 rows affected (0.00 sec)
17.Update the Last_Exec_Trans tracking table with the server identifier and the transaction identifier you got in step 2. This is the last step before committing the transaction through a COMMIT:
18.master> UPDATE Last_Exec_Trans SET server_id = 0, trans_id = 235;
19.Query OK, 1 row affected (0.00 sec)
20.
21.master> COMMIT;
Query OK, 0 rows affected (0.00 sec)
Each global transaction identifier represents a point where replication can be resumed. Therefore, you must carry out this procedure for every transaction. If it is not used for some transaction, the transaction will not be tagged properly and it will not be possible to start from that position.
To define these tables and configure a server to use these GTIDs, you can use the Promotable class (shown in Example B-2) to handle the new kind of server. As you can see, this example reuses the _enable_binlog helper method introduced in Server Roles, and adds a method to set thelog-slave-updates option. Because a promotable slave requires the special tables we showed earlier for the master, the addition of a promotable slave requires you to add these tables as well. To do this, we write a function named _add_global_id_tables. The function assumes that if the tables already exist, they have the correct definition, so no attempt is made to re-create them. However, the Last_Exec_Trans table needs to start with one row for the update to work correctly, so if no warning was produced to indicate that a table already exists, we create the table and add a row with NULL.
Example B-2. The definition of a promotable slave role
_GLOBAL_TRANS_ID_DEF = """
CREATE TABLE IF NOT EXISTS Global_Trans_ID (
number INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (number)
) ENGINE=MyISAM
"""
_LAST_EXEC_TRANS_DEF = """
CREATE TABLE IF NOT EXISTS Last_Exec_Trans (
server_id INT UNSIGNED DEFAULT NULL,
trans_id INT UNSIGNED DEFAULT NULL
) ENGINE=InnoDB
"""
class Promotable(Role):
def __init__(self, repl_user, master):
self.__master = master
self.__user = repl_user
def _add_global_id_tables(self, master):
master.sql(_GLOBAL_TRANS_ID_DEF)
master.sql(_LAST_EXEC_TRANS_DEF)
if not master.sql("SELECT @@warning_count"):
master.sql("INSERT INTO Last_Exec_Trans() VALUES ()")
def _relay_events(self, server, config):
config.set('mysqld', 'log-slave-updates')
def imbue(self, server):
# Fetch and update the configuration
config = server.get_config()
self._set_server_id(server, config)
self._enable_binlog(server, config)
self._relay_event(server, config)
# Put the new configuration in place
server.stop()
server.put_config(config)
server.start()
# Add tables to master
self._add_global_id_tables(self.__master)
server.repl_user = self.__master.repl_user
This routine configures the slaves and the master correctly for using global transaction identifiers.
Transaction Handling Using GTIDs
You still have to update the Last_Exec_Trans table when committing each transaction. In Example B-3 you can see an example implementation in PHP for committing transactions. The code is written using PHP, because this is part of the application code and not part of the code for managing the deployment.
Example B-3. Code for starting, committing, and aborting transactions
function start_trans($link) {
mysql_query("START TRANSACTION", $link);
}
function rollback_trans($link) {
mysql_query("ROLLBACK", $link);
}
function commit_trans($link) {
mysql_select_db("common", $link);
mysql_query("SET SQL_LOG_BIN = 0", $link);
mysql_query("INSERT INTO Global_Trans_ID() VALUES ()", $link);
$trans_id = mysql_insert_id($link);
$result = mysql_query("SELECT @@server_id as server_id", $link);
$row = mysql_fetch_row($result);
$server_id = $row[0];
$delete_query = "DELETE FROM Global_Trans_ID WHERE number = %d";
mysql_query(sprintf($delete_query, $trans_id),
$link);
mysql_query("SET SQL_LOG_BIN = 1", $link);
$update_query = "UPDATE Last_Exec_Trans SET server_id = %d, trans_id = %d";
mysql_query(sprintf($update_query, $server_id, $trans_id), $link);
mysql_query("COMMIT", $link);
}
We can then use this code to commit transactions by calling the functions instead of the usual COMMIT and ROLLBACK. For example, we could write a PHP function to add a message to a database and update a message counter for the user:
function add_message($email, $message, $link) {
start_trans($link);
mysql_select_db("common", $link);
$query = sprintf("SELECT user_id FROM user WHERE email = '%s'", $email);
$result = mysql_query($query, $link);
$row = mysql_fetch_row($result);
$user_id = $row[0];
$update_user = "UPDATE user SET messages = messages + 1 WHERE user_id = %d";
mysql_query(sprintf($update_user, $user_id), $link);
$insert_message = "INSERT INTO message VALUES (%d,'%s')";
mysql_query(sprintf($insert_message, $user_id, $message), $link);
commit_trans($link);
}
$conn = mysql_connect(":/var/run/mysqld/mysqld1.sock", "root");
add_message('mats@example.com', "MySQL Python Replicant rules!", $conn);
STORED PROCEDURES TO COMMIT TRANSACTIONS
The main approach shown here to sync servers is to implement the transaction commit procedure in the application, meaning that the application code needs to know table names and the intricacies of how to produce and manipulate the global transaction identifier. Once you understand them, the complexities are not as much of a barrier as they might seem at first. Often, you can handle them with relative ease by creating functions in the application code that the application writer can call without having to know the details.
Another approach is to put the transaction commit logic in the database server by using stored procedures. Depending on the situation, this can sometimes be a better alternative. For example, the commit procedure can be changed without having to change the application code.
For this technique to work, it is necessary to put the transaction identifier from the Global_Trans_ID table and the server identifier into either a user-defined variable or a local variable in the stored routine. Depending on which approach you select, the query in the binary log will look a little different.
Using local variables is less likely to interfere with surrounding code because user-defined variables can be viewed or changed outside the stored procedure. This is known as “leaking”.
The procedure for committing a transaction will then be:
CREATE PROCEDURE commit_trans ()
SQL SECURITY DEFINER
BEGIN
DECLARE trans_id, server_id INT UNSIGNED;
SET SQL_LOG_BIN = 0;
INSERT INTO Global_Trans_ID() values ();
SELECT LAST_INSERT_ID() INTO trans_id,
@@server_id INTO server_id;
SET SQL_LOG_BIN = 1;
INSERT INTO Last_Exec_Trans(server_id, trans_id)
VALUES (server_id, trans_id);
COMMIT;
END
Committing a transaction from the application code is then simple:
CALL Commit_Trans();
Now the task remains of changing the procedure for scanning the binary log for the global transaction identifier. So, how will a call to this function appear in the binary log? Well, a quick call to mysqlbinlog shows:
# at 1724
#091129 18:35:11 server id 1 end_log_pos 1899 Query thread_id=75
exec_time=0 error_code=0
SET TIMESTAMP=1259516111/*!*/;
INSERT INTO Last_Exec_Trans(server_id, trans_id)
VALUES ( NAME_CONST('server_id',1), NAME_CONST('trans_id',13))
/*!*/;
# at 1899
#091129 18:35:11 server id 1 end_log_pos 1926 Xid = 1444
COMMIT/*!*/;
As you can see, both the server identifier and the transaction identifier are clearly visible in the output. How to match this statement using a regular expression is left as an exercise for the reader.
Finding Positions of GTIDs
When connecting a slave to the promoted slave and start replication at the right position, it is necessary to find out what position on the promoted slave has the last executed transaction of the slave. This is done by scanning the binary log of the promoted slave to find the GTID.
The logs of the promoted slave can be retrieved using SHOW MASTER LOGS, so you can scan them for global transaction identifiers. For instance, when you read the slave-3-bin.000005 file using mysqlbinlog, part of the output will look like that shown in Example B-4. The transaction received by slave-3 starting at position 596 (highlighted in the first line of the output) has the global transaction identifier received by slave-1, as shown by an UPDATE of the Last_Exec_Trans table.
Example B-4. Output from the mysqlbinlog command for one transaction
# at 596
#091018 18:35:42 server id 1 end_log_pos 664 Query thread_id=952 ...
SET TIMESTAMP=1255883742/*!*/;
BEGIN
/*!*/;
# at 664
#091018 18:35:42 server id 1 end_log_pos 779 Query thread_id=952 ...
SET TIMESTAMP=1255883742/*!*/;
UPDATE user SET messages = messages + 1 WHERE id = 1
/*!*/;
# at 779
#091018 18:35:42 server id 1 end_log_pos 904 Query thread_id=952 ...
SET TIMESTAMP=1255883742/*!*/;
INSERT INTO message VALUES (1,'MySQL Python Replicant rules!')
/*!*/;
# at 904
#091018 18:35:42 server id 1 end_log_pos 1021 Query thread_id=952 ...
SET TIMESTAMP=1255883742/*!*/;
UPDATE Last_Exec_Trans SET server_id = 1, trans_id = 245
/*!*/;
# at 1021
#091018 18:35:42 server id 1 end_log_pos 1048 Xid = 1433
COMMIT/*!*/;
Example B-4 shows that the trans_id 245 is the last transaction seen by slave-1, so now you know that the start position for slave-1 is in file slave-3-bin.000005 at byte position 1048. So to start slave-1 at the correct position, you can now execute CHANGE MASTER and START SLAVE:
slave-1> CHANGE MASTER TO
-> MASTER_HOST = 'slave-3',
-> MASTER_LOG_FILE = 'slave-3-bin.000005',
-> MASTER_LOG_POS = 1048;
Query OK, 0 rows affected (0.04 sec)
slave-1> START SLAVE;
Query OK, 0 rows affected (0.17 sec)
By going backward in this manner you can connect the slaves one by one to the new master at exactly the right position.
This technique works well if the update statement is added to every transaction commit. Unfortunately, there are statements that perform an implicit commit before and after the statement. Typical examples include CREATE TABLE, DROP TABLE, and ALTER TABLE. Because these statements do an implicit commit, they cannot be tagged properly, hence it is not possible to restart just after them. Suppose that the following sequence of statements is executed and there is a crash:
INSERT INTO message_board VALUES ('mats@sun.com', 'Hello World!');
CREATE TABLE admin_table (a INT UNSIGNED);
INSERT INTO message_board VALUES ('', '');
If a slave has just executed the CREATE TABLE and then loses the master, the last seen global transaction identifier is for the INSERT INTO—that is, just before the CREATE TABLE statement. Therefore, the slave will try to reconnect to the promoted slave with the transaction identifier of theINSERT INTO statement. Because it will find the position in the binary log of the promoted slave, it will start by replicating the CREATE TABLE statement again, causing the slave to stop with an error.
You can avoid these problems through careful design and use of statements that perform implicit commits. For example, if CREATE TABLE is replaced with CREATE TABLE IF NOT EXISTS, the slave will notice that the table already exists and skip execution of the statement.
The first step is to fetch the binlog files remotely, similar to the method used in Chapter 3. In this case, we need to fetch the entire binlog file, as we do not know where to start reading. The fetch_remote_binlog function in Example B-5 fetches a binary log from a server and returns an iterator to the lines of the binary log.
Example B-5. Fetching a remote binary log
importsubprocess
def fetch_remote_binlog(server, binlog_file):
command = ["mysqlbinlog",
"--read-from-remote-server",
"--force",
"--host=%s" % (server.host),
"--user=%s" % (server.sql_user.name)]
if server.sql_user.passwd:
command.append("--password=%s" % (server.sql_user.passwd))
command.append(binlog_file)
return iter(subprocess.Popen(command, stdout=subprocess.PIPE).stdout)
The iterator returns the lines of the binary log one by one, so the lines have to be further separated into transactions and events to make the binary log easier to work with. Example B-6 shows a function named group_by_event that groups lines belonging to the same event into a single string, and a function named group_by_trans that groups a stream of events (as returned by group_by_event) into lists, where each list represents a transaction.
Example B-6. Parsing the mysqlbinlog output to extract transactions
delimiter = "/*!*/;"
def group_by_event(lines):
event_lines = []
for line inlines:
if line.startswith('#'):
if line.startswith("# End of log file"):
del event_lines[-1]
yield ''.join(event_lines)
return
if line.startswith("# at"):
yield ''.join(event_lines)
event_lines = []
event_lines.append(line)
def group_by_trans(lines):
group = []
in_transaction = False
for event ingroup_by_event(lines):
group.append(event)
if event.find(delimiter + "\nBEGIN\n" + delimiter) >= 0:
in_transaction = True
elifnotin_transaction:
yield group
group = []
else:
p = event.find("\nCOMMIT")
if p >= 0 and (event.startswith(delimiter, p+7)
orevent.startswith(delimiter, p+8)):
yield group
group = []
in_transaction = False
The last step is to implement the function to actually scan the binary logs of a server and locate the position given a GTID. This is done by scanning the binary log files starting with the most recent one and and processing older ones. The files are scanned in this order because the function assumes it is used for slave promotion, and here the most recent binary log should contain the GTID.
Example B-7. Finding file position from GTID
_GIDCRE = re.compile(r"^UPDATE Last_Exec_Trans SET\s+"
r"server_id = (?P<server_id>\d+),\s+"
r"trans_id = (?P<trans_id>\d+)$", re.MULTILINE)
_HEADCRE = re.compile(r"#\d{6}\s+\d?\d:\d\d:\d\d\s+"
r"server id\s+(?P<sid>\d+)\s+"
r"end_log_pos\s+(?P<end_pos>\d+)\s+"
r"(?P<type>\w+)")
def scan_logfile(master, logfile, gtid): 
frommysql.replicant.serverimport Position
lines = fetch_remote_binlog(master, logfile)
# Scan the output to find GTID update statements
for trans ingroup_by_trans(lines):
if len(trans) < 3:
continue
# Check for an update of the Last_Exec_Trans table
m = _GIDCRE.search(trans[-2])
if m:
server_id = int(m.group("server_id"))
trans_id = int(m.group("trans_id"))
if server_id == gtid.server_id andtrans_id == gtid.trans_id:
# Check for an information comment with end_log_pos. We
# assume InnoDB tables only, so we can therefore rely on
# the transactions to end in an Xid event.
m = _HEADCRE.search(trans[-1])
if m andm.group("type") == "Xid":
return Position(server_id, logfile, int(m.group("end_pos")))
return None
def find_position_from_gtid(server, gtid): 
# Read the master logfiles of the new master.
server.connect()
logs = [ row["Log_name"] for row inserver.sql("SHOW MASTER LOGS") ]
server.disconnect()
logs.reverse()
for log inlogs:
pos = scan_logfile(server, log, gtid)
if pos:
return pos
return None
This function scans the mysqlbinlog output for the GTID. The function accepts a server from which to fetch the binlog file, the name of a binlog file to scan (the filename is the name of the binary log on the server), and a GTID to look for. It returns either a position of the end of the transaction containing the GTID, or None if the GTID cannot be found in the binlog file.
This function iterates over the binlog files in reverse order and looks for a GTID. It starts with the most recent one and continue with older ones until either the GTID is found in a file, or all binlog files have been processed. If the GTID is found, the position of the end of the transaction containing the GTID is returned. Otherwise, None is returned.