Migrating a Database by Using DMS

This section shows how to use DMS to migrate a MySQL database from an Ubuntu Linux EC2 instance running in your cloud. You need to prepare two roles for use with DMS. The first role will use the built in AmazonDMSVPCManagementRole policy to allow access to the VPC, and the second one will allow access to CloudWatch through the built-in AmazonDMSCloudWatchLogsRole policy. The JSON script in Example 7-5 can be used to create both roles.

Example 7-5 IAM Policy That Allows the DMS Service to Assume the Role

Click here to view code image

{
   "Version": "2012-10-17",
   "Statement": [
   {
     "Effect": "Allow",
     "Principal": {
        "Service": "dms.amazonaws.com"
     },
   "Action": "sts:AssumeRole"
   }
 ]
}

Save this file as dms-role.json and use it in the following command to create the dms-vpc-role role:

Click here to view code image

aws iam create-role --role-name dms-vpc-role --assume-role-policy-
document file://dms-role.json

Assign the built-in AmazonDMSVPCManagementRole policy to the newly created dms-vpc-role role:

Click here to view code image

aws iam attach-role-policy --role-name dms-vpc-role --policy-arn
arn:aws:iam::aws:policy/service-role/AmazonDMSVPCManagementRole

Create the dms-cloudwatch-role role:

Click here to view code image

aws iam create-role --role-name dms-cloudwatch-logs-role
--assume-role-policy-document file://dms-role.json

Assign the built-in AmazonDMSCloudWatchLogsRole policy to the newly created dms-cloudwatch-role role:

Click here to view code image

aws iam attach-role-policy --role-name dms-cloudwatch-logs-
role --policy-arn arn:aws:iam::aws:policy/service-role/
AmazonDMSCloudWatchLogsRole

When you have these prerequisites sorted, you can launch a new Ubuntu Linux instance and install the MySQL service on it. Deploy the instance in the default VPC and ensure that you open port 22 for SSH and port 3306 for MySQL in the security group when launching the instance so that you can connect to the console and so the DMS service will be able to connect to the database. Once you have connected to the instance via SSH, install and configure the MySQL service:

Click here to view code image

sudo -i

apt-get update -y

apt-get install mysql-server -y

The service installs without a password for the root user, so you need to log in to the MySQL CLI and set a password that you will use with the DMS. To log in, leave the password blank:

mysql -u root -p

Once in the MySQL console, set the following:

Click here to view code image

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('mydmspassword');

exit;

Open the MySQL configuration file and set it up to listen on all IP addresses, so it will be accessible from the DMS service:

Click here to view code image

vi /etc/mysql/mysql.conf.d/mysqld.cnf

Change the bind address from 127.0.0.1 to 0.0.0.0:

bind-address = 0.0.0.0

Add the following lines to the config file:

server-id = 1

log_bin = mysql-bin

binlog_format = row

Restart the MySQL service to ensure that all changes have taken effect:

/etc/init.d/mysql restart

Now you need to create a database and give it some structure so you have some test data, so save the content in Example 7-6 as testdata.sql.

Example 7-6 SQL Script to Create a Sample Database

Click here to view code image

CREATE DATABASE books;
USE books;
CREATE TABLE `authors` (
        `id` INT(11) NOT NULL AUTO_INCREMENT,
        `first_name` VARCHAR(50) NOT NULL COLLATE 'utf8_unicode_ci',
        `last_name` VARCHAR(50) NOT NULL COLLATE 'utf8_unicode_ci',
        `email` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',
        `birthdate` DATE NOT NULL,
        `added` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        UNIQUE INDEX `email` (`email`)
)

Now you can import this data into the MySQL database by simply issuing the following command:

mysql < testdata.sql

List the databases in the server by issuing the following command:

mysql -e "show databases"

The output of the command should show that the book database now exists, as demonstrated in Example 7-7.

Example 7-7 Output of the show databases Command

Click here to view code image

+--------------------+
| Database           |
+--------------------+
| information_schema |
| books              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

Next, log in to the RDS Management Console and start an RDS instance by clicking the Create database button, as shown in Figure 7-1.

In the Create Database dialog, select the Standard Create method and the MySQL engine option, as shown in Figure 7-2.

Next, scroll down and select the free tier and enter the database name you would like to use, as shown in Figure 7-3.

