MySQL migration: 4.x to 5.0: Difference between revisions
From genomewiki
Jump to navigationJump to search
No edit summary |
m (add category tag) |
||
| (18 intermediate revisions by 3 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 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 | * Point the other 4 RR machines to mysqlrr) [admins] | ||
*'''Convert hgwdev to MySQL 5.0 [admins]''' | *'''Convert hgwdev to MySQL 5.0 [admins]''' | ||
| Line 23: | Line 23: | ||
** Copy tables on hgwdev to new location as backup (hive) and configure a MySQL 4.0 server to see it. [admins] | ** 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] | ** 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] | ** Convert dev to read-only and re-rsync to get stable backup copy. [admins] | ||
** Erase db on dev and install MySQL 5.0. [admins] | ** Erase db on dev and install MySQL 5.0. [admins] | ||
** 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] | ||
*** 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 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 | ** 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] | * 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''' | ||
* 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]''' | |||
[[Category:Technical FAQ]] | |||
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]
