Unable to restore a database from a full backup using xbcloud with encryption and compression

bmmalc · March 3, 2021, 3:34pm

I store full backup encrypted and compressed and now I want to retrieve only a specific database. Looking the documentation The xbcloud Binary about partial download, it’s not clear to me the files I have pass to restore a specific database from a full backup when I need to gather the files using xbcloud
I pass obviously the name of the database (that match the folder name that contains the files) and ibdata1. Do I need something else ?

so it gives this command-line

xbcloud get --parallel=10 --swift-container=db_full_20210303 ibdata1 my_database

do I need something else ?

edit: actually xbcloud does not retrieve ibdata1 and database files with the above command. it actually download nothing at all (but --verbose shows me it looks for files)
As files are compressed and encrypted during backup, they end with qp.xbcrypt (plus the file segment 0000000000000xx), does it impact the way I should provide the filename to xbcloud ?

Using --verbose I can see the HTTP requests and xbcloud is browsing files

GET /v1/AUTH_xxxxxx/full_db_backup_20210303?format=json&limit=10&marker=mysql_full_2021030302%2Fibdata1.qp.xbcrypt.00000000000000000013&prefix=mysql_full_2021030302%2F HTTP/1.1

I’m using xtrabackup 2.4.21.

Thank for clarifying that.

Best

bmmalc · March 4, 2021, 9:21am

In order to complete I’ve done a test where I send backup files unencrypted and uncompressed

xtrabackup --backup --stream=xbstream --parallel 10 | xbcloud put full --parallel 10 --swift-container="test_full_2021030401"

then I tried to download part of the full backup

xbcloud get full --parallel 10 --swift-container="test_full_2021030401" idbda1 one_database  | xbstream -xv -C /data/backups/partial --parallel=10

only the ibdata1 file get downloaded not the database files.

So now I’m a bit clueless

Marcelo_Altmann · March 4, 2021, 12:07pm

Hi @bmmalc

The way to restore files is either to pull all files from a folder, or manually specify every file you want. For example. If I want ibdata1 and a single file from a database (here named m1/a.frm) we have to use:

marcelo@marce-bld:/tmp/bkp$ /work/pxb/ins/2.4/bin/xbcloud get --storage=google  --google-bucket=operator-testing --google-access-key=x --google-secret-key=x marce_test ibdata1 m1/a.frm  |  /work/pxb/ins/2.4/bin/xbstream -xv -C ./ 
210304 08:55:52 /work/pxb/ins/2.4/bin/xbcloud: Successfully connected.
210304 08:55:53 /work/pxb/ins/2.4/bin/xbcloud: Downloading marce_test/ibdata1.00000000000000000000.
210304 08:55:53 /work/pxb/ins/2.4/bin/xbcloud: Downloading marce_test/m1/a.frm.00000000000000000000.
210304 08:55:54 /work/pxb/ins/2.4/bin/xbcloud: Download successfull marce_test/ibdata1.00000000000000000000, size 10485801
210304 08:55:54 /work/pxb/ins/2.4/bin/xbcloud: Downloading marce_test/ibdata1.00000000000000000001.
ibdata1
210304 08:55:54 /work/pxb/ins/2.4/bin/xbcloud: Download successfull marce_test/m1/a.frm.00000000000000000000, size 8598
210304 08:55:54 /work/pxb/ins/2.4/bin/xbcloud: Downloading marce_test/m1/a.frm.00000000000000000001.
m1/a.frm
210304 08:55:54 /work/pxb/ins/2.4/bin/xbcloud: Download successfull marce_test/ibdata1.00000000000000000001, size 2097193
210304 08:55:54 /work/pxb/ins/2.4/bin/xbcloud: Downloading marce_test/ibdata1.00000000000000000002.
210304 08:55:54 /work/pxb/ins/2.4/bin/xbcloud: Download successfull marce_test/m1/a.frm.00000000000000000001, size 22
210304 08:55:54 /work/pxb/ins/2.4/bin/xbcloud: Download successfull marce_test/ibdata1.00000000000000000002, size 21
210304 08:55:54 /work/pxb/ins/2.4/bin/xbcloud: Download completed.

