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
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.30names==0.3.0boto3==1.26.0
retrieve datbase credentials from secrete manager service
def get_db_credentials_from_sm():"""retrieve db credentials from secrete manager"""# sm clientsecrete_client = boto3.client("secretsmanager", region_name=REGION)# get secret stringtry:resp = secrete_client.get_secret_value(SecretId=SECRET_ID)credentials = json.loads(resp["SecretString"])print(credentials)except:print("error retrieving secret manager")credentials = None# returnreturn credentials
create a connector
def get_connect():"""test connecting to db endpoint"""# get db credentialscredentials = get_db_credentials_from_sm()# connectorconn = 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 connectionconn = get_connect()# cursorcur = conn.cursor()# drop table if existsdrop = "DROP TABLE IF EXISTS employees"cur.execute(drop)# create tableemployee_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 connectionconn = get_connect()conn.autocommit = Trueprint(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 connectioncursor.close()conn.close()
fetch data for the flask web app
def fetch_data():"""create a rds table"""# table dataemployees = []# initoutputs = []# connectconn = conect_db()# cursorcur = conn.cursor()# querystmt_select = "SELECT id, name, age, time FROM employees ORDER BY id LIMIT 1000"cur.execute(stmt_select)# parsefor row in cur.fetchall():outputs.append(row)print(row)# item objectfor output in outputs:employees.append(Item(output[0], output[1], output[2], output[3]))# close connectcur.close()conn.close()# returnreturn 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;