I checked a lot of articles on how to migration from percona xtraDB cluster 5.7.x on one box to another box running percona xtraDB cluster 8.0.19, but one thing never mention, can I just
1) add 1 new built percona xtraDB cluster 8.0.19 nodes to existing xtraDB cluster 5.7 and let replication take care of all data/table schema, mysql logic/code to the 8.0.19 nodes, once it is done we take that 8.0.19 nodes out of the xtraDB cluster 5.7 pool
2) In the grastate.dat of the 8.0.19 nodes, make the safe_to_bootstrap = 1
3) bootstrap that 8.0.19 nodes to form the first node of a new cluster.
4) let the rest of the other 8.0.19 nodes join this new cluster.
is it going to work ? can 8.0.19 nodes join existing xtraDB cluster 5.7 pool?
From our upgrade guide:
Redirecting
Not recommended to mix Percona XtraDB Cluster 5.7 nodes with Percona XtraDB Cluster 8.0 nodesShut down the cluster and upgrade each node to Percona XtraDB Cluster 8.0. It is important that you make backups before attempting an upgrade.
it will be different host, so it will be a migration project instead of upgrade. any idea on what is the best way to migration ALL data and mySQL logic from percona xtraDB cluster 5.7 to percona xtraDB cluster 8.019?
can mysqlo workbench has this feature to migrate data for us ?
You can do it in the following steps:
-copy data with xtrabackup to the new host
-start new single node XtraDB Cluster 8.0 on that new host
-add more nodes to the new established cluster, one-by-one. The data will be copied automatically to the new nodes.
hi,
tks.
“-copy data with xtrabackup to the new host”
how ? any full command line for me ? mysqldump can also do the job ?
“-start new single node XtraDB Cluster 8.0 on that new host”
bootstrap that node as the primary node of the new percona xtraDB cluster 8.0.19 ?
then add other slave node one by one .
For XtraBackup guide please check
https://www.percona.com/doc/percona-xtrabackup/8.0/backup_scenarios/full_backup.html
You also can use mysqldump if you are more familiar with this tool.
“bootstrap that node as the primary node of the new percona xtraDB cluster 8.0.19 ?” - yes this is correct.
hi,
tks.
so this is what we need:
-copy data with xtrabackup /mysqldump to the new host
-start new single node XtraDB Cluster 8.0 on that new host
-add more nodes to the new established cluster, one-by-one. The data will be copied automatically to the new nodes.
xtrabackup backup user account and password too ?
and under what situation must we do:
1) The util.checkForServerUpgrade()
2) mysqlcheck -u root -p --all-databases --check-upgrade
are they the same thing ? MySQL shell has to be install on both case before it works or only 1) ?
Percona XtraDB Cluster 8.0 will perform auto-upgrade when it detects data from 5.7. You do not need MySQL shell and mysqlcheck commands.
“You do not need MySQL shell and mysqlcheck commands.”
but there will be imcompatible between version, right ? you know from 8.0.16 to 8.0.18 mysql_upgrade is need once uninstall 8.0.16 and install with 8.0.18, however from 5.7 to 8.0.19 it is a big jump, still no need to run util.checkForServerUpgrade() and mysqlcheck? just backup and restore to the new version the restore process will take care everything ?
if it is true and mysqldump AND/OR xtabackup can handle it for us, are we going to upgrade the xtrabackup on xtraDB cluster 5.7 nodes to the same version as the new 8.0.19 nodes first then backup from 5.7 nodes and restore to 8.0.19 nodes?
if you can handle your backup with mysqldump, it is probably the best course of action.
In this case the flow is following:
- make mysqldump from existing 5.7
- start a new Percona XtraDB Cluster 8.0 node on the new emtpy data directory
- restore mysqldump to the new cluster
- add more nodes one-by-one
“if you can handle your backup with mysqldump, it is probably the best course of action”
oh, surprise that it is even better than xtabackup, any reason for this?
Can this command:
mysqldump --host=<DB host we want
to back from > --all-databases --events --routines --replace --master-data=2
> <dump_file.sql>
backup EVERTHING from old percona 5.7 using the mysqldump binary from 5.7 and when restore, using the mysqldump binary on percona 8.0.19 and restore good ?
" start a new Percona XtraDB Cluster 8.0 node on the new empty data directory"
why need empty data directory ? the cluster 8.0.19 will be empty at that moment, that’s why the above statement has –replace, will it be ok ?
So workflow will be:
1) on any percona cluster 5.7 node, backup using mysqldump binary from percona cluster 5.7 to the .sql files,
2) copy the .sql file to the first percona cluster 8.0.19 primay nodes.
2) bootstrap the first percona cluster 8.0.19 primay nodes
3) restore to the first percona cluster 8.0.19 primay nodes, using mysqldump binary on percona cluster 8.0.19 ,
4) once complete just add slave nodes one by one.
" You do not need MySQL shell and mysqlcheck commands."
just a quick question, if it is no need, why it release ? must having a purpose, right ?
oh, surprise that it is even better than xtabackup, any reason for this?
In this case it seems better because you are more familiar with mysqldump then with xtrabackup.
From what I understand it will be easier for you to use the tool which you already used and not to learn xtrabackup.
“In this case it seems better because you are more familiar with mysqldump then with xtrabackup.”
so using either one make no difference and either one can export and import all objects including username, password and MySQL code/logic from 5.7x to 8.0.19 ?
So workflow will be :
1) on any percona cluster 5.7 node, backup using mysqldump binary from percona cluster 5.7 to the .sql files,
2) copy the .sql file to the first percona cluster 8.0.19 primay nodes.
2) bootstrap the first percona cluster 8.0.19 primay nodes
3) restore to the first percona cluster 8.0.19 primay nodes, using mysqldump binary on percona cluster 8.0.19 ,
4) once complete just add slave nodes one by one.
and backup is:
mysqldump --host=<DB host we want
to back from > --all-databases --events --routines --replace --master-data=2
> <dump_file.sql>
restore is:
mysql --host=<DB host we want to
restore to > -u root -p < <dump_file.sql>
?
and I am not sure if –master-data=2 is necessary? for full DB backup and restore is it making any sense?
when I read xtraDB backup I read this too:
https://www.percona.com/doc/percona-xtrabackup/8.0/backup_scenarios/full_backup.html
https://www.percona.com/doc/percona-xtrabackup/2.3/xbstream/xbstream.html
https://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/innobackupex_script.html
https://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/preparing_a_backup_ibk.html
Restore:
https://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/restoring_a_backup_ibk.html
why we need xbstream and innobackupex to do xtrabackup ?sir, what is the data migration size is 6TB, using mysqldump to backup from Percona XtraDB cluster 5.7.x and restore to 8.0.19 still the best method ?
6TB is a significant size. For this size I would use xtrabackup. But you also need a fast network and a fast storage, otherwise it will be very slow.
I check some xtrabackup command ,what is it for ?
xbstream -x < <backup file name> -C /fmkdb_backup_dc2/restore
for example
xbstream -x < fmkdb_backup_dc2/backup/Backupfmklog_20190820.xbstream -C /fmkdb_backup_dc2/restore
innobackupex –decompress /fmkdb_innobackupex –apply-log /fmkdb_backup_dc2/restorebackup_dc2/restore/
innobackupex –apply-log /path/to/BACKUP-DIR
?
I just want to know how to backup the following using xtrabackup:
1) backup only table schema, index, primary and foreign key, constraint of all DB from 5.7 to 8.0.19
2) backup only mysql logic of all DB : view, function, SP, etc. from 5.7 to 8.0.19
3) backup only data of all DB from 5.7 to 8.0.19