This article contains my notes and detailed instructions on setting up a MySQL cluster. After reading it, you should have a good understanding of what a MySQL cluster is capable of, how and why it works, and how to set one of these bad boys up. Note that I'm primarily a developer, with an interest in systems administration but I think that every developer should be able to understand and set up a MySQL cluster, at least to make the dev environment more robust.

Notes

• High availability. If one or some of the machines go down, the cluster will stay up, as long as there is at least one copy of all data still present. The more redundant copies of data there are, the more machines you can afford to lose.
• Scalability. Distributed architecture allows for load balancing. If your MySQL …

Today I was (again) facing a log file from a machine that had for some reason not been able to start a temporary MySQL daemon during the night to prepare for a streaming MySQL slave installation. The necessary 2nd daemon had created its new ibdata files, however just after that aborted the startup process with the following message:

Can't start server: Bind on TCP/IP port: No such file or directory
071001 23:09:55 [ERROR] Do you already have another mysqld server running on port: 3310 ?
071001 23:09:55 [ERROR] Aborting
071001 23:09:55 [Note] mysql\bin\mysqld.exe: Shutdown complete

As you can see, the port is a different one from the default MySQL port, so I can be sure there was no conflict with the primary instance. Even more curiously the same process has been working on that and other machines flawlessly for some …

I am curious about character sets and collations, especially how they are used in databases. I got some time to play with them recently. I did some testing today on MySQL. I will do the same test on Sql Server, Oracle, and PostgreSql, time permitting. I am only dealing with simplified Chinese at this point. I may take up traditional Chinese too.

Here is my setup:

1. I created a table that stores simplified Chinese characters in different character set, along with collation used, pinyin, number of strokes, and tone value. There are 126 collations in MySQL, only 10 of which are suited for simplified Chinese.

2. I used the Chinese version of the golden rule,

Due to some discrepancies between the Eclipse 3.2.2 compiler and Sun's javac we needed to upgrade our development environments to Eclipse 3.3. Otherwise we could not tell for sure that something that looked ok in Eclipse would compile in the daily build process.

Even though I had used 3.3 privately for some time now, there is always some tension when switching a whole bunch of developers in a really large project.

At first everything seemed fine, apart from some minor issues that could be easily worked around.

However I ran into a nasty little bug regarding the CVS integration when I had to switch a workspace that had been checked out on HEAD to another branch. That branch had been created to keep the Helpers- and Utilities-Refactoring I wrote about before separate from the HEAD until it is complete.

One of the key features MySQL often uses to advertise for their database is the modular architecture that allows them to have different storage engines below the same SQL layer. In practice the application and/or database designer can choose from a variety of low level data storage implementations that each offer different characteristics and may be chosen on a per table basis. (Even though I personally believe most designs will use one type of table for all tables of a particular schema).

The idea behind this is that for example people who do not need transactions should not have to worry about them at all ? maybe there is performance impact involved which they cannot afford to take. Moreover some specialized types of index or column might not be available on all engines. Basically the concept is very interesting and can be really useful for developers.

However there is a weakness that in my opinion needs some severe work to be …

MySQL has a unique feature whereby you can select the storage engine for each table. If you don’t know what a storage engine is, it is responsible for managing the physical data on disk - from data files and indexes to the memory buffers used to access the data and the locking needed to prevent multiple people from clobbering each other’s changes. This is both a boon and a bane for Systems Administrators and DBAs.

It’s a great feature because developers, Sys Admins and DBAs can select the engine with the properties they require. For example, the InnoDB engine supports ACID-compliant transactions, foreign keys and row-level locking. The MyISAM engine, while being lightning fast on reads and supporting concurrent inserts, does not have ACID-compliant transactions or foreign keys and implements table-level locking. Other engines are available, such as memory-based tables, clustered tables, federated tables (reference a table on server B …

Here is my notes on setting up replication on MySQL. In a lot of cases, that is not good enough, because it replicates EVERYTHING from the master to slave(s), whereas you may just want one or two databases replicated.

At first I thought I could just add this to /etc/my.cnf on the slave:
[mysqld]
replicate-do-db=MyDb

That didn’t work very well for statements like this, assuming you are in a database other than MyDb:

insert into MyDb.TableInMyDb values (SomeValue)

Fine, I thought, let me add this to slave’s my.cnf:

replicate-wild-do-table=MyDb.%

It’s not enough. For the Sql statement above, it still couldn’t catch and execute that statement.

After some searching and testing, I found what I was looking for. As far as I can tell, it works beautifully. All you need to do is to modify my.cnf on slave. Just add the databases to ignore in the list. Below is a …

Some say Sun is as cool as OSCon (if not cooler) because, among most companies that support OSCon, only Sun can produce truly underground notes on OSCon.

David Van Couvering reviews Mike Olson's comments about his keynote at OSCon and pontificates about whether the value of Open Source could be limited to the collaboration it fosters. David aptly notes that

Open source and an open community gives you the assurance that the technology you are depending on is not going to be discontinued or put into "maintenance mode," it won't be acquired by someone who you would rather not do business with, and it won't be used as leverage against you to extract money or modify your behavior.

By way of further review, David contrasts MySQL as an Open Source project to …

A couple of weeks ago, a friend asked about replication on MySQL 4.1.7. I’ve worked with replication in the past, just a quick and dirty job on MySQL 5, and soon forgot about it. This time, I wanted to do it on MySQL 4, and make sure I take good notes for my own benefit. If it can help somebody else, all the better. The official documentation is here. It took a little time to wade through it.

The process below is used to replicate all databases on the master to the slave(s). I will talk about replicating only certain selected databases in a future post.

1. At the master server, check if binary logging is on. Use show variables like ‘%bin%’ to check if binary logging is on or not. If it is not on, turn it on by adding log-bin under [mysqld] section in …

Ok…I’ve been crazy busy with job and home stuff. Doesn’t mean I stop thinking about stuff.

• Do we really need another text editor/Personal Information Manager/MP3 player? I monitor Gnomefiles and Portable Freeware RSS feeds and I’m constantly seeing these types of apps being created, promising to be a better than anything out there with whiz-bang features not seen anywhere else. Got news for you, you aint showing us nuttin’ new.
• The First Bank of Delaware needs to DIAF for offering loans at 99.25% interest. That’s right, 99.25%! It’s done through their CashCall marketing arm. Yes. The one offered up by has-been Gary Coleman. They’re preying on people who can’t manage their money.
• MySQL-Proxy is …