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?
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
sorry it is not an upgrade by migration…
need to copy all existing data to target server.
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 ?
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.
“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 .
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 ) ?
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.
“–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?