How to backup and restore data to a new Cluster?

MySQL & MariaDB Percona XtraDB Cluster 8.x
1

Hi all,
I’m trying to back up then restore data to a new Cluster. The data is large as more than 70gb.

  1. I installed two new Galera Clusters, Cluster 1 and Cluster 2, these two Clusters replicate data each other via replication setting (using binlog). Cluster 1 has a node repicates data with a node from Cluster 2, and vise versa. So every write transaction to Cluster 1 can be replicated to Cluster 2. I tested writing data to Cluster 1 and Cluster 2, replicating process works correctly.
   +------------------+                          +------------------+
   |  Galera Node 1   |                          |  Galera Node 4   |
   | (Cluster 1, Node)|                          | (Cluster 2, Node)|
   +------------------+                          +------------------+
                                                    ^
                                                    |
   +------------------+                          +------------------+
   |  Galera Node 2   |                          |  Galera Node 5   |
   | (Cluster 1, Node)|                          | (Cluster 2, Node)|
   +------------------+                          +------------------+
                                                    ^
                                                    |
   +------------------+                          +------------------+
   |  Galera Node 3   |<------------------------>|  Galera Node 6   |
   | (Cluster 1, Node)|                          | (Cluster 2, Node)|
   +------------------+                          +------------------+
  1. I tried generating dump file and restore to a node in Cluster 1 (I see data come to all nodes in both Clusters), but after some minutes, there is an “Deadlock” error, even I try to restore data when Cluster 2 down, its still the same error, I tried to run restoring for a standalone mysql, it can easily succeed:
> mysqldump -u root -p <password> --set-gtid-purged=OFF  --skip_add_locks --skip-lock-tables --single-transaction <database_name> > backup.sql

> mysql -u root -p <password>  <database_name> < backup.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1213 (40001) at line 4188: Deadlock found when trying to get lock; try restarting transaction
  1. I dont know why it keeps that “Deadlock” error. With options in mysqldump command, it must not lock any table and it is supposed to restore line by line from dump file.
  2. Is it possible to use xtrabackup to generate backup then restore to my new Clusters ? How can I do that ?

Thank you very much.

2

Can you check the deadlock section from “show engine innodb status” to check which query is causing the deadlock while doing restore?

3

@yunus_shaikh , while restoring, seems like it locks a row at a time to insert, and one thing, the data of each insert is so large also:

root@clicque:/home/clicque# mysql -u root -p <password> -e 'show engine innodb status\G;' | grep -C20 lock
mysql: [Warning] Using a password on the command line interface can be insecure.
-----------------
srv_master_thread loops: 1139 srv_active, 0 srv_shutdown, 15665 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 885
OS WAIT ARRAY INFO: signal count 849
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 162033
Purge done for trx's n:o < 162031 undo n:o < 0 state: running but idle
History list length 9
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421409455507416, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421409455512600, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421409455511736, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421409455510872, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421409455510008, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421409455505688, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421409455504824, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421409455503960, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421409455503096, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 162031, ACTIVE (PREPARED) 0 sec
mysql tables in use 1, locked 1
1 lock struct(s), heap size 1128, 0 row lock(s), undo log entries 136
MySQL thread id 95, OS thread handle 139915506005568, query id 153582 localhost root wsrep: write set replicated and certified (49303)
INSERT INTO `tablename` VALUES (181841,32714,'{\"TestId\": 1356, \"Comment\": null, \"SampleId\": 32714, \"Sequence\": 0, \"Subtests\": [], \"TestName\": \"content screening \", \"SlitSmear\": {\"Other\": null, \"Site1\": {\"Solid\": null, \"SiteName\": null, \"Fragmented\": null, \"SiteDensity\": null}, \"Site2\": {\"Solid\": null, \"SiteName\": null, \"Fragmented\": null, \"SiteDensity\": null}, \"Site3\": {\"Solid\": null, \"SiteName\": null, \"Fragmented\": null, \"SiteDensity\": null}, \"Site4\": {\"Solid\": null, \"SiteName\": null, \"Fragmented\": null, \"SiteDensity\": nu
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests ((null))
I/O thread 1 state: waiting for completed aio requests (insert buffer thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:
Pending flushes (fsync) log: 1; buffer pool: 0
5568 OS file reads, 1575437 OS file writes, 117187 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 1460.54 writes/s, 14.10 fsyncs/s
-------------------------------------
4

Yes. It is the same manual SST process that I mentioned to you in the other post. Use xtrabackup to take a backup of any node from cluster 1. Shut down cluster 2, all nodes. Copy the backup to cluster2-node6. Restore backup. Bootstrap node6. Connect async replication. Start node5 (this will SST from node6). Wait then start node4. All done.

5

HI @matthewb, sorry for confusing description, the above two clusters are two NEW cluster, I already installed those, now they are new with no data there.
I did the xtrabackup from Old cluster, then now, I’m not sure I can restore that backup on these two new Clusters, which cluster, which node should I run restoration, and should I turn off all before running restoration.

6

Turn off all before starting restore. Take a backup of old cluster and restore to node3. Bootstrap node3. Start node2. Wait for SST. Start node1. Wait for SST.
Take a new backup of node3 and restore to node6. Bootstrap node6. Start node5. Wait SST. Start node4. Wait SST. Now you have 2 clusters with the same data. Configure async replication between node3 and node6 using GTIDs. This allows you to change the source/replica to any node at any time.