Amazon RDS – MySQL DBA Tasks

Spread the love

As with each different database, Amazon RDS MYSQL additionally wants DBA duties to wonderful tune the database and do periodic well being checks and many others. However because the AWS platform doesn’t permit the shell entry to the DB, there are a restricted variety of DBA duties that may be carried out as in comparison with the on-premise set up of MySQL. Under is an inventory of frequent DBA duties that may be carried out in AWS RDS MySQL database and their descriptions.

Accessing Error Logs

The MySQL error log ( mysql-error.log) file could be seen through the use of the Amazon RDS console or by retrieving the log utilizing the Amazon RDS CLI. mysql-error.log is flushed each 5 minutes, and its contents are appended to mysql-error-running.log. The mysql-error-running.log file is then rotated each hour and the hourly information generated over the last 24 hours are retained.

Utilizing RDS Console

Under there are hyperlinks to 2 log information described above.

 mysql_rds_log1.JPG

Utilizing CLI

Utilizing CLI the log information are revealed to CloudWatch Logs as a JSON Object.

aws rds modify-db-instance 
    --db-instance-identifier mydbinstance 
    --cloudwatch-logs-export-configuration '{"EnableLogTypes":["audit","error","general","slowquery"]}' 
    --apply-immediately

 

Killing a Lengthy Operating Session or Question

Generally the DBA must kill an extended operating session or question which isn’t giving the end result fast sufficient. This DBA activity is finished by first discovering the method ID of the question after which utilizing a RDS operate to kill the question. The beneath instructions are the examples.

# get the ID 
Choose * from INFORMATION_SCHEMA.PROCESSLIST 
#Apply the Kill Operate 
CALL mysql.rds_kill(processID);

 

Enhance Crash restoration Time

We will enhance the restoration time from a crash by setting a DB parameter referred to as innodb_file_per_table. We will discover this parameter within the RDS console as proven beneath.

 mysql_DBA_parameters.JPG

Subsequent we are able to Seek for the parameter title as proven beneath.

mysql_innodb_file_param.JPG

Amazon RDS units the default worth for innodb_file_per_table parameter to 1, which lets you drop particular person InnoDB tables and reclaim storage utilized by these tables for the DB occasion. This hurries up the restoration time from the crash.

Cease and Reboot DB

Stopping a DB, Rebooting it or creating snapshots and many others could be carried out simply via RDS console as proven within the beneath diagram.

mysql_db_stop_reboot.JPG