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]
• Convert hgwdev to MySQL 5.0 [admins]
  • Copy tables on hgwdev to new location as backup. [admins]
  • Convert any tables in pushQ sections A and B from backup (4.x) to MySQL 5.0 and move to hgwdev. [admins, Hiram?]
  • Convert and move any tables needed from hgwdev backup to MySQL 5.0 [all, inc grads]
• Test hgwdev. [qa]
• Point the other 7 RR machines to mysqlrr (do this sooner??) [admins]
  • Consider pointing 4 RR machines to hgsqlrr and 4 RR machines to mysqlrr (to test the speed/impact) [admins]

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

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