logoalt Hacker News

jpablo • yesterday at 4:52 PM • 2 replies • view on HN

  If you’re migrating a large MySQL database and you’re not 
  using mydumper/myloader, you’re doing it the hard way.
If you aren't using xtrabackup you are doing it wrong. I recently migrated a database with 2TB of data from 5.7 to 8.4 with about 15 seconds of down time. It wouldn't have been possible without xtrabackup. Mysqldumper requires a global write block, I wouldn't call blocking writes for hours a "zero downtime migration".

Replies

grasbergerm • yesterday at 10:23 PM

I have experience in migrating large DBs with replication and the article not discussing write blocks made my ears perk up as well.

Aside from the blocking you mentioned during the initial snapshot, you'd need to block writes to the old DB before the cutover as well. There's no way to guarantee in-flight writes to the old DB aren't lost when promoting the replica to a primary otherwise. I'm surprised the author didn't go into more detail here. Maybe it was fine given their workload, but the key issue I see is that they promoted the new DB to a primary before stopping the old application. During that gap, any data written to the old DB would be lost.

embedding-shape • yesterday at 4:56 PM

Correct me if I'm wrong, but done with a proxy in-between that can "pause" requests, you could have done the move with 0 seconds and no rejected requests, and I don't think mydumper/myloader/xtrabackup matters for that. The "migration" would be spinning up a new database, making it catch up, then switching over. If you can pause/hang in-flight requests while switching, not a single one needs to fail :)

➕ show 1 reply