Notes on Mysql Master/Slave

Here’s a good link from or friend Paul Moen on how to promote a slave to a master.

http://www.pythian.com/blogs/300/mysql-recipes-promoting-a-slave-to-master-or-changing-masters

The basic idea hear, is to simply tell the slave to no longer pay attention to the master… and things should work as is.

Kind of obvious really.

Advertisement

example master/slave on Ec2/S3

Great article from RightScale on an implementation of master/slave replication using Ec2/S3.

Not a lot of details, but a great high level explanation of what you want to accomplish.

Redundant MySQL set-up for Amazon EC2

What we’ve built is a mysql master/slave set-up with backup to Amazon S3. The set-up consists of one mysql master server which is the primary database server used by the application. We assume it runs on its own EC2 instance but it could probably share the instance with the application. We install LVM (linux volume manager) on the /mnt partition and place the database files there. We use LVM snapshots to back up the database to S3, this means that we get a consistent backup of the database files with only a sub-second hiccup to the database.

More EC2 Tools/Notes

Couple of random notes…

1) AWS Tools – This is a great set of command line tools for accessing S3 and EC2. I’ve only played with the S3 features, but they are great and make it a lot easier to implement some of my backup and restore to s3 features I am building on my Ec2 cloud/array

2) Ec2 First Run – Ok, this is totally obvious when you think about it, but it took me a couple days of scratching my head wondering why this wasn’t working before I had the “duh” moment. Mind you, I didn’t work on trying to solve this problem for 2 days, because I was working on other things… but it wasn’t obvious to me for a couple of calendar days. What the heck am I talking about!?!?! This:

• I wanted to build a custom AMI that would do a bunch of stuff on first run. Mostly create my lvm volumes, download a backup of my Database from S3, send me notification that it was up and running, stuff like that.
• The rc.local of the existing AMI has this nice little section of code labeled “first run” and it purports to do stuff on first run only, while the rest of rc.local would run on a reboot of the instance as well. Simple right? So I code up my first run shell scripts and get them all happy, drop them in the if branch of the code… build my AMI… launch a new instance… and (silence) nothing happens…. huh? Why’s that. Let’s look at the code:

  #!/bin/sh
  #
  # This script will be executed *after* all the other init scripts.
  # You can put your own initialization stuff in here if you don't
  # want to do the full Sys V style init stuff.
  
  # Stuff we want to do once at launch and never again:
  if [ -f "/root/firstrun" ]; then
  
      # do my stuff here --
      # create our logical volumes
      create-lvms.sh
  
      # restore from my latest backups
      restore-database-from-s3.sh
      restore-blogdata-from-s3.sh
  
      # send our admin team a notification
      firstrun-notify.sh
  
      ...
      # do some more stuff here -- from the orginal AMI
  
      rm -f /root/firstrun
  fi
  

• Well, it should have been obvious, but this code only runs if a file “/root/firstrun” exists, and at the end of the code, the code deletes that file. Well, since I made this AMI from a running instance, the first run had already run, and therefore “/root/firstrun” didn’t exist… DUH!
• So, I touched /root/firstrun and rebuilt my AMI, and now first run actually works. I need to remember to do this before I rebuild my AMI again.

More Notes on EC2 and LVM

Following up on my notes about running WordPressMU on EC2, I am still working on a clean implementation for setting up the disks on the EC2 m1.large instance the way I want. Once, I’ve made things work the way I want then I will go back and finish my other notes on running WordPressMU.

This is a collection of notes on what I think I want the disks to look like on first boot and reboot…

m1.large instances come with 850GB of disk space configured as:

• 850 GB instance storage (2 x 420 GB plus 10 GB root partition)
• /dev/sdb (/mnt on large and extra large instance types)
• /dev/sdc (on large and extra large instance types, not mounted)

I like the idea of creating a single large volume group on both physical disks, let’s call it “vgall” and then creating all of our logical volumes on that volume group:

• two database volumes (“lv_master”/”lv_slave”)
• one volume for back up (“lv_backup”)
• one volume for blog data (“lv_blogdata”)
• one swap volume (“lv_swap”)

Ok, so, we can’t create these logical volumes in the AMI setup, but we can create a script to run in rc.local that will set these up for us, and we can add these volumes to fstab to survive reboot.

So, our setupscript…

