Not the answer you need?
Register and ask your own question!

percona xtraDB cluster migration from 5.7 to 8.0.19

DBA100DBA100 Current User Role Patron
 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?

Answers

  • vadimtkvadimtk Contributor Percona Staff Role
    From our upgrade guide:
    https://www.percona.com/doc/percona-xtradb-cluster/LATEST/howtos/upgrade_guide.html#not-recommended-to-mix-pxc-5-7-nodes-with-pxc-8-0-nodes
    Not recommended to mix Percona XtraDB Cluster 5.7 nodes with Percona XtraDB Cluster 8.0 nodes
    Shut down the cluster and upgrade each node to Percona XtraDB Cluster 8.0. It is important that you make backups before attempting an upgrade.

  • DBA100DBA100 Current User Role Patron
    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 ?
  • vadimtkvadimtk Contributor Percona Staff Role
    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.
  • DBA100DBA100 Current User Role Patron
    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 .
  • vadimtkvadimtk Contributor Percona Staff Role
    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.
  • DBA100DBA100 Current User Role Patron
    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) ?
  • vadimtkvadimtk Contributor Percona Staff Role
    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.
  • DBA100DBA100 Current User Role Patron
    edited August 7
    "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?

  • vadimtkvadimtk Contributor Percona Staff Role
    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
  • DBA100DBA100 Current User Role Patron
    "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.

  • DBA100DBA100 Current User Role Patron
    edited August 7
    " 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 ?

  • vadimtkvadimtk Contributor Percona Staff Role
    `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.
  • DBA100DBA100 Current User Role Patron
    "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.
  • DBA100DBA100 Current User Role Patron
    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> 

    ?
  • DBA100DBA100 Current User Role Patron
    and I am not sure if --master-data=2 is necessary? for full DB backup and restore is it making any sense?
  • DBA100DBA100 Current User Role Patron
    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 ?
  • vadimtkvadimtk Contributor Percona Staff Role
    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.
  • DBA100DBA100 Current User Role Patron
    edited August 28
    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

Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.