You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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.
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.
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.
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.
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.
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
To clarify, in: GRANT EXECUTE ON PROCEDURE mysql.rds_stop_replication to '<your_ghost_user>'@... you don't actually need the user to be the gh-ost user you use for running the migration. This was your personal choice.
I ran around 7-8 migrations on MySQL in December using this. Everything seemed to have worked well.
Some minor changes I had to do with gh-ost were related to making --assume-master-host work without Tungsten. As RDS replica was using internal IPs(172.17.X.X) to communicate with master and the machine we were running gh-ost on couldn't access that, I had to manually pass the master hostname.
I've been testing this for the last couple of months using RDS and aurora as the engine. The steps here related to RDS are spot on 👍 , the only differing pain point I've found is that the aurora parameter group doesn't allow modifying read_only. It still uses the same value of {TrueIfReplica} however the aurora parameters are marked as a non-modifiable field which prevents connecting to replica but running on the master.
We're looking into a couple of work arounds for this and I'll report back if we get anything worth while but for now, best to just be aware you'll need to run it on the master.
From what I understand of Aurora, all nodes share the same underlying storage layer, so only one node is allowed to write to it. You may be able to get around this by replicating to another Aurora cluster. From gh-ost's point of view, it would still be connecting to a "master", but this master would be on a 2nd Aurora cluster (that is receiving binlog updates from the 1st one).
You may be able to get around this by replicating to another Aurora cluster.
This is a possibility however I don't believe it solves the issue of running on a replica but migrating the master (which is the intention here). Your comment further down explains why this is the case and right now I can't see a viable solution.
The best you can expect from RDS on Aurora at the moment is that you can run it on the cluster but only directly on the master. If you want to test, you need to stand up additional clusters - probably based on a recent snapshot.
I migrated a 1.5 billion record table on a highly concurrent system in Aurora. It took a couple attempts, because the inserts would slow even with no constraints added to the base command. We had to scale back some of the load on the server to be able to complete the task in a timely manner.
@HasMikeW which version of gh-ost were you using? More specifically, does your version support --dml-batch-size? Introduction of this functionality (indicated by the existence of this flag) significantly improved write throughput. Generally speaking I prefer to just have some sane configuration and not try to tune too much, but if you suffer from low throughput you may try a higher value for that flag (default: 10, try perhaps 50 or 100).
@shlomi-noach The version is 1.0.32 I had looked into using the --dml-batch-size but was unable to get it to work with this version. I didn't want to go back and have to run more tests that a new version of gh-ost would have dictated at that point. That was the next step if reducing the load did not work :-)
Just an update to my comment above - I've managed to find a nice workaround for testing/migrating on a replica in AWS RDS using aurora as the engine.
@pbitty was pretty close to the money however you don't use Aurora replication, you need to use standard MySQL-style replication. The AWS documentation page does an OK-ish job at explaining this but it's probably better to follow non-AWS docs and just setup replication as if you would elsewhere using a separate aurora cluster in the same region.
It would definitely be worth while! I'm happy to write it (since I've already got a bunch of notes from our internal documentation that would help here too).
@shlomi-noach I think everything in this issue has been addressed either via docs or changes. We've also managed to run ~20 successful test + production migrations on RDS so I'm happy most of the annoyances are out of the way. Would it be time to close this off and expect new issues to be in a thread of their own?
Activity
shlomi-noach commentedon Aug 18, 2016
First limitation is the
SUPERprivilege. This is defined in #132 and addressed in #156When you know you are using RBR (Row Based Replication), you can specify
--assume-rbrand this will skip the step ofSTOP SLAVE; START SLAVEwhich requiresSUPERprivilege.shlomi-noach commentedon Aug 18, 2016
I should note that I am not using RDS at the moment; will need community feedback for production issues.
pbitty commentedon Aug 18, 2016
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-replicaand did not have any significant issues.pbitty commentedon Aug 18, 2016
As far as I can tell, the only other blocker is the
STOP SLAVEcommand that is issued in--test-on-replicamode (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=ROWon 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 commentedon Aug 18, 2016
Thank you! You are now assigned the role of Chief RDS Officer at Gh-Ost Corp.
Changing a
statementreplica torowcan certainly be delegated to the user, no need to troublegh-oston this if this is an issue.pbitty commentedon Aug 19, 2016
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 commentedon Aug 31, 2016
Hooks have been merged, though not yet released.
pbitty commentedon Aug 31, 2016
Great! I will try them out with our next
--test-on-replicarun either today or tomorrow.pbitty commentedon Sep 1, 2016
The
gh-ost-on-stop-replicationworked like a charm. I was able to stop replication the RDS way before continuing with cut-over, using the hook along with--test-on-replicaand--test-on-replica-skip-replica-stop.shlomi-noach commentedon Sep 5, 2016
At this time (v.1.0.17), the way to run
gh-oston RDS is as follows:Real migration
RBR.gh-osthas no privilege to setbinlog_format=ROWfor you.gh-ost --assume-rbr ...This will make
gh-ostskip thestop slave; start slavestep, 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-stopThe
--test-on-replica-skip-replica-stopavoids issuing astop slaveat the end of migrationgh-ost-on-stop-replicationhook -- where you will invoke your own RDS script to stop replicationread_only=0on the replica's Parameter Group. By defaultread_only={TrueIfReplica}and therefore enabledOnce the above is tested more by the community, I will put this in documentation
pbitty commentedon Sep 20, 2016
This is the hook we use.
At
/hooks/gh-ost-on-stop-replication-rds-command.sh: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 commentedon Sep 20, 2016
This is the minimum privilege required for that:
shlomi-noach commentedon Sep 21, 2016
@pbitty thank you for this info!
To clarify, in:
GRANT EXECUTE ON PROCEDURE mysql.rds_stop_replication to '<your_ghost_user>'@...you don't actually need the user to be thegh-ostuser you use for running the migration. This was your personal choice.pbitty commentedon Sep 22, 2016
Good point. I've updated the comment above.
25 remaining items
dgtized commentedon Jan 24, 2017
I've run several migrations on RDS MySQL using the directions above and had no problems.
kamaljoshi commentedon Feb 16, 2017
I ran around 7-8 migrations on MySQL in December using this. Everything seemed to have worked well.
Some minor changes I had to do with
gh-ostwere related to making--assume-master-hostwork without Tungsten. As RDS replica was using internal IPs(172.17.X.X) to communicate with master and the machine we were runninggh-oston couldn't access that, I had to manually pass the master hostname.jacobbednarz commentedon Mar 10, 2017
I've been testing this for the last couple of months using RDS and aurora as the engine. The steps here related to RDS are spot on 👍 , the only differing pain point I've found is that the aurora parameter group doesn't allow modifying
read_only. It still uses the same value of{TrueIfReplica}however the aurora parameters are marked as a non-modifiable field which prevents connecting to replica but running on the master.We're looking into a couple of work arounds for this and I'll report back if we get anything worth while but for now, best to just be aware you'll need to run it on the master.
pbitty commentedon Mar 10, 2017
@jacobbednarz That's great to hear.
From what I understand of Aurora, all nodes share the same underlying storage layer, so only one node is allowed to write to it. You may be able to get around this by replicating to another Aurora cluster. From gh-ost's point of view, it would still be connecting to a "master", but this master would be on a 2nd Aurora cluster (that is receiving binlog updates from the 1st one).
I haven't tried this, just theorizing. :)
jacobbednarz commentedon Mar 13, 2017
This is a possibility however I don't believe it solves the issue of running on a replica but migrating the master (which is the intention here). Your comment further down explains why this is the case and right now I can't see a viable solution.
The best you can expect from RDS on Aurora at the moment is that you can run it on the cluster but only directly on the master. If you want to test, you need to stand up additional clusters - probably based on a recent snapshot.
HasMikeW commentedon Mar 20, 2017
I migrated a 1.5 billion record table on a highly concurrent system in Aurora. It took a couple attempts, because the inserts would slow even with no constraints added to the base command. We had to scale back some of the load on the server to be able to complete the task in a timely manner.
shlomi-noach commentedon Mar 20, 2017
@HasMikeW which version of
gh-ostwere you using? More specifically, does your version support--dml-batch-size? Introduction of this functionality (indicated by the existence of this flag) significantly improved write throughput. Generally speaking I prefer to just have some sane configuration and not try to tune too much, but if you suffer from low throughput you may try a higher value for that flag (default:10, try perhaps50or100).HasMikeW commentedon Mar 20, 2017
@shlomi-noach The version is 1.0.32 I had looked into using the --dml-batch-size but was unable to get it to work with this version. I didn't want to go back and have to run more tests that a new version of gh-ost would have dictated at that point. That was the next step if reducing the load did not work :-)
jacobbednarz commentedon Mar 23, 2017
Just an update to my comment above - I've managed to find a nice workaround for testing/migrating on a replica in AWS RDS using aurora as the engine.
@pbitty was pretty close to the money however you don't use Aurora replication, you need to use standard MySQL-style replication. The AWS documentation page does an OK-ish job at explaining this but it's probably better to follow non-AWS docs and just setup replication as if you would elsewhere using a separate aurora cluster in the same region.
shlomi-noach commentedon Mar 23, 2017
@jacobbednarz do you think it would be good to write some documentation for Aurora users, and if so, would you be able and willing to write it?
jacobbednarz commentedon Mar 23, 2017
It would definitely be worth while! I'm happy to write it (since I've already got a bunch of notes from our internal documentation that would help here too).
jacobbednarz commentedon Mar 24, 2017
I've opened a PR to add in AWS RDS docs at #385 for anyone following along at home.
jacobbednarz commentedon Apr 10, 2017
@shlomi-noach I think everything in this issue has been addressed either via docs or changes. We've also managed to run ~20 successful test + production migrations on RDS so I'm happy most of the annoyances are out of the way. Would it be time to close this off and expect new issues to be in a thread of their own?
shlomi-noach commentedon May 24, 2017
Documentation for RDS is available