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. Note that the process for eventual migration to 5.1 will be similar. In both cases, it is necessary to convert from the public site backwards toward the development site, because newly created tables cannot be pushed onto a system running an earlier version.

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.
• Start MySQl 5.0 (tables remain in place). [erich]
• 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 all tables. [galt]
• Special handling for tables with unique keys (mostly genbank - but not those in dbs already on RR) [galt]
• Add one second to file timestamps to be sure rsync of mirrors will see as new files. [hiram]

do not change timestamp of any tables that were reloaded from old dev after the backpush from new beta (hg18.dgv, etc).

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

• convert mypush to new machine

• Dump-and-load hgcentral (Time: Sunday, Sept 6 (labor-day weekend) 8AM - noon)
  • (before 9/6)announce down time to genome-announce. also warn that saving state in a session would be a good idea, just in case. [donna]
  • (before 9/6) announce new table types (released bit-by-bit) to genome-mirror [ann]
  • stop apache on RR [admins]
  • stop MySQL 4 on hgnfs1 [admins]
  • copy hgcentral to hgcentralback (on hgmisc1?) [admins]
  • update to MySQL 5 on hgnfs1 [admins]
  • copy hgcentral from hgmisc1 back to hgcentral [admins]
  • start MySQL 5 on hgnfs1 [admins]
  • ALTER TABLE "tablename" TYPE=myisam [admins]
  • start RR

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