0

I have database in MySQL for an application which basically generates some kind of numbers with some other info attached with each number and the current size of the database is 1.5 TB . Now I want to migrate that DB to another server which have MySQL 8.0.25 and older MySQL version is 5.6.

both servers are on same network so I can easily copy files, so I tried with copying the DB files but that didn't work. Reason might be any major release was missing in between.

Due to DB size its not possible to generate script and then load the DB into new through script.

1 Answer 1

1

You should look into using pt-archiver

I wrote a script recently to copy 170 millions rows from one table to another

I used these options

PT_ARCHIVER_OPTS="--analyze=sd"
PT_ARCHIVER_OPTS="${PT_ARCHIVER_OPTS} --no-delete"
PT_ARCHIVER_OPTS="${PT_ARCHIVER_OPTS} --commit-each"
PT_ARCHIVER_OPTS="${PT_ARCHIVER_OPTS} --no-version-check"
PT_ARCHIVER_OPTS="${PT_ARCHIVER_OPTS} --no-check-charset"
PT_ARCHIVER_OPTS="${PT_ARCHIVER_OPTS} --no-check-columns"
PT_ARCHIVER_OPTS="${PT_ARCHIVER_OPTS} --low-priority-insert"
PT_ARCHIVER_OPTS="${PT_ARCHIVER_OPTS} --share-lock"
PT_ARCHIVER_OPTS="${PT_ARCHIVER_OPTS} --progress 25000"
PT_ARCHIVER_OPTS="${PT_ARCHIVER_OPTS} --limit 1000"
PT_ARCHIVER_OPTS="${PT_ARCHIVER_OPTS} --statistics"

Using --no-delete copies a row to dest without deleting it from the source table

You must specify --source and --dest options

  • --source is the host with 5.6 (--source F=host1.cnf,D=db1,t=tbl)
  • --dest is the host with 8.0 (--dest F=host2.cnf,D=db2,t=tbl)
1
  • this will work for each table one by one , I am looking for a solution where I can copy the DB files and make it working . Commented Jan 18, 2023 at 6:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.