Testing AWS RDS MySQL Multi-AZ Failover Scenario

testing_aws_rds_mysql_multi-az_failover.sh

# https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_RebootInstance.html

# Running mysql commands from app server on private network

DATABASE_NAME="my_database_name"
mysql -D ${DATABASE_NAME} -e '
CREATE TABLE test_inserts (
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  client_timestamp varchar(255) DEFAULT NULL,
  client_serial int(11) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
'

DATABASE_NAME="my_database_name"
CUR_TIME=$(date '+%Y-%m-%d %H:%M:%S')
mysql -D ${DATABASE_NAME} -e "INSERT INTO test_inserts (client_serial, client_timestamp) VALUES ('1', '${CUR_TIME}')"

mysql -D ${DATABASE_NAME} -e "TRUNCATE test_inserts"

LOOP_SERIAL=1
DATABASE_NAME="my_database_name"
PROCESS_TIMEOUT="3"
while true; do
  CUR_TIME=$(date '+%Y-%m-%d %H:%M:%S')
  echo "${CUR_TIME}: ${LOOP_SERIAL} - "
  timeout ${PROCESS_TIMEOUT}s mysql -D ${DATABASE_NAME} -e "INSERT INTO test_inserts (client_serial, client_timestamp) VALUES ('${LOOP_SERIAL}', '${CUR_TIME}');"
  sleep 1
  LOOP_SERIAL=$((LOOP_SERIAL+1))
done

# I had to add a process timeout to the mysql command because the mysql client would get stuck just waiting for who knows what without dropping on it's own.

# During testing it took 10 seconds for the failover to take affect during the forced reboot

aws rds reboot-db-instance \
    --region us-east-1 \
    --profile my_aws_profile_name_here \
    --db-instance-identifier my_rds_db_identifier_here \
    --force-failover