109_Scaling Percona Servers

Preface

About

This page provides instructions for configuring a third (or more) Percona slave server with TDE and automatic key rotation. Each step includes an identity that indicates which application should be used for the instructions that proceed. Examples are also provided for steps that have varying parameters depending on the context (server names, usernames, passwords, etc).

Note: Even though each new step indicates shelling into the server, it is not necessary often as you can continue on a previously open terminal that is already shelled into the server. The goal is to provide ease of understanding if a specific portion needs to be configured or changed in contrast from going through start to finish.

Example Values

The following values will be used for the examples:

Parameter	Value
User on VPS servers	luke
Primary Percona Server	b13.jmaconsulting.biz
Third Percona Server	ovh13.jmaconsulting.biz
Active Vault Server	ovh12.jmaconsulting.biz

Prerequisites

New VPS configured
Primary Percona server configured

Main Documentation

1. Install Apache [source]

Identity: Local Machine, Terminal

# shell into the Third Percona Server
$ ssh [user]@[host]

# ===== example =====
$ ssh [email protected]

Identity: Third Percona Server, Terminal

# install apache
$ sudo apt update
$ sudo apt upgrade -y
$ sudo apt install apache2 -y

# verify installation
$ apache2 -version

# configure firewall
$ sudo ufw app list
$ sudo ufw allow ‘Apache’

# check ufw status and enable if inactive
$ sudo ufw status
$ sudo ufw enable

# IMPORTANT: YOU MUST ALLOW SSH OR YOU CANNOT SSH INTO THE SERVER
$ sudo ufw allow ssh

# allow https
$ sudo ufw allow https

# allow listen on port 3000 for Orchestrator
$ sudo ufw allow 3000
$ sudo ufw allow 6033

# check apache status
$ sudo systemctl status apache2

2. Setup TLS Using Certbot [source]

Identity: Local Machine, Terminal

# shell into server with Third Percona server
$ ssh [user]@[host]

# ===== example =====
$ ssh [email protected]

Identity: Third Percona Server, Terminal

$ sudo apt install snapd
$ sudo snap install core; sudo snap refresh core
$ sudo snap install --classic certbot
$ sudo ln -s /snap/bin/certbot /usr/bin/certbot

# get certificate for apache by following the prompts
$ sudo certbot --apache

# note the location of where the certificate and private key are stored
# Certificate is saved at: /etc/letsencrypt/live/b14.jmaconsulting.biz/fullchain.pem
# Key is saved at:         /etc/letsencrypt/live/b14.jmaconsulting.biz/privkey.pem

# test auto renewal
$ sudo certbot renew --dry-run

3. Install and Configure Percona [source]

Identity: Local Machine, Terminal

# shell into Third Percona Server
$ ssh [user]@[host]

# ===== example =====
$ ssh [email protected]

Identity: Third Percona Server, Terminal

# install Percona
$ sudo apt install gnupg2 curl
$ wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
$ sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
$ sudo percona-release setup ps80
$ sudo apt install percona-server-server

Identity: Third Percona Server, Terminal

# Make a copy of the LetsEncrypt cert and key
$ cp /etc/letsencrypt/live/[host].jmaconsulting.biz/fullchain.pem /etc/mysql
$ cp /etc/letsencrypt/live/[host].jmaconsulting.biz/privkey.pem /etc/mysql

# change ownership
$ chown mysql:mysql /etc/mysql/fullchain.pem
$ chown mysql:mysql /etc/mysql/privkey.pem

Identity: Third Percona Server, Terminal

# configure MySQL

# Notice that server-id=3. Each new Percona server will need a unique server-id
$ sudo nano /etc/mysql/my.cnf

Identity: my.cnf

[mysql]
# CLIENT #
port                           = 3306

[mysqld]
[mysql]
# CLIENT #
port                           = 3306

[mysqld]
# ssl_ca=/var/lib/mysql/cacert.pem
# ssl_cert=/var/lib/mysql/fullchain.pem
# ssl_key=/var/lib/mysql/mysql/privkey.pem

early-plugin-load="keyring_vault=keyring_vault.so"
loose-keyring_vault_config="/var/lib/mysql-keyring/keyring_vault.conf"
event_scheduler=OFF

