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

How to copy data from a Master PXC to a Slave PXC

sopranosoprano EntrantCurrent User Role Supporter
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 [email protected]
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

Best Answers

  • sopranosoprano Entrant Current User Role Supporter
    Accepted Answer
    I suppose this 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

Answers

  • sopranosoprano Entrant Current User Role Supporter
    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/
    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
  • sopranosoprano Entrant Current User Role Supporter
    Any hints ?
    Claudio
  • sopranosoprano Entrant Current User Role Supporter
    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/ibdata1
    Can not move file ibdata1 to /var/lib/mysql/ibdata1: No such file or directory
    what i have to do ?
    Thanks in advance
    Claudio
  • sopranosoprano Entrant Current User Role Supporter
    i found that syntax on the page linked 
    on this https://www.percona.com/doc/percona-xtrabackup/LATEST/howtos/setting_up_replication.html
    anyway i will try it and thanks for now
    Claudio
  • sopranosoprano Entrant Current User Role Supporter
    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
  • daniil.bazhenovdaniil.bazhenov Percona Admin Role, API
    Dear @soprano
    I created a task in Jira to fix a bug in the documentation
    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/
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.