aws_rds_mysql_workaround

aws_rds_mysql_workaround

Managing the global status history:
          CALL mysql.rds_enable_gsh_collector;
          CALL mysql.rds_enable_gsh_collector;
          CALL mysql.rds_set_gsh_collector(intervalPeriod); (Specifies the interval, in minutes)
          CALL mysql.rds_disable_gsh_collector;
          CALL mysql.rds_enable_gsh_rotation;
          CALL mysql.rds_set_gsh_rotation(intervalPeriod); (Specifies the interval, in days) Default value is 7days
          CALL mysql.rds_disable_gsh_rotation;
          CALL mysql.rds_collect_global_status_history;
          CALL mysql.rds_rotate_global_status_history; (mysql.global_status_history table to mysql.global_status_history_old on demand)
https://github.com/awsdocs/amazon-rds-user-guide/blob/master/doc_source/Appendix.MySQL.CommonDBATasks.md#Appendix.MySQL.CommonDBATasks.GoSH

InnoDB cache warming
          CALL mysql.rds_innodb_buffer_pool_dump_now();
          CALL mysql.rds_innodb_buffer_pool_load_abort();
          CALL mysql.rds_innodb_buffer_pool_load_now();

Terminating a session or query:
          CALL mysql.rds_kill(thread ID of that connection); SHOW PROCESSLIST
          CALL mysql.rds_kill_query(queryID); INFORMATION_SCHEMA PROCESSLIST for queryID

basics user alter and grants:
          ALTER USER 'encrypted_user'@'%' REQUIRE SSL;            
          GRANT USAGE ON *.* TO 'encrypted_user'@'%' REQUIRE SSL;            

To determine the file size of a table
           SELECT TABLE_SCHEMA, TABLE_NAME,round(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) As "Approximate size (MB)" FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema');

basics sql dump backup:
          mysqldump -h RDS instance endpoint \
              -u user \
              -p password \
              --port=3306 \
              --single-transaction \
              --routines \
              --triggers \
              --databases  database database2 \
              --compress  \
              --compact | mysql \
                  -h MySQL host \
                  -u master user \
                  -p password \
                  --port 3306
basics file dump backup:        
              sudo mysqldump \
                  --tab=<target_directory> \
                  --fields-terminated-by ',' \
                  --fields-enclosed-by '"' \
                  --lines-terminated-by 0x0d0a \
                  <database_name> \
                  --master-data=2 \
                  --single-transaction \
                  --order-by-primary \
                  -p <password>
              mysql> LOAD DATA LOCAL INFILE 'table1.txt' INTO TABLE table1 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '0x0d0a';

Creating a DB Instance Running the MySQL Database Engine
              aws rds create-db-instance --db-instance-identifier mydbinstance --db-instance-class db.m1.small --engine MySQL --allocated-storage 20 --master-username masterawsuser  --master-user-password masteruserpassword --backup-retention-period 1

Upgrading a MySQL DB Snapshot
              aws rds modify-db-snapshot --db-snapshot-identifier <mydbsnapshot> --engine-version <new_version>

Modifying a DB Instance Running the MySQL Database Engine
      aws rds modify-db-instance --db-instance-identifier mydbinstance --backup-retention-period 1 --no-auto-minor-version-upgrade --no-apply-immediately
      aws rds modify-db-instance --db-instance-identifier mydbinstance --backup-retention-period 1 --no-auto-minor-version-upgrade --apply-immediately

Rebooting a DB Instance
      aws rds reboot-db-instance --db-instance-identifier mydbinstance
To force a failover from one AZ to the other
      aws rds reboot-db-instance --db-instance-identifier mydbinstance --force-failover

Deleting a DB Instance
      aws rds delete-db-instance --db-instance-identifier mydbinstance  --final-db-snapshot-identifier mydbinstancefinalsnapshot --delete-automated-backups
      aws rds delete-db-instance --db-instance-identifier mydbinstance --skip-final-snapshot --no-delete-automated-backups

Maintaining a DB Instance
      aws rds describe-pending-maintenance-actions --resource-identifier arn:aws:rds:us-west-2:001234567890:db:mysql-db
      aws rds describe-pending-maintenance-actions --filters Name=db-instance-id,Values=sample-instance1,sample-instance2
      aws rds apply-pending-maintenance-action --resource-identifier arn:aws:rds:us-west-2:001234567890:db:mysql-db --apply-action system-update --opt-in-type immediate
      aws rds modify-db-instance --db-instance-identifier mydbinstance --preferred-maintenance-window Tue:04:00-Tue:04:30

Working with DB Security Groups (EC2-Classic Platform)
      aws rds describe-db-security-groups
      aws rds describe-db-security-groups --db-security-group-name mydbsecuritygroup
      aws rds create-db-security-group --db-security-group-name mydbsecuritygroup --db-security-group-description "My new security group"
      aws rds authorize-db-security-group-ingress --db-security-group-name mydbsecuritygroup --cidrip 192.168.1.10/27
      aws rds authorize-db-security-group-ingress --db-security-group-name default  --ec2-security-group-name myec2group --ec2-security-group-owner-id 987654321021
      aws rds revoke-db-security-group-ingress --db-security-group-name mydbsecuritygroup --cidrip 192.168.1.1/27

