Xtrabackup - Issue with encrypted volume

Hello Team,

We’re currently investigating the Xtrabackup tool for backing up our MySQL database. During our testing of Xtrabackup, we came across an error related to checksums. Interestingly this error only occurred in the application running on an encrypted AWS EBS volume, while we did not encounter the same checksum error in the application running on an unencrypted AWS EBS volume. Is there a specific reason Xtrabackup behaves differently on encrypted and unencrypted volumes.

[01] Copying ./ibdata1 to /var/tmp/backupxtra/ibdata1

[01] xtrabackup: Database page corruption detected at page 192, retrying...

[01] xtrabackup: Database page corruption detected at page 192, retrying...

[01] xtrabackup: Database page corruption detected at page 192, retrying...

[01] xtrabackup: Database page corruption detected at page 192, retrying...

230704 09:24:57 >> log scanned up to (24491496343)

[01] xtrabackup: Database page corruption detected at page 192, retrying...

[01] xtrabackup: Database page corruption detected at page 192, retrying...

[01] xtrabackup: Database page corruption detected at page 192, retrying...

[01] xtrabackup: Database page corruption detected at page 192, retrying...

[01] xtrabackup: Database page corruption detected at page 192, retrying...

[01] xtrabackup: Error: failed to read page after 10 retries. File ./ibdata1 seems to be corrupted.

[01] xtrabackup: Error: xtrabackup_copy_datafile() failed.

[01] xtrabackup: Error: failed to copy datafile.

230704 09:24:58 >> log scanned up to (24491496343)
mysql --version
mysql  Ver 8.0.31-23 for Linux on x86_64 (Percona Server (GPL), Release '23', Revision '71449379')
xtrabackup --version
xtrabackup version 8.0.31-24 based on MySQL server 8.0.31 Linux (x86_64) (revision id: f0754edb)

Hi @jegana,

EBS encryption is storage encryption, so it should be transparent. ie at process level, it doesn’t know that the data to be stored will be encrypted. It is encrypted at volume level.

That said, the encryption key access and privileges depend on the OS users (according to EBS documentation).

Mysql server is typically run as ‘mysql’ user process, so the access key might have been granted only to ‘mysql’ user.

Can you please verify the following things?

  1. Check if MySQL server is run as ‘mysql’ OS user?
  2. what is the OS user used for xtrabackup?
  3. can you switch to ‘mysql’ user and run xtrabackup?

Please let us know your observations.

@satya.bodapati

  1. Check if MySQL server is run as ‘mysql’ OS user?
    The mysql process is running as mysql user
  2. what is the OS user used for xtrabackup?
    Running Xtrabackup from root user
  3. can you switch to ‘mysql’ user and run xtrabackup?
    we don’t have shell access for the mysql user.

I think the best option is to provide KMS access to the root user.
See ’ Permissions for users’ at Amazon EBS encryption - Amazon Elastic Compute Cloud

Please check with AWS support if necessary.

Adding to what @jegana already mentioned.
For the same databases (just scaffolded with few tables) on the unencrypted volume, it works fine.
@satya.bodapati I agree it i should be transparent to user but it is not. Just FYI, mysqldump works fine but percona xtrabackup doesn’t. Have anyone from your internal team has tried this before and verified that it works?

Just FYI, mysqldump works fine but percona xtrabackup doesn’t.

mysqldump connects to mysql and run SELECT * to get access to the data, while xtrabackup physically accesses the files on disk.

Have anyone from your internal team has tried this before and verified that it works?

Yes, this has been tested and works:

root@ip-172-31-27-21:~# xtrabackup --backup --target-dir=/var/tmp/backupxtra/
2023-10-17T09:24:28.525107-00:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/var/lib/mysql
2023-10-17T09:24:28.525487-00:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --backup=1 --target-dir=/var/tmp/backupxtra/
xtrabackup version 8.0.34-29 based on MySQL server 8.0.34 Linux (x86_64) (revision id: 5ba706ee)
231017 09:24:28  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' (using password: NO).
231017 09:24:28  version_check Connected to MySQL server
231017 09:24:28  version_check Executing a version check against the server...
231017 09:24:28  version_check Done.
2023-10-17T09:24:28.603054-00:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: not set, password: not set, port: not set, socket: not set
2023-10-17T09:24:28.607792-00:00 0 [Note] [MY-011825] [Xtrabackup] Using server version 8.0.34
2023-10-17T09:24:28.610477-00:00 0 [Note] [MY-011825] [Xtrabackup] Executing LOCK INSTANCE FOR BACKUP ...
2023-10-17T09:24:28.611090-00:00 0 [Note] [MY-011825] [Xtrabackup] uses posix_fadvise().
2023-10-17T09:24:28.611119-00:00 0 [Note] [MY-011825] [Xtrabackup] cd to /var/lib/mysql
2023-10-17T09:24:28.611133-00:00 0 [Note] [MY-011825] [Xtrabackup] open files limit requested 0, set to 1024
2023-10-17T09:24:28.611501-00:00 0 [Note] [MY-011825] [Xtrabackup] using the following InnoDB configuration:
2023-10-17T09:24:28.611522-00:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_home_dir = .
2023-10-17T09:24:28.611530-00:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_file_path = ibdata1:12M:autoextend
2023-10-17T09:24:28.611559-00:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_group_home_dir = ./
2023-10-17T09:24:28.611569-00:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_files_in_group = 2
2023-10-17T09:24:28.611579-00:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_file_size = 50331648
2023-10-17T09:24:28.612981-00:00 0 [Note] [MY-011825] [Xtrabackup] inititialize_service_handles suceeded
2023-10-17T09:24:28.745716-00:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: not set, password: not set, port: not set, socket: not set
2023-10-17T09:24:28.751461-00:00 0 [Note] [MY-011825] [Xtrabackup] Redo Log Archiving is not set up.
2023-10-17T09:24:28.860405-00:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (19485666)
2023-10-17T09:24:28.862540-00:00 0 [Note] [MY-012953] [InnoDB] Disabling background ibuf IO read threads.
2023-10-17T09:24:29.108099-00:00 0 [Note] [MY-011825] [Xtrabackup] Generating a list of tablespaces
2023-10-17T09:24:29.108442-00:00 0 [Note] [MY-012204] [InnoDB] Scanning './'
2023-10-17T09:24:29.111717-00:00 0 [Note] [MY-012208] [InnoDB] Completed space ID check of 2 files.
2023-10-17T09:24:29.115023-00:00 0 [Warning] [MY-012091] [InnoDB] Allocated tablespace ID 1 for sys/sys_config, old maximum was 0
2023-10-17T09:24:29.115319-00:00 0 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_001'.
2023-10-17T09:24:29.117001-00:00 0 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_002'.
2023-10-17T09:24:29.120761-00:00 0 [Note] [MY-012910] [InnoDB] Opened 2 existing undo tablespaces.
2023-10-17T09:24:29.125890-00:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./ibdata1 to /var/tmp/backupxtra/ibdata1
2023-10-17T09:24:29.227361-00:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./ibdata1 to /var/tmp/backupxtra/ibdata1
... 
2023-10-17T09:24:31.891589-00:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

As stated before, this is transparent for the application. You should properly configure your KMS key to give access to the user running backup.