Overview

This guide will walk you through the process of migrating your packagecloud:enterprise deployment from the embedded MySQL to an Aurora database hosted on Amazon RDS using Amazon Database Migration Service with minimal downtime.

Terminology

We'll be consistently using the same terminology throughout this guide.

...

Target Instance

...

Workflow

The high-level workflow for this migration can be summarized to the following steps:

1. Create target instance on RDS
2. Securely expose source instance MySQL to DMS
3. Use DMS to transfer all data and replicate any ongoing changes to target instance
4. Monitor migration progress
5. Switch configuration to use target instance on RDS when migration is complete
6. Cleanup

Security

As you can see in the topology diagram below, all communications in and out of AWS from packagecloud:enterprise is encrypted using industry standard, 128-bit SSL. Furthermore, the highest level of SSL verification is used to prevent "Man-in-the-middle" attacks, to be specific:

• packagecloud:enterprise verifies the Server Name (VERIFY_IDENTITY) on the SSL certificate the RDS server presents and also verifies its signature was signed by Amazon's trusted Certificate Authority.
• A unique certificate is securely generated upon installing packagecloud:enterprise and uploaded to Amazon DMS Certificate Manager to verify communication to the source instance (VERIFY_CA).
• The DMS replication instance communicates to RDS within the same Amazon VPC, so SSL is not used nor needed here.

In addition to mutual SSL verification, this guide sets up firewall rules and security groups to ensure that only your frontend and DMS can access RDS and that only DMS can access your source instance. 

Image Removed

Detailed Walkthrough

Create target instance on RDS

Create Security Group

...

Ensure that the instance is launched and ready (takes a few minutes) and securely connect to RDS instance.

/opt/packagecloud/embedded/mysqlclient/bin/mysql --ssl-mode=VERIFY_IDENTITY --ssl-ca=/var/opt/packagecloud/packagecloud-rails/etc/rds-ca.pem -h packagecloud.xxxxxxx.us-east-1.rds.amazonaws.com -p -u aws

...

Create an 'external' user with a with a long, preferably randomly generated password.

mysql> CREATE USER 'external'@'%' IDENTIFIED BY 'xxxxxxxx';

Grant permissions and enforce SSL for 'external' user.

mysql> GRANT ALL PRIVILEGES ON packages_onpremise.* TO 'external'@'%' REQUIRE SSL;

Flush privileges to save user/permission settings and disconnect.

mysql> FLUSH PRIVILEGES;

Run the bootstrap-database command pointed at your RDS instance using the 'external' user and the password created above.

 sudo packagecloud-ctl bootstrap-database -e -h packagecloud.xxxxxxx.us-east-1.rds.amazonaws.com -u external -p xxxxxxxx --rds-ssl

Securely expose source instance MySQL to DMS

...

Under 'Advanced', make sure you choose the security group we created earlier:

...

Transfer the MySQL CA certificate from your packagecloud:enterprise instance to your local computer (This is generated automatically for you since version 2.0.2-1 of packagecloud:enterprise).

scp user@my-packagecloud-instance:/var/opt/packagecloud/lib/mysql/ca.pem .

...

Go back to 'Replication Instances' and take note of the provisioned instance's IP to create a firewall rule to only allow this IP to talk to our MySQL server, in our example our Replication Instance has the IP of 40.40.40.40 and our local MySQL is configured to use port 3306 (the default for packagecloud:enterprise).

sudo iptables -A INPUT -p tcp -s 40.40.40.40/32 --dport 3306 -m conntrack --ctstate NEW,ESTABLISHED -j ACCEPT

We'll need to allow outgoing traffic on that port as well.

sudo iptables -A OUTPUT -p tcp --sport 3306 -m conntrack --ctstate ESTABLISHED -j ACCEPT

Edit /etc/packagecloud/packagecloud.rb and make sure the following values are present/uncommented out.

mysql['ssl_enabled'] = true
mysql['bind_address'] = "0.0.0.0"
mysql['port'] = 3306

Connect to source instance MySQL.

/opt/packagecloud/embedded/mysqlclient/bin/mysql -S /var/opt/packagecloud/lib/mysql/mysql.socket

Create an 'aws' user that DMS will use to securely talk to source instance with a long, preferably randomly generated password.

mysql> CREATE USER 'aws'@'%' IDENTIFIED BY 'xxxxxxx';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'aws'@'%' REQUIRE SSL;
mysql> FLUSH PRIVILEGES;

Reconfigure and Restart.

sudo packagecloud-ctl reconfigure
sudo packagecloud-ctl restart

