MySQL Database migration for 1.5 TB database size from MySQL 5.6 to MySQL 8.0.25

Question

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.

Answer 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)