AWS creating a read-only replica that can be modified for reporting scripts

user_19454739 0
Viewed 129

I'm looking at creating a read-only replica inside our cluster in AWS that can have nightly scripts run on it to be used as a database that we will attach a front-end dashboard to for KPI and reporting type metrics. This database will need to be modified with scripts that will write information out of it for these metrics into a new table inside of this read-only replica.

ENGINE: 5.7.mysql_aurora.2.07.2 So far I've been able to:

  1. "Add read" inside the cluster, creating a read only database.
  2. Create a custom parameter group, modify field read_only=0 and reboot databases to sync with the new parameter group.
  3. After issue (see below) tried to create a custom cluster parameter group with read_only=0. No Change.
  4. Tried connecting from both host URL's, and have successful connections with both the endpoint inside the read-only replica connectivity&status, as well as the host URL from the cluster with the .ro with the same result.

The issue is that when I go to test and write a new record to our DB it is giving me the generic: ERROR 1290: 1290: The MySQL server is running with the --read-only option so it cannot execute this statement

amazon-aurora amazon-web-services architecture mysql
1 Answers

Issue resolved:

query sql variables to see iff innodb_read_only=1 (assuming you're running a single master instance, not a multi-master; as well as an engine that starts with it =1), shut down server, create file to alter server variables on boot changing innodb_read_only=0, then reboot.

user_194547390
Related