Tracking issue: support Amazon RDS

[shlomi-noach]

EDIT: Documentation for RDS is available

This is a tracking issue to discuss all things RDS and what it takes to be able to run gh-ost on RDS.

[shlomi-noach]

First limitation is the SUPER privilege. This is defined in #132 and addressed in #156

When you know you are using RBR (Row Based Replication), you can specify --assume-rbr and this will skip the step of STOP SLAVE; START SLAVE which requires SUPER privilege.

[shlomi-noach]

I should note that I am not using RDS at the moment; will need community feedback for production issues.

[pbitty]

I am using RDS and have been trying out gh-ost with success so far. I am happy to give feedback and contribute where possible.

I've succeeded in running a test with --test-on-replica (using the workaround described in #162) and table checksums matched. And I've done a migration with --migrate-on-replica and did not have any significant issues.

[pbitty]

As far as I can tell, the only other blocker is the STOP SLAVE command that is issued in --test-on-replica mode (to be solved via #162).

In RDS, it is possible to start/stop replication with stored procedures they provide, ie: CALL mysql.rds_stop_replication. So we could support automatic replication stop/start.

The only other thing that is not supported (that I can tell) is setting global variables, so we wouldn't be able to support auto-configuring RBR from mysql itself. It would be possible to do it via the RDS API, but there are non-trivial concerns since multiple RDS instances can share the same configuration, so setting binlog_format=ROW on a shared configuration could change it on multiple RDS instances at the same time. This could be left as a manual step and included in a doc, or a plugin from #62.

[shlomi-noach]

Thank you! You are now assigned the role of Chief RDS Officer at Gh-Ost Corp.

Changing a statement replica to row can certainly be delegated to the user, no need to trouble gh-ost on this if this is an issue.

[pbitty]

Ha, thanks!

I am working on documenting the steps required for using gh-ost in our RDS environment. I should be able to make it generic enough to help others. When I do, I can submit a PR if you are interested. Hopefully we can get other RDS users to provide some feedback.

[shlomi-noach]

Hooks have been merged, though not yet released.

[pbitty]

Great! I will try them out with our next --test-on-replica run either today or tomorrow.

[pbitty]

The gh-ost-on-stop-replication worked like a charm. I was able to stop replication the RDS way before continuing with cut-over, using the hook along with --test-on-replica and --test-on-replica-skip-replica-stop.

[shlomi-noach]

At this time (v.1.0.17), the way to run gh-ost on RDS is as follows:

Real migration

• You need to make sure replication is RBR. gh-ost has no privilege to set binlog_format=ROW for you.
• RDS will not honor the parameter group config for the binlog unless you enable automated backups on the read replica (for at least 1 day).
• Execute gh-ost --assume-rbr ...
  This will make gh-ost skip the stop slave; start slave step, which it does not have privileges for.
• 🍕

Test on replica

In addition to the above:

• gh-ost --assume-rbr --test-on-replica --test-on-replica-skip-replica-stop
  The --test-on-replica-skip-replica-stop avoids issuing a stop slave at the end of migration
• However replication must be stopped. Using hooks you need to supply gh-ost-on-stop-replication hook -- where you will invoke your own RDS script to stop replication
• You must make read_only=0 on the replica's Parameter Group. By default read_only={TrueIfReplica} and therefore enabled
• 🍕

---

Once the above is tested more by the community, I will put this in documentation

[pbitty]

This is the hook we use.

At /hooks/gh-ost-on-stop-replication-rds-command.sh:

#!/usr/bin/env bash

statement="call mysql.rds_stop_replication"
echo "Issuing statement to stop replication: ${statement}" 1>&2
exec mysql -h $DB_HOST -u $DB_USER -p$DB_PASSWORD -e "${statement}"

where --hooks-path=/hooks.

The DB_... env vars are set when running the gh-ost process, and then passed by gh-ost onto the hook process.

[pbitty]

This is the minimum privilege required for that:

GRANT EXECUTE ON PROCEDURE mysql.rds_stop_replication to '<user>'@'<host_or_wildcard>';