It’s bad practice to provide world-writable access to critical files in Linux, though we’ve seen time and time again that this is done to conveniently share files with other users, applications, or services. But with Xtrabackup, preparing backups could go wrong if the backup configuration has world-writable file permissions.
Say you performed a backup on a MySQL instance configured with data-at-rest encryption using the keyring plugin. On the backup directory, the generated backup-my.cnf contains these instructions to load this plugin that will be used by Xtrabackup while preparing the backup:
backup-my.cnf
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | [mysqld] innodb_checksum_algorithm=crc32 innodb_log_checksum_algorithm=strict_crc32 innodb_data_file_path=ibdata1:12M:autoextend innodb_log_files_in_group=2 innodb_log_file_size=1073741824 innodb_fast_checksum=false innodb_page_size=16384 innodb_log_block_size=512 innodb_undo_directory=./ innodb_undo_tablespaces=0 server_id=0 redo_log_version=1 plugin_load=keyring_file.so server_uuid=00005726-0000-0000-0000-000000005726 master_key_id=1 |
Perhaps you wanted to share the backup with another user, but made a mistake of making the directory and its contents world-writable: chmod -R 777 /backup/mysql
When that user prepares the backup, the corresponding output will show that Xtrabackup ignored reading backup-my.cnf and so it doesn’t know that it has to load the keyring plugin to decrypt the .ibd files:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 | ~$ xtrabackup --prepare --keyring-file-data=/backup/mysql/keyring --target-dir=/backup/mysql xtrabackup: [Warning] World-writable config file '/backup/mysql/backup-my.cnf' is ignored. xtrabackup: recognized server arguments: xtrabackup: [Warning] World-writable config file '/backup/mysql/backup-my.cnf' is ignored. xtrabackup: recognized client arguments: --prepare=1 --target-dir=/backup/mysql xtrabackup version 2.4.14 based on MySQL server 5.7.19 Linux (x86_64) (revision id: ef675d4) xtrabackup: cd to /backup/mysql/ xtrabackup: This target seems to be not prepared yet. InnoDB: Number of pools: 1 xtrabackup: xtrabackup_logfile detected: size=215089152, start_lsn=(3094928949) xtrabackup: using the following InnoDB configuration for recovery: xtrabackup: innodb_data_home_dir = . xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend xtrabackup: innodb_log_group_home_dir = . xtrabackup: innodb_log_files_in_group = 1 xtrabackup: innodb_log_file_size = 215089152 xtrabackup: [Warning] World-writable config file './backup-my.cnf' is ignored. xtrabackup: using the following InnoDB configuration for recovery: xtrabackup: innodb_data_home_dir = . xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend xtrabackup: innodb_log_group_home_dir = . xtrabackup: innodb_log_files_in_group = 1 xtrabackup: innodb_log_file_size = 215089152 xtrabackup: Starting InnoDB instance for recovery. xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter) InnoDB: PUNCH HOLE support available InnoDB: Mutexes and rw_locks use GCC atomic builtins InnoDB: Uses event mutexes InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier InnoDB: Compressed tables use zlib 1.2.8 InnoDB: Number of pools: 1 InnoDB: Using CPU crc32 instructions InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M InnoDB: Completed initialization of buffer pool InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority(). InnoDB: Highest supported file format is Barracuda. InnoDB: Encryption can't find master key, please check the keyring plugin is loaded. InnoDB: Encryption information in datafile: ./sbtest/sbtest2.ibd can't be decrypted. InnoDB: Encryption can't find master key, please check the keyring plugin is loaded. InnoDB: Encryption information in datafile: ./sbtest/sbtest1.ibd can't be decrypted. InnoDB: Encryption can't find master key, please check the keyring plugin is loaded. InnoDB: Encryption information in datafile: ./sbtest/sbtest4.ibd can't be decrypted. InnoDB: Encryption can't find master key, please check the keyring plugin is loaded. InnoDB: Encryption information in datafile: ./sbtest/sbtest3.ibd can't be decrypted. InnoDB: Encryption can't find master key, please check the keyring plugin is loaded. InnoDB: Encryption information in datafile: ./sbtest/sbtest5.ibd can't be decrypted. InnoDB: Log scan progressed past the checkpoint lsn 3094928949 ** redacted ** InnoDB: Doing recovery: scanned up to log sequence number 3097681408 (1%) InnoDB: Doing recovery: scanned up to log sequence number 3102924288 (4%) InnoDB: Doing recovery: scanned up to log sequence number 3108167168 (6%) InnoDB: Doing recovery: scanned up to log sequence number 3113410048 (9%) InnoDB: Doing recovery: scanned up to log sequence number 3118652928 (12%) InnoDB: Doing recovery: scanned up to log sequence number 3123895808 (15%) InnoDB: Doing recovery: scanned up to log sequence number 3129138688 (17%) InnoDB: Doing recovery: scanned up to log sequence number 3134381568 (20%) InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percent: 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 ** redacted ** InnoDB: Doing recovery: scanned up to log sequence number 3265453568 (89%) InnoDB: Doing recovery: scanned up to log sequence number 3270696448 (91%) InnoDB: Doing recovery: scanned up to log sequence number 3275939328 (94%) InnoDB: Doing recovery: scanned up to log sequence number 3281182208 (97%) InnoDB: Doing recovery: scanned up to log sequence number 3286158358 (100%) InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percent: 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: xtrabackup: Last MySQL binlog file position 568369058, file name mysql-bin.000004 InnoDB: Encryption can't find master key, please check the keyring plugin is loaded. InnoDB: Encryption information in datafile: ./sbtest/sbtest1.ibd can't be decrypted. InnoDB: Removing missing table `sbtest/sbtest1` from InnoDB data dictionary. InnoDB: Encryption can't find master key, please check the keyring plugin is loaded. InnoDB: Encryption information in datafile: ./sbtest/sbtest2.ibd can't be decrypted. InnoDB: Removing missing table `sbtest/sbtest2` from InnoDB data dictionary. InnoDB: Encryption can't find master key, please check the keyring plugin is loaded. InnoDB: Encryption information in datafile: ./sbtest/sbtest3.ibd can't be decrypted. InnoDB: Removing missing table `sbtest/sbtest3` from InnoDB data dictionary. InnoDB: Encryption can't find master key, please check the keyring plugin is loaded. InnoDB: Encryption information in datafile: ./sbtest/sbtest4.ibd can't be decrypted. InnoDB: Removing missing table `sbtest/sbtest4` from InnoDB data dictionary. InnoDB: Encryption can't find master key, please check the keyring plugin is loaded. InnoDB: Encryption information in datafile: ./sbtest/sbtest5.ibd can't be decrypted. InnoDB: Removing missing table `sbtest/sbtest5` from InnoDB data dictionary. InnoDB: Creating shared tablespace for temporary tables InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... InnoDB: File './ibtmp1' size is now 12 MB. InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active. InnoDB: 32 non-redo rollback segment(s) are active. InnoDB: page_cleaner: 1000ms intended loop took 6627ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.) InnoDB: 5.7.19 started; log sequence number 3286158358 InnoDB: xtrabackup: Last MySQL binlog file position 568369058, file name mysql-bin.000004 |
Even if you fix the permissions on backup-my.cnf, if you try to prepare the same backup again, Xtrabackup will warn you that it has already prepared the backup.
1 2 3 4 5 6 7 | ~$ xtrabackup --prepare --keyring-file-data=/backup/mysql/keyring --target-dir=/backup/mysql xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=1073741824 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=0 --redo-log-version=1 xtrabackup: recognized client arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=1073741824 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=0 --redo-log-version=1 --prepare=1 --target-dir=/backup/mysql xtrabackup version 2.4.14 based on MySQL server 5.7.19 Linux (x86_64) (revision id: ef675d4) xtrabackup: cd to /backup/mysql/ xtrabackup: This target seems to be already prepared. InnoDB: Number of pools: 1 |
This means that changes made while the backup was taking place will not be applied and what you have restored is an inconsistent, potentially corrupt backup. You need to perform a full backup again and make sure that you do not place world/other writable permissions on the backup this around so that you will not face the same issue.
This looks like a (usability) bug to me. The restore process should fail instead of restoring a potentially corrupted backup. This is especially dangerous since users restoring a backup may be under a higher level of stress so mistakes can be made and it’s very easy to overlook the errors in the output you posted.