I'll be talking about MySQL®, the companies that now exist to support it, and the third party products that are starting to proliferate in what appears to be a community effort to address perceived shortcomings in the Oracle offerings.  Many of these offerings are in the HA space, and there have been some pretty amazing developments recently.

So if you want to find some history of Oracle's effect on MySQL's development (and a hint, it started well before the Sun acquisition), or you want to find out what is out there to assist you in developing HA and Distributed database systems, come along.  I'll be wearing my SkySQL shirt so I should be easy to pick.

Replication is the technology that allows data to be stored on multiple servers. Typically this is used in "scale out" applications.  "Scale out" is used in contrast to "Scale up" where to scale a solution you buy a bigger box to run it on, where "scale out" means you buy more boxes.  Each has its benefits and drawbacks, with the usual benefit of scale out being that you get more bang for your buck.

The way replication works in MySQL is pretty simple.  One server is identified as the master, and writes every transaction to a file, the binary log.  Other servers (and there may be many) act as slaves and request information from the master. The slave keeps track of where it got up to and asks the master for the next transaction in the file.  In general the master doesn't know or care where the slave is up to, it just sends out the requested transaction to whoever has the right credentials to ask for it.

On the slave there are two threads running, one that requests events from the master binlog (binary log) and writes them to the relay log (which in reality is just another binlog), and the second thread that reads the relay log and executes the queries. In order to avoid non-deterministic outcomes the SQL thread is just that, a single thread.  Long running events can result in the replication lagging well behind the state of the master.

This is the traditional asynchronous replication.  Newer versions of MySQL now support semi-synchronous replication as well.  In this mode the master will not return control back to the querying process until at least one slave reports that it has received the transaction events and written them to its own relay log.  Note that this is written to the relay log but not necessarily written to the database.

You may wonder under what circumstance replication would be of use.  If your application is mainly read-intensive (as many web applications are) then replication gives you the ability to spread reads across multiple slaves to reduce contention.  If your application is mainly write intensive, then replication to multiple slaves will not, of itself, give you any relief.  Indeed, because the replication is single-threaded and all writes on the master must be written to the slaves, you can in fact hurt performance with a replicated setup.

What about more than one master?  This is possible but requires careful thought and planning.  MySQL replication keeps track of which server initiated the transaction, so "circular replication" where a slave acts also as a master should not cause problems, however there are some serious issues to consider.  Take for instance a customer table with the customer ID being auto incremented.  What happens if there are writes to both masters in a master<->master replication setup?  Both will grab the next ID in sequence, then the other will pull that transaction and try and will end up with a duplicate index error, causing replication to fail.  You can get around this by setting the initial auto_increment value on each server and setting the auto_increment_increment value to the number of masters in use.  Remebering however that both masters now have to write all traffic that goes to the pair, there is not a great deal of benefit in such an arrangement.  Other technologies, such as sharding, turn out to offer better scale out opportunities for multi-master deployments.

There are, however, some tools to make multi-master work more easily.  MMM (Multi-Master Replication Manager for MySQL) is one.  MHA (Master High Availability for MySQL) is somewhat different, allowing easy management of master failover.

That is probably enough theory, lets look at how to build a replication system.

For the master, the only requirement is that it writes its transactions to a binary log, and that it has a server ID set.  To do this we need to add the following to the my.cnf file:

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log

The server-id is an integer that must be unique for all servers in the same replication set.  This is because of the tracking of transactions to avoid circular replication errors.  The log_bin is the path to the binary log file to use.  Each file will be appended with a sequence number to allow the server to rotate log files when they grow too large, or a flush request is received.

One other value that is useful is to set an expiry on the binlogs so that they don't take over all of your disk.  You need to ensure that you have enough binlog capacity for normal replication lag and for handling backup/recovery, but beyond that you can get MySQL to automatically delete the older binlogs.

expire_log_days = 14