server-id = 1
log-bin = mysql-bin
binlog_format = row
gtid-mode=ON
enforce-gtid-consistency=ON
log-slave-updates
skip-slave-start
read_only=1
# require_secure_transport=ON

# INNODB #
innodb_flush_method            = O_DIRECT
innodb_log_files_in_group      = 2
innodb_log_file_size           = 128M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table          = 1
innodb_buffer_pool_size        = 2G

[client]
# ssl-ca=/var/lib/mysql/cacert.pem
# ssl-cert=/var/lib/mysql/fullchain.pem
# ssl-key=/var/lib/mysql/privkey.pem

4. Get the Vault Token

Identity: Local Machine, Terminal

# shell into Active Vault Server
$ ssh [user]@[host]

# ===== example =====
$ ssh [email protected]

Identity: Active Vault Server, Terminal

# set vault address and certificate
$ sudo bash
$ export VAULT_ADDR='https://[active_vault_host].jmaconsulting.biz:8200'
$ export VAULT_CACERT="/etc/letsencrypt/live/[active_vault_host].jmaconsulting.biz/fullchain.pem"

# login to vault as root using the inital root token stored in 1Password
$ vault login

# create the token using the policy created previously when configuring Vault
$ vault token create -policy=percona_policy_1

# Copy the token value and store it somewhere

6. Configure keyring_vault Plugin with Vault Token

Identity: Local Machine, Terminal*

# shell into server with Third Percona server
$ ssh [user]@[host]

# ===== example =====
$ ssh [email protected]

Identity: Primary Percona Server, Terminal

# copy the following code into the configuration file and write the vault token generated in the previous step in the token parameter. If you still have the token used by the Primary and Secondary Percona servers, you can use that token as well.

# Note: the use of a token generated manually is only temporary to ensure that the communcation between Percona and Vault is working. When Vault Agent and AppRole is configured, this token will be replaced.

$ sudo nano /var/lib/mysql-keyring/keyring_vault.conf

Identity: keyring_vault.conf

vault_url=https://[active_vault_host].jmaconsulting.biz:8200
secret_mount_point=kv/dc1/master
secret_mount_point_version=AUTO
token=[percona_policy_token]
vault_ca=/var/lib/mysql/cacert.pem

7. Configure GTID Replication on Percona: Primary Server [source]

Identity: Local Machine, Terminal

# shell into server with Primary Percona server
$ ssh [user]@[host]

# ===== example =====
$ ssh [email protected]

Identity: Primary Percona Server, Terminal

# Shut down Primary Percona server using root password
$ mysqladmin -uroot -p shutdown

# restart Percona
$ sudo service mysql restart

Identity: Primary Percona Server, Terminal

# Log into Percona with root password
$ mysql -uroot -p

Identity: Primary Percona Server, MySQL Console

/* Prevent primary server from writing to the databases */
mysql> SET @@GLOBAL.read_only = ON;

/* Create replication user for third Percona server*/
mysql> CREATE USER 'repl'@'[third_percona_server]' IDENTIFIED BY '[password]';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'[third_percona_server]';

/* ===== example ===== */
mysql> CREATE USER 'repl'@'ovh14.jmaconsulting.biz' IDENTIFIED BY 'pass1234';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'ovh14.jmaconsulting.biz';

8. Configure GTID Replication on Percona: Third Server

Identity: Local Machine, Terminal

# shell into server with Third Percona server
$ ssh [user]@[host]

# ===== example =====
$ ssh [email protected]

Identity: Third Percona Server, Terminal

# Shut down Third Percona server using root password
$ mysqladmin -uroot -p shutdown

# restart Percona
$ sudo service mysql restart

Identity: Third Percona Server, Terminal

# Log into Percona with root password
$ mysql -uroot -p

Identity: Third Percona Server, MySQL Console

/* configure secondary percona to listen to primary */
mysql> CHANGE MASTER TO MASTER_HOST='[primary_percona_host].jmaconsulting.biz', MASTER_USER='repl', MASTER_PASSWORD='[password]', MASTER_AUTO_POSITION = 1, MASTER_SSL=1;

/* ===== example ===== */
mysql> CHANGE MASTER TO MASTER_HOST='b13.jmaconsulting.biz', MASTER_USER='repl', MASTER_PASSWORD='pass1234', MASTER_AUTO_POSITION = 1, MASTER_SSL=1;

