At exactly midnight (EST) tonight, we are going to switch all 5 MySQL database servers, currently running MySQL4.1 to MySQL5.

There are plenty of differences between MySQL4 vs MySQL5, but the important ones that have made this switch inevitable for us are:

* Triggers: MySQL5 lets you specify a trigger - a set of MySQL statements to be executed whenever a certain tables is changed. This is particularly useful for our home-grown Indexing server and Tracking service, both of which previously had to continually SELECT COUNT(*) from several tables, as a way of identifying if stats need to be updated.

* Stored Procedures: Stored Procedures are a set of SQL statements that can be stored on the server (transmitted to other servers with replication) and executed in a single call. This helps in shifting business logic to the database server.

--

Upgrading all our internal database servers is going to require shutting down the databases for 5 to 10 minutes.

If anything is going to fail during the upgrade process, we're going to keep a mirror running with MySQL4 so that reverting back is easy.

What's the impact of taking all of our databases down?

Email Campaigns will continue to run (we deliver 5 million emails a day). The email sending engine is NOT database driven. We found implementing our own flat-files database, a hundred times more efficient than MySQL.

Website Tracking for all client websites will continue to collect results (they are collected into flat files on the tracking machine). Once the database will be back online, the tracking server will resume uploading stats to the database.

Client Website Front-ends will display all pages properly (because they're all cached to static files), not allowing posting of new comments or making any changes

Feed Management will be suspended

PPC Bid Management will be suspended. No bids will be updated by our engine for the duration of the upgrade process

--

Here are the steps our DBA is going to take on each machine:

Hopefully all will go well and at 10 minutes after midnight, we'll resume regular programming.

Note: Before upgrading to a new database version, it is VERY much recommended that you backup your entire database by doing a MySQL dump. We didn't have to worry about this because we have a live MySQL replication going for each database that serves as a hot backup.

"If all will go well"... Right. When's the last time that ever happened to you?

My name is Oleg and I'm a DBA here at SoftwareProjects responsible on this migration.

Let me start by explaining the "vinstall mysql5" above. We use NTT Verio as our back-bone data-center. Verio has a neat virtual-install for all popular packages. vinstall takes care of fetching the MySQL libraries, making and installing it for us.

Unfortunately, you cannot install MySQL5 on a machine running MySQL4. You have to first uninstall MySQL4. I was hoping there would be a way to avoid that, but no way.

So what I did is I setup the MySQL5 on a brand new machine. I set it up as a MySQL slave with the master set as the MySQL4 server. This way I can move a live database without having to do a shutdown-backup-wait

Once the new MySQL5 machine caught up (SQL Slave seconds behind master = 0), I proceeded to switch the IP address pointed to by db.softwareprojects.com so that it points to the new machine.

We use eNom DNS Server on all domains which gives us instant (4 seconds) propagation.

I proceeded to do a global mysqlcheck on all tables of the new server, just to make sure nothing got corrupted during the transfer.

Kept the MySQL4 server running for a while, so that any queries that ended up on this machine in the interim period of about 2 minutes, would replicate properly to the MySQL5 machine.

Once SHOW PROCESSLIST on the MySQL4 machine showed it was idle, I took it down.

Now just need to ping a level 2 engineer to test all core services and ensure there are no incompatibilities due to the new database version.

One thing I forgot to mention - after you're done importing all the data from MySQL4 to MySQL5, it's very important to run:


Some system tables (like mysql.user) have more fields in MySQL5 and the mysql_upgrade script takes care of updating everything

All systems go! Everything checked out okay

We are now officially running MySQL5 across the board