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.
Backing up from the master
First you need to install the latest Percona XtraBackup 2.4 package, in this tutorial I used this version:
[root@mysql1 ~]# xtrabackup --version xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --log_bin=mysqld-bin --server-id=1 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.
[root@mysql1 ~]# openssl rand -base64 24 NSu7kfUgcTTIY2ym7Qu6jnYOotOuMIeT
You can store the transition-key in Vault and retrieve it later:
[root@mysql1 ~]# # Store the transition-key to the Vault server [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" [root@mysql1 ~]# # Retrieve the transition-key from the Vault server [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 "NSu7kfUgcTTIY2ym7Qu6jnYOotOuMIeT" [root@mysql1 ~]# # Delete the transition-key from the Vault server [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:
[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:
[root@mysql1 ~]# xtrabackup --stream=xbstream --backup --target-dir=backup/ --transition-key=NSu7kfUgcTTIY2ym7Qu6jnYOotOuMIeT | nc 192.168.0.117 9999 xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --log_bin=mysqld-bin --server-id=1 --transition-key=* 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/ 180715 01:28:56 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root' (using password: NO). 180715 01:28:56 version_check Connected to MySQL server 180715 01:28:56 version_check Executing a version check against the server... 180715 01:28:56 version_check Done. 180715 01:28:56 Connecting to MySQL server host: localhost, user: root, password: not set, port: not set, socket: not set Using server version 5.7.22-22-log xtrabackup version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c) xtrabackup: uses posix_fadvise(). xtrabackup: cd to /var/lib/mysql xtrabackup: open files limit requested 0, set to 65536 xtrabackup: using the following InnoDB configuration: xtrabackup: innodb_data_home_dir = . xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend xtrabackup: innodb_log_group_home_dir = ./ xtrabackup: innodb_log_files_in_group = 2 xtrabackup: innodb_log_file_size = 50331648 InnoDB: Number of pools: 1 180715 01:28:56 Added plugin 'keyring_vault.so' to load list. 180715 01:28:56 >> log scanned up to (2616858) xtrabackup: Generating a list of tablespaces InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0 ... 180715 01:28:58 Finished backing up non-InnoDB tables and files 180715 01:28:58 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS... xtrabackup: The latest check point (for incremental): '2616849' xtrabackup: Stopping log copying thread. .180715 01:28:58 >> log scanned up to (2616865) 180715 01:28:58 Executing UNLOCK TABLES 180715 01:28:58 All tables unlocked 180715 01:28:58 [00] Streaming ib_buffer_pool to 180715 01:28:58 [00] ...done 180715 01:28:58 Backup created in directory '/root/backup/' 180715 01:28:58 [00] Streaming 180715 01:28:58 [00] ...done 180715 01:28:58 [00] Streaming 180715 01:28:58 [00] ...done 180715 01:28:58 Saving xtrabackup_keys. xtrabackup: Transaction log of lsn (2616849) to (2616865) was copied. Shutting down plugin 'keyring_vault' 180715 01:28:58 completed OK!
Restoring the backup on the Slave server
Extract the backup to a temporary location:
[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.
[root@mysql2 ~]# xtrabackup --prepare --target-dir=backup/ --transition-key=NSu7kfUgcTTIY2ym7Qu6jnYOotOuMIeT 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=* 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/ xtrabackup version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c) xtrabackup: cd to /root/backup/ xtrabackup: This target seems to be not prepared yet. ... xtrabackup: starting shutdown with innodb_fast_shutdown = 1 InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 2617384 180715 01:31:10 completed OK!
Configure keyring_vault.conf on slave
Create the keyring_vault.conf
file with the following contents:
[root@mysql2 ~]# cat /var/lib/mysql-keyring/keyring_vault.conf vault_url = https://192.168.0.114:8200 secret_mount_point = secret/dc1/slave token = be515093-b1a8-c799-b237-8e04ea90ad7a 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.
Use –copy-back option to finalize backup restoration
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.
[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 xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --log_bin=mysqld-bin --server-id=2 --transition-key=* --generate-new-master-key=1 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/ xtrabackup version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c) 180715 01:32:28 Loading xtrabackup_keys. 180715 01:32:28 Loading xtrabackup_keys. 180715 01:32:29 Generated new master key with ID 'be1ba51c-87c0-11e8-ac1c-00163e79c097-2'. ... 180715 01:32:29 [01] Encrypting /var/lib/mysql/mysql/plugin.ibd tablespace header with new master key. 180715 01:32:29 [01] Copying ./mysql/servers.ibd to /var/lib/mysql/mysql/servers.ibd 180715 01:32:29 [01] ...done 180715 01:32:29 [01] Encrypting /var/lib/mysql/mysql/servers.ibd tablespace header with new master key. 180715 01:32:29 [01] Copying ./mysql/help_topic.ibd to /var/lib/mysql/mysql/help_topic.ibd 180715 01:32:29 [01] ...done 180715 01:32:29 [01] Encrypting /var/lib/mysql/mysql/help_topic.ibd tablespace header with new master key. 180715 01:32:29 [01] Copying ./mysql/help_category.ibd to /var/lib/mysql/mysql/help_category.ibd 180715 01:32:29 [01] ...done 180715 01:32:29 [01] Encrypting /var/lib/mysql/mysql/help_category.ibd tablespace header with new master key. 180715 01:32:29 [01] Copying ./mysql/help_relation.ibd to /var/lib/mysql/mysql/help_relation.ibd 180715 01:32:29 [01] ...done ... 180715 01:32:30 [01] Encrypting /var/lib/mysql/encryptedschema/t1.ibd tablespace header with new master key. 180715 01:32:30 [01] Copying ./encryptedschema/db.opt to /var/lib/mysql/encryptedschema/db.opt 180715 01:32:30 [01] ...done ... 180715 01:32:31 [01] Copying ./xtrabackup_binlog_pos_innodb to /var/lib/mysql/xtrabackup_binlog_pos_innodb 180715 01:32:31 [01] ...done 180715 01:32:31 [01] Copying ./xtrabackup_master_key_id to /var/lib/mysql/xtrabackup_master_key_id 180715 01:32:31 [01] ...done 180715 01:32:31 [01] Copying ./ibtmp1 to /var/lib/mysql/ibtmp1 180715 01:32:31 [01] ...done Shutting down plugin 'keyring_vault' 180715 01:32:31 completed OK!
Once that’s done, change file/directory ownership to mysql.
[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.
early-plugin-load="keyring_vault=keyring_vault.so" loose-keyring_vault_config="/var/lib/mysql-keyring/keyring_vault.conf" encrypt_binlog=ON innodb_encrypt_online_alter_logs=ON innodb_encrypt_tables=ON innodb_temp_tablespace_encrypt=ON master_verify_checksum=ON binlog_checksum=CRC32 log_bin=mysqld-bin binlog_format=ROW server-id=2 log-slave-updates
[root@mysql2 ~]# systemctl status mysqld ? mysqld.service - MySQL Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabled) Active: active (running) since Sun 2018-07-15 01:32:59 UTC; 6h ago Docs: man:mysqld(8) http://dev.mysql.com/doc/refman/en/using-systemd.html Process: 1390 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS) Process: 1372 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS) Main PID: 1392 (mysqld) CGroup: /system.slice/mysqld.service ??1392 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid Jul 15 01:32:58 mysql2 systemd[1]: Starting MySQL Server... 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.
Is validating your security strategy a concern?
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.
The post Backing up Percona Server for MySQL with keyring_vault plugin enabled appeared first on Percona Database Performance Blog.