###  BEGIN OF SCRIPT ####!/bin/sh
#
# Script to make EC2 /mnt into a LVM volume

umount /mnt
pvcreate /dev/sdb /dev/sdc
vgcreate vg /dev/sdb /dev/sdc

lvcreate -L150G -n lv_master vg
mkfs -t ext3 /dev/vg/lv_master
mkdir -p /data/mysql_master
mount /dev/vg/lv_master /data/mysql_master

lvcreate -L150G -n lv_slave vg
mkfs -t ext3 /dev/vg/lv_slave
mkdir -p /data/mysql_slave
mount /dev/vg/lv_slave /data/mysql_slave

lvcreate -L150G -n lv_backup vg
mkfs -t ext3 /dev/vg/lv_backup
mkdir -p /data/mysql_backup
mount /dev/vg/lv_backup /data/mysql_backup

lvcreate -L150G -n lv_blogdata vg
mkfs -t ext3 /dev/vg/lv_blogdata
mkdir -p /data/blog-data
mount /dev/vg/lv_blogdata /data/blog-data

lvcreate -L140G -n lv_swap vg
mkswap /dev/vg/lv_swap
swapon /dev/vg/lv_swap

lvdisplay

mkdir -p /data/mysql_master/data
mkdir -p /data/mysql_slave/data

chown -R mysql:mysql /data/mysql_master
chown -R mysql:mysql /data/mysql_slave
chown -R mysql:mysql /data/mysql_backup
chown -R apache:apache /data/blog-data

### END OF SCRIPT ###

Our fstab:

# /etc/fstab
# modified to include lvm and swap

# original items
/dev/sda / ext3 defaults 1 1
none /proc proc defaults 0 0
none /sys sysfs defaults 0 0

# our LVM volumes and such
/dev/mapper/vg-lv_master /data/mysql_master ext3 defaults 0 0
/dev/mapper/vg-lv_slave /data/mysql_slave ext3 defaults 0 0
/dev/mapper/vg-lv_backup /data/mysql_backup ext3 defaults 0 0
/dev/mapper/vg-lv_blogdata /data/www ext3 defaults 0 0
/dev/mapper/vg-lv_swap / swap defaults 0 0

Some supporting notes:

• LVM can’t be set up and included in an AMI, so you need to write a script to set up the Volume groups and the logical volumes and run it in rc.local (Notes here)

• Notes on fstab – Take note of the comment on running swap on an LVM managed disk.

Experiments with Running WordPressMU on EC2

Now that Amazon offers fixed IP addresses for their EC2 service, I am starting the process of researching what it would realistically take to implement WordPressMU on EC2.

I’m going to capture some notes here about the process. This post is a raw unfiltered list of random thoughts.

I am not writing this per se for anyone else, but since I’ve found these items out there and they’re useful to me, they may be useful to someone else. I may assume you already are very familiar with WordPressMU and/or MySQL, and certainly that you got admin skillz if you’re reading this.

Meta Point: You have no dedicated disk drive!

So the big issue with Ec2, and arguable grid computing vs dedicated servers is that you’re server instances don’t have a physical hard drive associated with them. Sure, they have disk space when they’re running, but as soon as they shut down that disk image is gone FOREVER… Obviously, if you plan to run a database on one of these machines, you have to think about this carefully.

It’s not like if you accidentally reboot your dedicated server at least you know your DB probably shut down safely, and will probably wake up working just fine. That disk is dedicated to you. But not so with EC2… that disk doesn’t exist anywhere. So you need to really think about how to make that disk image persistent.

But wait a second? Is that really an issue?

Well, if you’re lazy, or even if you’re not lazy and you’re counting on regular backups and the idea that your system could lose some data and you’d be ok… then maybe you haven’t built a system that withstand a complete drive failure. You figure, hey, I’ve got my dedicated box, I do regular backups, I copy those backups off of the box… Maybe I’m already using S3 to store those backups… no big deal. I could rebuild my box if I needed to.

Well, if that’s your attitude, then you would be flying pretty much by the seat of your pants on a pure cloud system like Ec2.

But, if you already have a redundancy and backup strategy that can handle a complete disk failure (as in there is nothing left on the disk) then you’re in perfect shape for running in the clouds.

So… here are some notes that are mostly EC2, MySQL, Linux specific about implementing various pieces of this puzzle.

Some great articles out there