Now the master will at least be writing transactions to the binary logs.  Now we have to set up to allow slaves to replicate.  First step is to create a user that the slaves will connect with.  This user must have REPLICATION SLAVE permissions to be able to read the binlog.  Note that this user is created on the master. As a privileged user, in the MySQL client:

CREATE USER 'replicator'@'192.168.%' IDENTIFIED BY 'mypass';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'192.168.%';

The first command creates our user, the second grants the required permissions.  Note that I've used a wildcarded network address, you can also use a hostname wildcard.

For the slave we need a few things, first we need to set a server-id that is not the same as the master (or any other slave we are creating). Then we need a copy of the data on the master at the point at which the slave is to be started, and we need to then tell the slave about the master.

We can create the server-id in the same manner as we did for the master - but we don't need to specify a binlog (unless we are acting as a relay master).

server-id = 2

To get our first copy of the master data, the best idea is to use mysqldump.  Before we do, we need to stop the master from writing to the binary log and record its current position.  From the mysql command line:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000092 |      106 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

We need to keep this session open so that the lock remains in place, and from another terminal run a mysqldump.

mysqldump --all-databases > all_db.sql

Once the backup is completed we can either drop the lock session or issue:

UNLOCK TABLES;

Great, we now have a backup and we have the position in the binary log at the point at which the backup occurred.  These two together provide us with all we need to create our slave.

First step is to load the above SQL file into the slave.   On the slave you can use the mysql command line client with the SQL file copied from the master.

mysql < all_db.sql

On the slave server we now need to issue a CHANGE MASTER command to tell it where the master is, and where to start replicating from.  This information will be written to the master.info file that is read at startup and updated by the replication process so that the slave will be able to resync with the master on restart.

CHANGE MASTER TO MASTER_HOST = 'master'
  MASTER_USER = 'replicator'
  MASTER_PASSWORD = 'mypass'
  MASTER_LOG_FILE = 'mysql-bin.000092'
  MASTER_LOG_POS = 106;

The MASTER_HOST, MASTER_USER and MASTER_PASSWORD must match the master and the user created above with replication slave permission.  The MASTER_LOG_FILE and MASTER_LOG_POS come from the SHOW MASTER STATUS used when we did the backup of the master.

Now all that is left to do is to start the slave processes:

START SLAVE;

We can use the same backup and the same processes above to create as many slaves as we need.

If you have to set up a complex replication scenario that is not covered by the above, you may find the SkySQL Reference Architecture provisioning system useful.  This service creates master/slave high availability configurations and packages software and configurations together so that you can easily install a system from scratch that meets all your replication needs.

Disclaimer

I am employed by SkySQL Ab, which provides support, training and services for MySQL, MariaDB and Drizzle and is staffed by many ex-MySQL AB employees. While I try to be unbiased in all of my public opinions, and my opinions are all my own and not those of my employer, I work for SkySQL because I believe in what they are doing and therefore cannot be truly unbiased.

MySQL is a registered trademark of Oracle Corporation,  MariaDB is a trademark of MontyProgram, and Drizzle is a trademark.

In late 2005 I took a position in the web team (a part of Marketing) at MySQL AB.  It was a great company to work for, with a great team of people and a truly great spirit.  The idea that they were making a difference every day was palpable. I then saw it taken over by Sun Microsystems and shortly after by Oracle Corporation.  We went from 500 employees to 30,000 and then to 100,000.  My job satisfaction started to decline.  I felt more like I was battling for every concession rather than revelling in what we were able to achieve.  I needed a change.

When I joined SkySQL it was like going home - I knew pretty much everyone at the company and that great spirit of family, that sense of being able to achieve great things, the coming together of like minds with a shared vision was what drew me there and continues to inspire me.  It is, after all, about the people.  The team that created the world class support that MySQL AB was renowned for, the team that developed the training courses that brought MySQL knowledge to thousands, the management that inspired me in the early days at MySQL AB were all here, doing what they do best.

