0

i have been tasked with configuring a new MySQL server and splitting half our databases to it. i want to do this well and correctly. challenge is we can't bring the system down as our clients are constantly writing machine data to it. so i'm wondering, as i've never done this on a live system before, how i would best split it to the new server. i'm only migrating half the databases, and so far, i've just exported them for import as a starting point. but the databases are still being written to with new data AND the imports are taking forever: 4-10hrs each.

to try and speed things up, i've disabled autocommit and foreign key checks for each database, but i'm not seeing much time savings so far. my concern is for the data that's being written behind the export; if that makes any sense. how do i preserve data as i split this thing up? as you can likely tell, this is new to me. i know SQL, but not on this scale. and so the task falls to me because in the kingdom of the blind, the one-eyed man is king. :P

EDIT: i should note, these servers are running WinServer 2022 and MySQL Server 8.0.34

WhiteRau
  • 103
  • 3

1 Answers1

0

assum you want to split the data in server A to A & B (both hold half of the data finally)

you can try this:

  1. dump the source database from A (a time that the press of the server is low)
  2. restore the data to server B
  3. make B as slave of A in step 1, the dumpfile record the position that you dump from A, so after the master-slave channel is built, the data changed after dump time will continue to replicate to B. wait util B sync with A, you get two replica of the whole data.
  4. stop slave in B, change the application logic, (you decide to do this, means the application is ready to visite a two-shard-mysql-cluster).
  5. now half of the data in A and B is not used anymore, delete them in a proper time.
Klustron_dq
  • 75
  • 3