Migrating a Database to ApsaraDB on Alibaba Cloud

This tutorial introduces Apsara, the database service from Alibaba Cloud. It shows how to set up the service and create an ApsaraDB for RDS MySQL instance to which you can then migrate an existing database.

Prerequisites

To use ApsaraDB you'll need an Alibaba Cloud account. If you don't already have one, head to www.alibabacloud.com and sign up. To help you try out the facilities, you'll find a range of free trials and free account credit offers there.

About ApsaraDB

Traditionally, databases have resided within instances of a database server such as SQL Server, MySQL, MongoDB and others.

Sometimes the database engine runs on the same server as the application whose data it stores. For example, a single Linux server might run Apache, PHP and MySQL.

As the application scales and more performance is required, moving the database to its own server is the obvious next step. Just set up a new Linux or Windows server, install the requisite database engine and migrate the data. Then it's just a case of changing some configuration files within the application code to point the application at the new database server.

As part of the world moved towards cloud computing infrastructure, new database server architecture arrived. Cloud providers such as Alibaba Cloud now provide database server instances as a service. No longer is there any need to run a dedicated Windows or Linux server purely to host a copy of MySQL or SQL Server. No longer do you need to patch your database server or manage its security.

Alibaba Cloud's database service is called ApsaraDB and it's currently available for SQL Server, MySQL, PostgreSQL, and Postgres Plus Advanced Server (PPAS), which is compatible with Oracle.

ApsaraDB instances are fast, cost-effective, easy to back up and very quick to scale when you need additional performance. They all run on SSD drives and you simply pay according to the amount of data you process. If you need more performance or storage it can be arranged with just a few clicks on the management console. Pricing is based on a monthly subscription that starts from USD $29. Or if you prefer, a pay-as-you-go option starts from less than 3 cents ($0.29) per hour provided that your ApsaraDB instance is active (storage and Internet traffic are additional costs).

Just as with a cloud-based Linux or Windows server instance, ApsaraDB database instances include a comprehensive monitoring system that's built-in. You can keep a close eye on the performance of your hosted databases, in order to help you make decisions about whether additional resources might be required. You can set alarms and triggers so that you will automatically be notified if performance or disk space falls below specified thresholds.

With an ApsaraDB instance, there's no underlying operating system or virtual server for you to pay for, secure, patch or manage. You get to concentrate on managing just your database tables. Everything else is taken care of by Alibaba Cloud.

ApsaraDB includes security features such as real-time monitoring of network traffic, and automatic interception of SQL injection, brute force attacks and other types of database attacks. This is all built into each ApsaraDB instance and does not need to be configured or purchased separately, as would be the case if you hosted your databases on a conventional cloud server running Linux or Windows and a separate relational database management system (RDBMS).

This tutorial will show you how to set up an ApsaraDB instance for MySQL in order to migrate an existing database to it. To learn more about backing up databases, please see our tutorial here. Further information, as well as documentation about how to use other database technologies apart from MySQL, can be found in the Database Services section of the Products menu on the Alibaba Cloud website.

Setting up ApsaraDB for MySQL requires the use of the ApsaraDB for RDS service, and there are five steps involved: creating the ApsaraDB instance, setting the whitelist, setting the IP address, creating an account and starting to use the database. We’ll cover each of these steps in turn.

Will You Need a VPC?

When you create a virtual server or an ApsaraDB instance on Alibaba Cloud you need to assign it to a VPC, or Virtual Private Cloud. A VPC is your virtual LAN in the cloud, complemented by a virtual switch or VSwitch, that allows you to control the security and IP addressing of your instances.

When we create our ApsaraDB for RDS instance in the section that follows, we'll be asked to assign it to an existing VPC. Assuming that you already have one set up, in the hosting region that you'll be using for your ApsaraDB instance, you can move straight to Step 1 below to create your ApsaraDB instance. But if you don't already have a VPC and VSwitch, then it's best to create these first, as it makes things easier later.

To do this, log into your Alibaba Cloud console and, from the products menu, choose Virtual Private Cloud. You should see a screen that looks like this, with any existing VPCs listed if you have them.

For this tutorial we will create a new VPC. We will be using the Frankfurt region for our ApsaraDB instance so, from the menu at the top of the screen, we ensure that this is selected.

Click on Create VPC and the console will show a panel such as this:

Give your VPC a name. We'll call ours apsara-evaluation-vpc.

Next, choose an internal IP address block for your VPC.

You can choose from three ranges. We'll stick with the default 192.168 range.

You also need to create a VSwitch, which you can do from the same screen. Give it a name, choose the specific hosting region zone and set the destination CIDR block. Again, we will stick with the default IP address range and use a /24 suffix which allows for up to 252 usable private IP addresses in our switch.

With everything specified, the screen looks like this.

Press the OK button and, after a few seconds, you will see that everything has been created.

Close the confirmation panel by clicking the X symbol at the top right-hand corner.

You now have a VPC and VSwitch set up, ready to accommodate the ApsaraDB for RDS instance that you're about to create. You will see the VPC listed on the screen, along with its name and status.

