AWS - RDS

 

 

 


replica

 

standby replica

The RDS console shows the Availability Zone of the standby replica (called the secondary AZ).

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

read replica

Updates made to the primary DB instance are asynchronously copied to the read replica.

 


DB 's replication method

 

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

 


RDS multi az to single az

 

 


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