Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
MariaDB 10.2.6 - AWS KMS, Server Audit Plugin Crash (Vagrant)
#!/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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.