One of the prime reasons I moved from Oracle was that I saw from the inside the change in focus on MySQL. I saw that the team that manages the mysql.com domains cut from 6 down to 2. I saw the desperate backroom struggles to ensure the MySQL community sites were not taken down. I saw some truly stupid management decisions based solely on personal animosity rather than sound business sense. I saw the sales team starved of leads because of that stupidity. I saw the inter-departmental rivalry that instead of being a force for excellence was a block to cooperation and a stifling of innovation. I saw great talent leaving the company in an air of pessimism.

What I see in SkySQL is an optimism and a sense of shared goal, a vitality and a willingness to go beyond the normal to achieve excellence. I see what we used to call the "can-do" spirit.  That ethos is what keeps me showing up at work each day, trying to do my part in the bigger picture and making SkySQL the first place you think of when you think open databases.

There are some exciting things happening at SkySQL and the future is looking very bright.  I feel honoured to be a part of it all.

Transactions and Replication

We will discuss replication in depth later in the series, however it is sufficient for the moment to say that replication allows you to copy data in near real-time between MySQL servers and keep them synchronised.  What gets transferred are the changes that are made to your tables and data. So what about rolled-back (aborted) transactions?  Since the state after the rollback is essentially the same as the state before the transaction started, there seems little point in replicating those statements.  Indeed they are not replicated. Only completed transactions are.

I briefly mentioned that there is an autocommit setting that means that without using a transaction block, every query becomes its own transaction.  In the light of replication this makes a lot of sense.

Transaction Duration

Transactions may need to lock resources so that concurrent changes don't clobber each other, while allowing other queries access to the data that may be subject to a transaction, in a consistent manner. How this is done depends on the transaction isolation level, but in all levels you can be sure that inserting into a primary key will lock that index value out from other transactions doing the same thing.  In general this means that the second transaction will block until the first transaction is either committed or rolled back.  For web, having a locked resource hanging around for any length of time is a real killer.  This can be avoided by keeping transactions as short as possible, and to be careful of using range-based operations.

Let us say that we have two people trying to buy the same book.  As you saw from the previous episode the transaction we've defined does a number of operations, all of which need to complete before the book can be bought.  They are:

• Create a purchase order record
• Create purchase order item records for the book(s) purchased
• Update the summary field on the purchase order record
• Decrement the stock count for the book(s) purchased

If you look at those steps, you could see how a web application designer might get themselves into trouble.  If you created a purchase order and allow multiple books to be bought, then you may be tempted to keep the transaction open while the user browses and gets all their books purchased.  This would be a complete disaster.  It would mean there was an open-ended time the transaction was held, with locks on things like the book record (for decrementing the count) and therefore the entire system would almost certainly become deadlocked at some point.  This is where you need to be very aware of what the logic of the application is and how to bound the transactions.

In the above case we could handle this in a number of ways.  The easiest would be to create the purchase order record as a separate transaction, then for each book purchased, do the remaining three steps in another transaction.  Another way would be to separate the actual purchase order creation from the selection process.  You'll have seen this in process in a number of sites - where you have a Shopping Cart that you add books to and it isn't until checkout that it all ends up as a single purchase order.

No matter which way you do it - remember that you should never create a transaction that is unbounded in time. And on the web that means never creating a transaction that needs user interaction within the transaction.

Short Circuited Transactions

There are some operations that, if placed within a transaction, will perform an implicit commit on the statements that have preceeded it, effectively short-circuiting the transaction and creating a multiple transaction set.  Things like DDL (Data Definition Language) statements that modify the table structure or add indexes will be run in their own transaction, causing an implicit commit of any statements preceeding them.  DDL statements, in general, cannot be rolled back as they run in their own transaction.

More information on the caveats with transactions can be found in the online manual reference.

Disclaimer

I am employed by SkySQL Ab, which provides support, training and services for MySQL, MariaDB and Drizzle and is staffed by many ex-MySQL AB employees. While I try to be unbiased in all of my public opinions, and my opinions are all my own and not those of my employer, I work for SkySQL because I believe in what they are doing and therefore cannot be truly unbiased.

