News‎ > ‎

Drizzle Simple Replication Walkthrough

posted Nov 15, 2011, 1:19 AM by Kent Bozlinski

Replication in Drizzle is very simple and multi-source replication is supported. For a walk through of multi-master (multi-source) replication see David Shrewsbury's excellent post here. Because it was very succinctly here I am quoting a lot of his provisioning a new slave post on replication here. But I have added in some detail on the slave.cfg file for clarity for newbies like me, as well as some more detail on the options and their purpose.

A lot of this can also be found in the documentation but here I’m going to walk through the steps. Also see the slave docs here for any questions you may have.

For our purposes we will walk through the features of setting up basic replication between a master and slave server.

You will need to set up your slave.cfg file before you do anything else. It should be located in the "/usr/local" directory but could also be located anywhere you like. Mine is in the /tmp/slave.cfg.

This is a typical setup.

master-host = "your ip address"
master-port = 4427
master-user = kent
master-pass = samplepassword
io-thread-sleep = 10
applier-thread-sleep = 10

Setting up the master is the next step. An important requirement is to start the master Drizzle database server with the --innodb.replication-log option, and a few other options in most circumstances. More options can be found in the options documentation. These are the most common options needed for a replication master. For example:

The InnoDB replication log must be running:

--innodb.replication-log

PID must be set:

--pid-file=/var/run/drizzled/drizzled.pid

the address binding for Drizzle's default port (4427):

--drizzle-protocol.bind-address=0.0.0.0

The address binding for systems replicating through MySQL's default port (3306):

--mysql-protocol.bind-address=0.0.0.0

Data Directory can be set other than default:

--datadir=$PWD/var

For more complex setups, the server id option may be appropriate to use:

--server-id=?

To run Drizzle in the background, thereby keeping the database running if the user logs out:

--daemon

So the start command looks like this on my server:

master> usr/local/sbin/drizzled \
--innodb.replication-log \
--pid-file=/var/run/drizzled/drizzled.pid \
--drizzle-protocol.bind-address=0.0.0.0 \
--mysql-protocol.bind-address=0.0.0.0 \
--daemon

Starting the slave is very similar to starting the master but there are a couple of steps before you are ready to start it up. The following is quoted from David’s blog post on simple replication.

1. Make a backup of the master databases.
2. Record the state of the master transaction log at the point the backup was made.
3. Restore the backup on the new slave machine.
4. Start the new slave and tell it to begin reading the transaction log from the point recorded in #2.

Steps #1 and #2 are covered with the drizzledump client program. If you use the –single-transaction option to drizzledump, it will place a comment near the beginning of the dump output with the InnoDB transaction log metadata. For example:
master> drizzledump --all-databases --single-transaction > master.backup
master> head -1 master.backup
-- SYS_REPLICATION_LOG: COMMIT_ID = 33426, ID = 35074

The SYS_REPLICATION_LOG tells the slave where to start reading from. It has two pieces of information:

• COMMIT_ID: This value is the commit sequence number recorded for the most recently executed transaction stored in the transaction log. We can use this value to determine proper commit order within the log. The unique transaction ID cannot be used since that value is assigned when the transaction is started, not when it is committed.
• ID: This is the unique transaction identifier associated with the most recently executed transaction stored in the transaction log.

Now you need to start the server without the slave plugin, then import the backup from the master, then shutdown and restart the server with the slave plugin. This is straight out of the docs:

slave> sbin/drizzled --datadir=$PWD/var &
slave> drizzle < master.backup slave> drizzle –shutdown

Now that the backup is imported, restart the slave with the replication slave plugin enabled and use a new option, –slave.max-commit-id, to force the slave to begin reading the master’s transaction log at the proper location:

You need two options for sure, the add slave plugin and defining the slave.cfg file. So the most basic start command is:

slave> /usr/local/sbin/drizzled \
--plugin-add=slave \
--slave.config-file=/usr/local/etc/slave.cfg

A more typical startup will need more options, My startup looks like this:

slave> /usr/local/sbin/drizzled \
--plugin-add=slave \
-- datadir=$PWD/var \
--slave.config-file=/usr/local/etc//slave.cfg \
--pid-file=/var/run/drizzled/drizzled.pid \
--drizzle-protocol.bind-address=0.0.0.0 \
--mysql-protocol.bind-address=0.0.0.0 \
--daemon \
-- slave.max-commit-id=33426

The slave.max-commit-id is found in the dump file that we made from the master and tells the slave where to start reading from.

If you need more info for your particular setup you can view a lot of detail in the sys replication log and the innodb replication log tables that will help you with clarity.

Two tables in the DATA_DICTIONARY schema provide the different views into the transaction log: the SYS_REPLICATION_LOG table and the INNODB_REPLICATION_LOG table.

drizzle> SHOW CREATE TABLE data_dictionary.sys_replication_log\G
*************************** 1. row ***************************
Table: SYS_REPLICATION_LOG
Create Table: CREATE TABLE `SYS_REPLICATION_LOG` (
`ID` BIGINT,
`SEGID` INT,
`COMMIT_ID` BIGINT,
`END_TIMESTAMP` BIGINT,
`MESSAGE_LEN` INT,
`MESSAGE` BLOB,
PRIMARY KEY (`ID`,`SEGID`) USING BTREE,
KEY `COMMIT_IDX` (`COMMIT_ID`,`ID`) USING BTREE
) ENGINE=InnoDB COLLATE = binary

drizzle> SHOW CREATE TABLE data_dictionary.innodb_replication_log\G
*************************** 1. row ***************************
Table: INNODB_REPLICATION_LOG
Create Table: CREATE TABLE `INNODB_REPLICATION_LOG` (
`TRANSACTION_ID` BIGINT NOT NULL,
`TRANSACTION_SEGMENT_ID` BIGINT NOT NULL,
`COMMIT_ID` BIGINT NOT NULL,
`END_TIMESTAMP` BIGINT NOT NULL,
`TRANSACTION_MESSAGE_STRING` TEXT COLLATE utf8_general_ci NOT NULL,
`TRANSACTION_LENGTH` BIGINT NOT NULL
) ENGINE=FunctionEngine COLLATE = utf8_general_ci REPLICATE = FALSE

There you are, you should be up and running with your replication set up.

For more details you can always check the online documentation. And make sure you check out dshrewsbury.blogspot.com.

Comments