Working with Option Groups
      aws rds describe-option-groups
      aws rds describe-option-groups --option-group-name testoptiongroup
      aws rds create-option-group --option-group-name testoptiongroup --engine-name mysql --major-engine-version 5.6 --option-group-description "Test option group"
      aws rds create-option-group --option-group-name testoptiongroup --engine-name mysql --major-engine-version 5.7 --option-group-description "Test option group"
Making a Copy of an Option Group
      aws rds copy-option-group --source-option-group-identifier arn:aws:rds:us-west-2:123456789012:og:my-remote-option-group --target-option-group-identifier new-local-option-group --target-option-group-description "Option group 2"
Adding an Option to an Option Group
      aws rds describe-option-groups
      aws rds describe-option-groups --option-group-name testoptiongroup
      aws rds add-option-to-option-group --option-group-name testoptiongroup  --options OptionName=MARIADB_AUDIT_PLUGIN --apply-immediately
      aws rds add-option-to-option-group --option-group-name testoptiongroup  --options OptionName=MEMCACHED --apply-immediately
      aws rds describe-option-groups
      aws rds describe-option-groups --option-group-name testoptiongroup
Modifying an Option Setting
      aws rds add-option-to-option-group --option-group-name testoptiongroup --options OptionName=MARIADB_AUDIT_PLUGIN --apply-immediately
      aws rds add-option-to-option-group --option-group-name testoptiongroup --options OptionName=MEMCACHED --apply-immediately
Removing an Option from an Option Group
      aws rds remove-option-from-option-group --option-group-name testoptiongroup --options MARIADB_AUDIT_PLUGIN --apply-immediately
      aws rds remove-option-from-option-group --option-group-name testoptiongroup --options MEMCACHED --apply-immediately

MySQL Database Log Files
  Accessing MySQL Error Logs
     mysql-error.log: is flushed every 5 minutes -> mysql-error-running.log rotated every hourly and last 24 hours files are retained. 
  Accessing the MySQL Slow Query and General Logs
      slow_query_log :1/0 (default 0) (1:enable,0:disable)
      general_log:1/0 (default 0)(1:enable,0:disable)
      long_query_time:(seconds)(default 10 seconds)
      log_output: TABLE/FILE/NONE (default TABLE)(table names : mysql.general_log,mysql.slow_log) (NONE: Disable logging) 
      log_queries_not_using_indexes:1/0(1:enable,0:disable)
      tables rotated : mysql.general_log ->mysql.general_log_backup and mysql.slow_log->mysql.slow_log_backup
      CALL mysql.rds_rotate_general_log; (for table) (need to run this twice to reclaim full space)
      CALL mysql.rds_rotate_slow_log; (for table) (need to run this twice to reclaim full space)
  To publish MySQL logs to CloudWatch Logs using the console
      aws rds modify-db-instance --db-instance-identifier mydbinstance --cloudwatch-logs-export-configuration '{"EnableLogTypes":["audit","error","general","slowquery"]}'
      aws rds create-db-instance --db-instance-identifier mydbinstance --enable-cloudwatch-logs-exports '["audit","error","general","slowquery"]' --db-instance-class db.m4.large --engine MySQL

Binary Logging Format
    row-based, statement-based, and mixed (default:mixed)
    binlog_format:(ROW, STATEMENT, or MIXED)
    Accessing MySQL Binary Logs
        SHOW BINARY LOGS (list of the available logs)
        mysqlbinlog --read-from-remote-server --host=MySQL56Instance1.cg034hpkmmjt.region.rds.amazonaws.com --port=3306 --user ReplUser --password --raw --result-file=/tmp/ binlog.00098
            (--stop-never for all files)
        Managing additional configuration (for example, binlog file retention)
        CALL mysql.rds_show_configuration;
        CALL mysql.rds_set_configuration(parameter name,parameter value); (call mysql.rds_set_configuration('binlog retention hours', 24);)
        CALL mysql.rds_show_configuration;
Replication:
    CALL mysql.rds_stop_replication;
    CALL mysql.rds_start_replication;
    CALL mysql.rds_skip_repl_error;

Working with DB Parameter Groups
    DB instance uses a default DB parameter group(default:mysql-5-6 or default:mysql-5-7 based on engine, compute class)
    You can change character set or collation values for an existing database 
          ALTER DATABASE database_name CHARACTER SET character_set_name COLLATE collation;
    Creating a DB Parameter Group
          aws rds describe-db-engine-versions --query "DBEngineVersions[].DBParameterGroupFamily"				
          aws rds create-db-parameter-group --db-parameter-group-name mydbparametergroup --db-parameter-group-family MySQL5.6 --description "My new parameter group"
    Modifying Parameters in a DB Parameter Group
          aws rds modify-db-parameter-group --db-parameter-group-name mydbparametergroup --parameters "ParameterName=max_connections,ParameterValue=250,ApplyMethod=immediate" "ParameterName=max_allowed_packet,ParameterValue=1024,ApplyMethod=immediate"
    Copying a DB Parameter Group
          aws rds copy-db-parameter-group --source-db-parameter-group-identifier mygroup1 --target-db-parameter-group-identifier mygroup2 --target-db-parameter-group-description "DB parameter group 2"
    Listing DB Parameter Groups
          aws rds describe-db-parameter-groups
          aws rds describe-db-parameter-groups --db-parameter-group-name mydbparamgroup1
    Viewing Parameter Values for a DB Parameter Group
          aws rds describe-db-parameters --db-parameter-group-name mydbparametergroup