/* Notes: This command will be used whenever we need to rejoin a failed primary percona server back as a secondary percona server performing replication. See the documentation on Percona Failover for more information.

Quick access: Percona Failover

Identity: Third Percona Server, MySQL Console

/* start replication on the third percona server */
mysql> START SLAVE;

/* check if replication is running */
mysql> SHOW SLAVE STATUS \G;

9. Start Primary Percona Server

Identity: Local Machine, Terminal

# shell into server with Primary Percona server
$ ssh [user]@[host]

# ===== example =====
$ ssh [email protected]

Identity: Primary Percona Server, Terminal

# log into percona with root password
$ mysql -uroot -p

Identity: Primary Percona Server, MySQL Console

/* Allow primary to write to the databases */
mysql> SET @@GLOBAL.read_only = OFF;

10. Configure Vault Agent on Percona Servers [source]

Identity: Local Machine, Terminal

# shell into Primary Percona Server
# Note: you will need to repeat these steps for the secondary percona server
$ ssh [user]@[host]

# ===== example =====
$ ssh [email protected]
$ ssh [email protected]

Identity: Primary (or Secondary) Percona Server, Terminal

# create a directory for Vault Agent
$ sudo mkdir /var/lib/vault
$ sudo mkdir /var/lib/vault_agent
$ chown vault:vault -R /var/lib/vault
$ cd vault_agent
# create a new config file and copy in the code below
$ sudo nano /var/lib/vault_agent/vault-agent-config.hcl

Identity: vault-agent-config.hcl

pid_file = "./pidfile"

vault {
  address = "https://[host].jmaconsulting.biz:8200"
}

auto_auth {
  method {
    type      = "approle"

    config = {
      role_id_file_path = "./vault_role_id"
      secret_id_file_path = "./vault_wrapped_secret_id"
      remove_secret_id_file_after_reading = false
    }
  }

  sink {
    type = "file"
    config = {
      path = "sink_file_wrapped_1.txt"
    }
  }

  sink {
    type = "file"
    config = {
      path = "./vault_token"
    }
  }
}

cache {
  use_auto_auth_token = true
}

listener "tcp" {
  address = "[host].jmaconsulting.biz:8100"
  tls_disable = false
  tls_cert_file="/etc/letsencrypt/live/[host].jmaconsulting.biz/fullchain.pem"
  tls_key_file="/etc/letsencrypt/live/[host].jmaconsulting.biz/privkey.pem"
}

Identity: Primary (or Secondary) Percona Server, Terminal

$ sudo bash
$ cd /var/lib/vault/vault_agent/
$ touch pidfile
$ touch sink_file_wrapped_1.txt
$ touch vault_role_id
$ touch vault_token
$ touch vault_wrapped_secret_id
$ chown vault:vault -R /var/lib/vault/vault_agent/
$ chmod 700 vault_role_id
$ chmod 700 vault_token
$ chmod 700 vault_wrapped_secret_id

# Copy and paste the AppRole Role ID from the previous step into vault_role_id

# Copy and past AppRole Secret Id from the previous step into vault_wrapped_secret_id

# Note: the same token and secret ID can be used for both the Primary and Secondary Percona servers

# start vault agent with the config file
$ vault agent -config=/path/to/agent-config.hcl

Repeat step 2 for the secondary percona server, taking note of values that need to be changed.

11. Configure Bash Script and Cron Job

Identity: Local Machine, Terminal

# shell into Primary Percona Server
# Note: you will need to repeat these steps for the secondary percona server
$ ssh [user]@[host]

# ===== example =====
$ ssh [email protected]
$ ssh [email protected]

Identity: Primary (or Secondary) Percona Server, Terminal

# navigate to vault agent directory
$ cd vault_agent

# create a bash script and copy the following code into the file
# you will need to replace the mysql password with the password you just created for the new MySQL user
$ sudo nano renew_token.sh

Identity: renew_token.sh

#!/bin/bash
pkill -9 vault
vault agent -config=vault-agent-config.hcl &
sleep 60
new_token="$( cat vault_token )"
sed -i "s/token=.*/token=${new_token}/" /var/lib/mysql-keyring/keyring_vault.conf
mysql -urenew_token_user -p[password] -e "UNINSTALL PLUGIN keyring_vault";
mysql -urenew_token_user -p[password] -e "INSTALL PLUGIN keyring_vault SONAME 'keyring_vault.so';"

