hi,
"
mysqldump --single-transaction --master-data=2 --databases $dbname > dbname.sql"
I knew this, and i even use zip format to do it but it is not the whole point, it is about it takes for a long time to restore and it make putty session keep dropping out.
“2) When you say data only do you mean you do not need the CREATE DATABASE/TABLE commands included in the dump? If so then use --no-create-info in the mysqldump command. You can also take a dump of the table structure only with --no-data.”
only data to save space, table schema and all other logic can script out and redeploy in target cluster 8.0.19 ! how ? how about no any other DB logic? e.g. trigger, view and SP? what I am missing is data at this moment and the mysql dump file is 20GB, gziped is around 10-15GB one single file! a lot of error when importing using mysql -uxxx -p < has problem on 8.0.19 when the dump from 5.7.x has trigger , view, function and SP recreation! this make the whole dump progress stopped!
“3) No! It is not advisable to copy a single folder from the mysql data directory and expect that it will work on the new host.”
so the rsync IS NOT GOING TO WORK? if I want rsync to work in that way , from 5.7x and 8.0.19 any intermedia version in between 5.7.x and 8.0.19 should I try this method first and then from that version to do the same thing to upgrade to 8.0.19 , e.g.:
5.7.x → a stable version and data migrate by rsync (it will upgrade the meta data) - > 8.0.19 ?
"Since you are migrating the data from 5.7 to 8.0 it is best to use mysqldump also considering that the 8.0 cluster already has existing data.’
So I don’t mysqdump import: mysql DB, sys DB and performance_schema DB, which is different for different MySQL, right ? the rest of DB is ok to import right ?
"If so then use --no-create-info in the mysqldump command. "
something like :
mysqldump -uroot -p --databases --no-create-info --replace | sed ‘s/NO_AUTO_CREATE_USER//g’ | gzip -9 > UAT_fDB_bak.sql.gz
previously we do:
mysqldump -uxxx -p --databases <dbname. --events --routines --triggers --replace | sed ‘s/NO_AUTO_CREATE_USER//g’ | gzip -9 > UAT_bak.sql.gz
SO no need to export to a .CSV file and import the .CSV file ?