Over the weekend we consolidated some of our databases onto a single RDS instance, as a dual effort to reduce costs as well as to allow for cross database joins.
Our primary RDS instance has a read only replica, so imagine my surprise when the next morning I see this:
primadmin@mysql_prod > show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| prod_db_1 |
| prod_db_2 |
| innodb |
| mysql |
| performance_schema |
| sys |
| tmp |
+--------------------+
8 rows in set (0.01 sec)
---
readadmin@mysql_prod-ro ]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| prod_db_1 |
+--------------------+
2 rows in set (0.00 sec)
Looks like something, somewhere is missing from how we added prod_db_2 to mysql_prod last night. I took a look at the read only replica properties to verify its parameter group and noticed this:
Hrm 🤔
Since it's the middle of the day, I needed to schedule a reboot rather than just rebooting the instance. So we're going to cycle back around to this later.
While I await my reboot window, I decide to rule out two other possibilities and see if there are any "suprises" for how AWS implements MySQL v5.7.19. Specifically I want to know if the read only replica will pick up a second database in both of the following scenarios, as opposed to only grabbing the primary database:
- Create the read only replica before creating a second database
- Create the read only replica after creating a second database
As a quick note about the instance creation process itself: I noticed I couldn't create a new instance with the same version of MySQL that our production instance is running:
Note the lack of instance types available.
And here are all the instance types.
So to even start my test, I needed to create an instance with 5.7.16, then upgrade to 5.7.19. Lovely.
Skipping past that part of today's mini-🔥, after spinning up the test instance I went through the above test cases. For brevity, the steps for test case #2 are shown below:
primary mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| innodb |
| mysql |
| performance_schema |
| sys |
| testingonetwothree |
+--------------------+
6 rows in set (0.00 sec)
primary mysql> create database iambluedabudee;
Query OK, 1 row affected (0.01 sec)
primary mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| iambluedabudee |
| innodb |
| mysql |
| performance_schema |
| sys |
| testingonetwothree |
+--------------------+
7 rows in set (0.00 sec)
Moment of truth: did the read only replica pick up the second database?
replica mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| iambluedabudee |
| innodb |
| mysql |
| performance_schema |
| sys |
| testingonetwothree |
+--------------------+
7 rows in set (0.00 sec)
Brilliant 😄 It turns out that in both cases (#1 not being documented above) that the replica picked up the second database.
Upon the arrival of the reboot window I was able to reboot the production replica without an issue and found that both databases were now in the replica:
readadmin@mysql_prod-ro ]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| prod_db_1 |
| prod_db_2 |
+--------------------+
3 rows in set (0.00 sec)
Something else that occurred to me to check, that I neglected to run before the reboot, is checking the version in console like so:
readadmin@mysql_prod-ro ]> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.7.19 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1 |
| version | 5.7.19-log |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+------------------------------+
8 rows in set (0.00 sec)
It's entirely probable, nay likely, that the version being reported in the AWS console (below) was incorrect:
Pinocchio versioning.
The actual version was probably a prior minor release, the upgrade being applied only on reboot.
Documented on my frequently used assets page.
Source for header: Cloud database image from Iconfinder, firey background from burnt embers created by Shutterstock user Bernatskaya Oxana.