Identity: Primary (or Secondary) Percona Server, Terminal

# configure cron job and add the following code to the file
$ sudo bash
$ crontab -e

Identity: Cron Job configuration file

# configure cron job to repeat the first day of every month
# this will generate a new vault token, inject it into the keyring_vault.conf file, and  uninstall/install keyring_vault plugin to activate the new token
0 0 1 * * cd /path/to/vault_agent/folder && /bin/bash /path/to/renew_token.sh

12. Configure SSL Reloading When Certificate and Key Renew [source]

Identity: Local Machine, Terminal

# shell into Primary Percona server
# note: you will need to repeat the following steps for the secondary Percona server except the user creation step (already created through replication)
$ ssh [user]@[host]

# ===== example =====
$ ssh [email protected]
$ ssh [email protected]

Identity: Primary Percona Server, Terminal

# log into percona with root password
$ mysql -uroot -p

Identity: Primary Percona Server, MySQL Console

/* Create user with permissions to reload SSL cert and key */
/* You will not need to do this again on the secondary server because replication will have copied it over already */
mysql> CREATE USER 'renew_tls_user'@'localhost' IDENTIFIED BY '[password]';
mysql> GRANT CONNECTION_ADMIN ON *.* TO 'renew_tls_user'@'localhost';

Identity: Primary Percona Server, Terminal

# install mysql client
# source: https://docs.percona.com/percona-server/8.0/installation/apt_repo.html
$ sudo apt install gnupg2 curl
$ wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
$ sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
$ sudo percona-release setup ps80
$ sudo apt install percona-server-client
$ sudo apt install libperconaserverclient21-dev

# configure a renew_tls_user and enter the password when prompted
$ mysql_config_editor set --login-path=renew_tls_user@localhost --host=localhost --user=renew_tls_user --password
$ service mysql restart

Identity: Primary Percona Server, Terminal

# create a bash script to copy over and reload tls cert and key whenever certbot renews
$ sudo nano /etc/letsencrypt/renewal-hooks/deploy/mysqld-deploy.sh
$ sudo chmod 700 /etc/letsencrypt/renewal-hooks/deploy/mysqld-deploy.sh

Identity: mysqld-deploy.sh

#!/bin/sh
domain=[primary/secondary_host].jmaconsulting.biz
cert_dir=/var/lib/mysql
user=mysql.mysql
cp /etc/letsencrypt/live/$domain/privkey.pem $cert_dir/privkey.pem

openssl x509 -in /etc/letsencrypt/live/$domain/fullchain.pem > $cert_dir/fullchain.pem

wget https://curl.se/ca/cacert.pem -P /etc/letsencrypt/live/$domain/
cat /etc/letsencrypt/live/$domain/fullchain.pem >> /etc/letsencrypt/live/$domain/cacert.pem
cp /etc/letsencrypt/live/$domain/cacert.pem $cert_dir/cacert.pem
rm /etc/letsencrypt/live/$domain/cacert.pem

chown $user $cert_dir/*.pem
chmod 700 $cert_dir/*.pem

mysql --login-path=renew_tls_user@localhost --execute="ALTER INSTANCE RELOAD TLS"

Identity: Primary Percona Server, Terminal

# run bash script once to load the cert and key
$ suso bash /etc/letsencrypt/renewal-hooks/deploy/mysqld-deploy.sh

Identity: Primary Percona Server, Terminal

# configure MySQL to use ssl
$ sudo nano /etc/mysql/my.cnf

Identity: my.cnf

[mysql]
# CLIENT #
port                           = 3306

[mysqld]
ssl_ca=/var/lib/mysql/cacert.pem
ssl_cert=/var/lib/mysql/fullchain.pem
ssl_key=/var/lib/mysql/mysql/privkey.pem

early-plugin-load="keyring_vault=keyring_vault.so"
loose-keyring_vault_config="/var/lib/mysql-keyring/keyring_vault.conf"
event_scheduler=OFF

server-id = 1
log-bin = mysql-bin
binlog_format = row
gtid-mode=ON
enforce-gtid-consistency=ON
log-slave-updates
skip-slave-start
read_only=1
# require_secure_transport=ON

# INNODB #
innodb_flush_method            = O_DIRECT
innodb_log_files_in_group      = 2
innodb_log_file_size           = 128M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table          = 1
innodb_buffer_pool_size        = 2G

[client]
ssl-ca=/var/lib/mysql/cacert.pem
ssl-cert=/var/lib/mysql/fullchain.pem
ssl-key=/var/lib/mysql/privkey.pem

Identity: Primary Percona Server, Terminal

# restart percona
$ sudo service mysql restart

13. Configure Automatic TDE Key Rotation

Identity: Local Machine, Terminal

# shell into server with Primary Percona server
# note: you will need to repeat the following steps for the secondary Percona server except the user creation step (already created through replication)
$ ssh [user]@[host]

# ===== example =====
$ ssh [email protected]
$ ssh [email protected]

Identity: Primary Percona Server, Terminal

# log into percona with root password
$ mysql -uroot -p

Identity: Primary Percona Server, MySQL Console

/* Create user (or just grant if the user is already created) that has permissions to rotate the master encrpytion key */
/* You will not need to do this again on the secondary server because replication will have copied it over already */
mysql> CREATE USER 'rotate_key_user'@'localhost' IDENTIFIED BY '[password]';
mysql> GRANT ENCRYPTION_KEY_ADMIN ON *.* TO 'rotate_key_user'@'localhost';

