102_Percona MySQL Server Setup

Preface

About

This page provides instructions for configuring a Primary and Secondary Percona server with replication, 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
Secondary Percona Server	ovh13.jmaconsulting.biz
Active Vault Server	ovh12.jmaconsulting.biz

Prerequisites

Two VPSs configured on different bare metal servers
Vault HA cluster configured

Main Documentation

1. Install Apache [source]

Identity: Local Machine, Terminal

# shell into the Primary 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 apache
$ sudo apt update
$ sudo apt upgrade -y
$ sudo apt install apache2 -y

# check apache status
$ sudo systemctl status apache2

# 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

# 3306: Percona
# 8080: HAProxy
# 8443: HAProxy
$ sudo ufw allow 3306
$ sudo ufw allow 8080
$ sudo ufw allow 8443

2. Setup TLS Using Certbot [source]

Identity: Local Machine, Terminal

# shell into server with Primary 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 certbot
$ 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

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

3. Install and Configure Percona [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
$ sudo percona-release setup ps80
$ sudo apt install percona-server-server

Identity: Primary Percona Server, Terminal

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

# NOTE: you will need to change the server-id value to be different on the secondary percona server. For example, server-id = 2
# SSL also won't work until it is configured in a future step because the cert and key files have not been loaded to the directory yet

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_bin_trust_function_creators=1
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 [source]

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 for use in the following steps

# ===== example =====
$ sudo bash
$ export VAULT_ADDR='https://ovh12.jmaconsulting.biz:8200'
$ export VAULT_CACERT="/etc/letsencrypt/live/ovh12.jmaconsulting.biz/fullchain.pem"
$ vault login
$ vault token create -policy=percona_policy_1

5. Configure keyring_vault Plugin with Vault Token [source]

Identity: Local Machine, Terminal*

# shell into server with 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 Percona Server, Terminal

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

# copy the following code into the configuration file and write the vault token generated in the previous step in the token parameter. The same token can be used for both the Primary and Secondary Percona servers.

# 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.

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

# ===== example =====
vault_url=https://ovh12.jmaconsulting.biz:8200
secret_mount_point=kv/dc1/master
secret_mount_point_version=AUTO
token=xxxx-xxxx-xxxx-xxxx
vault_ca=/var/lib/mysql/cacert.pem

Repeat steps 1 to 5 on the secondary percona server

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

/* Create replication user for both the primary and secondary percona servers (we need both in the case of failover) */
mysql> CREATE USER 'repl'@'[secondary_percona_server]' IDENTIFIED BY '[password]';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'[secondary_percona_server]';
mysql> CREATE USER 'repl'@'[primary_percona_server]' IDENTIFIED BY '[password]';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'[primary_percona_server]';

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

8. Configure GTID Replication on Percona: Secondary Server

Identity: Local Machine, Terminal

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

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

Identity: Secondary Percona Server, Terminal

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

# restart Percona
$ sudo service mysql restart

Identity: Secondary Percona Server, Terminal

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

Identity: Secondary 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: Secondary Percona Server, MySQL Console

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

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

/* IMPORTANT: When you start replication, the root login for the secondary server will turn into the root password of the primary server

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 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
log_bin_trust_function_creators=1
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

11. 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

Repeat all of step 11 for the secondary percona server, except the mysql user creation

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

102_Percona MySQL Server Setup

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 [source]

5. Configure keyring_vault Plugin with Vault Token [source]

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

8. Configure GTID Replication on Percona: Secondary Server

9. Start Primary Percona Server

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

11. Configure Automatic TDE Key Rotation

Clone this wiki locally