Installing Galera MySQL clustering on Ubuntu 10

Galera is a patch and a library for MySQL that work together at the engine level to provide simple, multi-master replication between MySQL instances. It promises to be easier to set up than ordinary MySQL replication, and allow you to write to more than one machine, a feature currently unavailable from vanilla MySQL.

The documentation is laughably sparse, but I found a helpful post on the mailing list which gave me the clues I needed to get it up and running on a trio of local virtual machines. Once I’ve run some more tests I’ll give a more thorough review of the tech, but the smoke-test confirms: yes, you can get replication between 3 masters that survives the loss of any one of the machines, and even supports auto-incrementing indexes! If this tech turns out to be stable, it’s pretty much the holy grail of MySQL.

Installing Galera

You need to install both the mySQL deb with the wsrep patch, and the galera library itself:

wget http://launchpad.net/codership-mysql/5.1/5.1.59-22.2/+download/mysql-server-wsrep-5.1.59-22.2-i386.deb
wget http://launchpad.net/galera/1.x/22.1.1/+download/galera-22.1.1-i386.deb


Install the packages. dpkg will complain that things are broken, so get apt-get to install the dependencies for you:
dpkg -i galera-22.1.1-i386.deb mysql-server-wsrep-5.1.59-22.2-i386.deb
apt-get -f install


Now you need to temporarily start the server so you can configure it (you’ve missed the usual mySQL setup wizard):
/usr/bin/mysqld_safe &


Run the secure installation setup to configure your root password, etc.:
/usr/bin/mysql_secure_installation


Now, set MySQL to bind to the external adapter instead of localhost (127.0.0.1), for example:

nano /etc/mysql/my.cnf
bind: 10.0.1.109


Now that’s done, kill your temporary server. I’m dumb, so I did it this messy way:
ps axf | grep mysqld
kill -9 any pids from above


Now start mySQL properly:
service mysql restart

Now you need to configure galera itself. Galera config is under mySQL’s:
nano /etc/mysql/conf.d/wsrep.cnf

First, tell it where you’ve installed the galera lib:
wsrep_provider=/usr/lib/galera/libgalera_smm.so

Now give it the cluster address. For the very first node, that address is just:
wsrep_cluster_address="gcomm://"

For any subsequent nodes, set it to point at the first node:
wsrep_cluster_address="gcomm://10.0.1.109"

At the moment it’s not clear to me why this works this way, since if the first node falls over the other two nodes seem to remain capable of syncing with each other, i.e. there is no “master” node. I’ll understand better once I’ve done more reading, I guess.

You will need to provide a replication username/password between the nodes:
wsrep_sst_auth=username:password

And restart mysql again.

Finally, connect to MySQL and grant permissions to your replication user (NB: you need to run these two commands as a single line — galera seems to do weird things with connections to clients).

set wsrep_on = OFF; grant all on *.* to 'username'@'%' identified by 'password';

Done! Repeat these steps for as many nodes as you want, changing only the gcomm address, and you’ll get that many replicated nodes. Amazing!

Fun facts

I’m just starting to get to grips with the features of Galera, but so far the cool things I’ve noticed are:

1. New nodes can sync from empty, i.e. no need to take a mysqldump snapshot beforehand
2. Database and table creates (and drops!) are respected and synced to all boxes
3. Tables with auto_increment indexes create non-conflicting IDs (although they are no longer strictly sequential, so you will get gaps in your numerical sequence).
4. Any of the nodes can drop out temporarily and will catch back up
5. Restarting MySQL seems to fix most syncing problems

I’ll write more about pros and cons once I’ve had a chance to play with Galera some more.

You can host static websites on Amazon S3

Hosting websites on Amazon Simple Storage Service is just a matter of uploading HTML files to an S3 bucket and then pointing a domain at it. Great if you have a static website with enormous traffic, but who has a completely static website?

Introducing PHP 5’s Standard Library

Introducing PHP 5’s Standard Library.

A pretty good overview of how to create iterators, pseudo arrays, etc. in PHP’s class structures.

Rails Flash.now – Blog – Tim Harding – g0

Rails’ flash.now is what to use if you want a flash message to appear only once, on the page you’re currently rendering.

Before PostgreSQL, Bruce Momjian wrote an SQL in Shell

Before PostgreSQL, Bruce Momjian wrote an SQL in Shell – Andrew Cholakian’s Blog.

Sending mail in PHP with Zend and Sendgrid

I use the Zend Framework and quite like it, but the documentation is pretty bad sometimes. In particular, the documentation of their Zend_Mail class is too simple, and the API docs totally unhelpful. So I thought I’d write down what worked for me.

We use Sendgrid to handle our email delivery. It does all the tedious signing and things that are necessary to avoid getting caught in spam traps, which is a huge time-saver. It uses plain authentication, which is a little odd, but it works. Here’s the full code:

