Automated installation of MySQL 5.7 community server on Amazon EC2 instances with Amazon Linux AMI

Update

MySQL server with version 5.7 is now available in Amazon Linux yum repo list. You can easily install it with command:

yum install mysql57-server

You can read the post if you want to know how I installed mysql community server from binary package and automated the password change with the help of expect program when mysql57-server was not available in yum repo on Amazon Linux!

Original Post

Couple of days ago, we upgraded our MySQL server to version 5.7 in development. So we need to give the support of MySQL server installation in our build system’s program which manages sandboxes (EC2 instances with Amazon Linux AMI image) for build and testing purposes on AWS.

Challenges with Amazon Linux AMI and MySQL 5.7 Installation

Well, as of now, writing this, Amazon Linux AMI’s yum package manager doesn’t have mysql with version 5.7 in their repository list. So, we have to choose the option to install MySQL from distribution source package in next step after creating sandbox on Amazon AWS.

To create sandbox, we’re using AWS Command Line Interface as follows:

aws ec2 run-instances --image-id {IMAGE_ID} --count 1 --instance-type {INSTANCE_TYPE} --key-name {KEY_PAIR_NAME} --security-groups {SECURITY_GROUP} --profile {PROFILE} --user-data mysql57_yum.sh >> out.json

You may find other APIs for fuzzy auto-completion for ec2 here such as to know the status, to create/start/stop/terminate ec2 instances.

Installing MySQL 5.7 on Amazon Linux AMI with Bash Script

This is the bash script (mysql57_yum.sh) which installs MySQL 5.7 community server on Amazon Linux AMI type machine.

1#!/bin/bash
2
3# This script will download mysql version as specified in `MYSQL_RELEASE`
4# Aim to install latest image of mysql release on machine of amazon linux AMI.
5# Guidance: https://dev.mysql.com/doc/refman/5.7/en/linux-installation-yum-repo.html
6
7MYSQL_RELEASE=mysql57-community-release-el6-11.noarch.rpm
8MYSQL_RELEASE_URL="https://dev.mysql.com/get/${MYSQL_RELEASE}"
9MYSQL_SERVICE=mysqld
10MYSQL_LOG_FILE=/var/log/${MYSQL_SERVICE}.log
11
12exitError() {
13    echo "Error: $1" >&2
14    exit 1
15}
16
17if [[ $EUID -ne 0 ]]; then # we can compare directly with this syntax.
18    echo "Please run as root/sudo"
19    exit 1
20fi
21
22# download the mysql release package from url
23echo "Downloading mysql release package from specified url: $MYSQL_RELEASE_URL..."
24wget -O $MYSQL_RELEASE $MYSQL_RELEASE_URL || exitError "Could not fetch required release of mysql: ($MYSQL_RELEASE_URL)"
25
26# The installation command adds the MySQL Yum repository to your system's repository list
27# and downloads the GnuPG key to check the integrity of the software packages.
28echo "Adding mysql yum repo in the system repo list..."
29yum localinstall -y $MYSQL_RELEASE || exitError "Unable to install mysql release ($MYSQL_RELEASE) locally with yum"
30
31# check that the MySQL Yum repository has been successfully added or not
32echo "Checking that mysql yum repo has been added successfully..."
33yum repolist enabled | grep "mysql.*-community.*" || exitError "Mysql release package ($MYSQL_RELEASE) has not been added in repolist"
34
35# check whather release subrepo is enabled (at least one of them should be enabled)
36echo "Checking that at least one subrepository is enabled for one release..."
37yum repolist enabled | grep mysql || exitError "At least one subrepository should be enabled for one release series at any time."
38
39# now install mysql-community-server
40echo "Installing mysql-community-server..."
41yum install -y mysql-community-server || exitError "Unable to install mysql mysql-community-server"
42
43# # set proper sql-mode=ALLOW_INVALID_DATES with just appending 
44# (or maybe better include it right under [mysqld] section with some script!)
45# if cat /etc/my.cnf | grep "sql-mode"; then
46#     echo "sql-mode already has been set !!!"
47# else
48#     echo "Setting up sql-mode in /etc/my.cnf..."
49#     echo 'sql-mode="ALLOW_INVALID_DATES"' >> /etc/my.cnf || exitError "Unable to set sql-mode in /etc/my.cnf file"
50#     echo "sql-mode has been set to ALLOW_INVALID_DATES in /etc/my.cnf"
51# fi
52
53# after installation, start the server
54echo "Starting mysql service..."
55service $MYSQL_SERVICE start || exitError "Could not start mysql service"
56
57# check whether service is running or not
58echo "Checking status of mysql service..."
59service $MYSQL_SERVICE status || exitError "Mysql service is not running"
60
61echo "Setting up mysql as in startup service..."
62chkconfig mysqld on
63
64# get auto generated password from log file
65echo "extracting password from log file: ${MYSQL_LOG_FILE}..."
66MYSQL_PWD=$(grep -oP '(?<=A temporary password is generated for root@localhost: )[^ ]+' ${MYSQL_LOG_FILE})
67# echo "Auto generated password is: ${MYSQL_PWD}"
68
69# install expect program to interact with mysql program
70yum install -y expect
71
72MYSQL_UPDATE=$(expect -c "
73set timeout 5
74spawn mysql -u root -p
75expect \"Enter password: \"
76send \"${MYSQL_PWD}\r\"
77expect \"mysql>\"
78send \"ALTER USER 'root'@'localhost' IDENTIFIED BY 'MySQL!57';\r\"
79expect \"mysql>\"
80send \"uninstall plugin validate_password;\r\"
81expect \"mysql>\"
82send \"ALTER USER 'root'@'localhost' IDENTIFIED BY '';\r\"
83expect \"mysql>\"
84send \"CREATE USER 'ec2-user'@'localhost';\r\"
85expect \"mysql>\"
86send \"quit;\r\"
87expect eof
88")
89
90echo "$MYSQL_UPDATE"
91
92# remove expect program
93yum remove -y expect

Thanks to expect program and this gist, which helped me to figure out how to interact with MySQL dialogues.

If you just want to install MySQL community server 5.7 simply on your server which supports yum package manager then just save above gist as mysql57_yum.sh and just run it as root user.

Check this to see the number of Linux distributions on which MySQL is supported.

Note: If mysql community server 5.7 is available in yum repo list, then it is highly recommended that you install it directly from there.

I hope sharing this would help others to install MySQL automatically using above script.

Also read: Managing Potential Null Values from Database Rows in Golang