MySQL migration: 4.x to 5.0: Difference between revisions
From genomewiki
Jump to navigationJump to search
No edit summary |
No edit summary |
||
| Line 15: | Line 15: | ||
* Point hgwbeta to mysqlbeta [admins] | * Point hgwbeta to mysqlbeta [admins] | ||
** Test hgwbeta using mysqlbeta [qa] | ** 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 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 (do this sooner??) [admins] | * Point the other 4 RR machines to mysqlrr (do this sooner??) [admins] | ||
| Line 28: | Line 28: | ||
** Back-push MySQL dbs from mysqlbeta (hg18 first) [hiram, 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] | *** 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] | ||
** 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 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] | ** Convert (dump-and-load. + touch, if needed) any tables needed from hgwdev backup to MySQL 5.0 [all, inc grads] | ||
Revision as of 17:52, 28 August 2009
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 (do this sooner??) [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]
- 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]
- 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]
- Test hgwdev. [qa]
At this point, we can resume push requests and CGI releases
- Turn on GenBank updates on the RR, hgwbeta, hgwdev [Mark]
