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 |
[mysqld]<br>innodb_checksum_algorithm=crc32<br>innodb_log_checksum_algorithm=strict_crc32<br>innodb_data_file_path=ibdata1:12M:autoextend<br>innodb_log_files_in_group=2<br>innodb_log_file_size=1073741824<br>innodb_fast_checksum=false<br>innodb_page_size=16384<br>innodb_log_block_size=512<br>innodb_undo_directory=./<br>innodb_undo_tablespaces=0<br>server_id=0<br>redo_log_version=1<br>plugin_load=keyring_file.so<br>server_uuid=00005726-0000-0000-0000-000000005726<br>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 |
~$ xtrabackup --prepare --keyring-file-data=/backup/mysql/keyring --target-dir=/backup/mysql <br>xtrabackup: [Warning] World-writable config file '/backup/mysql/backup-my.cnf' is ignored.<br>xtrabackup: recognized server arguments: <br>xtrabackup: [Warning] World-writable config file '/backup/mysql/backup-my.cnf' is ignored.<br>xtrabackup: recognized client arguments: --prepare=1 --target-dir=/backup/mysql <br>xtrabackup version 2.4.14 based on MySQL server 5.7.19 Linux (x86_64) (revision id: ef675d4)<br>xtrabackup: cd to /backup/mysql/<br>xtrabackup: This target seems to be not prepared yet.<br>InnoDB: Number of pools: 1<br>xtrabackup: xtrabackup_logfile detected: size=215089152, start_lsn=(3094928949)<br>xtrabackup: using the following InnoDB configuration for recovery:<br>xtrabackup: innodb_data_home_dir = .<br>xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend<br>xtrabackup: innodb_log_group_home_dir = .<br>xtrabackup: innodb_log_files_in_group = 1<br>xtrabackup: innodb_log_file_size = 215089152<br>xtrabackup: [Warning] World-writable config file './backup-my.cnf' is ignored.<br>xtrabackup: using the following InnoDB configuration for recovery:<br>xtrabackup: innodb_data_home_dir = .<br>xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend<br>xtrabackup: innodb_log_group_home_dir = .<br>xtrabackup: innodb_log_files_in_group = 1<br>xtrabackup: innodb_log_file_size = 215089152<br>xtrabackup: Starting InnoDB instance for recovery.<br>xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)<br>InnoDB: PUNCH HOLE support available<br>InnoDB: Mutexes and rw_locks use GCC atomic builtins<br>InnoDB: Uses event mutexes<br>InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier<br>InnoDB: Compressed tables use zlib 1.2.8<br>InnoDB: Number of pools: 1<br>InnoDB: Using CPU crc32 instructions<br>InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M<br>InnoDB: Completed initialization of buffer pool<br>InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().<br>InnoDB: Highest supported file format is Barracuda.<br>InnoDB: Encryption can't find master key, please check the keyring plugin is loaded.<br>InnoDB: Encryption information in datafile: ./sbtest/sbtest2.ibd can't be decrypted.<br>InnoDB: Encryption can't find master key, please check the keyring plugin is loaded.<br>InnoDB: Encryption information in datafile: ./sbtest/sbtest1.ibd can't be decrypted.<br>InnoDB: Encryption can't find master key, please check the keyring plugin is loaded.<br>InnoDB: Encryption information in datafile: ./sbtest/sbtest4.ibd can't be decrypted.<br>InnoDB: Encryption can't find master key, please check the keyring plugin is loaded.<br>InnoDB: Encryption information in datafile: ./sbtest/sbtest3.ibd can't be decrypted.<br>InnoDB: Encryption can't find master key, please check the keyring plugin is loaded.<br>InnoDB: Encryption information in datafile: ./sbtest/sbtest5.ibd can't be decrypted.<br>InnoDB: Log scan progressed past the checkpoint lsn 3094928949<br>** redacted **<br>InnoDB: Doing recovery: scanned up to log sequence number 3097681408 (1%)<br>InnoDB: Doing recovery: scanned up to log sequence number 3102924288 (4%)<br>InnoDB: Doing recovery: scanned up to log sequence number 3108167168 (6%)<br>InnoDB: Doing recovery: scanned up to log sequence number 3113410048 (9%)<br>InnoDB: Doing recovery: scanned up to log sequence number 3118652928 (12%)<br>InnoDB: Doing recovery: scanned up to log sequence number 3123895808 (15%)<br>InnoDB: Doing recovery: scanned up to log sequence number 3129138688 (17%)<br>InnoDB: Doing recovery: scanned up to log sequence number 3134381568 (20%)<br>InnoDB: Starting an apply batch of log records to the database...<br>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 <br>** redacted **<br>InnoDB: Doing recovery: scanned up to log sequence number 3265453568 (89%)<br>InnoDB: Doing recovery: scanned up to log sequence number 3270696448 (91%)<br>InnoDB: Doing recovery: scanned up to log sequence number 3275939328 (94%)<br>InnoDB: Doing recovery: scanned up to log sequence number 3281182208 (97%)<br>InnoDB: Doing recovery: scanned up to log sequence number 3286158358 (100%)<br>InnoDB: Starting an apply batch of log records to the database...<br>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 <br>InnoDB: Apply batch completed<br>InnoDB: xtrabackup: Last MySQL binlog file position 568369058, file name mysql-bin.000004<br>InnoDB: Encryption can't find master key, please check the keyring plugin is loaded.<br>InnoDB: Encryption information in datafile: ./sbtest/sbtest1.ibd can't be decrypted.<br>InnoDB: Removing missing table `sbtest/sbtest1` from InnoDB data dictionary.<br>InnoDB: Encryption can't find master key, please check the keyring plugin is loaded.<br>InnoDB: Encryption information in datafile: ./sbtest/sbtest2.ibd can't be decrypted.<br>InnoDB: Removing missing table `sbtest/sbtest2` from InnoDB data dictionary.<br>InnoDB: Encryption can't find master key, please check the keyring plugin is loaded.<br>InnoDB: Encryption information in datafile: ./sbtest/sbtest3.ibd can't be decrypted.<br>InnoDB: Removing missing table `sbtest/sbtest3` from InnoDB data dictionary.<br>InnoDB: Encryption can't find master key, please check the keyring plugin is loaded.<br>InnoDB: Encryption information in datafile: ./sbtest/sbtest4.ibd can't be decrypted.<br>InnoDB: Removing missing table `sbtest/sbtest4` from InnoDB data dictionary.<br>InnoDB: Encryption can't find master key, please check the keyring plugin is loaded.<br>InnoDB: Encryption information in datafile: ./sbtest/sbtest5.ibd can't be decrypted.<br>InnoDB: Removing missing table `sbtest/sbtest5` from InnoDB data dictionary.<br>InnoDB: Creating shared tablespace for temporary tables<br>InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...<br>InnoDB: File './ibtmp1' size is now 12 MB.<br>InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.<br>InnoDB: 32 non-redo rollback segment(s) are active.<br>InnoDB: page_cleaner: 1000ms intended loop took 6627ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)<br>InnoDB: 5.7.19 started; log sequence number 3286158358<br>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 |
~$ xtrabackup --prepare --keyring-file-data=/backup/mysql/keyring --target-dir=/backup/mysql <br>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 <br>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 <br>xtrabackup version 2.4.14 based on MySQL server 5.7.19 Linux (x86_64) (revision id: ef675d4)<br>xtrabackup: cd to /backup/mysql/<br>xtrabackup: This target seems to be already prepared.<br>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.
Resources
RELATED POSTS
