MySQL migration: 4.x to 5.0: Difference between revisions

From genomewiki
Jump to navigationJump to search
No edit summary
m (add category tag)
 
(9 intermediate revisions by 2 users not shown)
Line 1: Line 1:
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.
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:
The Process:
Line 19: Line 19:
* Point the other 4 RR machines to mysqlrr) [admins]
* Point the other 4 RR machines to mysqlrr) [admins]


''' YOU ARE HERE <----'''
*'''Convert hgwdev to MySQL 5.0 [admins]'''
*'''Convert hgwdev to MySQL 5.0 [admins]'''
** Drop any tables not needed from dev (including encode). [all, including grads]
** Drop any tables not needed from dev (including encode). [all, including grads]
Line 36: Line 35:
*** This step can be done in parallel with some of above (already in progress).
*** This step can be done in parallel with some of above (already in progress).
* Test hgwdev. [qa]  
* 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'''
'''At this point, we can resume push requests and CGI releases'''
* Turn on GenBank updates on the RR, hgwbeta, hgwdev [Mark]
* 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


* Dump-and-load hgcentral* everywhere
'''Turn on GenBank updates on the RR, hgwbeta, hgwdev [Mark]'''
** preliminary plan:
[[Category:Technical FAQ]]
** 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.

Latest revision as of 19:43, 20 January 2010

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]

Retrieved from "https://genomewiki.ucsc.edu/index.php?title=MySQL_migration:_4.x_to_5.0&oldid=9308"