i want to backup one table:
xtrabackup --tables='test.transactions' --backup -u devxxxxxx -p --target-dir=/home/xtrabackup4test
But extrabackup starts backup all tables like switching-on the patterns. Tables: transactions_t1, transactions_t2, transactions_t3… almost 15 tables. When I need only one master table. The rest tables are very big, because they are child tables.
I tried:
--tables=test.transactions
--tables='test.transactions'
--tables="test.transactions"
--tables="^test[.]transactions"
Nothing helps.
HOW to backup only one table?
xtrabackup version 8.0.35-30 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 6beb4b49)
Hi @Modestas_Mockus
Thanks for the details.
I have tested with the same approach that you have, and I am sure it takes all other tables’ backups.
[root@default ~]# xtrabackup --backup -uuns -p123 --target-dir=/tmp/backup4/ --tables="^test[.]transactions"
...
2024-06-03T09:25:28.739650-00:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./ibdata1 to /tmp/backup4/ibdata1
2024-06-03T09:25:28.781977-00:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./ibdata1 to /tmp/backup4/ibdata1
2024-06-03T09:25:28.782350-00:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./test/transactions.ibd to /tmp/backup4/test/transactions.ibd
2024-06-03T09:25:28.782481-00:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./test/transactions.ibd to /tmp/backup4/test/transactions.ibd
2024-06-03T09:25:28.782555-00:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./test/transactions_t1.ibd to /tmp/backup4/test/transactions_t1.ibd
2024-06-03T09:25:28.782640-00:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./test/transactions_t1.ibd to /tmp/backup4/test/transactions_t1.ibd
2024-06-03T09:25:28.782684-00:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./test/transactions_t2.ibd to /tmp/backup4/test/transactions_t2.ibd
2024-06-03T09:25:28.782766-00:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./test/transactions_t2.ibd to /tmp/backup4/test/transactions_t2.ibd
2024-06-03T09:25:28.782813-00:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./test/transactions_t3.ibd to /tmp/backup4/test/transactions_t3.ibd
2024-06-03T09:25:28.782896-00:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./test/transactions_t3.ibd to /tmp/backup4/test/transactions_t3.ibd
It’s related to the regex that you were used.
You’re looking for this option --tables="^test[.]transactions$"
This pattern ^test[.]transactions$ will match only the test.transactions table and exclude any other tables that start with transactions (like transactions_t1 , transactions_t2 , etc.).
[root@default ~]# xtrabackup --backup -uuns -p123 --target-dir=/tmp/backup6 --tables="^test[.]transactions$"
...
2024-06-03T09:29:01.201606-00:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./ibdata1 to /tmp/backup6/ibdata1
2024-06-03T09:29:01.246563-00:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./ibdata1 to /tmp/backup6/ibdata1
2024-06-03T09:29:01.246887-00:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./test/transactions.ibd to /tmp/backup6/test/transactions.ibd
2024-06-03T09:29:01.247057-00:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./test/transactions.ibd to /tmp/backup6/test/transactions.ibd
2024-06-03T09:29:01.247131-00:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./mysql.ibd to /tmp/backup6/mysql.ibd
2024-06-03T09:29:01.326182-00:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./mysql.ibd to /tmp/backup6/mysql.ibd