Restoring one or more tables from Google storage

MySQL & MariaDB Percona XtraBackup
1

Hi,
I’m looking for a way to restore a single (or more) table(s) from a backup in GCP.
I’m trying this command :

sudo xbcloud get --storage=google --google-endpoint=storage.googleapis.com --google-access-key= --google-secret-key= --google-bucket= --parallel=10 2022-06-01_15-23-43 ibdata1 DB1/TABLE1 DB2/TABLE2 | xbstream -xv -C /mysql_data/import

The result is :

220601 16:09:44 xbcloud: Successfully connected.
220601 16:09:46 xbcloud: Download completed.

But nothing is downloaded.

BR
Johan

2

I would test this by changing your xbcloud to download first, then use xbstream.

From our documentation:

xbcloud get ... full_backup ibdata1 sakila/payment.ibd > /storage/partial/partial.xbs

xbstream -xv -C /storage/partial < /storage/partial/partial.xbs

Ensure that the xbcloud did indeed download data first. If that doesn’t work, try downloading the entire backup. If that doesn’t work, then there was a problem with your upload that you’ll need to investigate.

3

Hi Matthew,

Just running this command :
sudo xbcloud get --storage=google --google-endpoint=storage.googleapis.com --google-access-key= --google-secret-key= --google-bucket= --parallel=10 2022-06-01_15-23-43 ibdata1 DB1/TABLE1 DB2/TABLE2
gives the same result as above but no files are downloaded.

Adding > /mysql_data/import/restore.xbs to the command gives an empty file

Downloading the entire backup works without any issue

4

What about getting just a single file? ibdata for instance. Also, please add --verbose to see if there’s anything else going on.

5

Same result with just ibdata1. The result is an empty file.
Log from verbose run adde
perc.log (27.8 KB)

6

Just to clarify, this works fine?

xbcloud get --storage=google 
 --google-endpoint=http://storage.googleapis.com
 --google-access-key=xxx --google-secret-key=xxx
 --google-bucket=yyy --parallel=10 2022-06-01_15-23-43

That downloads the entire backup?

7

Yes, downloading the entire backups works as it should

8

Hi Matthew,

Any news ?

9

Hello @matthewb ,

Any news ?

10

Unfortunately, I have nothing else at this time on the issue and I have not seen any others reporting this problem. If you find you have a repeatable test case, you can open a bug report on our website.

Is there any possibility of testing against an AWS S3 endpoint? If it works from AWS, we can narrow this down to being a GCS-specific issue.

11

Hi,

Well, it’s repeatable in the way that it gets the same result every time. It says ‘Download completed’ but no files are actually downloaded.
I’m not sure i can test with AWS since we don’t really use it but i’ll file a bug report in your Jira

12

Hi @Catoman .

I’ve done some testing and I can selectively download the files from google cloud.
xbcloud selective file download works on an exact match including the file extension. So for example, this does not works:

 🐬 marcelo  📂 ~  ▶
 ╰▶ $ xbcloud get --storage=google --google-access-key=* --google-secret-key=*  --google-region=europe-west3 --google-bucket=pxb-test  marce db1/tb1001 > /dev/null
220715 10:25:14 xbcloud: Successfully connected.
220715 10:25:19 xbcloud: Download completed.

It doesn’t work because the file is named db1/tb1001.ibd. By adjusting it to have the .ibd at the end of the file is downloaded successfully:

 🐬 marcelo  📂 ~  ▶
 ╰▶ $ ls /tmp/selective/

 🐬 marcelo  📂 ~  ▶
 ╰▶ $ xbcloud get --storage=google --google-access-key=* --google-secret-key=* --google-region=europe-west3 --google-bucket=pxb-test  marce db1/tb1001.ibd | xbstream -x -C /tmp/selective/
220715 10:28:06 xbcloud: Successfully connected.
220715 10:28:10 xbcloud: Downloading marce/db1/tb1001.ibd.00000000000000000000.
220715 10:28:11 xbcloud: Download successfull marce/db1/tb1001.ibd.00000000000000000000, size 114736
220715 10:28:11 xbcloud: Downloading marce/db1/tb1001.ibd.00000000000000000001.
220715 10:28:11 xbcloud: Download successfull marce/db1/tb1001.ibd.00000000000000000001, size 28
220715 10:28:11 xbcloud: Download completed.

 🐬 marcelo  📂 ~  ▶
 ╰▶ $ ls -lah /tmp/selective/
total 76K
drwxrwxr-x  3 marcelo marcelo 4,0K jul 15 10:28 .
drwxrwxrwt 31 root    root     64K jul 15 10:27 ..
drwxr-x---  2 marcelo marcelo 4,0K jul 15 10:28 db1

 🐬 marcelo  📂 ~  ▶
 ╰▶ $ ls -lah /tmp/selective/db1/
total 120K
drwxr-x--- 2 marcelo marcelo 4,0K jul 15 10:28 .
drwxrwxr-x 3 marcelo marcelo 4,0K jul 15 10:28 ..
-rw-r----- 1 marcelo marcelo 112K jul 15 10:28 tb1001.ibd

Can you please validate you are including an exact match to the files you are trying to download and let us know if it works?

13

Hi Marcelo,

Sounds interesting. I’m just back from vacation so i’ll try this as soon as i have a chance and have
located the script i used for the tests.

BR
Johan

14

Hi Marcelo,

I still get the same “error”, that is after two seconds it says “Download successful” but no files.
Two questions here :

  1. In your command above, what is “marce”
  2. Under my bucket i have the databases saved under a folder named after a date timestamp, then the databases and files
    gs://bucket_name/2022-08-09_06-18-03/database_name/tables
    The tables are compressed during backup and the files end like this : table.ibd.qp.00000000000000000000
    Could that cause an issue ?

BR
Johan

15

Hi @Catoman

  1. In your command above, what is “marce”

marce is the folder where I stored the backup. In your case it should be 2022-08-09_06-18-03.

I have not tested with compression, which results in .qp being appended before the chunk number.
Can you test two variants of the command:

  1. (passing database_name/table.ibd) xbcloud get --storage=google --google-access-key=* --google-secret-key=* --google-region=europe-west3 --google-bucket=pxb-test 2022-08-09_06-18-03 database_name/table.ibd | xbstream -x -C /tmp/selective/

  2. (passing database_name/table.ibd.qp) xbcloud get --storage=google --google-access-key=* --google-secret-key=* --google-region=europe-west3 --google-bucket=pxb-test 2022-08-09_06-18-03 database_name/table.ibd.qp | xbstream -x -C /tmp/selective/

In case it doesn’t work. Please send the full command back (redact the access/secret key)

16

Hi,

Adding .qp to the table name did the trick (why didn’t i think of that ?)

BR
Johan