MySQL migration: 4.x to 5.0

During the late summer of 2009, we are migrating the UCSC Genome Browser MySQL server from v4.x to v5.0. This page will be used to discuss and track that migration.

The Process:

• Turn off GenBank updates on the RR, hgwbeta, and hgwdev machines [Mark]
• Load all databases from hgdownload dumps to mysqlrr, which will load as MySQL v5.0 [Hiram]
  • Touch tables to restore update times. [Hiram]
• Sync mysqlrr to mysqlbeta, which is also running MySQL 5.0 [Erich]
  • Verify that the conversion worked and the new tables match the old tables (compare to hgsqlrr) [Hiram]

At this point there is a moratorium on push requests and on CGI releases

• Point one RR machine that's not in the pool (hgw2) to look at mysqlrr [admins]
  • Test hgw2 using mysqlrr [qa]
• Convert "extra" tables from hgsqlbeta (4.x to 5.0) and place on mysqlbeta (e.g. qapushq, random not-already-pushed tables) [admins]
• Point hgwbeta to mysqlbeta [admins]
  • Test hgwbeta using mysqlbeta [qa]
• Move mysqlrr to the Communications Bldg! [admins]
• Point 4 RR machines to hgsqlrr and 4 RR machines to mysqlrr (to test the speed/impact) [admins]
• Point the other 4 RR machines to mysqlrr) [admins]

• Convert hgwdev to MySQL 5.0 [admins]
  • Drop any tables not needed from dev (including encode). [all, including grads]
  • Copy tables on hgwdev to new location as backup (hive) and configure a MySQL 4.0 server to see it. [admins]
  • Turn off encode pipeline and notify consortium [galt, tim]
  • Dump innodb encpipeline_* db [galt]
  • Dump 10Kgenomes innodb database [markd or admins]
  • Convert dev to read-only and re-rsync to get stable backup copy. [admins]
  • Erase db on dev and install MySQL 5.0. [admins]
  • Back-push MySQL dbs from mysqlbeta (hg18 first) [hiram, admins]
    • Dump-and-load and touch hg18.wgEncode* tables from dev backup to new dev even while backpush of non-hg18 tables continues. [qa]
    • Restore innodb encpipeline_* db and turn on encode pipeline. [galt, tim]
    • Import 10Kgenomes indodb database [markd or admins]
  • Convert and touch any tables in pushQ sections A and B from backup (4.x) to MySQL 5.0 and move to hgwdev. [QA, hiram]
  • Convert (dump-and-load. + touch, if needed) any tables needed from hgwdev backup to MySQL 5.0 [all, inc grads]
    • This step can be done in parallel with some of above (already in progress).
• Test hgwdev. [qa]

Plan B for dev conversion

The convert server hiram was using was too small to handle all of it, so we are converting in place:

• Take dev MySQL offline. YOU ARE HERE <----
• Start MySQl 5.0 (tables remain in place).
• Move copy of 5.0 beta/RR tables into place to replace 4.0 tables on dev.
• Convert-in-place hgcentraltest.
• Convert tables: get dates first by running, e.g, (script is in /cluster/bin/scripts if that is not in your path):

  makeTouchCommands.csh hgwdev hg18 netMm9

• • Convert-in-place and touch hg18.wgEncode* tables.
  • Convert-in-place and touch tables from pushQ.
  • Convert-in-place and touch any tables that people want the timestamps preserved (e.g., 2x assemblies).
• Special handling for tables with unique keys (mostly genbank - but not those in dbs already on RR) [galt]

At this point, we can resume push requests and CGI releases

• Turn on GenBank updates on the RR, hgwbeta, hgwdev [Mark]

• Dump-and-load hgcentral* everywhere
  • preliminary plan:
  • pick a time on a sunday night (labor-day weekend?)
    • announce down time and simply pull the plug?
    • warn users that things will be weird for an hour or two?)
  • dump-and-load (userDb and sessionDb are big!) to mysql 5.0 to new table name
    • rename userDb5 to userDb -- only seconds of down time, but users will lose changes they did since the dump.
    • hiram is testing how long the dump-and-load might take.
  • apologize to any users who lost the their state during the hour (more?) it was down.