# How to copy data from a Master PXC to a Slave PXC
**URL:** https://forums.percona.com/t/how-to-copy-data-from-a-master-pxc-to-a-slave-pxc/7589
**Category:** Percona Server for MySQL 5.6
**Created:** [April 29, 2020, 10:24am UTC](https://forums.percona.com/t/how-to-copy-data-from-a-master-pxc-to-a-slave-pxc/7589 "2020-04-29T10:24:12Z")
**Posts on this page:** 9
**Page:** 1
### Author:  [@soprano](https://forums.percona.com/u/soprano)
#### Post date: [April 29, 2020, 10:24am UTC](https://forums.percona.com/t/how-to-copy-data-from-a-master-pxc-to-a-slave-pxc/7589/1 "2020-04-29T10:24:12Z")
More than two years ago i set
a) 1st Percona xtradb Cluster multimaster, then i linked it to
b) 2nd Slave Percona xtradb Cluster multimaster and at the end i linked it to
c) 3rd Slave Percona xtradb Cluster
Lately we had several issue with the 1st Percona xtradb Cluster and i discovered that 2nd and 3rd PXC are out of sync but from long time, about one year.
I don’t remember how i was copying the data files from 1st PXC to the 2nd PXC and then on the 3rd PXC, but i did it because it worked for more than 1 year and half before it stopped.
So i would like to recover this scenario because now i need it, but seems i can’t get the data from 1st PXC copied to 2nd PXC, this is that i did and i think there is something wrong:
1) I connect to the third node of 1st PXC and run
mysql\> FLUSH TABLES WITH READ LOCK;
- with another terminal i get the info i need
mysql\> SHOW MASTER STATUS;
±------------------±---------±-------------±-----------------±------------------+| File | Position | Binlog\_Do\_DB | Binlog\_Ignore\_DB | Executed\_Gtid\_Set |±------------------±---------±-------------±-----------------±------------------+| XXX-bin.000024 | 29005320 | | | |±------------------±---------±-------------±-----------------±------------------+
if i run this command several times the value doesn’t change
I didn’t need to create the Replication users because they were set before.
2) I connected to the 1st node of 2nd PXC and ran
removed all the mysql files
rm -rf /var/lib/mysql
copied all the db files from 3rd node of 1st PXC
rsync -av 3node1stPXC:/var/lib/mysql /var/lib
started the PXC with the bootstrap option
systemctl start mysql@bootstrap
then inserted the information about the master
mysql\> CHANGE MASTER TO MASTER\_HOST=‘domainname’, MASTER\_USER=‘repl’, MASTER\_PASSWORD=‘pwd’, MASTER\_LOG\_FILE=‘XXX-bin.000024’, MASTER\_LOG\_POS= 29005320
started the slave
mysql\> SLAVE START;
show the slave status
mysql\> SHOW SLAVE STATUS\G;
it shows all is ok also the binary log file and position
- On the 3rd node of 1st PXC in the first mysql windows where i locked tables i ran
mysql\> UNLOCK TABLES;
now on the slave i got some errors that some files can’t be found but those files exist on the server.
I suppose that the LOCK TABLES with the rsync are not doing the right jobs for copying raw data files.
I tried also to stop mysql after the LOCK TABLES command (to copy the files with the DB shutted down) but it didn’t stop the server up to i UNLOCKed TABLES;
but in this way if i copy the files i don’t know in which position i will be at the end.
Let me know how i can achieve the copied and get the binary log right position.
I would like to make a rsync because it is faster than a DB dump/restore.
Thanks in advance
Claudio
---
### Author:  [@soprano](https://forums.percona.com/u/soprano)
#### Post date: [April 29, 2020, 11:10am UTC](https://forums.percona.com/t/how-to-copy-data-from-a-master-pxc-to-a-slave-pxc/7589/2 "2020-04-29T11:10:03Z")
I’m sorry this topic would be moved to the Percona Xtradb Cluster section.
I read this [https://www.percona.com/blog/2013/10/08/taking-backups-percona-xtradb-cluster-without-stalls-flow-control/](https://www.percona.com/blog/2013/10/08/taking-backups-percona-xtradb-cluster-without-stalls-flow-control/ "Link: https://www.percona.com/blog/2013/10/08/taking-backups-percona-xtradb-cluster-without-stalls-flow-control/")
So maybe the solution is:
1) Put the 3rd Node of the 1st PXC with wsrep\_desync=ON
2) Take a FLUSH TABLES WITH READ LOCK (FTWRL)
3) Get the Binlog info
4) UNLOCK TABLES;
5) SHUTDOWN THE NODE
6) rsync the data to the 1st Node of 2nd PXC
7) Restart the 3rd Node of the 1st PXC
8) Put wsrep\_desync=OFF
Is this the right way to do ?
Claudio
---
### Author:  [@soprano](https://forums.percona.com/u/soprano)
#### Post date: [May 4, 2020, 2:03am UTC](https://forums.percona.com/t/how-to-copy-data-from-a-master-pxc-to-a-slave-pxc/7589/3 "2020-05-04T02:03:54Z")
Any hints ?
Claudio
---
### Author:  [@soprano](https://forums.percona.com/u/soprano)
#### Post date: [May 4, 2020, 2:16am UTC](https://forums.percona.com/t/how-to-copy-data-from-a-master-pxc-to-a-slave-pxc/7589/4 "2020-05-04T02:16:13Z")
I suppose this [https://www.percona.com/doc/percona-xtrabackup/LATEST/howtos/setting\_up\_replication.html](https://www.percona.com/doc/percona-xtrabackup/LATEST/howtos/setting_up_replication.html "Link: https://www.percona.com/doc/percona-xtrabackup/LATEST/howtos/setting\_up\_replication.html")
would be the right way to do.
I will try and let you know if works.
Is there a way to check that master and slaves are aligned perfectly when they are online ? or i need to verify them shutting them down ?
Claudio
---
### Author:  [@soprano](https://forums.percona.com/u/soprano)
#### Post date: [May 4, 2020, 3:51am UTC](https://forums.percona.com/t/how-to-copy-data-from-a-master-pxc-to-a-slave-pxc/7589/5 "2020-05-04T03:51:59Z")
hmmm i have still some problems
on the slave i removed all the datadir files
on the 3rd node of 1 PXC i ran
xtrabackup --backup --target-dir=/backup/backup\_20200504
xtrabackup --prepare --target-dir=/backup/backup\_20200504
then rsync on the slave /backup directory
then on the slave i did
cd /backup
rm -rf /var/lib/mysql (this is the datadir of Percona mysql)
xtrabackup --move-back --target-dir=/var/lib/mysql
and i get
xtrabackup version 2.3.10 based on MySQL server 5.6.24 Linux (x86\_64) (revision id: bd0d4403f36)200504 11:21:31 [01] Moving ibdata1 to /var/lib/mysql/ibdata1Can not move file ibdata1 to /var/lib/mysql/ibdata1: No such file or directory
what i have to do ?
Thanks in advance
Claudio
---
### Author:  [@satya.bodapati](https://forums.percona.com/u/satya.bodapati)
#### Post date: [May 4, 2020, 4:22am UTC](https://forums.percona.com/t/how-to-copy-data-from-a-master-pxc-to-a-slave-pxc/7589/6 "2020-05-04T04:22:08Z")
Your xtrabackup command to move prepared files from backupdir to main dir looks wrong. Please try: xtrabackup --copy-back --datadir=/var/lib/mysql --target-dir=/backup/backup\_20200504
---
### Author:  [@soprano](https://forums.percona.com/u/soprano)
#### Post date: [May 4, 2020, 6:05am UTC](https://forums.percona.com/t/how-to-copy-data-from-a-master-pxc-to-a-slave-pxc/7589/7 "2020-05-04T06:05:01Z")
i found that syntax on the page linked
on this [https://www.percona.com/doc/percona-xtrabackup/LATEST/howtos/setting\_up\_replication.html](https://www.percona.com/doc/percona-xtrabackup/LATEST/howtos/setting_up_replication.html)
anyway i will try it and thanks for now
Claudio
---
### Author:  [@soprano](https://forums.percona.com/u/soprano)
#### Post date: [May 4, 2020, 6:22am UTC](https://forums.percona.com/t/how-to-copy-data-from-a-master-pxc-to-a-slave-pxc/7589/8 "2020-05-04T06:22:55Z")
thanks it worked and now i’m synced again, i suppose that page has to be updated
also the
cat /var/lib/mysql/xtrabackup\_binlog\_info
become
cat /var/lib/mysql/xtrabackup\_binlog\_pos\_innodb
in my case.
Anyway thank you again
Claudio
---
### Author:  [@daniil.bazhenov](https://forums.percona.com/u/daniil.bazhenov)
#### Post date: [May 4, 2020, 6:44am UTC](https://forums.percona.com/t/how-to-copy-data-from-a-master-pxc-to-a-slave-pxc/7589/9 "2020-05-04T06:44:00Z")
Dear @soprano
I created a task in Jira to fix a bug in the documentation
[https://jira.percona.com/browse/PXB-2169](https://jira.percona.com/browse/PXB-2169)
You can sign and receive a notice of correction.
@Satya thank you for your help
P.S. Perhaps you will be interested to become a contributor to this project and fix the bug in the documentation yourself. We have instructions on how to contribute to Percona documentation. [https://www.percona.com/community-blog/2020/01/28/how-to-contribute-to-pmm-documentation/. ](https://www.percona.com/community-blog/2020/01/28/how-to-contribute-to-pmm-documentation/. );