First you need to download and use the latest Percona XtraBackup 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 xtrabackup to encrypt keys of files being backed up.
[root@mysql1 ~]# openssl rand -base64 24
NSu7kfUgcTTIY2ym7Qu6jnYOotOuMIeT
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, run xtrabackup 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 <STDOUT>
180715 01:28:58 [00] ...done
180715 01:28:58 Backup created in directory '/root/backup/'
180715 01:28:58 [00] Streaming <STDOUT>
180715 01:28:58 [00] ...done
180715 01:28:58 [00] Streaming <STDOUT>
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!
Extract the backup in 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 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!
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/ca.crt.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 and re-encrypts all the files that should be encrypted.
[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 mysqld instance on the slave server.
[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.