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
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
Any hints ?
Claudio
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
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
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
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
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
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/.