With the VPC set up, we can now move onto the actual stages of creating an ApsaraDB for RDS instance.

Step 1. Creating the ApsaraDB Instance

Log into your Alibaba Cloud account console and, from the database products menu, choose ApsaraDB for RDS. You can go straight to the correct page by browsing to https://rdsnew.console.aliyun.com.

Your screen will look something like this.

Click on the Create Instance button on the top right-hand side of the screen.

You’ll need to decide whether you want to opt for pay-as-you-go billing or a monthly subscription. The ApsaraDB pages on the Alibaba Cloud web site include information to help you decide which is best, including this chart:

For this tutorial we’ll use the pay-as-you-go billing method, as it’s ideally suited to short-term experiments and evaluations. You pay by the hour while your instance is active and terminating the instance will result in no further charges. So start by clicking on the relevant billing option tab to select it.

Next, select the hosting region where your ApsaraDB instance will be located. If you already have one or more Alibaba Cloud products in a particular region, then it makes sense to use that one.

For this tutorial we will choose Frankfurt.

Now we need to choose our database engine and version, and the zone of the hosting region that we want (if there is more than one). For this tutorial we choose MySQL v5.7 in Frankfurt zone A. We don't need high availability, for now, so we will opt for the basic version, which is cheaper.

Next, we will set up the networking for our ApsaraDB instance.

In some hosting regions you have the option of either the classic network or VPC (virtual private cloud). Classic network is a legacy mode and is not recommended for new ApsaraDB instances. VPCs are more flexible and more secure, and – in the case of the Frankfurt region – it's the only option available to us anyway, so that's what we will use.

