Migrate Self-built Database to RDS

1. Experiment introduction

1.1 Knowledge points

The main content of this experiment is to explain how to migrate self-built databases based on ECS to the RDS database. The data migration process is implemented through the Data Transmission Service (DTS). The study of this experiment includes the following knowledge points:

• MySQL user permission management of self-built databases
• RDS account creation
• Creation of migration process through DTS
• Migration of self-built database to the RDS

1.2 Experiment process

When the experiment environment is started, the system will automatically create an ECS instance and an RDS instance, and we need to install the MySQL services on the ECS instance. In the experiment, we use the MySQL database on ECS as the source database for data migration, and RDS as the target database for data migration. Log on to the ECS instance, manage the source MySQL database, create a master account, and add test data. Then enter the RDS console, and create an RDS administrator account. At last, create the migration process from the source database to the target database through the Data Transmission Service (DTS), to successfully migrate the data in the ECS self-built database to the RDS database.

1.3 Scene architecture diagram

1.4 Cloud resources required

• ECS (Ubuntu)
• RDS database（MySQL）
• Data Transmission Service (DTS)

1.5 Prerequisites

• Understand simple Linux commands
• Understand simple use of MySQL
• Understand RDS console operation
• Understand DTS operation process

1.6 Hint

• When you enter the ECS console, the default region area may be different because of the different network addresses of your logon location. If you find that the resource automatically created in the document does not exist at the console interface, please manually switch to the United States - Silicon Valley’s region, you will find them there.

• If you encounter some differences between the real experimental environment and the captured pictures in the lab manual, it may be caused by cloud portal version difference, which does not have a great impact on your experimental experience. You can click comment to give feedback to us. We will update the document in time. Thank you for your cooperation.

2. Start experiment environment

Click Start Lab in the upper right corner of the page to start the experiment.

.

After the experiment environment is successfully started, the system has deployed resources required by this experiment in the background, including the ECS instance, RDS instance, Server Load Balancer instance, and OSS bucket. An account consisting of the username and password for logging on to the Web console of Alibaba Cloud is also provided.

After the experiment environment is started and related resources are properly deployed, the experiment starts a countdown. You have two hours to perform experimental operations. After the countdown ends, the experiment stops, and related resources are released. During the experiment, pay attention to the remaining time and arrange your time wisely. Next, use the username and password provided by the system to log on to the Web console of Alibaba Cloud and view related resources:

Go to the logon page of Alibaba Cloud console.

Fill in the sub-user account and click Next.

Fill in the sub-user password and click Log on.

After you successfully log on to the console, the following page is displayed.

3. ECS self-built database management

3.1 Login ECS

Click Elastic Compute Service, as shown in the following figure.

We can see one running ECS instance in Silicon Valley region. Click it to go to the ECS console as shown in the following figure.

Referring to the figure below, we first modify the security group rules.

Click Add Rule,Refer to the following figure to set.

Add complete.

Copy this ECS instance’s Internet IP address and remotely log on to this ECS (Ubuntu system) instance. For details of remote login, refer to login。

The default account name and password of the ECS instance:

Account name: root

Password: nkYHG890..

3.2 Install mysql

After logging on to ECS with SSH, execute the command apt-get update to update the apt source code:

Install MySQL service and client. Here we uniformly set the password of the root account to Aliyun-test, and the installation command is as follows:

apt-get install mysql-server mysql-client -y

Start MySQL service

service mysql start

Enter the following command, and enter the password you set during the MySQL service installation. A successful logon denotes that the installation succeeded:

mysql -uroot -p

3.3 Modify the listening address

Use the command exit to exit the database, and modify the configuration file:

The command for modifying the configuration file is as follows:

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

Comment out the content of line 43 in the configuration file through adding # to the start of the line.

Restart service

service mysql restart

View the effect after the modification. 0 denotes that any hosts can access MySQL.

netstat -anpt|grep 3306

3.4 Create a master account

After logging on to the database, we use the following commands to operate the database:

Create a user, the user information is as follows:

Username: labexuser

Password: Aliyun-test

CREATE USER 'labexuser'@'%' IDENTIFIED BY 'Aliyun-test';

Create a database

CREATE DATABASE labextest;

View the created database

SHOW DATABASES;

