Setup Rehat EC2#

Create a key pair from EC2 console. Create a EC2 with Rehat 9 AMI. Let remote access. Setup security group and then remote access.

ssh -i "key.pem" ec2-user@ec2-1-2-3-4.ap-southeast-1.compute.amazonaws.com

Setup SSM agent for RHEL 8.

sudo dnf install -y https://s3.amazonaws.com/ec2-downloads-windows/SSMAgent/latest/linux_amd64/amazon-ssm-agent.rpm

Setup MySQL on Redhat#

Follow this to setup. First, download adding the MySQL repository.

wget https://dev.mysql.com/get/mysql84-community-release-el9-1.noarch.rpm
sudo dnf localinstall mysql84-community-release-el9-1.noarch.rpm

Double check the added repositories.

sudo dnf repolist enabled | grep mysql.*-community
sudo dnf repolist all | grep mysql

To install the latest release from a specific series other than the latest LTS series, disable the bug subrepository for the latest LTS series and enable the subrepository for the specific series before running the installation command.

sudo dnf config-manager --disable mysql-8.4-lts-community
sudo dnf config-manager --enable mysql80-community
yum repolist enabled | grep mysq
sudo yum module disable mysql

Finally, let install MySQL.

sudo dnf install mysql-community-server
systemctl start mysqld
systemctl status mysqld

Now let login the MySQL server and change admin password.

sudo grep 'temporary password' /var/log/mysqld.log
mysql -uroot -p
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';

Connect to MySQL#

Let connect to Amazon RDS MySQL.

mysql -h localhost -P 3306 -u root -p

Show databases and tables.

show databases;
show tables;
use demo;

Create a book table.

CREATE TABLE IF NOT EXISTS book (
    id int auto_increment primary key,
    author text,
    title text,
    amazon text,
    image text);

Insert some records.

INSERT INTO book(author, title, amazon, image) VALUES ('Hai Tran', 'Deep Learning', '', 'hello.jpg');

Create Database User#

Let check current user in the database.

select user from mysql.user;

Let create a developer user in the database and grant privileges.

CREATE USER IF NOT EXISTS 'dev'@'localhost' IDENTIFIED by 'Admin@2024';
GRANT ALL PRIVILEGES ON * . * TO 'dev'@'localhost';
FLUSH PRIVILEGES;

Grant access only to database demo.

GRANT ALL PRIVILEGES on 'demo'.*to 'dev'@'localhost';

Then finally login as a demo user and check.

mysql -h localhost -P 3306 -u dev -p Admin@2024;

Sample Data#

Let clone this repository test_db

git clone https://github.com/datacharmer/test_db.git

Let load employees.sql for either localhost or RDS as the following command.

mysql -h localhost -P 3306 -u root -p demo < employees.sql

If we want to install with two large partitioned tables, run.

mysql -h localhost -P 3306 -u root -p demo < employees_partitioned.sql

Testing after setup the tables.

mysql -t < test_employees_md5.sql
# OR
mysql -t < test_employees_sha.sql

Golang App#

Here is example of config.go file

package main


const (
  HOST="localhost"
  PORT="3306"
  USERNAME="root"
  PASSWORD="pass"
  DBNAME="employees"
)

Let create a first hello world example with a home page and an employees page.

package main


import (
	"database/sql"
	"encoding/json"
	"fmt"
	"log"
	"net/http"
	"os"
	"time"


	"github.com/go-sql-driver/mysql"
	_ "github.com/go-sql-driver/mysql"
)


type Employee struct {
	Id        int
	BirthDate string
	FirstName string
	LastName  string
	Gender    string
	HireDate  string
}


func GetEmployees(numEmployee int) []Employee {


	var employees []Employee
	var employee Employee


	// database configuration
	config := mysql.Config{
		User:                 USERNAME,
		Passwd:               PASSWORD,
		Net:                  "tcp",
		Addr:                 fmt.Sprintf("%s:%s", HOST, PORT),
		DBName:               DBNAME,
		AllowNativePasswords: true,
	}


	// create connection to database
	db, error := sql.Open("mysql", config.FormatDSN())


	if error != nil {
		log.Fatal("unable to use data source name", error)
	}


	// query employee table
	rows, error := db.Query("SELECT * FROM employees limit ?", numEmployee)


	if error != nil {
		log.Fatal("unable to execute query", error)
	}


	// parse row response
	for rows.Next() {
		error := rows.Scan(&employee.Id, &employee.BirthDate, &employee.FirstName, &employee.LastName, &employee.Gender, &employee.HireDate)


		if error != nil {
			log.Fatal("unable to scan row", error)
		}
		fmt.Println(employee)


		// update employees array
		employees = append(employees, employee)
	}


	// return
	return employees
}


func main() {


	// create a http multiplexer
	mux := http.NewServeMux()


	// render a home page
	mux.HandleFunc("/", func(w http.ResponseWriter, r *http.Request) {
		content, error := os.ReadFile("./static/index.html")
		if error != nil {
			fmt.Println("unable to read file", error)
		}
		w.Write(content)
	})


	// get employees
	mux.HandleFunc("/employees", func(w http.ResponseWriter, r *http.Request) {


		employees := GetEmployees(20)


		data, error := json.Marshal(employees)
		if error != nil {
			fmt.Println("unable to marshal json", error)
		}
		w.Header().Set("Content-Type", "application/json")
		w.Write(data)
	})


	// create a web server
	server := &http.Server{
		Addr:           ":3000",
		Handler:        mux,
		ReadTimeout:    30 * time.Second,
		WriteTimeout:   30 * time.Second,
		MaxHeaderBytes: 1 << 20,
	}


	// enable logging
	log.Fatal(server.ListenAndServe())
}

Setup Atop#

Follow How do I configure the ATOP Monitoring and SAR monitoring tools for my EC2 instance running Amazon Linux, RHEL, CentOS, or Ubuntu? to setup atop tool. Install the EPEL release package for RHEL 8.

sudo dnf -y install https://dl.fedoraproject.org/pub/epel/epel-release-latest-8.noarch.rpm -y

Install the monitoring tools.

sudo dnf -y install sysstat atop --enablerepo=epel

Setup config file interval 5ms

sudo edit /etc/sysconfig/atop

Content sample

LOGOPTS=""
LOGINTERVAL=5
LOGGENERATIONS=28
LOGPATH=/var/log/atop

Start as a service.

sudo systemctl start atop
sudo systemctl status atop

Record historical data and check log.

atop -r /var/log/atop/abc_log

Troubleshooting#

Get public ip of an EC2 instance.

curl http://checkip.amazonaws.com

Enable log_bin_trust_function_creators from parameter_group.

mysql -u USERNAME -p
set global log_bin_trust_function_creators=1;

Modify the ENGINE in sql.

CREATE TABLE film_text (
  film_id SMALLINT NOT NULL,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  PRIMARY KEY  (film_id),
  FULLTEXT KEY idx_title_description (title,description)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Increase max-allowed-packet in parameter_group for aurora

max-allowed-packet

Show the original values of a parameter.

SHOW GLOBAL VARIABLES where Variable_Name='innodb_buffer_pool_size';
SHOW GLOBAL VARIABLES where Variable_Name='max-allowed-packet';

Check Linux distribution.

ls /etc/os-release

IMDSv1 Setup#

Let update IMDSv2.

aws ec2 modify-instance-metadata-options \
  --instance-id i-0b3f2ae36eaeb011d \
  --http-tokens required \
  --http-endpoint enabled

Reference#