If you want all files from a specific database, I advise you to run a single xbcloud passing that database as a parameter:

/work/pxb/ins/2.4/bin/xbcloud get --storage=google  --google-bucket=operator-testing --google-access-key=x --google-secret-key=x marce_test/m1  |  /work/pxb/ins/2.4/bin/xbstream -xv -C ./ 
210304 08:59:12 /work/pxb/ins/2.4/bin/xbcloud: Successfully connected.
210304 08:59:12 /work/pxb/ins/2.4/bin/xbcloud: Downloading marce_test/m1/a.frm.00000000000000000000.
210304 08:59:12 /work/pxb/ins/2.4/bin/xbcloud: Downloading marce_test/m1/a.ibd.00000000000000000000.
210304 08:59:12 /work/pxb/ins/2.4/bin/xbcloud: Downloading marce_test/m1/db.opt.00000000000000000000.
210304 08:59:12 /work/pxb/ins/2.4/bin/xbcloud: Download successfull marce_test/m1/a.frm.00000000000000000000, size 8598
210304 08:59:12 /work/pxb/ins/2.4/bin/xbcloud: Downloading marce_test/m1/a.frm.00000000000000000001.
m1/a.frm
210304 08:59:12 /work/pxb/ins/2.4/bin/xbcloud: Download successfull marce_test/m1/a.ibd.00000000000000000000, size 98346
210304 08:59:12 /work/pxb/ins/2.4/bin/xbcloud: Downloading marce_test/m1/a.ibd.00000000000000000001.
m1/a.ibd
210304 08:59:12 /work/pxb/ins/2.4/bin/xbcloud: Download successfull marce_test/m1/db.opt.00000000000000000000, size 108
210304 08:59:12 /work/pxb/ins/2.4/bin/xbcloud: Downloading marce_test/m1/db.opt.00000000000000000001.
m1/db.opt
210304 08:59:12 /work/pxb/ins/2.4/bin/xbcloud: Download successfull marce_test/m1/a.frm.00000000000000000001, size 22
210304 08:59:12 /work/pxb/ins/2.4/bin/xbcloud: Download successfull marce_test/m1/a.ibd.00000000000000000001, size 22
210304 08:59:12 /work/pxb/ins/2.4/bin/xbcloud: Download successfull marce_test/m1/db.opt.00000000000000000001, size 23
210304 08:59:12 /work/pxb/ins/2.4/bin/xbcloud: Download completed.

The way xbcloud works it connects, enter to the folder you specify (in this last example marce_test/m1) list all files, and if you have specified a list of file it will pull those files, if no files are specified it will download all files on that folder and its subfolders. In this last example, it downloaded all the files from m1 database.

Also, please note that you will need xtrabackup_checkpoints, xtrabackup_info and xtrabackup_logfile in order to prepare the backup.

bmmalc · March 8, 2021, 9:37am

Hi @Marcelo_Altmann

first, thanks for answering my post.

Perhaps adding the information about the necessity to get xtrabackup_checkpoints , xtrabackup_info and xtrabackup_logfile would be a nice addition to the man / documention to give more information for other users.

About your mention that I had to match filename for downloading files (hence that xbcloud doesn’t perform globbing or regexp matching) I discover that after my second post, however I’m unable to download for instance ibdata1 for a compressed and/or cyphered backup.
I’ve passed ibdata1.qp.bcrypt and I got nothing downloaded at the end, same applies for folders.

is this configuration supported ? I’m using swift storage if it matters.

bmmalc · March 8, 2021, 9:49am

so I launch a partial download this way on a cloud backup which is just compressed (so files end with qp extension)

xbcloud get all_2021030101 --swift-container=database_backup_2021030101 ibdata1.qp

I can see that xbcloud can see chunk of the file ibdata1.qp

