ChapTEr 8 ■ DEmonsTraTion of high availabiliTy TEChniquEs
■Note The replication examples in this chapter require mysql server version 5.6.5 or higher.
Experience with using MySQL replication will help overcome many of the normal needs you may
have along the way. Indeed, a quick read through the replication section in the online MySQL reference
manual will help considerably. However, there are a few things that can occur that are less obvious in the
documentation (but are described, just not called out so much). I cover these topics using examples in this
section starting with failover and switchover.
You will also examine a number of advanced MySQL replication techniques, best practices, and even
some examples of how to leverage MySQL replication in your IOT solutions. Let’s begin with a collection of
key techniques to get the most out of MySQL replication; first up is a key database concept called transaction
processing.
Transaction Processing
A transaction is a set of operations on a database that you want to make atomic. That is, if one fails, you want
all the changes since the start of the transaction to be reversed (reverted). The classic example is moving
money from one bank account to another. This requires deducting the amount from the first account and
adding it to the second—an act that requires two steps. If either should fail, neither of those changes should
occur; otherwise, you’ve got money unallocated or allocated without a transfer!
We start a transaction with the special START TRANSACTION or BEGIN statement. Should all statements
succeed, we issue a COMMIT statement to complete the action and instruct the database server to write the
changes permanently. Should we want to revert the changes, we can issue a ROLLBACK statement to undo all
the changes.
Transactions are supported in MySQL when using the InnoDB storage engine. This engine is the default
storage engine in MySQL, so transactions are enabled by default.
Listing 8-1 shows an example of this in action. We start with a test table containing only two columns—
an auto-increment column and an integer column. Observe how transactions can be used to preserve data.
Listing 8-1. Demonstration of Transaction Processing in MySQL
mysql> USE test;
Database changed
mysql> CREATE TABLE test_trans(id INT AUTO_INCREMENT PRIMARY KEY, value INT);
Query OK, 0 rows affected (0.07 sec)
mysql> INSERT INTO test_trans VALUES (NULL,1), (NULL,2), (NULL,3);
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO test_trans VALUE (NULL,4);
Query OK, 1 row affected (0.00 sec)