Ah well, since my first article on this, MySQL has had multiple version changes and I have gained more experience in using MySQL. (Off topic...... I really like the fact that I am stuck in everything from MySQL server config/support thru Apache thru WebObjects/Java development, deployment and administration ...... never a dull day in the job!). Here is my "new improved" process for setting up a replication master-slave configuration using MySQL 4.1.21. These instructions will definitely not work for version 4.0.XX and earlier and may not work for some earlier versions of 4.1. Earlier version incompatabilities are mostly related to the parameters used in mysqldump.

This article assumes a basic knowledge of unix (cd, ssh, scp, mkdir, chown) and a basic knowledge of mysql (mysqld, mysql, mysqldump, mysqladmin)

Ah well, since my first article on this, MySQL has had multiple version changes and I have gained more experience in using MySQL. (Off topic...... I really like the fact that I am stuck in everything from MySQL server config/support thru Apache thru WebObjects/Java development, deployment and administration ...... never a dull day in the job!). Here is my "new improved" process for setting up a replication master-slave configuration using MySQL 4.1.21. These instructions will definitely not work for version 4.0.XX and earlier and may not work for some earlier versions of 4.1. Earlier version incompatabilities are mostly related to the parameters used in mysqldump.

This article assumes a basic knowledge of unix (cd, ssh, scp, mkdir, chown) and a basic knowledge of mysql (mysqld, mysql, mysqldump, mysqladmin)

There are essentially two main choices for scaling MySQL - cluster (NDB) or replication.

For many people replication works fine because their application is mostly read based. Just throw a few MySQL slave servers into the mix and you can scale out pretty well.

My guess is that this only works well for about 80-90% of users.

You update your database on the master but perform queries on the slaves. If you need more queries you can just add more slaves.

The problem with replication is that you can't scale your writes. If you buy an expensive RAID array you can probably get 1500 transactions per second (maybe more) out of your IO array but that's the best you can do for the whole cluster.

As soon as you hit 100% of your transactions as writes you're done. You've hit a scaling wall with replication and you can't go any farther.

You can of course go with vertical replication partitioning which works …

Many of you might know HowToForge, but for those who don't - there's a bunch of excellent and interesting tutorials relating to Linux and OpenSource.

There are also many about MySQL or using MySQL - here are some examples:

• How To Set Up Database Replication In MySQL
  
• MySQL Master Master Replication
  
• How To Set Up A Load-Balanced MySQL Cluster
  
• Running A MySQL-Based DNS Server: MyDNS
  
• …

Dear Diary, today I ventured into one of the darkest realms of the sysadmin profession: I started playing with SNMP. My goal was very simple and quite clearly stated: Make the output of "SHOW GLOBAL STATUS" available to a SNMP client. One would think that this is a reasonable and easily fulfilled wish.

Little did I know of the madness and despair that linger in the depths which are guarded by the dread named ASN.1 and where the noxious fumes of the organisation no longer called CCITT can still strongly be smelled.

But let us begin this story at the beginning - with a clean install of Suse Linux 10.0 and my trusty apt4rpm and me. It was my thought that the perl support of net-snmp might me handy to get me where I wanted to me: perl is easily the more convenient language for prototyping that C or C++ and getting to the data …

I got a lot of feedback on my question a few weeks back when pondering what I might choose for an OS to run MySQL. I also got a few "so what did you go with" questions.

A week ago I decided to give Ubuntu server a shot at winning my favor. I haven't used Ubuntu much, but all the fuss over it warrants giving it a shot.

I'm quite pleased so far. It meets at least two of the items on my list from that original post (minimal processes and minimal footprint). I'm not familiar with the philosophy behind building the Ubuntu kernel, but I'll accept the fact that there's nothing specifically tailored for MySQL.

Four clues that the server is installed minimal:

• 
  
• 17 minutes from start to finish (including times when it sits …

The sixteenth edition of Log Buffer, the weekly review of database blogs, has been published by Guy Bowerman on his blog, Informix Application Development. Log Buffer has a distributed editorship. If you’d like to share your POV on the database blogosphere, read the Log Buffer homepage and get involved. Take it away, Guy.

A bit ago ago I posted some optimizer hints and recommended using them to write more readable queries. Today I stumbled across an example of my own work that I changed. This is not quite a case of using BETWEEN instead of AND statements, but it sort of is. Basically, I needed to reuse this query:

SELECT count(*),city,
CASE WHEN lastLogin < now ()-interval 90 day THEN 90
WHEN lastLogin < NOW()-interval 60 day THEN 60
WHEN lastLogin < NOW()-interval 30 day THEN 30
WHEN lastLogin > NOW()-interval 30 day THEN 0
ELSE -1
END
FROM . . .

I wrote this query less than a month ago.

I looked at this query today and wondered, “why did I not put lastLogin < now ()-interval 60 AND lastLogin>NOW()-interval 90 in there?” I then realized what I did.

Because the CASE statement …

A bit ago ago I posted some optimizer hints and recommended using them to write more readable queries. Today I stumbled across an example of my own work that I changed. This is not quite a case of using BETWEEN instead of AND statements, but it sort of is. Basically, I needed to reuse this query:

SELECT count(*),city,
CASE WHEN lastLogin < now ()-interval 90 day THEN 90
WHEN lastLogin < NOW()-interval 60 day THEN 60
WHEN lastLogin < NOW()-interval 30 day THEN 30
WHEN lastLogin > NOW()-interval 30 day THEN 0
ELSE -1
END
FROM . . .

I wrote this query less than a month ago.

I looked at this query today and wondered, “why did I not put lastLogin < now ()-interval 60 AND lastLogin>NOW()-interval 90 in there?” I then realized what I did.

Because the CASE statement …

If foreign keys are a part of the database namespace, why is it that you have to ALTER TABLE to add and remove them? Why not alter the database? I suspect the issue is more in MySQL’s limitations that names of foreign keys be unique among tables in a database. . . but it does make me wonder.