Next, scroll down and enter the master username and password you want to use with this instance, as shown in Figure 7-4. In this section, also select the size of the instance as db.t2.micro.

Continue scrolling down to the Storage section and allocate 20 GB and disable storage autoscaling, as shown in Figure 7-5. Leave all other settings at their defaults.

In the Connectivity section, select your default VPC and ensure that you select the security group that you created when you deployed the EC2 instance, as shown in Figure 7-6.

In the final step, ensure that Password authentication is selected, as shown in Figure 7-7, and then click the Create database button.

After the database is created, wait for the instance to become active and collect the DNS name from the Management Console. Next, log in to the Ubuntu Linux EC2 instance and list the databases in the RDS instance. You can use the following command, replacing the instance DNS name after -h with your RDS instance name:

Click here to view code image

mysql -h mydb.cpwpqyrglyaz.us-east-2.rds.amazonaws.com -u admin
-p -e "show databases"

The output of the command should show the default RDS databases and should look something like the output shown in Example 7-8.

Example 7-8 Output of the show database Command for the RDS Database

Click here to view code image

+--------------------+
| Database           |
+--------------------+
| information_schema |
| innodb             |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

Now that you have a source and a target, you need to set up a replication instance in the DMS. Log back in to the Management Console and navigate to the DMS service. Click the Create replication instance button, as shown in Figure 7-8.

In the Create Replication Instance dialog, configure a name and description for your instance and allocate 10 GB of storage to it, as shown in Figure 7-9. (This will be more than enough storage space for this example.) In this dialog, also select the VPC identifier of your default VPC.

Next, ensure that the instance is publicly accessible. Expand the Advanced security and network configuration section and ensure that the security group is set to the same one you used for the EC2 instance and the RDS database, as shown in Figure 7-10.

Now that the instance is created, you need to wait a few minutes for it to become available. Note the public IP address of the DMS instance, as shown in Figure 7-11, for use in the next step.

Now that the instance is created, you need to log back in to the EC2 Ubuntu instance and enable access from the DMS instance, using the IP address of the DMS instance that you just noted:

Click here to view code image

GRANT ALL ON *.* to root@'18.220.106.21' IDENTIFIED BY
'mydmspassword';

FLUSH PRIVILEGES;

EXIT;

Next, you need to return to the managed console and create the source and target endpoints. For the source endpoint, configure the EX2 instance as the target. Enter the public IP address of the instance as the server name, as shown in Figure 7-12.

Set the port to 3306 and enter the authentication details for your root user, as shown in Figure 7-13.

Next, set up the target endpoint. Select the Select RDS DB instance check box and choose your RDS instance as the target, as shown in Figure 7-14.

Enter the password for the admin user that you set when you created the RDS instance and click Create endpoint (see Figure 7-15).

Now that both endpoints are created and active, as shown in Figure 7-16, you can click each one and run a connectivity test from the replication instance.

For each endpoint, click the Test connections button in the body of the page, as shown in Figure 7-17.

When you run the test, select the replication instance that you created, as shown in Figure 7-18. The test should be run on both the source and target endpoints, and both tests should be successful before you move on to the next step.

After the target endpoints are created and tested, you need to set up a database migration task. Enter your source and destination endpoints and create a task identifier and description. In the example in Figure 7-19, Migrate existing data is selected as the migration type, but you could also set up a continuous migration with the Migrate existing data and replicate ongoing changes option.

For table preparation, select Do nothing, as shown in Figure 7-20, as you do not want the server to remove the DRS default tables. Leave the other settings at their defaults.

You also need to set up a table mapping. Under Schema, select the Enter a schema option and name the schema books, as shown in Figure 7-21. For the table, enter authors so that the books database is replicated from the EC2 instance.

When the task is started, it takes a few minutes for the replication to complete. When the replication has completed, you should see the task finish with a Load complete message, as shown in Figure 7-22.

Now all that you need to do is run the command to show the databases against your RDS instance on your Ubuntu Linux EC2 and verify that the database has been replicated:

Click here to view code image

mysql -h mydb.cpwpqyrglyaz.us-east-2.rds.amazonaws.com -u admin
-p -e "show databases"

The output of the command should now show the books database as well as the default RDS databases in the RDS instance and should look something like Example 7-9.

Example 7-9 Output of the show database Command After Successful Migration—with the Books Database Present

