OpsFire: Not All Databases

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:

RDS MySQL instance: Pending Reboot

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:

  1. Create the read only replica before creating a second database
  2. 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:

No instance types for MySQL 5.7.19
Note the lack of instance types available.

All the instance types for MySQL 5.7.16
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
Pinocchio versioning.

The actual version was probably a prior minor release, the upgrade being applied only on reboot.

OpsFire Ribbon

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.