Identity: Primary Percona Server, Terminal

# install mysql client if not already installed
# source: https://docs.percona.com/percona-server/8.0/installation/apt_repo.html
$ sudo apt install gnupg2 curl
$ wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
$ sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
$ sudo percona-release setup ps80
$ sudo apt install percona-server-client
$ sudo apt install libperconaserverclient21-dev

# configure a renew_tls_user and enter the password when prompted
$ mysql_config_editor set --login-path=rotate_key_user@localhost --host=localhost --user=rotate_key_user --password
$ service mysql restart

Identity: Primary Percona Server, Terminal

$ sudo bash
# create bash script to update 
$ nano /etc/mysql/rotate_master_key.sh

Identity: rotate_master_key.sh

#!/bin/bash
mysql --login-path=rotate_key_user@localhost --execute="ALTER INSTANCE ROTATE INNODB MASTER KEY;"

Identity: Primary Percona Server, Terminal

# remove read permissions for all users except root
$ chmod 700 /etc/mysql/rotate_master_key.sh
$ chown root:root /etc/mysql/rotate_master_key.sh

# add script to cron
$ sudo bash
$ crontab -e

Identity: Cron Job configuration file

# add the following to configure cron job to repeat every 5 minutes
# this will reload the ssl certificate and private key without restarting percona
*/5 * * * * cd /etc/mysql && /bin/bash /etc/mysql/rotate_master_key.sh

14. Install and Configure Percona XtraBackup [source]

Identity: Local Machine, Terminal

# shell into Active Percona Server
# Note: you will need to repeat the following steps on the Secondary Percona server
$ ssh [user]@[host]

# ===== example =====
$ ssh [email protected]
$ ssh [email protected]

Identity: Primary Percona Server, Terminal

# install Percona
$ sudo apt install gnupg2 curl
$ wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
$ sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
$ percona-release enable-only tools release
$ sudo apt install percona-xtrabackup-80
$ sudo apt install qpress

# create backup directory
$ sudo mkdir /data
$ sudo mkdir /data/backups
$ sudo mkdir /data/backups/mysql
$ sudo chown -R mysql:mysql /data
$ sudo chmod -R 700 /data

Identity: Primary Percona Server, Terminal

# add the following to the configuration file
$ sudo nano /etc/mysql/my.cnf

Identity: my.cnf

[xtrabackup]
target_dir = /data/backups/mysql/

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

109_Scaling Percona Servers

Preface

About

Example Values

Prerequisites

Main Documentation

1. Install Apache [source]

2. Setup TLS Using Certbot [source]

3. Install and Configure Percona [source]

4. Get the Vault Token

6. Configure keyring_vault Plugin with Vault Token

7. Configure GTID Replication on Percona: Primary Server [source]

8. Configure GTID Replication on Percona: Third Server

9. Start Primary Percona Server

10. Configure Vault Agent on Percona Servers [source]

11. Configure Bash Script and Cron Job

12. Configure SSL Reloading When Certificate and Key Renew [source]

13. Configure Automatic TDE Key Rotation

14. Install and Configure Percona XtraBackup [source]

Clone this wiki locally