Nov
15
2011

Simple Replication Example

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”
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.

Apr
12
2010

LearningDrizzle at the MySQL Conference

Learning Drizzle is back!

The Learning Drizzle Blog has been on hiatus for a while. I have been working entirely too much (who hasn’t) on projects less interesting and less fun, but I also had my eye on the MySQL DBA exams at the MySQL Conference. When I decided I would come to the conference I thought I should take advantage of the cheap cost of the test and get myself ready for it. Now I’m at the conference and still not sure I’m ready for the test but will give it a go.

Today’s Tutorials will be a big help in that regard, I hope.

It seems like a distraction from Leaning Drizzle, but I’m fairly certain that learning MySQL in tandem with Drizzle, especially as a beginner, will pay dividends.

I arrived at the MySQL conference last night and began with a couple good beers, and had a chance to just chill and talk to people who have written the books I have been studying from. The heart of the Drizzle team is here of course. It’s pretty inspiring to be around so many intelligent people sharing amazing ideas.

This is a great opportunity for those of us beginners to get to know a lot of the players and learn as much as we can about the open source database universe. As Mark Atwood said well in his blog earlier today: “So much of the history and development of technology is based on a foundation of personal relationships.”

During the conference I will be blogging, sharing what I learn, and the new things coming that I hear about. I use Twitter (@kentbozlinski) for non-work related commentary as well but during the conference I will keep it to MySQL, MySQL Conference, and Drizzle related posts. There are some great talks planned, some on Drizzle that I will attend, but I will also be going to a number of more MySQL specific talks.

Drop me a tweet or an e-mail (kent at learningdrizzle)! I would love to meet and have a beer with you at the conference!

OK, Kai Voigt’s Tutorial for the MySQL DBA Exam is about to start up again. Back to it!

Dec
23
2009

Getting Drizzle Started -or- “Know thy Path!”

After being gone a while for the day job I have finally gotten around to setting up my storage file locations and permissions in Drizzle. Stuart Smith helped me get past some problems, mostly associated with not knowing basic syntax, but I also ran into one or two that are due to changes ongoing in Drizzle. After spending a lot of time not knowing what I didn’t know (one of the quirks of teaching yourself), a little chat finally straightened me out.

So after Drizzle is installed on Ubuntu you obviously have to have a place to store your data. To avoid all the time we spent figuring out what was going on, just pay attention to the last lines after you run your “make install” so you know where the damn thing is.

Run your sudo command then add your user group where drizzle is the name of the group you want to have access to the drizzle server:

$ sudo groupadd drizzle

then create a new user within that group, replacing USER with your user name.

$ sudo useradd -g drizzle USER

After running this procedure a couple times I found that on one install the directory was created for me, on another I had to create it. Either way, make sure you have a new directory for drizzle and then a data directory in that:

$ sudo mkdir /home/USER/drizzle
$ sudo mkdir /home/USER/drizzle/data

Now set up the permission on that directory for your user:

$ sudo chown -R drizzle:USER /home/drizzle/data

Finally start up the Drizzle Server with:

$ sudo -u USER /usr/local/sbin/drizzled --datadir=/home/USER/drizzle/data

You should get a bunch of lines of code saying the Drizzle server and InnoDB are now running and what ports it is listening too. Now you can start up the Drizzle Client:

sudo -u USER /usr/local/bin/drizzle

and you should see:

Welcome to the Drizzle client.. Commands end with ; or \g.
Your Drizzle connection id is 1
Server version: 2009.12.1251 Source distribution (drizzle)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
drizzle>

I found that if I installed this on a server that already had MySQL running on it, the start up command would return an error where the InnoDB (a storage engine drizzle uses) was already using the port that Drizzle wanted to use. I had to shut down MySQL on port 3306 to get it up and running. Mark Atwood helped me get past that issue.

Everything seemed to work much cleaner installing on a clean system but that I guess is to be expected. I don’t know all the ways Drizzle will overlap and conflict with other programs and I expect MySQL is the first and most frequent one I will notice.

Nov
25
2009

Drizzle is Running on my Jaunty Box(possibly the strangest sentence ever)

I fancy myself an astute computer user but some of the ways to get things running in Linux are challenging for a born-and-raised windows user like myself. You don’t just click on a button to install Drizzle. I ran into a few blocks, that can mostly be attributed to me tripping over my self.

For the noobie,  make sure you run all of this from the root user which means you will see a $ sign after your user name. for example, my command line starts with: kent@server:-$.

Jauder Ho had a great walk-through that I am going to steal and re-post here, with a couple alterations only for the sake of clarity and a minor change due to dependencies. His text is in red:

The Drizzle wiki was useful in getting started but did not include all of the available packages.

First, the basic dependencies:

sudo apt-get install libpcre3-dev libevent-dev autoconf automake
bison libtool ncurses-dev libreadline-dev libz-dev g++
libssl-dev uuid-dev libpam0g libpam0g-dev gperf

Next, the libevent on 8.04 is too old so a newer version needs to be installed. Did this on 9.04 too just to make sure it is up-to-date.

sudo apt-get install libevent-dev

Google’s Protocol Buffers as well as libdrizzle need to be installed and the packages are available on the drizzle-developers PPA on Launchpad.

