RDS support in Orchestrator? about orchestrator HOT 6 OPEN

We're still feeling out RDS over here - we're in the process of migrating stuff over to AWS so we haven't put a lot of effort into testing things out. Orchestrator did see the node when I put it in to be discovered from its IP address, but had trouble tracing back the replication agreement, as the IP address the replica used to connect to the master isn't routable to things that aren't running in RDS.

I'll stand up an RDS instance again and try to get answers to your questions. I know that SHOW BINLOG EVENTS only needs REPLICATION SLAVE privs which I think might work, but I'll have to try it out.

Thanks for your feedback, all; I'll update this issue again when I have more concrete stuff for everyone to look at.

from orchestrator.

@shlomi-noach @cswingler While orchestrator isn't something high on my priority list, it is something we will be exploring in the future and we are running gh-ost on RDS so we've got over these sorts of hurdles in the past. If you have any RDS issues or questions, I'm happy to be pinged and help out where I can.

from orchestrator.

I think that orchestrator has just not been used on RDS. Recognising any differences which are needed should not be hard, it would just then be an issue of adjusting behaviour where that's needed.

I have not used RDS and I'm not sure if Shlomi has, so probably providing information on errors you see in orchestrator when trying to talk to an RDS instance would help us see what could be done to make things work better. Changing the way to do a "change master" should be easy but you first need to recognise a server is an RDS server so this can be applied at the appropriate moment. I'm not sure how that's best determined.

Looking in the code around here: https://github.com/github/orchestrator/blob/master/go/inst/instance.go#L184 should indicate how this is done to distinguish Oracle MySQL from MariaDB or Percona Server. A similar entry would be needed for RDS.

from orchestrator.

Indeed I'm not using RDS. However, I got some insights from gh-ost on RDS: github/gh-ost#163

From that issue I learned that you can't just issue a STOP SLAVE. And that you don't have SUPER privileges. I'd need to first understand (and someone else will have to do the research, I'm afraid, as this isn't a priority for me) exactly what queries orchestrator uses today are incompatible with RDS. I suspect many of them, including CHANGE MASTER TO.. (do I have to pass credentials?), SHOW BINLOG EVENTS and others.

For such queries I need to know what alternatives exist, in the form of specialized stored routines or external hooks.
Running external hooks is not trivial and would require faith in the user's implementation.

I likewise have little knowledge of the naming/resolve methods in RDS. I'd need more info.

from orchestrator.

I don't think the identification part can be done as for MariaDB/MySQL because RDS does not change @@version_comment. However, the datadir or the existence of one routine could be used to check this:

mysql> select @@datadir;
+----------------+
| @@datadir |
+----------------+
| /rdsdbdata/db/ |
+----------------+
1 row in set (0.16 sec)
mysql> select @@version_comment;
+------------------------------+
| @@version_comment |
+------------------------------+
| MySQL Community Server (GPL) |
+------------------------------+
1 row in set (0.16 sec)
mysql> select count(routine_name) from information_schema.routines where routine_name='rds_kill_query';
+---------------------+
| count(routine_name) |
+---------------------+
| 1 |
+---------------------+
1 row in set (0.17 sec)

from orchestrator.

RDS doesn't allow true root access, but it exposes some of the necessary functionality via stored procedures. E.g.

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/mysql_rds_set_external_master.html

CALL mysql.rds_start_replication;, CALL mysql.rds_stop_replication

CALL mysql.rds_set_external_master (
  host_name
  , host_port
  , replication_user_name
  , replication_user_password
  , mysql_binary_log_file_name
  , mysql_binary_log_file_location
  , ssl_encryption
);

There are certain things that doable, like creating RDS read replica from another RDS instance w/o using RDS replication. Some other things aren't doable like specifying only a subset of tables to be replicated.

They've expanded the set of available operations over time, so it's possible if someone gets a list of needed operations, they'd expose them one day.

from orchestrator.