MySQL is a registered trademark of Oracle Corporation,  MariaDB is a trademark of MontyProgram, and Drizzle is a trademark.

Database transactions are very similar to real world transactions. They define a set of steps required to happen together in order for a transaction to be complete.  A real-world example might be that you buy a trinket from a store.  You find the trinket, then take it to the counter, find out the price, hand over the cash and receive your trinket.  That is a completed transaction.  Should you not have the available cash, the transaction would not be able to be completed and you would need to return the item, effectively rolling back that transaction.  It is not a great deal different in the database, except that the transaction can be controlled to a greater degree.

Before going too far, we need to build a few more tables for our database to enable us to sell our books.  To make life easier I've created these tables in this downloadable file.  You can use your favourite MySQL client to create the database for the purposes of following this episode.  The tables I've created are customer, purchase_order and order_item.  We'll see how they work in handling a typical transaction - we can use SHOW CREATE TABLE to see how they are defined:

SHOW CREATE TABLE `customer`\G
*************************** 1. row ***************************
       Table: customer
Create Table: CREATE TABLE `customer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET latin1 NOT NULL,
  `address1` varchar(80) CHARACTER SET latin1 NOT NULL,
  `address2` varchar(80) CHARACTER SET latin1 DEFAULT NULL,
  `postcode` varchar(8) CHARACTER SET latin1 DEFAULT NULL,
  `city` varchar(80) CHARACTER SET latin1 DEFAULT NULL,
  `country` varchar(80) CHARACTER SET latin1 DEFAULT NULL,
  `state` varchar(20) CHARACTER SET latin1 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

SHOW CREATE TABLE `purchase_order`\G
*************************** 1. row ***************************
       Table: purchase_order
Create Table: CREATE TABLE `purchase_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer` int(11) NOT NULL DEFAULT '0',
  `order_date` datetime NOT NULL,
  `ship_date` datetime DEFAULT NULL,
  `order_total` decimal(9,2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_order_customer` (`customer`),
  CONSTRAINT `fk_order_customer` FOREIGN KEY (`customer`) REFERENCES `customer` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

SHOW CREATE TABLE `order_item`\G
*************************** 1. row ***************************
       Table: order_item
