MariaDB 10.2.6 - AWS KMS, Server Audit Plugin Crash (Vagrant)

mariadb-10-aws-audit.sh

#!/bin/bash

# I need this
sudo echo "alias l='ls -l --color'" >> /etc/bashrc

# Configure mariadb yum repo, force to 10.2.6
curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version=mariadb-10.2.6
yum --assumeyes install MariaDB-server MariaDB-aws-key-management percona-xtrabackup-24

# Show love to Percona, need percona-toolkit
yum --assumeyes install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
yum --assumeyes install percona-toolkit

################### MARIADB AWS CONFIG #######################

sudo cat <<EOF > /etc/my.cnf.d/aws_key_management.cnf
[mariadb]
# AWS
plugin-load-add                  = aws_key_management.so
aws_key_management_master_key_id = alias/mariadb-enc-test
aws_key_management_log_level     = info
aws_key_management_key_spec      = AES_256
aws_key_management_region        = us-east-1
aws_key_management_rotate_key    = -1
ignore-db-dirs                   = .pki
ignore-db-dirs                   = .aws
!include /etc/my.cnf.d/enable_encryption.preset
EOF

################### MARIADB CONFIG #######################

sudo cat <<EOF > /etc/my.cnf.d/ehs.cnf
[mysqld]
server-id                       = 1
datadir                         = /var/lib/mysql/data
innodb_file_per_table           = 1
innodb_buffer_pool_size         = 128M
slow_query_log                  = 1
log_output                      = table
log_slow_verbosity              = explain
long_query_time                 = 10.0
sync_binlog                     = 1
log_bin                         = /var/lib/mysql/log-bin/mysql-bin
aria_log_dir_path               = /var/lib/mysql/log-aria
binlog_format                   = MIXED
log_bin_trust_function_creators = 1
expire_logs_days                = 7
thread_cache_size               = 100
max_connections                 = 2000
max_user_connections            = 250
max_binlog_size                 = 100M
tmp_table_size                  = 100M
max_heap_table_size             = 100M
max_allowed_packet              = 20M
query_cache_size                = 0
query_cache_type                = 0
table_definition_cache          = 20000
table_open_cache                = 20000
open_files_limit                = 100000
wait_timeout                    = 3600
skip-slave-start                = 1
skip-name-resolve               = 1
read_only                       = 1

# SSL support
# ssl-ca=/etc/certs/mie-ca.pem
# ssl-cert=/etc/certs/cert.pem
# ssl-key=/etc/certs/key.pem

# FULL TEXT SEAECH #
ft_min_word_len=3
ft_max_word_len=35

# Replication
gtid_strict_mode        = 1
log_slave_updates       = 1
relay-log               = /var/lib/mysql/log-relay/relay-bin
relay-log-space-limit   = 10G
relay_log_purge         = 1

# innodb
innodb_file_per_table          = 1
innodb_flush_method            = O_DIRECT
innodb_buffer_pool_size        = 128M
innodb_log_file_size           = 32M
innodb_log_files_in_group      = 2
innodb_thread_concurrency      = 0
innodb_flush_log_at_trx_commit = 2

# Encryption
loose-innodb-encryption-threads        = 4
loose-innodb-encryption-rotate-key-age = 1
EOF

################### AUDIT LOGGING #######################

sudo cat <<EOF > /etc/my.cnf.d/audit.cnf
[mariadb]
# https://mariadb.com/kb/en/mariadb/mariadb-audit-plugin-installation/
# https://mariadb.com/kb/en/mariadb/server_audit-system-variables/#server_audit_file_path

# Enable
plugin-load-add               = server_audit.so
server_audit_logging          = ON
server_audit_events           = CONNECT,QUERY_DCL,TABLE,QUERY_DDL
server_audit                  = FORCE_PLUS_PERMANENT
# flat file (Pick file or syslog, not both)
server_audit_output_type      = FILE
server_audit_file_path        = /var/lib/mysql/log-audit/audit.log
server_audit_file_rotate_size = 1000000
server_audit_file_rotations   = 10
# syslog
## server_audit_output_type      = SYSLOG
## server_audit_syslog_facility  = LOG_LOCAL6
## server_audit_syslog_ident     = mariadb_audit
## server_audit_syslog_info      = supermax-db1
## server_audit_syslog_priority  = LOG_INFO
EOF

# TODO: COnfig sys log
### $ cat /etc/rsyslog.d/10-mysqlaudit.conf
### # keep in /var/log as syslog user can’t access /var/log/mysql usually
### /var/log/mysql-audit.log {
### daily
### rotate 7
### missingok
### create 640 syslog adm
### compress
### sharedscripts
### postrotate
### reload rsyslog >/dev/null 2>&1 || true
### endscript
### }

################### AWS KMS #######################
sudo mkdir /var/lib/mysql/.aws
sudo chown -R mysql: /var/lib/mysql/.aws

sudo cat <<EOF > /var/lib/mysql/.aws/credentials
[default]
aws_access_key_id = XXXXXX
aws_secret_access_key = XXXXX
region = us-east-1
EOF

sudo chown -R mysql: /var/lib/mysql/.aws/credentials
sudo chmod -R 600 /var/lib/mysql/.aws/credentials

################### systemd, increase file limit #######################

# Increase Systemd LimitNOFILE
sudo cat <<EOF > /etc/systemd/system/mariadb.service.d/filelimit.conf
[Service]
# https://mariadb.com/kb/en/mariadb/systemd/
# https://mariadb.com/kb/en/mariadb/server-system-variables/#open_files_limit
LimitNOFILE=infinity
EOF

# reload systemctl
systemctl daemon-reload

################### SELinux #######################      

# If SELinux is enabled, this gets around it.
# Allow MariaDB to talk to AWS via SELinux
setsebool -P mysql_connect_any 1

################### clean up and rebuild data #######################
sudo rm -Rf /var/lib/mysql/*

# Make Directory / Chown
sudo mkdir -p /var/lib/mysql/data
sudo mkdir /var/lib/mysql/log-bin
sudo mkdir /var/lib/mysql/log-relay
sudo mkdir /var/lib/mysql/log-aria
sudo mkdir /var/lib/mysql/log-audit
sudo chown -R mysql: /var/lib/mysql

# this should be successful
sudo -u mysql mysql_install_db
sudo -u mysql mysql_upgrade
sudo systemctl start mariadb

# On success convert mysql tables to ARIA (for encryption)
if [[ $? -eq 0 ]]; then
  # Disable slow log so we can convert the CSV table to ARIA encrypted table.
  mysql -e "SET @@global.slow_query_log=0"

  # Convert mysql data to engine=AIRA
  mysql_convert_table_format -e ARIA -f mysql

  # Enable slow query low
  mysql -e "SET @@global.slow_query_log=1"

  # Disable slow log so we can convert the CSV table to ARIA encrypted table.
  mysql -e "SET @@global.slow_query_log=0"
fi