GET /v1/AUTH_7542981824cf4bcb883cf4c4321195ae/database_backup_2021030101?format=json&limit=10&prefix=all_2021030101%2F HTTP/1.1
GET /v1/AUTH_7542981824cf4bcb883cf4c4321195ae/database_backup_2021030101?format=json&limit=10&marker=all_2021030101%2Fibdata1.qp.00000000000000000009&prefix=all_2021030101%2F HTTP/1.1
GET /v1/AUTH_7542981824cf4bcb883cf4c4321195ae/database_backup_2021030101?format=json&limit=10&marker=all_2021030101%2Fibdata1.qp.00000000000000000019&prefix=all_2021030101%2F HTTP/1.1
GET /v1/AUTH_7542981824cf4bcb883cf4c4321195ae/database_backup_2021030101?format=json&limit=10&marker=all_2021030101%2Fibdata1.qp.00000000000000000030&prefix=all_2021030101%2F HTTP/1.1
GET /v1/AUTH_7542981824cf4bcb883cf4c4321195ae/database_backup_2021030101?format=json&limit=10&marker=all_2021030101%2Fibdata1.qp.00000000000000000042&prefix=all_2021030101%2F HTTP/1.1
GET /v1/AUTH_7542981824cf4bcb883cf4c4321195ae/database_backup_2021030101?format=json&limit=10&marker=all_2021030101%2Fibdata1.qp.00000000000000000052&prefix=all_2021030101%2F HTTP/1.1
GET /v1/AUTH_7542981824cf4bcb883cf4c4321195ae/database_backup_2021030101?format=json&limit=10&marker=all_2021030101%2Fibdata1.qp.00000000000000000062&prefix=all_2021030101%2F HTTP/1.1
GET /v1/AUTH_7542981824cf4bcb883cf4c4321195ae/database_backup_2021030101?format=json&limit=10&marker=all_2021030101%2Fibdata1.qp.00000000000000000072&prefix=all_2021030101%2F HTTP/1.1
GET /v1/AUTH_7542981824cf4bcb883cf4c4321195ae/database_backup_2021030101?format=json&limit=10&marker=all_2021030101%2Fibdata1.qp.00000000000000000082&prefix=all_2021030101%2F HTTP/1.1
GET /v1/AUTH_7542981824cf4bcb883cf4c4321195ae/database_backup_2021030101?format=json&limit=10&marker=all_2021030101%2Fibdata1.qp.00000000000000000092&prefix=all_2021030101%2F HTTP/1.1
GET /v1/AUTH_7542981824cf4bcb883cf4c4321195ae/database_backup_2021030101?format=json&limit=10&marker=all_2021030101%2Fibdata1.qp.00000000000000000102&prefix=all_2021030101%2F HTTP/1.1
GET /v1/AUTH_7542981824cf4bcb883cf4c4321195ae/database_backup_2021030101?format=json&limit=10&marker=all_2021030101%2Fibdata1.qp.00000000000000000112&prefix=all_2021030101%2F HTTP/1.1
GET /v1/AUTH_7542981824cf4bcb883cf4c4321195ae/database_backup_2021030101?format=json&limit=10&marker=all_2021030101%2Fibdata1.qp.00000000000000000122&prefix=all_2021030101%2F HTTP/1.1
GET /v1/AUTH_7542981824cf4bcb883cf4c4321195ae/database_backup_2021030101?format=json&limit=10&marker=all_2021030101%2Fibdata1.qp.00000000000000000132&prefix=all_2021030101%2F HTTP/1.1
GET /v1/AUTH_7542981824cf4bcb883cf4c4321195ae/database_backup_2021030101?format=json&limit=10&marker=all_2021030101%2Fibdata1.qp.00000000000000000142&prefix=all_2021030101%2F HTTP/1.1
GET /v1/AUTH_7542981824cf4bcb883cf4c4321195ae/database_backup_2021030101?format=json&limit=10&marker=all_2021030101%2Fibdata1.qp.00000000000000000152&prefix=all_2021030101%2F HTTP/1.1
GET /v1/AUTH_7542981824cf4bcb883cf4c4321195ae/database_backup_2021030101?format=json&limit=10&marker=all_2021030101%2Fibdata1.qp.00000000000000000163&prefix=all_2021030101%2F HTTP/1.1
GET /v1/AUTH_7542981824cf4bcb883cf4c4321195ae/database_backup_2021030101?format=json&limit=10&marker=all_2021030101%2Fibdata1.qp.00000000000000000173&prefix=all_2021030101%2F HTTP/1.1
GET /v1/AUTH_7542981824cf4bcb883cf4c4321195ae/database_backup_2021030101?format=json&limit=10&marker=all_2021030101%2Fibdata1.qp.00000000000000000183&prefix=all_2021030101%2F HTTP/1.1
GET /v1/AUTH_7542981824cf4bcb883cf4c4321195ae/database_backup_2021030101?format=json&limit=10&marker=all_2021030101%2Fibdata1.qp.00000000000000000193&prefix=all_2021030101%2F HTTP/1.1
GET /v1/AUTH_7542981824cf4bcb883cf4c4321195ae/database_backup_2021030101?format=json&limit=10&marker=all_2021030101%2Fibdata1.qp.00000000000000000203&prefix=all_2021030101%2F HTTP/1.1
GET /v1/AUTH_7542981824cf4bcb883cf4c4321195ae/database_backup_2021030101?format=json&limit=10&marker=all_2021030101%2Fibdata1.qp.00000000000000000213&prefix=all_2021030101%2F HTTP/1.1
GET /v1/AUTH_7542981824cf4bcb883cf4c4321195ae/database_backup_2021030101?format=json&limit=10&marker=all_2021030101%2Fibdata1.qp.00000000000000000223&prefix=all_2021030101%2F HTTP/1.1
GET /v1/AUTH_7542981824cf4bcb883cf4c4321195ae/database_backup_2021030101?format=json&limit=10&marker=all_2021030101%2Fibdata1.qp.00000000000000000233&prefix=all_2021030101%2F HTTP/1.1