...

Initiate Transfer and Replication on DMS

...

Under 'Table Mappings', switch to the 'JSON' tab, check the 'Enable JSON editing' and paste the following rules into the text area. Note: Make sure you replace the entire contents with the following.

{
	"rules": [
		{
			"rule-type": "selection",
			"rule-id": "1",
			"rule-name": "1",
			"object-locator": {
				"schema-name": "packages_onpremise",
				"table-name": "%"
			},
			"rule-action": "include"
		},
		{
			"rule-type": "selection",
			"rule-id": "2",
			"rule-name": "2",
			"object-locator": {
				"schema-name": "mysql",
				"table-name": "%"
			},
			"rule-action": "exclude"
		},
		{
			"rule-type": "selection",
			"rule-id": "3",
			"rule-name": "3",
			"object-locator": {
				"schema-name": "performance_schema",
				"table-name": "%"
			},
			"rule-action": "exclude"
		},
		{
			"rule-type": "selection",
			"rule-id": "4",
			"rule-name": "4",
			"object-locator": {
				"schema-name": "information_schema",
				"table-name": "%"
			},
			"rule-action": "exclude"
		},
		{
			"rule-type": "selection",
			"rule-id": "5",
			"rule-name": "5",
			"object-locator": {
				"schema-name": "test",
				"table-name": "%"
			},
			"rule-action": "exclude"
		},
		{
			"rule-type": "selection",
			"rule-id": "6",
			"rule-name": "6",
			"object-locator": {
				"schema-name": "packages_onpremise",
				"table-name": "ar_internal_metadata"
			},
			"rule-action": "exclude"
		}
	]
}

...

Monitor migration progress

Once the task has started, it will first do a full load of all your data, then replicate any changes that happened since the full load. Depending on the total size of your data set, the rate/amount of incoming changes, and the size of the replication instance, this task might take several minutes to several days to complete. This section will show you how you can view the progress of your task. More details are available here: https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Monitoring.html

Table statistics

Clicking on the 'Table Statistics' tab shows the status of each table. To refresh this information, you'll have to periodically click the refresh symbol icon on the top right. Note: This refresh button is separate from the task refresh button at the top of the main Tasks page.

Image Removed

...

Image Removed

A 'Validation State' of 'Validated' means thats all of that table's data has been migrated and verified to be correct on both sides.

A 'Validation State' of 'Pending Records' means that there are still records to transfer and validate. For example:

Image Removed

Then after a few moments, we can see that it's finally done with a state of 'Validated'

Image Removed

Task monitoring

We can also use the 'Task monitoring' tab to view metrics/graphs associated with our migration:

Image Removed

Of particular importance is the 'CDCIncomingChanges' metric. This is how many records are left to be replicated to the Target Instance. This is an example of a spike of created rows waiting to be replicated:

Image Removed

Which we can see fall to zero after it finishes replicating those rows:

Image Removed

Switch Configuration to use target instance on RDS

Before we switch over, let's ensure that any last minute remaining changes are sent over. Shut down all of packagecloud:enterprise services (except the database):

packagecloud-ctl stop nginx
packagecloud-ctl stop unicorn
packagecloud-ctl stop rainbows
packagecloud-ctl stop resque

...

Edit '/etc/packagecloud/packagecloud.rb' and make the following changes:

packagecloud_rails['database_host'] = 'packagecloud.xxxxxxxxxxxx.us-east-1.rds.amazonaws.com'
packagecloud_rails['database_user'] = 'external'
packagecloud_rails['database_password'] = 'xxxxxxxx'
packagecloud_rails['database_port'] = 3306
packagecloud_rails['database_name'] = 'packages_onpremise'
packagecloud_rails['database_ssl'] = true
packagecloud_rails['rds_ssl'] = true

Comment out, or delete the following lines:

#mysql['ssl_enabled'] = true
#mysql['bind_address'] = "0.0.0.0"
#mysql['port'] = 3306

...

Then reconfigure and restart:

packagecloud-ctl reconfigure
packagecloud-ctl restart

...

Cleanup

...

Remove the firewall rule we created earlier to allow our replication instance to communicate with our source instance
(replace 40.40.40.40/32 with your replication instance IP, you can also use iptables -S to find this IP):

sudo iptables -D INPUT -p tcp -s 40.40.40.40/32 --dport 3306 -m conntrack --ctstate NEW,ESTABLISHED -j ACCEPT

Troubleshooting

Task appears to be stuck, not working

You can view logs by going to the 'Logs' tab for under 'Tasks' in DMS

Image RemovedComing soon!