General EC2 Articles

• The Official Amazon Ec2 Getting started guide -This is a pretty basic article but it explains everything you really need to know about setting up a basic EC2 instance.
• The Official Amazon EC2 Documentation (v2008-02-01) – This contains the actual documentation for all of the command line utilities, etc, that you need to get rolling. I’m the kind of guy that likes to know all the parameters available to me, so if I was walking through a how to guide that’s saying what to type in where, I still want to know all the options and why they work the way they do.

Mysql Database “strategy” articles:

• CodeWord’s Article on Running MySQL for large datasets on EC2 – This article is more than a year old, so it describes limitations in Ec2 that have been fixed, but the concepts of running MySQL in a double paired master/slave mode is a clever solution and totally appropriate for the project I’m working on.
• MySql documentation on mysqld_multi – This is the tool you’ll want to use when you run multiple mysqld instances on a single machine.
• Setting up Master-slave
• Setting up Master-Master

Articles about LVM backups:

• DBA Dojo’s Article on Setting up EC2 with LVM snapshots – Also totally appropriate, includes some handy scripts which demonstrate things like setting up your logical volumes, etc.
• PointyHair Article on Snapshot backups – Here’s another article about snapshots. It’s interesting as it shows how to do this from mysql, using mysqls ability to call shell commands in between SQL commands. This article also describes the (should be obvious) aspect of actually doing the real backup on the snapshot results…. so for example this is where you might copy to content to S3 using one of the below products.
• Another article that talks about LVM, MySQL Replication, and backups.
• Mike Kruckenberg’s post on the topic of LVM snapshot backups.

Articles related to file systems useful as “backing stores”:

• Great Thread Discussing different types of “backing stores” using S3 to make your a persistent image of your Ec2 data.
• ElasticDrive – Tool mentioned in the above thread… not exactly free, but looks like it has some useful features at scale.
• JungleDisk – Tool mentioned in above threads… practically free, solves a slightly different problem then ElasticDrive, but certainly would work nicely in the model described by CodeWord.
• PerstistFS – This is a FUSE based file system designed for S3/Ec2

Setting up my Image

I wanted to utilize the EC2 m1.large type for my database so that I would have plenty of room for DB performance. And I wanted to use Fedora Core (6 or 8).

So I started with the amazon public AMI for fedora core 6, which pretty much doesn’t include anything but the OS. I then installed: httpd, mysql, php, and subversion (since we like to update our code from our svn server). [hint: yum install httpd mysql mysql-devel mysql-server php subversion]

From this I made my own image that was fedora core, plus the basic LAMP components. This means following the standard directions building a bundle uploading it to S3, registering it, etc. Now I can start up new instances without having to reinstall all that stuff.

Installing WordPress MU: The code

Well, this is actually pretty easy, I just checked out our version of wpmu from our svn server. Our version includes all of our custom plugins and themes we’ve built.

Installing WordPress MU: The database

More to come.

Random Issues:

• For some reason I had some problems with the default amazon image for fedora core 6 when using an assigned IP attempting to do yum. I kept getting manifest checksum errors. Not sure if it’s a F6 issue or an assigned IP issue. I was able to yum a bunch of stuff with both FC6 and FC8 when not assigned a fixed IP. I had no problem when I recreated the instance (without associating it with the IP) and was able to yum and ultimately create an image I wanted. But it was a pain. Since then I’ve also been able to yum from an instance that is assigned to an IP… so really I have no idea what was going on here.

WordPressMU Customized Signup Wizard for Las Vegas Half Marathon Running Group

At Konamoxt we just started a test campaign to have new users signup for our service and get free online coaching to run the Las Vegas Half Marathon. If you’re a runner and a blogger, this is a great chance to get some great free coaching from a certified running coach… and to have a great time at a pretty nice Half Marathon race. The Las Vegas Half Marathon, and Marathon is really a well run race in a great location.

Anyway, since this is my development blog, you might ask, why am I posting this here? Is it just a shameless promotion of the event? Well… kinda… I mean I would like you to sign up and have a great time running the Half Marathon.

But you also might find it of interest how we did this sign up process.

First of all early on when we deployed WordPressMU we wanted to customize the signup process. And we wanted a different look and feel, have it integrated more into our default website design, etc. We did this through hacking the core wordpressmu files. Our main signup process still does this. But we’ve never really liked it.