Click here to view code image

+--------------------+
| Database           |
+--------------------+
| information_schema |
| awsdms_control     |
| books              |
| innodb             |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

S3 Sync

When you need to transfer large numbers of files, S3 Sync is a nice option that is built in to the S3 CLI. As you can imagine, the tool simply synchronizes the state of the files on the local directory with S3 or vice versa. You can use S3 Sync to copy large numbers of files to and from S3 in a single command.

To test S3 Sync, you can create a new folder that contains some files. Use the following command to create the directory, enter it, and create five files:

Click here to view code image

mkdir sync && cd sync && touch file{1..5}.txt

Next, you simply need to create an S3 bucket to sync with and run the following command:

Click here to view code image

aws s3 sync . s3://mylittlesyncbucket

In this example, a bucket called mylittlesyncbucket is created. You can replace this part of the command with your bucket name.

The output should look as shown in Example 7-10. Note that the files are uploaded in random sequence.

Example 7-10 Output of the s3 sync Command

Click here to view code image

upload: ./file2.txt to s3://mylittlesyncbucket/file10.txt
upload: ./file1.txt to s3://mylittlesyncbucket/file1.txt
upload: ./file5.txt to s3://mylittlesyncbucket/file5.txt
upload: ./file3.txt to s3://mylittlesyncbucket/file8.txt
upload: ./file4.txt to s3://mylittlesyncbucket/file4.txt

Now you can log in to the AWS Management Console and see that the bucket has been synced with the local directory, as shown in Figure 7-23.

You can now modify or add more files to the directory and rerun the s3 sync command. As you will see, only the files that have changed are copied over.

To synchronize your files in the opposite direction, simply swap the command to specify the S3 target first and the directory second, like so:

Click here to view code image

aws s3 sync s3://mylittlesyncbucket .

This command copies any files that do not exist in the local directory from S3.

Consider using versioning if you are cloning files through S3 Sync so that any accidental rewrites can be reverted.

S3 Multipart Uploads

When working with large files, you want to be able to upload them to S3 as quickly as possible. To do this, you can use the S3 multipart upload functionality, which gives you the ability to upload a large file in several segments. There are some limitations to multipart uploads:

• The smallest part of a multipart upload is 5 MB.

• The maximum number of segments is 10,000.

• The largest possible combined file size is 5 TB.

You can see that even with these limitations, S3 multipart has great potential.

You need to consider a few additional key points about each multipart upload. First, you need to split up the file that you are uploading on your operating system before you begin the operation. Next, you need to upload the file parts in parallel to get the most benefit from this operation.

When you split up a file into parts, your first instinct may be to iterate through the filenames by pushing the parts through a do-while loop, but that wouldn’t really make a lot of sense because it would still upload the parts sequentially within the do-while loop. You need to execute a function on each file at the same time to start the part uploads at the same time and thus take full advantage of the multipart process. You will learn how to achieve this in the example that follows using the AWS CLI; however, you should take note of the lessons learned about not iterating through the files into your development practices and make sure you parallelize your execution as demonstrated in this example.

Begin by downloading this large 100 MB image file:

Click here to view code image

wget https://everyonelovesaws.s3.us-east-2.amazonaws.com/world.png

Next, use the Linux split command to split the file into 10 pieces and remove the original file and store the file pieces in the multipart/ directory:

Click here to view code image

mkdir multipart && mv world.png multipart/ && cd multipart && 

split -n 10 world.png && rm world.png && cd ..

Now look at what happens when you initiate a multipart upload:

Click here to view code image

aws s3api create-multipart-upload --bucket mylittlesyncbucket
--key world.png

The output of this command provides you with a bucket name, a key name, and an UploadId that you need to use when you upload your parts, as demonstrated in Example 7-11.

Example 7-11 Output of the create-multipart-upload Command

Click here to view code image

{
    "Bucket": "mylittlesyncbucket",
    "UploadId": "2HscWfRZYJCILJVdMpJJ4dfaAKD4ntjYKqgw.uFNrgvXv0Qk5e.
10q_UMsvOsGTUKNoc1CqDdQ6oAtHI6KTTkPRpJurOrtbd7C3xKbDQSkVwwhX3k3O4a8UQb.DSm.at",
    "Key": "world.png"
}
aws s3api upload-part --bucket mylittlesyncbucket 
--key world.png --part-number 1 --body multipart/xaa --upload-id "2HscWfRZYJCILJV
dMpJJ4dfaAKD4ntjYKqgw.uFNrgvXv0Qk5e.10q_UMsvOsGTUKNoc1CqDdQ6oAtHI6KTTkPRpJurOrtbd
7C3xKbDQSkVwwhX3k3O4a8UQb.DSm.at" --profile default