If you created a VPC and VSwitch specifically for evaluating Apsara, as explained earlier in this tutorial, then select it here. Or if you have an existing VPC and Vswitch that you use with your Alibaba Cloud account, select those instead. In this instance, we will use the ApsaraDB evaluation VPC and VSwitch that we created at the start of this tutorial. (If you don't see your VPC name in the dropdown list, make sure that you have selected the same hosting region for your ApsaraDB instance as the one for your VPC).

Finally, choose the specification of your ApsaraDB instance and the quantity. As you select different options for the server and the amount of storage, you will see the price reflected on the right-hand side of the screen. You can also see details about the maximum number of simultaneous connections, total IOPS, processor cores and RAM.

The smallest MySQL instance offers 1 core, 1GB of RAM, 300 connections, 600 IOPS and 5GB of storage. The largest provides 60 cores, 470GB, 64,000 connections, 50,000 IOPS and 5TB of storage.

With everything specified, the screen looks like this:

Our ApsaraDB instance (excluding storage and Internet traffic) will cost 2.9 cents per hour or around $21 per month, until it’s terminated. To proceed, click on Buy Now.

Review the specification, tick the box to agree to the terms of service and click Pay Now.

The order has been placed. Click the button to return to the console, click the ApsaraDB link in your list of products and you’ll see that your instance is being created.

The instance will shortly start running.

Note that a pay-as-you-go instance attracts hourly charges all the time that it is in existence. When you no longer need the instance, terminate it in order to avoid being charged. You can do this by clicking on the More link in the instance detail line and then choosing Release Instance. You will lose any data that hasn't been backed up. Another option is to convert it to a monthly subscription instance by clicking the Subscription Billing link.

Step 2. Set a Whitelist

With the ApsaraDB instance created, step 2 is to set a whitelist. This is a list of IP addresses which are permitted to connect to it. By default, no addresses are allowed to connect so we need to change this.

Log into your Alibaba Cloud console, head to the ApsaraDB for RDS section and choose your correct region to see your ApsaraDB instances.

Click on the instance name.

Click on the Security link on the left-hand side and you will see the following.

You can create and modify lists of IP addresses in order to grant access to your ApsaraDB instance.

For the purpose of this tutorial, we will allow access from all IP addresses. To do this, click on the Modify link for the default group. Remove 127.0.0.1 from the list and add 0.0.0.0/0. Remember that this is only for the purpose of this tutorial. In normal circumstances, you would add only the address or addresses of the servers that are allowed to connect to the ApsaraDB instance.

Step 3. Apply for an IP Address

If you will be connecting to the ApsaraDB instance from an Alibaba Cloud ECS server that is in the same region (not necessarily the same zone), you simply need to use the connection information that is shown on the ApsaraDB page under “Basic Information”. The connection will be made via Alibaba Cloud’s intranet infrastructure.

Here you can see the intranet address and the port number.

If you will be connecting from elsewhere, such as a different Alibaba Cloud region or from another location, you will need to apply for an external address for the ApsaraDB instance.

To do this, click on the “Apply for Internet Address” link (as shown in the image above). A few seconds later, the request is actioned and we now have an external connection address:

We can ping the address to verify that it is indeed accessible.

Step 4. Create an Account

With the database instance now up and running, and accessible via an Internet address and a suitable whitelist, we now need to create an account so that we can connect to the instance in order to use it for storing databases.

Each ApsaraDB instance allows up to 500 accounts and 500 databases. If you are going to migrate an existing database to your ApsaraDB instance, you need to ensure that the database name and the account name are the same as the database that you're migrating. If this is not the case your existing application will not be able to connect to the migrated database without additional configuration changes.

To start, log into your Alibaba Cloud console at https://rdsnew.console.aliyun.com and ensure you are at the ApsaraDB for RDS section for your chosen region (Frankfurt in our case).

Click on the id of your ApsaraDB instance and you'll see the Basic Information screen. Then click on the Accounts link in the left-hand menu:

As you can see, there’s an account called root that has been set up already. To reset its password, click on the Reset Password link.

Choose a new password, which must be between 8 and 32 characters in length and also conforms to the requirements shown on the screen. We can use this to connect to our ApsaraDB instance.

You'll need to know the Internet address of your instance for the next step of the tutorial. So, go to your Basic Information screen and copy it, then store it somewhere safe.

Once you’ve noted the connection address you can log out of the Alibaba Cloud console for now.

Step 5. Access the ApsaraDB Instance

You should now be able to access the ApsaraDB instance. You can use any MySQL client to do this, from any location (assuming that the IP address from where you're connecting has been added to the instance's whitelist where necessary).

For this tutorial, we will use a free MySQL client called MySQL-Front, running on Windows. This will be done from a desktop PC rather than a cloud server to illustrate that you can connect to an ApsaraDB instance from anywhere, which is useful during migrations. This will be permitted because we added 0.0.0.0/0 to the instance's whitelist, which allows connections from any IP address.

Download MySQL-Front from http://www.mysqlfront.de and install it, then run it. The screen will look like this:

Choose a name for your database connection. For the host name, use the Internet connection address you copied earlier. Enter the password that you chose for the root account. For the database entry, click on the 3 dots and then choose MySQL when the choice appears.

Now click OK and you will be connected to your new ApsaraDB instance.

To create a new database, use the Database menu.

To import an existing database that has been backed up to a .SQL file, use the Import option from the File menu.

Alternatively, you can use any other method to migrate your database to the new ApsaraDB instance. For example, a PHP script that copies tables from one database server to another, or a dedicated third-party migration tool.

Migrating between ApsaraDB Instances with DTS

If you need to migrate databases from one ApsaraDB instance to another, the Alibaba Cloud Data Transmission Service makes the process easy.

DTS automatically copies or synchronizes data between ApsaraDB instances, even if those instances are in different regions or they use different database engines.

DTS can be used as a one-off migration tool which copies data from one instance to another, converting between storage technologies automatically (e.g. MySQL to MongoDB). Alternatively, it can synchronize the two instances in real time, with any changes to the source databases automatically replicated to the destination. Thus when you're preparing for a migration, you can switch over at any time safe in the knowledge that the new instance is fully ready to use and contains up-to-date data.

Full information about DTS is outside the scope of this tutorial, but you can find details at https://www.alibabacloud.com/product/data-transmission-service.

Clone the ApsaraDB for RDS Instance

You can clone an ApsaraDB for RDS instance. You can do this with a single click from the backup and monitoring tab on your instance's menu.

Your cloned instance can then be used as a "hot standby", to be brought online if needed. Just change your application's configuration so that it connects to the cloned instance. Or migrate the data from the clone to the original instance.

Clones can be created on a pay-as-you-go basis and cost from just a few cents per hour. So, whenever you're doing important work on a database instance, such as updating lots of data, creating a clone beforehand will provide a useful safety net in case the maintenance task doesn't go to plan.

Monitoring Your ApsaraDB Instance

ApsaraDB instances include a comprehensive monitoring system so you can keep an eye on the performance of your database engine.

To use it, head to your Alibaba Cloud console and the ApsaraDB for RDS view for your chosen region. Click on the id of your ApsaraDB instance and then, from the left-hand menu, choose Monitoring and Alarms.

You can see a series of reports that show performance over various time periods, and you can monitor queries per second, transactions per second, and a variety of other key metrics.

You can set alarms and triggers, and then keep track of them. To do so, click the Alarms tab on the monitoring page.

The default alarm rules check for excessive IOPS, connections and CPU usage, and shortage of disk space. You can modify these rules or create additional ones if you wish.

Database Engine Parameters

As with a conventional server running a copy of MySQL, ApsaraDB allows you to customize the system’s parameters. From the instance’s menu, click on the Parameters link on the left-hand side.

You can view and edit all of the parameters, and also view helpful information about each one.

Summary

This tutorial has shown you how to establish and utilize an ApsaraDB for RDS instance on Alibaba Cloud in order to provide a cloud-hosted database engine that is fully compatible with MySQL. You should now be able to create an ApsaraDB instance, connect to it and store data. You should also be able to back up your data, and monitor the performance of your ApsaraDB instance.

You can find more information about ApsaraDB for RDS and the database types it supports at https://www.alibabacloud.com/product/apsaradb-for-rds.