最後更新: 2023-02-01

目錄

 

---

Automated backups

 

Your DB instance must be in the ACTIVE state for automated backups to occur.

During the automatic backup window, storage I/O might be suspended briefly while the backup process initializes
(typically under a few seconds).

For MariaDB, MySQL, Oracle, and PostgreSQL, I/O activity isn't suspended on your primary during backup for Multi-AZ deployments,
because the backup is taken from the standby.

Automated backups might occasionally be skipped if the DB instance has a heavy workload at the time a backup is supposed to start.
(mazon RDS assigns a default 30-minute backup window.)

For the MySQL DB engine, automated backups are only supported for the InnoDB storage engine.
Use of these features with other MySQL storage engines, including MyISAM,
can lead to unreliable behavior while restoring from backups.

mysql> FLUSH TABLES myisam_table, myisam_table2 WITH READ LOCK;
mysql> UNLOCK TABLES;

Or

ALTER TABLE table_name ENGINE=innodb, ALGORITHM=COPY;

Disabling automated backups

Disabling automatic backups for a DB instance deletes all existing automated backups for the instance.

If you disable and then re-enable automated backups, you can restore starting only from the time you re-enabled automated backups.

We highly discourage disabling automated backups because it disables point-in-time recovery.

 

---

Delete a DB instance

 

If you don't choose Retain automated backups when you delete a DB instance,
all automated backups are deleted with the DB instance.
(Manual snapshots are not deleted.)

Backup retention

Backups can be retained longer than the backup retention period if a DB instance has been stopped.
RDS doesn't include time spent in the stopped state when the backup retention window is calculated.

Final snapshots

Final snapshots are independent of retained automated backups.
We strongly suggest that you take a final snapshot even if you retain automated backups,
because the retained automated backups eventually expire. The final snapshot doesn't expire.

 

---

Using AWS Backup to manage automated backups

 

Backups managed by AWS Backup are considered manual DB snapshots,

but don't count toward the DB snapshot quota for RDS.

 

---

Public snapshots

 

You can also share an unencrypted manual snapshot as publicn

You aren't billed for the backup storage of public snapshots owned by other accounts.
(You're billed only for snapshots that you own.)

You can delete only the public snapshots that you own.

 

---

DB 's replication method

 

• MySQL and MariaDB: Logical replication.
• PostgreSQL: Physical replication.
• SQL Server: Physical replication.

 

 

---

DB with function/procedure

 

Import db.sql 時出現 Error

ERROR 1419 (HY000) at line 877: You do not have the SUPER privilege and binary logging is enabled
(you *might* want to use the less safe log_bin_trust_function_creators variable)

原因:

使用 multi az 時沒有 root, 獲得的 username 是 admin

Fix

啟用: log_bin_trust_function_creators

This variable applies when binary logging is enabled.

It controls whether stored function creators can be trusted not to create stored functions

that causes unsafe events to be written to the binary log.

If set to 0 (the default), users are not permitted to create or alter stored functions

unless they have the SUPER privilege in addition to the CREATE ROUTINE or ALTER ROUTINE privilege.

A setting of 0 also enforces the restriction that a function must be declared with the DETERMINISTIC characteristic,

or with the READS SQL DATA or NO SQL characteristic.

If the variable is set to 1, MySQL does not enforce these restrictions on stored function creation.

Step

1. Open the RDS web console
2. Open the "Parameter Groups" tab
3. Create a new Parameter Group
4. Select the just created Parameter Group and issue "Edit Parameters"
5. Look for the parameter "log_bin_trust_function_creators" and set its value to ‘1’
6. Save the changes.
7. Open the "Instances" tab. Expand your MySQL instance and issue the "Instance Action" named “Modify”
8. Select the just created Parameter Group and enable "Apply Immediately"
9. Open the “Instances” tab. Expand your MySQL instance and issue the “Instance Action” named “Reboot”

仍有 Error ?!

ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation

Fix: Removing DEFINER from MySQL Dump

mysqldump 生成的 .sql 會有

/*!50003 CREATE*/ 
/*!50017 DEFINER=`MyDBName`@`%`*/ 
/*!50003 TRIGGER `MyDBName`.`Queue_BEFORE_UPDATE` BEFORE UPDATE ON `Queue` FOR EACH ROW

CLI

sed -e 's/DEFINER=[^*]*\*/\*/' orig.sql > new.sql

/*!50003 CREATE*/ 
/*!50017 */
/*!50003 TRIGGER `MyDBName`.`Queue_BEFORE_UPDATE` BEFORE UPDATE ON `Queue` FOR EACH ROW

 

---

Multi-AZ instance vs Multi-AZ DB cluster

 

Multi-AZ DB instance deployment

One standby (standby replica 是不能 readwrite 的 !!)

Multi-AZ is Yes
Role: Primary / Instance

Replication

• MySQL use the Amazon failover technology (block-level replication)
• MSSQL use SQL Server Database Mirroring (DBM) or Always On Availability Groups (AGs)

Failover

Failover times are typically 60–120 seconds.

(You can force a failover manually when you reboot a DB instance.)

The failover mechanism automatically changes the Domain Name System (DNS) record of the DB instance to point to the standby DB instance.

Set up DB event subscriptions to notify you by email or SMS that a failover has been initiated.

CLI

aws rds reboot-db-instance \
    --db-instance-identifier mydbinstance

# reboot with failover

aws rds reboot-db-instance \
    --db-instance-identifier mydbinstance \
    --force-failover

Multi-AZ DB cluster deployment

Two standby(reader)

writer x 1, reader x 2

cluster-level: Role is Multi-AZ DB cluster
instance-level: 
  Role: Writer instance / Reader instance
  Multi-AZ: 3 Zones

Replication

• It using the DB engine's native replication capabilities

 * Single-AZ DB / Multi-AZ DB 不可以直接轉成 Multi-AZ DB cluster

 

---

價錢比較

 

EC2

• t3.small            $0.0292
• t3.medium        $0.0584

RDS

• db.t3.small        $0.0572
• db.t3.medium    $0.1144

 * 它沒有 saving plan, 是用 Reserved Instances 的

RDS Multi-AZ (One Standby)

• db.t3.small        $0.1144
• db.t3.medium    $0.2288

Multi-AZ (readable standbys) = Multi-AZ DB cluster deployments

 * 最平的 Readable standbys 要用 m5d.large

RDS(db.m5d.large) # 2C(3.1 GHz Intel Xeon), 8 GiB

Single-AZ            $0.319    # 自己砌: $0.155
Multi-AZ             $0.638    # 自己砌: $0.31
Readable standbys    $0.884    # 自己砌: $0.465

* 自己砌的話, 可能選擇用 t3.large $0.1168, t3.micro $0.0146 (2C, 1GiB)

Reserved Instances

• Apply to usage of any size in the same instance family (M5, T3, R5, etc.)
• don't cover storage or I/O costs

All Upfront@1yr

• db.t3.small        $0.042 (27%)
• db.t3.medium    $0.084 (26%)

 

---

Check RDS Storage Usage

 

RDS > Databases > YOUR_DB > Configuration     # Check Size

RDS > Databases > YOUR_DB > Monitoring > "Free Storage Space (MB)"