To use Percona XtraBackup with keyring_vault plugin enabled you need to take some special measures to secure a working backup. This post addresses how to backup Percona Server for MySQL with keyring_vault plugin enabled. We also run through the steps needed to restore the backup from the master to a slave.
This is the second of a two-part series on setting up Hashicorp Vault with Percona Server for MySQL with the keyring_vault plugin. First part is Using the keyring_vault plugin with Percona Server for MySQL 5.7.
First you need to install the latest Percona XtraBackup 2.4 package, in this tutorial I used this version:
|
1 |
[root@mysql1 ~]# xtrabackup --version<br>xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --log_bin=mysqld-bin --server-id=1<br>xtrabackup version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c) |
Create a transition key using any method you prefer. This transition key will be used by Percona XtraBackup to encrypt keys of the files being backed up. Make sure to keep the transition key and not lose it or else the backup will be unrecoverable.
|
1 |
[root@mysql1 ~]# openssl rand -base64 24<br>NSu7kfUgcTTIY2ym7Qu6jnYOotOuMIeT |
You can store the transition-key in Vault and retrieve it later:
|
1 |
[root@mysql1 ~]# # Store the transition-key to the Vault server<br>[root@mysql1 ~]# curl -H "Content-Type: application/json" -H "X-Vault-Token: be515093-b1a8-c799-b237-8e04ea90ad7a" --cacert "/etc/vault_ca/vault.pem" -X PUT -d '{"value": "NSu7kfUgcTTIY2ym7Qu6jnYOotOuMIeT"}' "https://192.168.0.114:8200/v1/secret/dc1/master/transition_key"<br>[root@mysql1 ~]# # Retrieve the transition-key from the Vault server<br>[root@mysql1 ~]# curl -s -H "X-Vault-Token: be515093-b1a8-c799-b237-8e04ea90ad7a" --cacert "/etc/vault_ca/vault.pem" -X GET "https://192.168.0.114:8200/v1/secret/dc1/master/transition_key" | jq .data.value<br>"NSu7kfUgcTTIY2ym7Qu6jnYOotOuMIeT"<br>[root@mysql1 ~]# # Delete the transition-key from the Vault server<br>[root@mysql1 ~]# curl -H "Content-Type: application/json" -H "X-Vault-Token: be515093-b1a8-c799-b237-8e04ea90ad7a" --cacert "/etc/vault_ca/vault.pem" -X DELETE "https://192.168.0.114:8200/v1/secret/dc1/master/transition_key" |
We will stream the backup to the slave server using netcat, first run this on the slave:
|
1 |
[root@mysql2 ~]# ncat -l 9999 | cat - > backup.xbstream |
Then on the master I used --stream=xbstream since it fails with --stream=tar reported here (PXB-1571). Run the xtrabackup command like this:
|
1 |
[root@mysql1 ~]# xtrabackup --stream=xbstream --backup --target-dir=backup/ --transition-key=NSu7kfUgcTTIY2ym7Qu6jnYOotOuMIeT | nc 192.168.0.117 9999<br>xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --log_bin=mysqld-bin --server-id=1 --transition-key=*<br>xtrabackup: recognized client arguments: --datadir=/var/lib/mysql --log_bin=mysqld-bin --server-id=1 --transition-key=* --user=root --stream=xbstream --backup=1 --target-dir=backup/<br>180715 01:28:56 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root' (using password: NO).<br>180715 01:28:56 version_check Connected to MySQL server<br>180715 01:28:56 version_check Executing a version check against the server...<br>180715 01:28:56 version_check Done.<br>180715 01:28:56 Connecting to MySQL server host: localhost, user: root, password: not set, port: not set, socket: not set<br>Using server version 5.7.22-22-log<br>xtrabackup version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c)<br>xtrabackup: uses posix_fadvise().<br>xtrabackup: cd to /var/lib/mysql<br>xtrabackup: open files limit requested 0, set to 65536<br>xtrabackup: using the following InnoDB configuration:<br>xtrabackup: innodb_data_home_dir = .<br>xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend<br>xtrabackup: innodb_log_group_home_dir = ./<br>xtrabackup: innodb_log_files_in_group = 2<br>xtrabackup: innodb_log_file_size = 50331648<br>InnoDB: Number of pools: 1<br>180715 01:28:56 Added plugin 'keyring_vault.so' to load list.<br>180715 01:28:56 >> log scanned up to (2616858)<br>xtrabackup: Generating a list of tablespaces<br>InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0<br>...<br>180715 01:28:58 Finished backing up non-InnoDB tables and files<br>180715 01:28:58 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...<br>xtrabackup: The latest check point (for incremental): '2616849'<br>xtrabackup: Stopping log copying thread.<br>.180715 01:28:58 >> log scanned up to (2616865)<br><br>180715 01:28:58 Executing UNLOCK TABLES<br>180715 01:28:58 All tables unlocked<br>180715 01:28:58 [00] Streaming ib_buffer_pool to <br>180715 01:28:58 [00] ...done<br>180715 01:28:58 Backup created in directory '/root/backup/'<br>180715 01:28:58 [00] Streaming <br>180715 01:28:58 [00] ...done<br>180715 01:28:58 [00] Streaming <br>180715 01:28:58 [00] ...done<br>180715 01:28:58 Saving xtrabackup_keys.<br>xtrabackup: Transaction log of lsn (2616849) to (2616865) was copied.<br>Shutting down plugin 'keyring_vault'<br>180715 01:28:58 completed OK! |
Extract the backup to a temporary location:
|
1 |
[root@mysql2 backup]# xbstream -x < ../backup.xbstream |
And then prepare it with the following command. Notice that we are still using the same transition key we used when backing up the database in the master server.
|
1 |
[root@mysql2 ~]# xtrabackup --prepare --target-dir=backup/ --transition-key=NSu7kfUgcTTIY2ym7Qu6jnYOotOuMIeT<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=50331648 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=1 --transition-key=*<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=50331648 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=1 --transition-key=* --prepare=1 --target-dir=backup/<br>xtrabackup version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c)<br>xtrabackup: cd to /root/backup/<br>xtrabackup: This target seems to be not prepared yet.<br>...<br>xtrabackup: starting shutdown with innodb_fast_shutdown = 1<br>InnoDB: FTS optimize thread exiting.<br>InnoDB: Starting shutdown...<br>InnoDB: Shutdown completed; log sequence number 2617384<br>180715 01:31:10 completed OK! |
Create the keyring_vault.conf file with the following contents:
|
1 |
[root@mysql2 ~]# cat /var/lib/mysql-keyring/keyring_vault.conf<br>vault_url = https://192.168.0.114:8200<br>secret_mount_point = secret/dc1/slave<br>token = be515093-b1a8-c799-b237-8e04ea90ad7a<br>vault_ca = /etc/vault_ca/vault.pem |
Notice that it uses the same token as the master server but has a different secret_mount_point. The same CA certificate will be used across all servers connecting to this Vault server.
Next use the --copy-back option to copy the files from the temporary backup location to the mysql data directory on the slave. Observe that during this phase XtraBackup generates a new master key, stores it in the Vault server and re-encrypts tablespace headers using this key.
|
1 |
[root@mysql2 ~]# xtrabackup --copy-back --target-dir=backup/ --transition-key=NSu7kfUgcTTIY2ym7Qu6jnYOotOuMIeT --generate-new-master-key --keyring-vault-config=/var/lib/mysql-keyring/keyring_vault.conf<br>xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --log_bin=mysqld-bin --server-id=2 --transition-key=* --generate-new-master-key=1<br>xtrabackup: recognized client arguments: --datadir=/var/lib/mysql --log_bin=mysqld-bin --server-id=2 --transition-key=* --generate-new-master-key=1 --copy-back=1 --target-dir=backup/<br>xtrabackup version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c)<br>180715 01:32:28 Loading xtrabackup_keys.<br>180715 01:32:28 Loading xtrabackup_keys.<br>180715 01:32:29 Generated new master key with ID 'be1ba51c-87c0-11e8-ac1c-00163e79c097-2'.<br>...<br>180715 01:32:29 [01] Encrypting /var/lib/mysql/mysql/plugin.ibd tablespace header with new master key.<br>180715 01:32:29 [01] Copying ./mysql/servers.ibd to /var/lib/mysql/mysql/servers.ibd<br>180715 01:32:29 [01] ...done<br>180715 01:32:29 [01] Encrypting /var/lib/mysql/mysql/servers.ibd tablespace header with new master key.<br>180715 01:32:29 [01] Copying ./mysql/help_topic.ibd to /var/lib/mysql/mysql/help_topic.ibd<br>180715 01:32:29 [01] ...done<br>180715 01:32:29 [01] Encrypting /var/lib/mysql/mysql/help_topic.ibd tablespace header with new master key.<br>180715 01:32:29 [01] Copying ./mysql/help_category.ibd to /var/lib/mysql/mysql/help_category.ibd<br>180715 01:32:29 [01] ...done<br>180715 01:32:29 [01] Encrypting /var/lib/mysql/mysql/help_category.ibd tablespace header with new master key.<br>180715 01:32:29 [01] Copying ./mysql/help_relation.ibd to /var/lib/mysql/mysql/help_relation.ibd<br>180715 01:32:29 [01] ...done<br>...<br>180715 01:32:30 [01] Encrypting /var/lib/mysql/encryptedschema/t1.ibd tablespace header with new master key.<br>180715 01:32:30 [01] Copying ./encryptedschema/db.opt to /var/lib/mysql/encryptedschema/db.opt<br>180715 01:32:30 [01] ...done<br>...<br>180715 01:32:31 [01] Copying ./xtrabackup_binlog_pos_innodb to /var/lib/mysql/xtrabackup_binlog_pos_innodb<br>180715 01:32:31 [01] ...done<br>180715 01:32:31 [01] Copying ./xtrabackup_master_key_id to /var/lib/mysql/xtrabackup_master_key_id<br>180715 01:32:31 [01] ...done<br>180715 01:32:31 [01] Copying ./ibtmp1 to /var/lib/mysql/ibtmp1<br>180715 01:32:31 [01] ...done<br>Shutting down plugin 'keyring_vault'<br>180715 01:32:31 completed OK! |
Once that’s done, change file/directory ownership to mysql.
|
1 |
[root@mysql2 ~]# chown -R mysql:mysql /var/lib/mysql/ |
Start the mysqld instance on the slave server configured similarly to the master configuration in the first part of this series.
|
1 |
early-plugin-load="keyring_vault=keyring_vault.so"<br>loose-keyring_vault_config="/var/lib/mysql-keyring/keyring_vault.conf"<br><br>encrypt_binlog=ON<br>innodb_encrypt_online_alter_logs=ON<br>innodb_encrypt_tables=ON<br>innodb_temp_tablespace_encrypt=ON<br>master_verify_checksum=ON<br>binlog_checksum=CRC32<br><br>log_bin=mysqld-bin<br>binlog_format=ROW<br>server-id=2<br>log-slave-updates |
|
1 |
[root@mysql2 ~]# systemctl status mysqld<br>● mysqld.service - MySQL Server<br> Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabled)<br> Active: active (running) since Sun 2018-07-15 01:32:59 UTC; 6h ago<br> Docs: man:mysqld(8)<br> http://dev.mysql.com/doc/refman/en/using-systemd.html<br> Process: 1390 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)<br> Process: 1372 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)<br> Main PID: 1392 (mysqld)<br> CGroup: /system.slice/mysqld.service<br> └─1392 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid<br><br>Jul 15 01:32:58 mysql2 systemd[1]: Starting MySQL Server...<br>Jul 15 01:32:59 mysql2 systemd[1]: Started MySQL Server. |
From here, you should be able to create the replication user on the master, and then configure slave replication based on the coordinates in the xtrabackup_binlog_info file. You can follow this section of the manual on starting replication.
For further reference, please read the manual related to Encrypted InnoDB tablespace backups.
Do you need to demonstrate that the security strategy that you have implemented for your databases is sufficient and appropriate? Perhaps you could benefit from a professional database audit? It could provide the reassurance that your organization needs.
Resources
RELATED POSTS
