Introduction#

Github shows basics of AWS RDS

  • launch a RDS database instance
  • test connector with credentials from secret manager or config.json
  • read/write iops multi-thread
  • launch a multi-az db and check failover handle

Untitled Diagram

Install DB CLI#

sudo yum install mariadb

connect via terminal

mysql --version
mysql -h $ENDPOINT -P 3306 -u $USER -p

show databases

show databases

create a table

TODO

insert data into a table

TODO

Python Connector#

save database configuration in a config.json or retrieve from secrete manager service

{
"host": "",
"username": "",
"password": "",
"dbname": ""
}

install dependencies

python3 -m pip install -r requirements.txt

requirements.txt

mysql-connector-python==8.0.30
names==0.3.0
boto3==1.26.0

retrieve datbase credentials from secrete manager service

def get_db_credentials_from_sm():
"""
retrieve db credentials from secrete manager
"""
# sm client
secrete_client = boto3.client("secretsmanager", region_name=REGION)
# get secret string
try:
resp = secrete_client.get_secret_value(SecretId=SECRET_ID)
credentials = json.loads(resp["SecretString"])
print(credentials)
except:
print("error retrieving secret manager")
credentials = None
# return
return credentials

create a connector

def get_connect():
"""
test connecting to db endpoint
"""
# get db credentials
credentials = get_db_credentials_from_sm()
# connector
conn = mysql.connector.connect(
host=credentials["host"],
user=credentials["username"],
password=credentials["password"],
database=credentials["dbname"],
)
return conn

then create a table and write/read data using thread

Insert/Read Employees#

create an employee table

def create_table() -> None:
"""
create a rds table
"""
# get connection
conn = get_connect()
# cursor
cur = conn.cursor()
# drop table if exists
drop = "DROP TABLE IF EXISTS employees"
cur.execute(drop)
# create table
employee_table = (
"CREATE TABLE employees ("
" id VARCHAR(36) UNIQUE, "
" name VARCHAR(200) DEFAULT '' NOT NULL, "
" age INT, "
" time TEXT, "
"PRIMARY KEY (id))"
)
cur.execute(employee_table)
cur.close()
conn.close()

write some random data to the table

def write_to_table():
"""
write random data to table
"""
# get connection
conn = get_connect()
conn.autocommit = True
print(f"thread {current_thread().name} connect {conn}")
cursor = conn.cursor()
print(f"thread {current_thread().name} cursor {cursor}")
for k in range(NUM_ROW):
print(f"{current_thread().name} insert item {k}")
stmt_insert = (
"INSERT INTO employees (id, name, age, time) VALUES (%s, %s, %s, %s)"
)
cursor.execute(
stmt_insert,
(
str(uuid.uuid4()),
f"{str(uuid.uuid4())}-{str(uuid.uuid4())}",
30,
datetime.datetime.now().strftime("%Y-%M-%D-%H-%M-%S"),
),
)
if k % CHUNK_SIZE == 0:
print(f"{current_thread().name} commit chunk {k // CHUNK_SIZE}")
conn.commit()
# close connection
cursor.close()
conn.close()

fetch data for the flask web app

def fetch_data():
"""
create a rds table
"""
# table data
employees = []
# init
outputs = []
# connect
conn = conect_db()
# cursor
cur = conn.cursor()
# query
stmt_select = "SELECT id, name, age, time FROM employees ORDER BY id LIMIT 1000"
cur.execute(stmt_select)
# parse
for row in cur.fetchall():
outputs.append(row)
print(row)
# item object
for output in outputs:
employees.append(Item(output[0], output[1], output[2], output[3]))
# close connect
cur.close()
conn.close()
# return
return ItemTable(employees)

Test Failover#

you will see DNS of the database endpoint changes after reboot (click the failover box and rds multi-az assumed enlabed before)

while true; \
do host database-1.cxa01z0gy4dn.ap-northeast-1.rds.amazonaws.com; \
sleep 3; \
done;

Reference#

mysql docs

aws rds reboot

rds multi-az cluster