I need to restore a backup made by xtrabackup, but I am using xtradbcluster to replicate the main database in the secondary database in an almost synchronous way. I was able to restore it to the main database using resync, but it did not replicate to the second database.
What is “resync”?
I’m confused. Can you provide a diagram of your topology?
Sorry, I ended up writing it wrong, it would be “rsync”, it was the only method that worked. I saw about it in this documentation Restore full, incremental, compressed backups - Percona XtraBackup.
Yes, I will make the diagram:
I restored the backup from node 1, but it did not replicate the data to node 2.
Do I understand it correctly that you have 2 nodes PXC setup. Node A and Node B. You stopped B gracefully(I hope) and restored the backup from Node A on Node B. Then you start server on node B and expect PXC to work as before?
Please clarify if you use async replication or PXC cluster between nodes?
OK, I stopped both nodes, restored the backup on node 1, and started node 1 and node 2. However, the backup did not replicate to node 2. Should I have done something else?
I am using PXC Cluster.
Correct. You should have started node1 in bootstrap mode (systemctl start mysql@bootstrap). This creates the cluster. You must always bootstrap the first node when starting the cluster.
Did you configure node2 wsrep_cluster_address to include node1? If you do not configure node2 to connect to node1, then yea, nothing will happen.
I configured it this way:
Node 1:
#Template my.cnf for PXC
#Edit to your requirements.
[client]
socket=/var/run/mysqld/mysqld.sock
[mysqld]
server-id=1
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
log-error=/var/log/mysql/error.log
pid-file=/var/run/mysqld/mysqld.pid
port=3306
#Binary log expiration period is 604800 seconds, which equals 7 days
binlog_expire_logs_seconds=604800
######## wsrep ###############
#Path to Galera library
wsrep_provider=/usr/lib/galera4/libgalera_smm.so
#Cluster connection URL contains IPs of nodes
#If no IP is found, this implies that a new cluster needs to be created,
#in order to do that you need to bootstrap this node
wsrep_cluster_address=gcomm://10.48.77.227, 10.48.77.226
#In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW
#Slave thread to use
wsrep_slave_threads=8
wsrep_log_conflicts
#This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2
#Node IP address
wsrep_node_address=10.48.77.227
#Cluster name
wsrep_cluster_name=pxc-cluster
#If wsrep_node_name is not specified, then system hostname will be used
wsrep_node_name=percona-cluster-node
#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
pxc_strict_mode=ENFORCING
#SST method
wsrep_sst_method=xtrabackup-v2
Node 2
#Template my.cnf for PXC
#Edit to your requirements.
[client]
socket=/var/run/mysqld/mysqld.sock
[mysqld]
server-id=1
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
log-error=/var/log/mysql/error.log
pid-file=/var/run/mysqld/mysqld.pid
port=3307
#Binary log expiration period is 604800 seconds, which equals 7 days
binlog_expire_logs_seconds=604800
######## wsrep ###############
#Path to Galera library
wsrep_provider=/usr/lib/galera4/libgalera_smm.so
#Cluster connection URL contains IPs of nodes
#If no IP is found, this implies that a new cluster needs to be created,
#in order to do that you need to bootstrap this node
wsrep_cluster_address=gcomm://10.48.77.227, 10.48.77.226
#In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW
#Slave thread to use
wsrep_slave_threads=8
wsrep_log_conflicts
#This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2
#Node IP address
wsrep_node_address=10.48.77.226
#Cluster name
wsrep_cluster_name=pxc-cluster
#If wsrep_node_name is not specified, then system hostname will be used
wsrep_node_name=application-cluster-node
#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
pxc_strict_mode=ENFORCING
#SST method
wsrep_sst_method=xtrabackup-v2
Is there something wrong that I need to fix?
It is replicating normally when I create a database and insert values. However, it does not replicate the backup.
Did you bootstrap node1? What does this show on node1 and node2? SHOW GLOBAL STATUS LIKE 'wsrep_cluster_size'
I started node 1 with ‘systemctl start mysql@bootstrap.service’ and node 2 with ‘systemctl start mysql’
It has the same value in both nodes ‘wsrep_cluster_size = 2’
I would shut down node2. Erase everything in the datadir of node2. Then start node2 mysql normally. This should force an SST of the entire data set on node1 to copy to node2.
It worked! I deleted everything that was inside /var/lib/mysql/ and tried to start it again, it gave an error because it didn’t have the certificates from node 1, I copied them and tried to start node 2 again, it started and synchronized the data.
Thank you very much!