[AuroraDB]: How to determine to which DB Instance we connected to

You can determine which DB instance in an Aurora DB cluster that a connection is connected to by checking the innodb_read_only global variable, as shown in the following example.

SHOW GLOBAL VARIABLES LIKE 'innodb_read_only';
            

The innodb_read_only variable will be set to ON if you are connected to an Aurora Replica and OFF if you are connected to the primary instance.

This can be helpful if you want to add logic to your application code to balance the workload or to ensure that a write operation is using the correct connection.

[root@dbversity.com ec2-user]# mysql -h auroradb.cluster-dbversity.us-east-1-beta.rds.amazonaws.com -P 3306 -u myawsuser -p”MyPassw0rd” -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4325
Server version: 5.6.10 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>
mysql>
mysql> SHOW GLOBAL VARIABLES LIKE ‘innodb_read_only’;
+——————+——-+
| Variable_name | Value |
+——————+——-+
| innodb_read_only | OFF |
+——————+——-+
1 row in set (0.01 sec)

mysql> Bye
[root@dbversity.com ec2-user]# mysql -h my-replica1.dbversity.us-east-1-beta.rds.amazonaws.com -P 3306 -u myawsuser -p”MyPassw0rd” -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4330
Server version: 5.6.10 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> SHOW GLOBAL VARIABLES LIKE ‘innodb_read_only’;
+——————+——-+
| Variable_name | Value |
+——————+——-+
| innodb_read_only | ON |
+——————+——-+
1 row in set (0.00 sec)

mysql> Bye
[root@dbversity.com ec2-user]# mysql -h my-replica2.dbversity.us-east-1-beta.rds.amazonaws.com -P 3306 -u myawsuser -p”MyPassw0rd” -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4332
Server version: 5.6.10 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>
mysql> SHOW GLOBAL VARIABLES LIKE ‘innodb_read_only’;
+——————+——-+
| Variable_name | Value |
+——————+——-+
| innodb_read_only | ON |
+——————+——-+
1 row in set (0.00 sec)

mysql>