For our Las Vegas Half Marathon promotion. We wanted an improved look and feel. In fact part of what we wanted was the ability to have a custom sign up process integrated into some of our “professional blogs” that included event specific signup fields. For example, for something like this, there may be additional terms of service. Since we are offering free online coaching, we needed the system to present an additional Online Coaching Training Waiver.

Users that sign up for this running group, get custom themes only available to them. These new blogging themes are specifically designed to highlight that the event is in Las Vegas and that the users are training together to run a Las Vegas Half Marathon together… etc. We’ve included a bunch of integrated header images of the sights and icons of Las Vegas. We’ve added widgets that automatically include a countdown to the race, and links to other team members. All of these are straight forward in WordPressMU when you develop a custom theme.

But having a custom signup process isn’t something we’ve seen alot of… and we really wanted to streamline the signup process.

Here are a list of improvements to the signup process we wanted in no particular order:

• Single Step Signup – Which is to say, we understand why the WordPressMU team decided to have multiple email roundtrips to verify that the user is a real person and not a robot… but for our user base this process was causing a lot of users to get stuck in the signup process. It’s one thing to ask dedicated bloggers to step through the process. But we want runners of all skills and interest levels, and we don’t want to scare them off with a complicated signup process.
• Integrated Captcha
• Terms of Service/Privacy Policy checkbox
• Online Training Waiver checkbox
• User Name for new users
• User settable password for new users – We’ve gotten complaints from many users that they don’t like the autogenerated passwords, and they couldn’t figure out how to change their password. So like other services we wanted to allow the new users signing up for this free online coaching to set their own password.
• User settable blog domain – but default to same name as user name
• User Profile information – Since we’re looking for runners to signup who are taking on a Half Marathon, we want the new blogs to come stocked with a better about page and first post than the standard boring ones that come with WordPressMU… we do this by asking the runners who are signing up for the Half Marathon to tell us about their running history. How many Half Marathons have they run? What is the Personal Best for the Half Marathon? What are their goals for the Half Marathon? What other activities are they interested in? We store all of this as user meta data, and we format it into an automatically generated post and about page.
• Theme Selection – Since we added a custom theme exclusive to the runners who sign up for the Half Marathon, we let them select from preview images of the custom theme. If their an existing user we let them choose their existing theme, but new users are stongly encouraged to pick one of the Las Vegas Half Marathon themes.
• Blog title and description

Ok, so those are the features we wanted how did we do this?

Well, first of all, we wrote most of this code as if it was a plugin for wordpressmu. However in the end we ended up moving it to be scoped just to the custom theme we developed for the running coach who’s offering the training. Our plan is to eventually move this to a more generalized process.

The bulk of the code is pretty object oriented PHP. We developed a wizard class, that takes wizard page objects. Each page of the wizard is responsible for rendering it’s html, and then doing a preprocessing check and eventually a processing phase. Of course we use prototype.js and script.licio.us for a bunch of the Javascript heavy lifting. The actual wizard behavior comes from some open source code that uses scriptalicious effects to move around the “pages”.

We essentially call the wordpress mu functions for blog signup and post creation just like the standard wp-signup.php pages do. I guess the point is, instead of trying to change wp-signup.php we realized that the right thing was to treat the functions in wp-admin and files like registration.php as an API that we would call into. Once we made this leap, it becomes pretty straightforward to build a custom signup process that gets past all of the ‘features’ of the standard wordpressmu signup.

Anyway, check it out… if you’re a runner, then for sure check it out and signup for the free coach and run training advice.

Crazy Globals Bug in Recent Posts Widget

I’ve just discovered and fixed a small problem in the “Recent Posts” widget.

Basically this widget creates a second “loop” and will side effect several of the post globals (like $id and $post). The result will effect any downstream plugin that thinks it can count on these globals being available. This can also manifest in any theme (like Tarski) that has post related template calls AFTER the side bar widget.

I’ve fixed this problem but saving $post in a local variable and then resetting it after the loop call. I also call setup_postdata() to restore the other globals.

I just checked the latest version of widgets.php from “http://svn.wp-plugins.org/widgets/trunk/” and it still has this issue in it.

Here’s the new function…