Create Table: CREATE TABLE `order_item` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `purchase_order` int(11) NOT NULL,
  `book` int(11) NOT NULL,
  `quantity` int(11) NOT NULL DEFAULT '0',
  `price` decimal(5,2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_item_order` (`purchase_order`),
  KEY `fk_item_book` (`book`),
  CONSTRAINT `fk_item_order` FOREIGN KEY (`purchase_order`) REFERENCES `purchase_order` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_item_book` FOREIGN KEY (`book`) REFERENCES `book` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

You'll notice a few things that may not immediately make sense.  In most cases you don't want duplicate data in different tables.  This is why we break out things like publisher from book, for instance.  However in purchase_order we have an order_total value that could be derived from the order_item records, and similarly we have a price field in order_item that is the same as the price field in book.  There is a lot of sense in doing this however.  Because we are dealing with financial transactions we must ensure they can stand alone.  So we need to ensure that the price of the book is not whatever the current price is, but the price it was at the  time it was sold.  This is why we have the price field in two places as they are not exactly the same.  The order_total field is a summary data field.  It means that we don't necessarily have to have a complicated multi-table join if we want to find how much a customer has bought.  Summary data fields are often used to speed up common queries by simplifying them.

One other field I've added is the stock_on_hand value in the book table.  This will allow us to set a count of books available for sale and decrement them when we sell something.

If we are to sell a book to a customer, we can create the customer record at any time because it won't matter if they don't buy straight away, so customer is not tied directly to a sale, so we can keep it out of our transaction.  When they purchase a book, however, we need to do a number of steps that have to be kept together.   If we don't we could end up with inconsistent data.

Lets go with an example.  I'll create a new customer and set a stock_on_hand value for Peter Temple's The Broken Shore:

INSERT INTO `customer` SET `name` = 'Adam Donnison',
 `address1` = '41 Bendigo Street',
 `city` = 'Collingwood',
 `state` = 'VIC', `postcode` = '3066', `country` = 'Australia';
Query OK, 1 row affected (0.00 sec)

UPDATE `book` SET `stock_on_hand` = 5 WHERE `id` = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

It is important to realise at this point that every single INSERT, UPDATE or other data manipulation statement that we have performed so far has been a transaction in its own right.  This implicit transaction is as a result of the default value of the MySQL server variable 'autocommit', which is normally turned on.  If this is turned off we need to explictly make all queries part of a transaction.  More confusingly if you issue a statement that changes data, a transaction will be automatically started, but you need to commit the transaction for it to be permanently recorded in the database.  But let us look at the anatomy of a simple transaction.

In this example we need to ensure the following happen at effectively the same time:

• Decrement the stock on hand value of the book sold by the quantity sold
• Create a purchase order entry
• Create an entry for each book in the order_item table
• Ensure the purchase order total is the total of the items in the order

START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

INSERT INTO `purchase_order` SET `customer` = 1, `order_date` = NOW();
Query OK, 1 row affected (0.00 sec)

SET @po_number = LAST_INSERT_ID();
Query OK, 0 rows affected (0.00 sec)

INSERT INTO `order_item` SET `purchase_order` = @po_number,
 `book` = 1,
 `price` = (SELECT `price` FROM `book` WHERE `id` = 1),
 `quantity` = 1;
Query OK, 1 row affected (0.00 sec)

UPDATE `purchase_order` SET `order_total` =
 ( SELECT SUM(`price` * `quantity`) FROM `order_item` WHERE `purchase_order` = @po_number )
 WHERE `id` = @po_number;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1

UPDATE `book` SET `stock_on_hand` = `stock_on_hand` - 1 WHERE `id` = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

COMMIT;
Query OK, 0 rows affected (0.00 sec)

There are a few constructs here that are potentially confusing, so we need to go through each one and make sure everything is clear.

First, the SET @po_number = LAST_INSERT_ID() uses a MySQL function to find the ID of the record that we just inserted (the id field for the purchase_order record) and assigns it to a variable, @po_number, that we can use in subsequent queries. The reason we need to do this is to ensure we get an order_item record correctly associated with our newly created purchase order.

We've also used sub-selects. These are stand-alone select queries that are used to provide values for other queries.  In the first case it is a simple select to get the price field from the book table and insert it as the selling price for the order_item.  The second one uses an aggregation function, SUM(`price` * `quantity`) to find all of our order_item records (in this case, one) and determine what the purchase order total is.  You are more likely to be doing this from an application so you probably wouldn't do a sum up.  Alternatively you could use a separate variable that you updated as you added order_item records.  Regardless, what we have done is a set of queries that together make up a transaction.

If at any point in the transaction we decided that we had an error and we couldn't continue (perhaps we found the customer record was incorrect, or the application received an error) we can use ROLLBACK to abort the transaction and wind the database back to the point it was before we issued the START TRANSACTION.  By default if we were to drop the connection to the database (by closing the connection or a terminal failure in our application) or if the database server failed, any pending transaction, that is one that was STARTed but never COMMITted, would be rolled back.

Disclaimer

I am employed by SkySQL Ab, which provides support, training and services for MySQL, MariaDB and Drizzle and is staffed by many ex-MySQL AB employees. While I try to be unbiased in all of my public opinions, and my opinions are all my own and not those of my employer, I work for SkySQL because I believe in what they are doing and therefore cannot be truly unbiased.

MySQL is a registered trademark of Oracle Corporation,  MariaDB is a trademark of MontyProgram, and Drizzle is a trademark.