Alibaba Relational Database Service
This course explores Alibaba's RDS service looking at RDS instances, features, security, and the foundational concepts of the service. You'll follow along with guided demos from the Alibaba Cloud platform that will show you how to use and manage the RDS service.
- Get a foundational understanding of the RDS service
- Create an RDS instance
- Set up backups and temporary instances
- Set up read-only instances
- Use monitoring, metrics, and alerts in RDS
- Upgrading RDS Instance Configuration
This course is intended for anyone who wants to learn more about Alibaba RDS, as well as anyone studying for the ACP Cloud Computing certification exam.
To get the most out of this course, you should have a basic understanding of the Alibaba Cloud platform.
So let's now try a demo. So we'll create a new RDS instance, we'll create an account on that instance, a database on that instance and a table, we'll upload some data into the table, and then we'll make a backup. Afterwards, we'll try deleting the table and then we'll try to restore the table from our backup. So to do all this, I'm going to start from the alibabacloud.com homepage, and the first thing I want to do is navigate over to the console by clicking on the Console button in the upper right-hand corner. That will take me to the console homepage.
From the console homepage, I can mouse over this orange and white button here in the upper left, mouse over Products and Services, and then I can either search for RDS or just pick RDS from the recent visits at the top since I've used this product recently. That will take me over to the RDS console. And you can see, currently I'm in the Singapore region and I have no database instances running. So let's go ahead and create one.
So I'll click on Create Instance, that will take me over to the instance creation page, the purchase page. And I have two options for my billing method. I can choose either Subscription or Pay-As-You-Go. I'm going to choose Pay-As-You-Go. This is because I will be deleting my instance when I'm done with the test. If I were to choose Subscription, I'd have to keep my instance for a month. I don't want to do that, so I'll choose Pay-As-You-Go.
Database engine, you can see that for each of the four engines we offer, PostgreSQL, MariaDB, Microsoft SQL Server, and MySQL, we offer multiple database engine versions for all of those, which you can see here. In my case, I'm going to stick with MySQL and I'll choose 5.7. There are three different additions. There's Basic, High-availability, and Enterprise. I'm going to choose High-availability and I will use a local SSD. So databases with local SSD have higher I/O throughput, higher performance, and also they allow more backup types.
So if I were to choose standard SSD, I would only be allowed to make what is called a snapshot backup where you make backups of the entire instance. Further, those backups cannot be downloaded. If I choose a local SSD type, which is only available for HA or Enterprise, then I'll be able to make snapshot backups of the entire instance, physical backups of the database, and also logical backups of a particular database or table. I then need to choose a zone. I'll choose Singapore Zone A, although I could easily place my database in A, B, or C. And I will choose a multi-zone deployment, meaning that the master and slave nodes are in different zones. This is the ideal scenario for robustness and availability because this type of database configuration can survive the failure of a single zone.
I have to choose an instance type as well. Obviously, the price scales with the hardware selected. If I go up to 16 cores and 96 gigabytes of RAM, then I'm going to be paying quite a lot. It's $5 an hour. But if I choose a smaller type, like four vCPUs and eight gigabytes of RAM, then I can have my instance for maybe 50 cents an hour instead. And there are both Enterprise and Entry-level categories for the CPU and memory configuration. I'll stick with Entry-level. Capacity, it can be between five gigabytes and two terabytes. In my case, five gigabytes is plenty, so I'll scale my database down to five. I then choose Instance Configuration, and I have to select a VPC and vSwitch for my instance.
I have to select the parameters that will be used to configure the database engine. So I'm using MySQL's InnoDB. And I'll choose this high-performance default template. My time zone is UTC+08 because I'm in China, and I will make table names case insensitive. And then I go click on Next. To confirm my order, confirm how many instances I want to buy, in this case, just one, agree to the terms of service, and then I can click on Pay Now. And once I do that, that will purchase the instance and begin the setup process. So it will take a few minutes to configure the instance.
So you can see here, it says, "Order complete." I can now close this tab, return to the RDS console, and once I refresh, I should see that my instance has entered the creating state. So here you are, you can see the instance is now being created. I now just need to wait for that process to finish. It can take between two and five minutes, so we'll fast forward through that. So we now have our RDS instance up and running, let's go ahead and create an account and a database. So I'll click on Manage here to go to the management interface for this RDS instance.
So you can see here an overview of whether or not there are any disaster recovery instances configured, any read-only instances. I can configure whitelists for access to the database, I can do a crossover migration, and I can even switch to subscription billing or delete the instance if I want to. Down here, if I click Change Specification, I could also upgrade or downgrade the instance as needed. What I'm going to do first is create a new account. So what I'm going to do is create an account for managing student records. So I'll call it student_admin. Again, I just clicked on the Create Account button to bring this menu out from the right-hand side. I'll call the account student_admin, and I'll make it a privileged account so it has full access to the RDS instance and all databases on it. And then I'll choose a password.
Okay, and then I'll click OK. And that will create my new student_admin account. Let's make it student database now as well. So I'll click on Databases here to go over to the database management page and I'll click on Create Database. I'll call it students. The encoding type will be UTF-8, and I'll click Create. And now I'll have a student records database as well. So now that I have my student database and my student database account set up, I need to insert some records into this database. So I can actually use DMS, Data Management Service, to do that.
The DMS tool does a lot more than just permissions management. So if I click on Log On to DB, that will open a new tab that takes me over to the DMS management console. And you can see here, there's some information I need to plug in to be able to log in to this database. I need the name of my database account and I need the account password. I can check this box to remember the password so that the next time I log into DMS, I'll be connected directly. And then I need to test the connection. And this should fail because we haven't created a whitelist to allow access to the database. And when it does fail, DMS will send me an information, it will pop up an informational dialogue that tells me what IP address ranges I need to insert into the whitelists.
So you can see here it says, "Whitelist issues. Please add these IPs to the RDS instance whitelist." So I can copy those, go back to the console here, go up to Basic Information, and then there is this Internal Endpoint configuration here, I'll click on Configure Whitelist. That will take me to the whitelist configuration page. And before I create whitelists, I'll switch to the enhanced whitelist, which is a security enhanced whitelist configuration, and it's the default that we recommend. I'll click on Confirm. And then I'll click to create a whitelist. And I'll call my whitelist dms_access. I then need to insert those two IP address ranges separated by a comma. And then I'll click Add. And now I've created an internal endpoint for my database that's protected by a whitelist and allows traffic from these two IP address ranges used by DMS in the Singapore region.
So I can now close this info dialog and retest my connection, which should now succeed. And you can see I connected successfully. So I can click Login to log into my database, my RDS instance. There we go, let me close that password dialogue. So here we are, this is the DMS console. And you can see I have on the left-hand side a list of all of my RDS instances. These are five instances that I'm not currently logged into. And here's the one that I am currently logged into. If I expand that tab, I can see all the databases that live on this RDS instance.
Some of these are just default system databases that were created alongside the default MySQL database. My database here, students, the one I created, is the one we want to work with. So we can ignore these other database items and we'll focus on the students database. The first thing I want to do is create a table. So you'll see, I actually have the ability to execute SQL statements right here from the web console. But I have no tables, so I can't really run any select queries, can I?
So I need to create a table first. I'll do that by clicking on this little link. It says, "Click here to start building the table." Let's make a table. I'll call it student_table. And you can see, I can set the database engine type, character set, I can insert validation rules, I can have row format requirements, and so on. I'm going to leave all of that at the defaults and I'll just create some new columns. And I actually have a CSV file with some fake student information in it. And so what I'm going to do is create columns that match that file.
So just for reference, I've pulled up my CSV file, my students.csv, which contains the table of student information I'm going to insert into my database. Let's go ahead and use DMS to make a student table that has these columns. So I'll start with student name. Click on New, we'll call that column student_name. I need to choose a type. I'll choose a varchar type, a variable character. And we'll say student names can be up to 80 characters. And then the next column is student average grade. So student_average_grade. And you do want these to match the CSV exactly. So student_average_grade, and that will be a double.
Then we need, I believe, student gender. Let's double-check. Yes, student gender, student age, and student ID. So student_gender, student_age, and student_id. That last one can be an integer, and it will be the primary key for the database, so it cannot be null. And then the student_gender, will, again, be a variable character type. This time, it'll be a little bit shorter. And the student_age will be a bigint. Great, so now, if I want to actually go ahead and create this database table, I can do that easily by clicking on Modify, which will show the DDL statement that needs to be executed to create this table. And we'll even give some recommendations on how I should actually execute this.
For instance, it recommends that for some of these fields, I should add a note or a description. It also doesn't like some of the types. For instance, student_average_grade, it doesn't think should be a double. I'm going to leave it as a double anyway because that is the type that I want. And then I can click Execute, which will directly execute that DDL statement and make my student table. In fact, if I wanted to, I can now right-click and go to Open Table and see a copy of all the content in the table. Right now, there's nothing. So I'll go ahead and close that.
Let's close some of these unnecessary tabs here. Refresh. Oops, let's do that again. We'll start from, some of these tabs are from databases I'm not logged into. So let's start over. This is the students database, and within that, we have the student table. So if I want, I can do SELECT all from the student_table, limit 20. And I should get nothing back because the table is currently empty. We can also use DMS to import data, which is what we'll do right now. So let's try and put some data into this table.
So I will go to Data Plans, and there's an option here, Data Import. I'll click on that, which will open a new tab, and I'll choose this Large Data Import option right here at the top. This will help me import large data sources like a large SQL script or CSB file. I need to choose my database, so I'll choose students. I need to choose file encoding. I will not do auto identify, I'll use UTF-8. My file type is CSV. I want to put my records into the student table, and I'll do that using INSERT statements. And now I have to upload a CSV file to insert student records from. I have one right here, students.csb. I'll choose that. And then we'll click Submit. And that will actually create a task.
So I haven't actually imported anything yet. I am the DMS administrator right now, but DMS, again, is designed as a data management tool for multiple users. So, normally, what I would be doing as a RAM user would be submitting a task and waiting for the database administrator to approve it. That's why when I click Execute, what I end up with is a work order. This would normally need to be approved by a database administrator before this import could run. But in my case, it can run right away because I am the administrator. All I need to do is click on Execute Change, Run immediately, Confirm Execution, and then the task will queue up and start. And if there weren't any problems, I should see the status as executed. And if I want, I can see some details. It looks like 100 rows of data were imported successfully. So that did work.
So I can now close that tab, go back here, and rerun my SELECT statement. And I can see there are now some entries in my table. So I have some fake names, some fake average grades, some fake genders, everything worked, I do now have data loaded into my database. So now let's try to make a backup of our database. So let's go back to Backup and Restore in the RDS console. And you'll see, I have the ability to restore from a previous backup or to restore even an individual database or table, or to upload a bin log if I've got an offline bin log. I can do all that from here.
If I want to make a new backup, I click on Backup Instance. So I have a few choices, I can make either a physical or logical backup, as I mentioned. Let's make a physical backup. And I'll click OK. And that will start the backup process. It might take a little while for that to run. If you're curious of what's happening, you'll notice that refreshing doesn't cause the backup status to appear here. Nothing appears here until the backup is complete. The status shows up here. So if you click on this little icon, you'll see the status of the manual backup tasks. So let's click on that again.
You can see it's 0%. I click refresh one more time, click on that status, you can now see it's 25% complete. So let's just wait for that to finish. So after waiting a little while, the database backup is done. You can see that the full size of the physical backups, all tables and schemas and all the data, is only about 5.8 megabytes because we really don't have a lot of data in our database. So if I wanted, I could then download this backup so I have a local copy, or restore, or if I wanted, I could restore an individual database or table from this backup. So let's try that, actually.
Let's go drop our table. So we'll go back to DMS, and I will choose DROP TABLE, and we'll drop the student_table, execute. Whoops, no more students. So you can see that we now have no tables. What am I gonna do? How am I going to get my data back? One way to do that would be Restore Individual Database or Table. So I can choose from my list of backups here. I can restore onto my current instance or onto a new temporary instance. I'll choose the current instance. And I'll say I want to restore the students table, and do the students database, and we'll click OK. And that should go ahead and bring the table back for us.
So, again, we may have to wait a minute for that. Let's try refreshing the page. You can see, we have a task pending, and we are restoring that individual table. So now let's wait for that task to complete. So after refreshing, you can see the option to restore an individual table is missing. That's because we have to wait for the current restore to finish. You can see that we're at 17% now. Part of the reason this is taking so long is that in order to restore an individual table or database, the RDS system actually has to create a temporary RDS instance in the background, restore the entire physical backup onto that instance, and import the table from that temporary instance onto our current RDS instance.
Once all that is done, then the table should be restored. So let's wait a few more moments for that to finish. All right, so the Restore Individual Database or Table button is back and the little red icon up here next to the task list has disappeared. That should indicate that our database table has been restored. So let's go back over to DMS and refresh the list of tables. And sure enough, there's a new table called student_table_backup, actually, to indicate that we're recovering from a backup. Let's take a look at the content of that table. So I'll do SELECT all from student_table_backup, limit 10. And we'll execute that, and we'll see if the contents look right.
So sure enough, this is our student data. We got it back. If I wanted, I could also have set up a RDS instance on my own and restored onto that. So I can restore an entire database that way with the Restore Database button. To do that, I actually have to create a brand new ECS instance first, and the restore happens on this new instance. If I restore an individual table or an individual database from an existing physical backup, then that creates a temporary instance in the background, which then is automatically deleted once the data is back on my original RDS instance here. Whereas, if I choose this Restore Database option, I'm restoring the entire content of the RDS instance from the physical backup onto a new RDS, which is why it takes me over to the buy page. And that's all for the backup and restore demo.
Alibaba Cloud, founded in 2009, is a global leader in cloud computing and artificial intelligence, providing services to thousands of enterprises, developers, and governments organizations in more than 200 countries and regions. Committed to the success of its customers, Alibaba Cloud provides reliable and secure cloud computing and data processing capabilities as a part of its online solutions.