function widget_recent_entries($args) {
    // This global $post is side effected by the call to $r->the_post()
    // and so in order to protect any uses of $post after our widget is
    // instantiated, we will save the value of the global and restore
    // it at the end of our function.
    global $post;
    $save_global_post = $post;

	extract($args);
	$title = __('Recent Posts', 'widgets');
	$r = new WP_Query('showposts=10');
	if ($r->have_posts()) :
?>
		<?php echo $before_widget; ?>
			<?php echo $before_title . $title . $after_title; ?>
			<ul>
			<?php  while ($r->have_posts()) : $r->the_post(); ?>
			<li><a href="<?php the_permalink() ?>"><?php if ( get_the_title() ) the_title(); else the_ID(); ?> </a></li>
			<?php endwhile; ?>
			</ul>
		<?php echo $after_widget; ?>
<?php
	endif;
    $post = $save_global_post; // restore the global that was side effected by $r->the_post()
    setup_postdata($post); // and restore the other globals that we also side effected when $r->the_post() called setup_postdata()
}

Common Themes Directory for Multiple Instances of WordPress?

I have multiple installs of WordPress and WordPressMU running in the same system, and I want them to share a common Themes. I’ve been diligently updating the same theme in multiple directories… and frankly it sucks. Then I came across this forum post and was blown away!

So, I’ve implemented a plugin that allows you to set an absolute or relative path to a common themes directory.

Installation: Download the code from the link below. Rename the file to common-themes.php and place it in your plugins directory. Activate it.

Download: You can get a copy of the code here.

Usage: Set options in “Options->Theme Root Options”. Enjoy.

Improved WordPress to WordPress Importer

I’ve been playing around with importing WordPress blogs from one instance to another, and I’ve been frustrated by some of the default features of the built in WordPress to WordPress importer.

So, I’ve implemented a new version of the importer that has the following features:

• Attachments – This importer will download and “insert” the actual image files of an attachment record. So for example, if you’ve uploaded images to your WordPress blog, then they will be listed as attachments. The normal importer will store these records but no do anything with the actual image. So you basically end up with dead links. Not anymore. Now if the image is an attachment, the importer will attempt to download it and insert it into the new blog. All of this downloading happens on your server, so you don’t actually have the files download to your client machine.
• Links – Links to your own blog are remapped. This is particularly important for image links to attachments (obviously) or else the attachment feature described above would be pretty whacked! But it also applies to cross links between your own blog posts. Seems like an obvious feature right? I thought so. This also applies to internal ping backs.
• Spam Comments – For some reason the default importer decides to import comments tagged as spam. I guess I can imagine a few cases where this might make sense… but nah… I don’t think so. So this importer will discard any comments marked as spam.

Installation: Download the code from the link below. Rename the file to zappo_wordpress.php and place it in your wp-admin/import directory.

Download: You can get a copy of the code here.

Usage: Same as any other importer, go to Manage->Import. Enjoy.

By the way, I really only tested this on WordPressMu, but there isn’t anything about it that I would expect to fail on standalone WordPress. Feel free to report bugs if you see issues.

Hey Zappo – This thing is really chatty, what gives? Do you think you could make it not print out so much noise? – You

No. But you can. Enjoy! – Me

Latest wpmu-topposts plugin

Version 0.42.4 of wpmu-topposts plugin now available…

Lots of changes:

• Added support for top_blogs(), same calling convention as top_posts() but returns
  top blogs based on total hits to the blog for all posts on the blog.
• Added a couple new arguments to top_posts() include a new max_per_blog which if set
  to 1 will limit the posts to 1 per blog.
• Added days_back argument which will limit the stats to a certain number of days
  into the past.
• Added many new formatting arguments to make it easyer to use the get_*_html functions
  and get the formating you want.
• Fixed a couple bugs that would cause cache corruption (removed a call to
  update_post_caches() which was definitley corrupting the post cache)
• Also correctly call switch_to_blog() in the get_*_html functions so that the
  blog template tags actually work properly.

Definitely my favorite new feature is the max_per_blog since it uses cool nested queries in SQL. yummy!

Here are some usage examples:

This will give you top posts in an ORDERED list, with hits included surrounded by square brackets.

zap_top_posts_html('before_list=<ol>&
           after_list=</ol>&max_per_blog=-1&
           before_hits=[&after_hits=]');

This will give you top blogs in an UNORDERED list, without hits included.

zap_get_top_blogs_html('include_hits=0');

As always, get the latest code here…