Your cart is currently empty!
Category: Amazonrds
-
Khóa học miễn phí Amazon RDS – MySQL DBA Tasks nhận dự án làm có lương
Amazon RDS – MySQL DBA Tasks
As with every other database, Amazon RDS MYSQL also needs DBA tasks to fine tune the database and do periodic health checks etc. But as the AWS platform does not allow the shell access to the DB, there are a limited number of DBA tasks that can be performed as compared to the on-premise installation of MySQL. Below is a list of common DBA tasks that can be performed in AWS RDS MySQL database and their descriptions.
Accessing Error Logs
The MySQL error log ( mysql-error.log) file can be viewed by using the Amazon RDS console or by retrieving the log using the Amazon RDS CLI. mysql-error.log is flushed every 5 minutes, and its contents are appended to mysql-error-running.log. The mysql-error-running.log file is then rotated every hour and the hourly files generated during the last 24 hours are retained.
Using RDS Console
Below there are links to two log files described above.
Using CLI
Using CLI the log files are published 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 Long Running Session or Query
Sometimes the DBA needs to kill a long running session or query which is not giving the result quick enough. This DBA task is done by first finding the process ID of the query and then using a RDS function to kill the query. The below commands are the examples.
# get the ID Select * from INFORMATION_SCHEMA.PROCESSLIST #Apply the Kill Function CALL mysql.rds_kill(processID);
Improve Crash recovery Time
We can improve the recovery time from a crash by setting a DB parameter called innodb_file_per_table. We can find this parameter in the RDS console as shown below.
Next we can Search for the parameter name as shown below.
Amazon RDS sets the default value for innodb_file_per_table parameter to 1, which allows you to drop individual InnoDB tables and reclaim storage used by those tables for the DB instance. This speeds up the recovery time from the crash.
Stop and Reboot DB
Stopping a DB, Rebooting it or creating snapshots etc can be done easily through RDS console as shown in the below diagram.
Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc
Khóa học miễn phí Amazon RDS – Oracle DBA Tasks nhận dự án làm có lương
Amazon RDS – Oracle DBA Tasks
As an industry leading database technology, oracle has many in-built features which makes it easy to manage the DBA activities, even in the cloud. The Amazon RDS oracle DB provides access to many stored procedures and functions which can be accessed using the SQL developer client tool. This procedure can be executed using the user ID and password created during the Amazon RDS instance creation. Below are the examples of some of the most frequently used DBA activities.
Killing a Session
Sometimes a long running query or any other DB activity needs to be killed by killing the session. We use the Amazon RDS procedure rdsadmin.rdsadmin_util.kill to kill a session. The following code does that.
# First get the session identifier and the session serial number, select SID, SERIAL#, STATUS from V$SESSION where USERNAME = ''AWSUSER # Next use the procedure begin rdsadmin.rdsadmin_util.kill( sid => sid, serial => serial_number); end; /
Setting the Default Tablespace
The Amazon RDS procedure rdsadmin.rdsadmin_util.alter_default_tablespace can be used to set to the default tablespace for a DB using the following command.
exec rdsadmin.rdsadmin_util.alter_default_tablespace(tablespace_name => ''AWSuser'');
Setting the Database Time Zone
We can use the Amazon RDS procedure rdsadmin.rdsadmin_util.alter_db_time_zone to changes the time zone for the DB.
# Change the time zone of the DB to UTC + 5.30 exec rdsadmin.rdsadmin_util.alter_db_time_zone(p_new_tz => ''+5:30''); # Change the time zone to a specific region exec rdsadmin.rdsadmin_util.alter_db_time_zone(p_new_tz => ''Asia/Kolkata'');
Adding Online Redo Logs
We can use the Amazon RDS procedure rdsadmin.rdsadmin_util.add_logfile to add additional redo logs. The following command adds a log file of size 128MB.
exec rdsadmin.rdsadmin_util.add_logfile(p_size => ''128M'');
Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc