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

transfer a DB size 6GB

DBA100DBA100 Current User Role Patron
hi experts,

I am new to percona xtraDB cluster and i just know mysqldump export and import for MySQL.

if we need to transfer 6TB of data from percona xtradB cluster 5.7.x to 8.0.19, what is the best method of that ?

any practical URL for it?

Answers

  • vinicius.grippavinicius.grippa Percona Percona Staff Role
    The golden rule is test everything before going to production. Validate all steps in a test environment and in case something goes wrong you can destroy it.
    The steps are documented here:
    https://www.percona.com/doc/percona-xtradb-cluster/LATEST/howtos/upgrade_guide.html
  • DBA100DBA100 Current User Role Patron
    sorry it is not an upgrade by migration..
    need to copy all existing data to target server.

  • 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 ?
  • vinicius.grippavinicius.grippa Percona Percona Staff Role
    You can use xtrabackup to backup and restore on the destination and then perform the in-place upgrade to 8.0.19. It should be faster than mysqldump.

    If you want to go with a logical dump, I recommend using mydumper and myloader which provides parallel processing.
  • DBA100DBA100 Current User Role Patron
    "You can use xtrabackup to backup and restore on the destination and then perform the in-place upgrade to 8.0.19. It should be faster than mysqldump."
    what is the full command line to do it ?

    As we will use more from mysql 5.7.23 on redhat 6.x to CentOS 8, so do you think MySQL 5.7.23 has a version on CentOS 8 ?
    and you mean install Mysql 5.7.23 on both side, then just use xtrabackup to backup and restore (then why don't we just use rsync to copy the whole data directory to the same path on target linux box and start the target mysql 5.7.23 on CentOS 8) to the target MySQL 5.7.23 on CentOS 8, then in place upgrade the new installed 5.7.23 on CentOS 8 ?

    "If you want to go with a logical dump, I recommend using mydumper and myloader which provides parallel processing."

    how to do it in command line, please share URL .
  • DBA100DBA100 Current User Role Patron
    edited August 28
    hi,
    any udpate for me ?
    "I recommend using mydumper and myloader which provides parallel processing."
    both tools included in the PXC installation? what is the full command for it if I ONLY want to backup:
    1) table schema, index, primary and foreign key ?
    2) DB logic, SP, view, function and trigger.
    3) data.

    and also how can I do compressed backup using xtraDB Backup  on 1) and 2) and 3 ) ?

  • vinicius.grippavinicius.grippa Percona Percona Staff Role
    Using Xtrabackup will backup the database at physical level and you do not have a way to execute only for options 1,2 and 3.
    If you want to split by these options you can use traditional mysqldump with the available options (--routines, --triggers and --no-data). 
    For mydumper/myloader these are examples:
    mydumper -B <db-name> --threads=50 --user=root --password=msandbox --host=127.0.0.1 --port=46008 --trx-consistency-only --events --routines --triggers --compress --outputdir /home/vinicius.grippa/sandboxes/backup/ --logfile /home/vinicius.grippa/sandboxes/mydumper.out --verbose=3
    And:
     myloader -B <db-name>  --threads=50 --user=root --password=msandbox --host=127.0.0.1 --port=46008 --directory=/home/vinicius.grippa/sandboxes/backup/ --overwrite-tables --verbose 3
    The mydumper/myloader are not part of Percona of the Percona Toolkit and you can download here:
    https://github.com/maxbube/mydumper

    For Xtrabackup here is an example:
    xtrabackup --defaults-file=my.sandbox.cnf -uroot -proot -H 127.0.0.1 -P 45007 --backup --parallel=4 --compress --compress-threads=2 --datadir=/var/lib/mysql --target-dir=./backup/ 
    Again, these are examples of the commands and they may need to be changed according to your needs and hardware capacity. 
  • DBA100DBA100 Current User Role Patron
    "--threads=50"
    This is for multi threading in mysqldump ?

    "mydumper -B <db-name> --threads=50 --user=root --password=msandbox --host=127.0.0.1 --port=46008 --trx-consistency-only --events --routines --triggers --compress --outputdir /home/vinicius.grippa/sandboxes/backup/ --logfile /home/vinicius.grippa/sandboxes/mydumper.out --verbose=3"

    sorry this seems backup everything?  what if I want to backup
    1)ONLY backup table 
    2) ONLY data
    3) MySQL logic only ?

    "myloader -B <db-name>  --threads=50 --user=root --password=msandbox --host=127.0.0.1 --port=46008 --directory=/home/vinicius.grippa/sandboxes/backup/ --overwrite-tables --verbose 3"

    what is the different between myloader and mysqldump?



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.