However the file is not downloaded. The same applies if I try to match a directory as you suggested previously,

Marcelo_Altmann · March 10, 2021, 11:40am

Hi @bmmalc

I can see it works fine for google/S3:

marcelo@marce-bld:/tmp/bkp$ cat /tmp/a.out
marcelo@marce-bld:/tmp/bkp$ /work/pxb/ins/2.4/bin/xbcloud get --storage=google  --google-bucket=operator-testing --google-access-key= --google-secret-key=marce_test ibdata1.qp |  /work/pxb/ins/2.4/bin/xbstream -xv -C ./ --parallel=10
210310 08:22:48 /work/pxb/ins/2.4/bin/xbcloud: Successfully connected.
210310 08:22:49 /work/pxb/ins/2.4/bin/xbcloud: Downloading marce_test/ibdata1.qp.00000000000000000000.
210310 08:22:50 /work/pxb/ins/2.4/bin/xbcloud: Download successfull marce_test/ibdata1.qp.00000000000000000000, size 201561
210310 08:22:50 /work/pxb/ins/2.4/bin/xbcloud: Downloading marce_test/ibdata1.qp.00000000000000000001.
ibdata1.qp
210310 08:22:50 /work/pxb/ins/2.4/bin/xbcloud: Download successfull marce_test/ibdata1.qp.00000000000000000001, size 24
210310 08:22:50 /work/pxb/ins/2.4/bin/xbcloud: Download completed.

However, swift uses a different implementation. I currently don’t have access to any swift container. If I sent you my public gpg key, would you be able to create a temporary access key/secret and upload a test backup so I can test ?

bmmalc · March 11, 2021, 7:27am

Sorry I cannot do that for various reason related to security
Is there some action I can do myself to provide information?

Marcelo_Altmann · March 11, 2021, 11:12am

In that case, please open a JIRA bug. The bug validation team will check that against Swift.
For now, as a workaround please download all files in order to restore your backup.

bmmalc · March 12, 2021, 6:34am

Ok I reported https://jira.percona.com/browse/PXB-2438

bmmalc · May 26, 2021, 8:09am

Fast forwarding to today, I found swift has a S3 API so now I use this.

This time I can download single files if I put their names (like xbcloud get full --s3-bucket my-bucket xtrabackup_binlog_info.qp.xbcrypt) but not a whole directory (xbcloud get full --s3-bucket my-bucket my_db) as for swift.

Is this case supposed to work as it is or is this a bug ?