The response from this command is the ETag ID of the key that will be created when the operation is complete. You need to use the ETag ID in a properly formatted JSON file to complete the multipart operation:

Click here to view code image

{
     "ETag": ""cea5605333e43d86aaa1d751ca03e0f8""
}

As you can see, the only way to complete this operation in an efficient manner would be to automate and feed the JSON responses from your commands and create the required input data for the next command. You can cancel the operation and prepare to automate. To cancel the multipart upload, you need to specify the bucket, key, and UploadId:

Click here to view code image

aws s3api abort-multipart-upload --bucket mylittlesyncbucket
--key world.png --upload-id "2HscWfRZYJCILJVdMpJJ4dfaAKD4ntjYKqgw.
uFNrgvXv0Qk5e.10q_UMsvOsGTUKNoc1CqDdQ6oAtHI6KTTkPRpJurOrtbd7C3
xKbDQSkVwwhX3k3O4a8UQb.DSm.at"

Before you can automate, you also need something to read the JSON tags being returned by the AWS commands. For this purpose, you can install a JSON filter called jq that will allow you to capture the output and feed it into a variable that you will be able to use in your commands to both simplify the operation and create the JSON file for the multipart upload. Assuming that you are running Amazon Linux, you can install jq with the following command:

sudo yum -y install jq

Next, create the UploadID variable by feeding it the create-multipart-command into and filtering just the UploadId JSON content with the jq command. The following parts are included with this command:

• UploadId=`: Opens a quotation for the variable

• aws s3api create-multipart-upload --bucket mylittlesyncbucket --key world.png: Runs the AWL CLI command

• jq -r '.UploadId': Reads the content of the UploadId response

• `: Closes the quotation for the variable

The complete command to run looks like this:

Click here to view code image

UploadId=`aws s3api create-multipart-upload --bucket
mylittlesyncbucket --key world.png | jq -r '.UploadId'`

Because you fed the command into a variable, no response is given. You can run the following command to see the UploadId variable:

echo $UploadId

Next, you need to create the input.json file to record the ETag ID for all the parts. The first line defines the parts so you simply echo the correct formatting for the first line:

Click here to view code image

echo "{"Parts":[ " > input.json

Because you need the ETag from each upload, you can feed the upload-part command into the eTag# variable by reading the JSON of the AWS CLI output with jq, just as you did for the previous AWS command. You can then pipe the output into an echo command for each file part to create a separate new line entry in the input.json file for its respective part. Finally, the && operator ensures that all the commands are executed at the same time and all parts are uploaded in parallel:

• eTag1=`: Opens a quotation for the unique ETag variable for each file.

• aws s3api upload-part --bucket mylittlesyncbucket --key world.png --part-number 1 --body multipart/xaa --upload-id $UploadId: Runs the AWS CLI command using the previously defined $UploadId variable. This command also uploads the file part to AWS.

• | jq -r '.ETag': Reads the content of the ETag response.

• `: Closes the quotation for the variable.

• && echo "{"ETag":$eTag1,"PartNumber":1}", >> input.json: Feeds the $eTag variable into an echo operation and appends a new line in the input.json file.

• &&: Ensures that the next command is run in parallel.

• : Adds a new line.

You need a command for each file, and the last file must end the echo without the, separator to maintain the sanity of JSON. The final command to upload the 10 parts should look as shown in Example 7-12.

Example 7-12 An Example of Parallel Input for the Multipart Upload Operation

Click here to view code image