set_include_path(
				dirname(__FILE__) . PATH_SEPARATOR .
				get_include_path() . PATH_SEPARATOR .
				'/usr/share/php5'
				);

require_once 'Zend/Loader/Autoloader.php';
$autoloader = Zend_Loader_Autoloader::getInstance();

$mailConfig = array(
			'host'						=> "smtp.sendgrid.net",
			'port'						=> 25,
			'domain'					=> "your.domain.name",
			'authentication'	=> "plain",
			'username'				=> "youremail@your.domain.name",
			'password'				=> "yoursendgridpassword"
);

$transport = new Zend_Mail_Transport_Smtp(
				$mailConfig['host'],
				array(
						'port' => $mailConfig['port'],
						'auth' => $mailConfig['authentication'],
						'username' => $mailConfig['username'],
						'password' => $mailConfig['password']
				)
);
Zend_Mail::setDefaultTransport($transport);

$message = new Zend_Mail();
$message->setFrom('support@your.domain.name','The Almighty Server');
$message->addTo('your.customer@example.com','John Q. Customer');
$message->setSubject('Oh hai');
$message->setBodyText('
Why hello,
This is an email from us.

Regards,
The Almighty Server.
');
$message->send();

Hopefully this is pretty self-explanatory.

Generating a UUID in PHP

We need to generate UUIDs in PHP. This is actually extremely easy: there is a PHP UUID package in PECL, but you’d never know, because there’s no documentation, a problem that’s apparently been the case forever — a documentation bug was opened in 2006, but never resolved, even though there is some documentation in the source code.

Even more simply, in ubuntu there is a package called php5-uuid. It can be installed just with

apt-get install php5-uuid

However, documentation is even more sparse for this one — the best that exists appears to be a comment on the PHP manual page for uniqid() (of course, if all you need is a unique ID, that function is probably easier still, but we needed a UUID specifically). Anyway, the magic code to create a UUID is simply this:


uuid_create(&$v4);
uuid_make($v4, UUID_MAKE_V4);
uuid_export($v4, UUID_FMT_STR, &$v4String);
echo "My UUID is now in $v4string";


This is obviously a version 4 UUID; you can trivially make the others just by changing the arguments appropriately. Wikipedia has a good page explaining the difference between UUID versions.

Getting the total number of matching rows from MySQL when using LIMIT

It’s an extremely common requirement, when designing an API or GUI that includes pagination, to provide 10 rows at a time, but also include a figure of the total number of matching rows. Traditionally people do this by running two requests: first the query, then the same conditions with a COUNT(*) instead. This however is two potentially heavy DB hits. There is a quicker and easier way, and it’s laughably simple.

First, in your SQL, add the SQL_CALC_FOUND_ROWS modifier in front of your SELECT:

SELECT SQL_CALC_FOUND_ROWS name, price FROM fruits LIMIT 10;

Then, after running your query, immediately run the FOUND_ROWS() query:

SELECT FOUND_ROWS();

Done! The second query is returned instantly using the metadata returned from the first, with no additional heavy lifting on the DB’s part. Brilliant!

FOUND_ROWS() works no matter how complicated your SELECT statement. Note that with a GROUP_BY it will return the number of rows in the result, not the number of rows matched before they were grouped. This behaviour is usually going to be more useful to you anyway.

Thanks to ArrayStudio for the well-written tip.

Python errors running s3cmd on Amazon AWS

Whenever I ran s3cmd on my AWS instance, I got the following errors:


/usr/lib/python2.6/dist-packages/S3/S3.py:9: DeprecationWarning: the md5 module is deprecated; use hashlib instead
/usr/lib/python2.6/dist-packages/S3/S3.py:10: DeprecationWarning: the sha module is deprecated; use the hashlib module instead


The problem is that this is an old, crappy version of s3cmd, 0.9.8.4. You can get version 0.9.9 using the instructions in the linked blog post. It’s just a matter of adding a new source to your apt-get sources list and then running apt-get update.

ssh_exchange_identification: read: Connection reset by peer

I got this while attempting to ssh into a VM on ubuntu. When I pinged the box, I got this:

PING 192.168.1.86 (192.168.1.86) 56(84) bytes of data.
64 bytes from 192.168.1.86: icmp_seq=1 ttl=64 time=0.614 ms
64 bytes from 192.168.1.86: icmp_seq=1 ttl=64 time=1.89 ms (DUP!)
64 bytes from 192.168.1.86: icmp_seq=2 ttl=64 time=0.602 ms
64 bytes from 192.168.1.86: icmp_seq=2 ttl=64 time=0.925 ms (DUP!)

Whoops! The VM was already running and had been granted the exact same IP. It was responding from two places at the same time, which was confusing the hell out of SSH. Just kill one of the boxes and you’re fine (DHCP release/refresh doesn’t work, because both boxes have the same MAC address).

Thanks to this thread for the hint.