Google  Protocol Buffers is a bit more of an issue. I got an error later on that said protobuf was out of date. You need to go here to get a concise walkthrough to update your software sources on Ubuntu Desktop.

***Amendment*** if you are running Ubuntu Server (which is probably the ultimate goal) you need to access your repositories by running the following command: sudo nano /etc/apt/sources.list. Then you can add the lines as the sources walk-through suggests***

Add the PPA to your software sources, add the key and then run the following:

sudo apt-get install libprotobuf-dev protobuf-compiler

Then:

sudo aptitude install libdrizzle-dev

I was beating my head against a wall for a while trying to install libdrizzle. I kept trying to run sudo apt-get install on libdrizzle-dev but for some reason that didn’t work. Then I did sudo aptitude install and it worked fine. If anyone can tell me why, that would be great. Aside from that Jauder’s walk through worked fine.

*** Amendment, some people mentioned that I forgot to let you know to install Bazaar to compile Drizzle as well as make. Those commands are:”sudo apt-get install bzr” and “sudo apt-get install make“***

Now it’s time to finally download, compile, and install Drizzle. Run the following commands:

bzr branch lp:drizzle
cd drizzle
./config/autorun.sh
./configure
make
sudo make install

You should now have Drizzle installed on Ubuntu 9.04 Jaunty Jackalope. If you have an problems or see a way to make anything more concise or clear please tell me.

I did find a few things in the wiki that could be made more clear for us noobies. I will get on that.

I actually spent a lot of time doing this the hard way before I found Jauder’s succinct walk-through and will elaborate in the next post.  Next time I will be setting up my user accounts and beginning testing.

Nov
12
2009

Equipment and stuff…

Last week I was at a lecture Brian Aker gave to the Seattle MySQL community. During his talk I got a feel for how the team is developing Drizzle, where they stand on various operating systems, and what they are testing on.

Drizzle, when it’s completed, will be able to run on virtually any type of system, though it is specifically designed for web servers and cloud computing. At the beginning I will be doing almost everything on a 1U 64-bit AMD Athlon 2800+ rack mounted server with two gigs of Ram. Not a beast of a machine by any measure but certainly fine for our purposes, and running Ubuntu Server on it will mean it’s pretty zippy. I also have a couple of windows machines and a server running Open-Solaris which I will be playing with. Drizzle is being tested on astonishingly powerful machines, some of which are running 64 or more processing cores and hundreds of Gigs of Ram. In other words, I will not be pushing the limits with the software just yet.

I am fan of Linux, specifically the Ubuntu distribution, because it is easy to get the feel after switching from Windows, even for the non-hacker like myself. It is not quite the type of OS I would set my grandma up with but it’s pretty straight forward.

Installing software on Ubuntu (I’m using 9.04) can be incredibly easy and beats a trip to the store to spend a few hundred bucks any day. While the Graphical interface in Ubuntu is pretty simple, in reality we will be using the command line for most of what we do with Drizzle. I suspect most people interested in anything resembling a database will be relatively familiar with command line, but for those of you who are new to that as well I will be making the commands very specific. When in doubt, copy and paste; but please don’t forget to change things like <username> to your own username. It won’t work too well otherwise.

Another piece of the puzzle we will need is a sample database. If you have one you want to use that’s great, but I was pointed in the direction of the Sakila sample database, often used for testing and practice with MySQL. After I get a feel for things I will actually use some free US environmental databases I downloaded for a project and see about real world implementation.

The next post will actually include the installation of Drizzle! I promise! I learned a few new things about compiling and dependencies, and made a couple stumbles.

Nov
6
2009

Drizzle is Scary (A Little)

I’m not really scared of rain after living in Seattle for seven years. I am a little scared of sticking my neck out and writing about something which (for the moment) I know almost nothing about.

See, I am formerly a TV Producer stretching myself to learn a few new tricks. A hacker friend has been helping me with some new concepts, including using databases in creating websites. Being a fairly well known hacker, Brian is at the heart of the team developing one of the next generation of databases. He suggested the best way to learn is to teach, so he asked me to begin contributing to the online manual for Drizzle, which I have never touched and is not even released!

The scariness of this thought aside, I was intrigued. If I write the manual as I learn, I might just come to truly understand the Drizzle DBMS. By documenting every single (many), little (large), tiny (massive), mistake I make along the way, I might just  make a different kind of road-map for others to learn by as well.

We will get into the details in future posts, but the basics are:

  1. Drizzle is a fork from the MySQL Database; it rethinks it and refocuses it toward use on the internet and in cloud computing.
  2. It’s open source. Anyone can contribute and everything about the development is public.
  3. Drizzle is being built for the next generation of server technology and will be lean, fast, reliable, and  scalable.

That’s (mostly) the extent of what I know.

I am not a developer, and certainly no expert in databases. I am simply excited by this project and more excited by my Brian’s suggestion that I get involved in something that I suspect will be very big. The philosophy of a radically open and collaborative project is infectious, and my aim is to share that excitement with a whole bunch of people who might otherwise be daunted by the thought of trying something new.

In the coming weeks and months I will dive into Drizzle, learn how to install it, use it, configure it, and maintain it. As I do, you will see links to my updates to the online manual here, as well as a more colloquial exploration of my successes and failures.

And for those of you who are familiar with Drizzle… Please keep the feedback coming.