Amazon-Linux 2 MariaDB UserData

user-data.sh

#!/bin/bash

# Update the system packages
yum update -y

# Install MariaDB
yum install -y mariadb-server

# Start MySQL service
systemctl start mariadb
systemctl enable mariadb

# Secure DB installation via
# amazon-linux-extras install -y mysql8.0

# Secure MySQL installation
cat > mysql_secure_installation.sql <<EOF2
# IMPORTANT - In case you enable this consider manage credentials
# in a more secure way!
#
# Make sure that NOBODY can access the server without a password
UPDATE mysql.user SET Password=PASSWORD('rootpw') WHERE User='root';

# Kill the anonymous users
DELETE FROM mysql.user WHERE User='';

# disallow remote login for root
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');

# Kill off the demo database
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';

# Grant permissions to localhost exclusive user
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' WITH GRANT OPTION;

# IMPORTANT - In case you enable this consider manage credentials
# in a more secure way
# Grant permissions to remote user
# CREATE USER 'consumer'@'%' IDENTIFIED BY 'consumer';
# GRANT ALL PRIVILEGES ON *.* TO 'consumer'@'%' WITH GRANT OPTION;

# Make our changes take effect
FLUSH PRIVILEGES;
EOF2

# Execute custom secure db installation   
mysql -uroot  <mysql_secure_installation.sql

# Specify your initdb.sql script
cat > initdb.sql <<EOF3
# Create DB
CREATE DATABASE IF NOT EXISTS my_db;
USE my_db;

CREATE TABLE IF NOT EXISTS my_table (
id INT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
name VARCHAR(255) NOT NULL
);
EOF3

# Execute InitDB Script
mysql -uroot -p"rootpw" <initdb.sql

# End script