Use Data Management Service To Operate And Maintain ApsaraDB For RDS
1. Experiment
1.1 Knowledge points
This experiment uses Alibaba Cloud Data Management Service (DMS). It is an all-in-one data management service that supports data management, structure management, user authorization, security audit, data trends, data trace, BI charts, performance trends and optimization, and server management. DMS is used to implement an easy-to-use portal for centralized management of databases and servers, improving data security, management efficiency, and data value visualization.
This experiment describes common operations of DMS in operating and maintaining databases. It helps the O&M personnel to operate databases in a graphical manner, which is faster and more convenient.
1.2 Experiment process
- Configure an RDS instance.
- Prepare RDS data.
- Use DMS to manage RDS.
1.3 Scene architecture diagram
1.4 Cloud resources required
- Elastic Compute Service (ECS)
1.5 Prerequisites
- If you are using your own Alibaba Cloud account instead of the account provided by this lab to operate the experiment, please note that you will need to choose the same Ubuntu 16.04 operating system for your ECS in order to run the experiment smoothly.
- Before starting this experiment, make sure that you have closed and exited from the previous experiment.
2. Start the experiment environment
Click Start Lab in the upper-right corner of the page to start the experiment.
.
After the experiment environment is started, the system has deployed the resources required by this experiment in the background, including the ECS instance, RDS instance, Server Load Balancer (SLB) instance, and Object Storage Service (OSS) bucket. An account that consists of the username and password for logging on to the Alibaba Cloud console is also provided.
After the experiment environment is started and related resources are deployed, the experiment countdown starts. 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 Alibaba Cloud console and view related resources.
Go to the logon page of the Alibaba Cloud console.
Enter the RAM user account and click Next.
Enter the password and click Log on.
After you log on to the console, the following page appears.
3.1 Set a whitelist
Refer to the figure below and go to the rds console.
Select the US (Silicon Valley) area, you can see that an rds instance is being created, please wait a few minutes.
After the creation is complete, click Manage.
Then, you can see the details of the RDS instance:
Click Configure Whitelist.
Click Modify.
Set according to the figure below and click OK.
The whitelist is added:
3.2 Create an administrator 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. Prepare the RDS data
4.1 Log on to the ECS console
Click Elastic Compute Service, as shown in the following figure.
We can see one running ECS instance in Silicon Valley region.
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..
4.2 Prepare data
After you log on, run the following command to install the MySQL client and other tools:
apt update && apt -y install git mysql-client
Run the following command to download a MySQL project:
git clone https://github.com/datacharmer/test_db.git
Run the following commands to import data to your RDS instance: Note: Replace YOUR-RDS-PRVIATE-ADDRESS with the private IP address of your RDS instance.
cd test_db
mysql -ulabex -pAliyun-test -hYOUR-RDS-PRVIATE-ADDRESS < employees.sql
You can view the private IP address of your RDS instance, as shown in the following figure.
The data has been imported.
5. Use DMS to manage RDS
5.1 Log on to the database
Refer to the figure below to log in to the database on the Alibaba Cloud RDS console.
Refer to the following figure to set up.
If the user jumps to a new version, because the new version has just been released, it may not be stable. You can click the button in the lower right corner to experiment with the old version.
If the user wants to use the new version of the console operation is also ok, most of the interface and the old version are similar.
After logging on, select the employees database to view the tables. These tables are all imported in Section 4.2.
5.2 Table Data Amount
Refer to the figure below to view the table details of the database “employees”.
You can view the detailed information of all tables in the current database, including the number of rows, data size, index size, and occupied space.
Note that the number of rows displayed here is not actually counted in real time. Some storage engines, such as MyISAM, store accurate counts. For other storage engines, such as InnoDB, this value is an approximate value, which may be 40% to 50% higher or lower than the actual value. In this case, use SELECT COUNT (*) to obtain an accurate count.
The number of rows in an InnoDB table is only a rough estimate used in Structured Query Language (SQL) optimization.
5.3 Single Database query
Refer to the figure below and click Query of the “employee” database.
Count the number of employees owned by each department and sort the employees in a descending order.
SELECT dept_no, COUNT(*) AS emp_sum
FROM dept_emp
GROUP BY dept_no
ORDER BY emp_sum DESC;
Run the following SQL statement to count employees who have stayed in more than two departments. Copy the statement to the SQL Window and place it below the previous statement. Select this statement, and click execute. Only the selected statement is run. If you do not select the statement, all SQL statements will be run in sequence.
SELECT *
FROM dept_emp
WHERE emp_no IN (SELECT emp_no
FROM dept_emp
GROUP BY emp_no
HAVING COUNT(emp_no)>1
);
5.4 Clone a database
If you need to test a database in the production environment, but you cannot operate it directly in the production environment, you can clone a database and directly operate the clone database.
We first create a target database “employee2”.
Click Database management.
Click Create DB。
Set the database name and click OK.
The creation is complete.
In the top navigation bar, choose Data Plans > Database Clone, as shown in the following figure.
Click Database Clone。
Refer to the settings below and click Submit.
Being cloned.It takes about 20-30 minutes.
After executing for a period of time, the user can also manually stop and continue to the next steps.
Cloning is complete.
Open the “employees2” database.
You can see that the data table has been cloned.
5.5 Export data
Refer to the figure below and click Database Export.
Refer to the figure below to set the export configuration, select the data export of the specified table, and click Submit.
Exporting.
Users can refresh manually to view the export progress. After the export is complete, click Download to download the data file.
<font color='red'>The user can cut off the above result picture when doing the experiment and send it to the teacher, indicating that the part of the current chapter has been completed.</font>
5.6 Automatically generate test data
Sometimes, you need to automatically generate some test data. In the traditional way, you need to import some random data by writing some scripts, which is troublesome. DMS provides the function of automatic test data generation.
Refer to the figure below to open the single database query of the “employees” database.
Copy the following SQL statement to the window and click execute.
create table if not exists goods(
id int primary key auto_increment,
name varchar(20) not null,
price float(6,2) not null default 0,
category varchar(10) not null,
isfact enum('yes', 'no') not null,
isforeign boolean not null
)ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci;
Refer to the figure below and click Test Data Generate.
Click Test Data Generate.
Refer to the figure below to set the configuration for generating random data, and click Submit.
The execution is complete.
Looking at the data in the “goods” table, you can see that it has been randomly generated.
<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>
Note:
Before you leave this lab, remember to log off from your Alibaba RAM user account before you click the stop button of your lab. Otherwise, you will encounter some issues when opening a new lab session in the same browser.
6. Experiment summary
DMS can be used to manage relational databases such as ApsaraDB RDS for MySQL, ApsaraDB RDS for SQL Server, ApsaraDB RDS for PostgreSQL, ApsaraDB RDS for PPAS, and AnalyticDB for MySQL. Meanwhile, DMS supports online transaction processing (OLTP) databases such as Distributed Relational Database Service (DRDS), online analytical processing (OLAP) databases such as AnalyticDB and Data Lake Analytics (DLA), and NoSQL databases such as ApsaraDB for MongoDB and ApsaraDB for Redis. DMS also supports Linux server management.
