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 written, 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”
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:
PID must be set:
the address binding for Drizzle’s default port (4427):
The address binding for systems replicating through MySQL’s default port (3306):
Data Directory can be set other than default:
For more complex setups, the server id option may be appropriate to use:
To run Drizzle in the background, thereby keeping the database running if the user logs out:
So the start command looks like this on my server:
master> usr/local/sbin/drizzled \
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.
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:
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
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 &
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 \
A more typical startup will need more options, My startup looks like this:
slave> /usr/local/sbin/drizzled \
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
drizzle> SHOW CREATE TABLE data_dictionary.innodb_replication_log\G
There you are, you should be up and running with your replication set up.
Learn More >