eTag1=`aws s3api upload-part --bucket mylittlesyncbucket --key world.png --part-
number 1 --body multipart/xaa --upload-id $UploadId --profile default | jq -r
'.ETag'` && 
echo "{"ETag":$eTag1,"PartNumber":1}", >> input.json && 
eTag2=`aws s3api upload-part --bucket mylittlesyncbucket --key world.png --part-
number 2 --body multipart/xab --upload-id $UploadId --profile default | jq -r
'.ETag'` && 
echo "{"ETag":$eTag2,"PartNumber":2}", >> input.json && 
eTag3=`aws s3api upload-part --bucket mylittlesyncbucket --key world.png --part-
number 3 --body multipart/xac --upload-id $UploadId --profile default | jq -r
'.ETag'` && 
echo "{"ETag":$eTag3,"PartNumber":3}", >> input.json && 
eTag4=`aws s3api upload-part --bucket mylittlesyncbucket --key world.png --part-
number 4 --body multipart/xad --upload-id $UploadId --profile default | jq -r
'.ETag'` && 
echo "{"ETag":$eTag4,"PartNumber":4}", >> input.json && 
eTag5=`aws s3api upload-part --bucket mylittlesyncbucket --key world.png --part-
number 5 --body multipart/xae --upload-id $UploadId --profile default | jq -r
'.ETag'` && 
echo "{"ETag":$eTag5,"PartNumber":5}", >> input.json && 
eTag6=`aws s3api upload-part --bucket mylittlesyncbucket --key world.png --part-
number 6 --body multipart/xaf --upload-id $UploadId --profile default | jq -r
'.ETag'` && 
echo "{"ETag":$eTag6,"PartNumber":6}", >> input.json && 
eTag7=`aws s3api upload-part --bucket mylittlesyncbucket --key world.png --part-
number 7 --body multipart/xag --upload-id $UploadId --profile default | jq -r
'.ETag'` && 
echo "{"ETag":$eTag7,"PartNumber":7}", >> input.json && 
eTag8=`aws s3api upload-part --bucket mylittlesyncbucket --key world.png --part-
number 8 --body multipart/xah --upload-id $UploadId --profile default | jq -r
'.ETag'` && 
echo "{"ETag":$eTag8,"PartNumber":8}", >> input.json && 
eTag9=`aws s3api upload-part --bucket mylittlesyncbucket --key world.png --part-
number 9 --body multipart/xai --upload-id $UploadId --profile default | jq -r
'.ETag'` && 
echo "{"ETag":$eTag9,"PartNumber":9}", >> input.json && 
eTag10=`aws s3api upload-part --bucket mylittlesyncbucket --key world.png --part-
number 10 --body multipart/xaj --upload-id $UploadId --profile default | jq -r
'.ETag'` && 
echo "{"ETag":$eTag10,"PartNumber":10}" >> input.json

You can freely retry this part of the exercise by removing the && operators to run the operation in sequence instead of in parallel and compare the time it takes to complete the operation. When the previous command completes, you need to close the input.json file with the following command:

echo "]}" >> input.json

Finally, you finish the multipart upload by specifying the input.json file in the following command:

Click here to view code image

aws s3api complete-multipart-upload --multipart-upload file://
input.json --bucket mylittlesyncbucket --key world.png --upload-id
$UploadId --profile default

You should receive a response with the S3 location from AWS, indicating that the file has been uploaded successfully, as demonstrated in Example 7-13.

Example 7-13 Output of the complete-multipart-upload Command

Click here to view code image

{
    "ETag": ""e0f0997c8e0e56492867164435ab8eb9-10"",
    "Bucket": "mylittlesyncbucket",
    "Location": "https://mylittlesyncbucket.s3.us-east-2.amazonaws.com/world.png",
    "Key": "world.png"
}

The file is now uploaded and reassembled in the AWS back end. You can navigate to your bucket and download the file to see if the image has been preserved. If you have completed the exercise correctly, you should see a topographical map of the world. Now you just need to clean up the multipart directory:

Click here to view code image

rm multipart/* && rmdir multipart/

AWS DataSync

As you can see, copying files with S3 can, in some cases, be quite challenging. In addition, the target for your files might need to be a shared file system where production servers need access to the files with as little replication lag as possible. The AWS DataSync service can ease the migration of shared files from an on-premises file server to an AWS EFS service as well as simplify the delivery of files to S3. The service requires you to install an agent in the on-premises environment, which then connects through a TLS-encrypted Internet connection to the DataSync service. When this connection is made, you can simply set up the source and target in the AWS Management Console and set up a one-time or continuous migration. The service works in a very similar fashion to the DMS service, giving you the ability to not only migrate but also maintain two active copies of your file shares: one on premises and one in the cloud.