Authorize the user

GRANT ALL PRIVILEGES ON labextest.* to 'labexuser'@'%' IDENTIFIED BY 'Aliyun-test';

Refresh the authorization

FLUSH PRIVILEGES;

The user has been created. We will use the new user to log on to the database and create some test data.

3.5 Add test file

After exiting the database, we use a new user to logon to the database. The operation is as follows:

mysql -u labexuser -p

After logon, check whether the created database exists. If it exists, switch to the created database:

SHOW DATABASES;

The command for switching database is as follows:

USE labextest;

The command for viewing the current database is as follows:

SELECT DATABASE();

After switching to the database, we can create a table in it for testing. The command is as follows:

CREATE TABLE aliyuntest(name char(10));
DESC aliyuntest;

The command for inserting data is as follows:

INSERT INTO aliyuntest VALUES(123);
SELECT * FROM aliyuntest;

In this step, ECS database creation is finished, followed by the RDS data operations.

4. RDS target database management

When the experiment environment is started, the system has created an RDS instance for us. We can see the RDS-related information through the operations in the figure below:

After entering to the control terminal, we can see an RDS instance. A normally running RDS is as follows:

4.1 Create Account

Refer to the figure below, and then create an administrator account.

Create a user, the user information is as follows:

Username: labex

Password: Aliyun-test

Superuser account creation takes a few minutes, please wait patiently。

4.2 Configure the whitelist

Click Configure Whitelist.

Click Modify.

Set according to the figure below and click OK.

The whitelist is added:

After the whitelist is modified, the intranet address of the RDS instance can be viewed.

4.3 Applying for Internet address

After setting the whitelist, you can obtain the intranet address. However, an Internet address also needs to be requested to manage RDS:

Because the ECS instance is on the VPC network and the RDS is on the classic network, they cannot connect through the intranet and must use the public address of the RDS.

Click OK.

wait for a moment, and then refresh the page to obtain the Internet address:

5. Database migration

When the ECS database and RDS database are created, we can migrate the databases through DTS. The options are as shown in the figure:

5.1 Create a migration task

After entering the console, we select the marked option. Note that the selection of region must be consistent with that of ECS and RDS.

Fill the related information of the source database and target database, and test the connection. If the test passes, go to the next step. The operation is as shown in the figure below:

As can be seen from above, both the connection tests passed. The following is the pre-check of the migration.

After filling the related information and ensuring that the databases can be normally connected to, click Set Whitelist and Next.

Here we are to select the data to be migrated, that is, the test data in the self-built ECS database:

After the selection, we can see that data is moved to the right box, which denotes a successful move. Click “Precheck” to continue:

After clicking the button, the pre-check of the migration starts. The pre-check results are shown in the figure below:

5.2 Start the migration task

After the pre-check, click “Next” to go to the following confirmation page. The operations are shown in the figure below:

The effect of the completed migration is as shown:

<font color='red'>Users can cut off the above result picture when they are doing the experiment and send it to the teacher, indicating that the current experiment has been completed.</font>

Here the data has been migrated successfully, and we will verify the data migration results.

5.3 Verifying migration results

We use RDS Internet address for remote verification, which has been previously applied for, and the address is as follows:

We remotely connect to RDS database on ECS with the following code, in which “rds-internet-address” is the Internet address. The operations are as follows:

mysql -ulabex -h rds-internet-address -p

Now we check whether the data is successfully migrated through SQL statements:

SHOW DATABASES;

USE labextest;

SELECT * FROM aliyuntest;

Through viewing the RDS database content, we can see that the migrated data is already in the RDS database, and the experiment ends.

Reminder:
Before you leave this lab, remember to log out your Alibaba RAM account before you click the ‘stop’ button of your lab. Otherwise you’ll encounter some issue when opening a new lab session in the same browser:

6. Experiment summary

What we have learnt in this experiment is to migrate the ECS self-built database to an RDS database through the DTS. First, we created a MySQL database and a master account on an ECS instance. Then we created a test database and added test data. Subsequently, we created an account on RDS for testing. At last, we migrated data on an ECS database to RDS database through the DTS. For the experiment, we need to know the basic CentOS operations, MySQL database installation and basic SQL statements, as well as related RDS and TDS operations.