Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog Migrating a Self-built MySQL Database to Alibaba Cloud RDS for MySQL with Minimal Downtime

Migrating a Self-built MySQL Database to Alibaba Cloud RDS for MySQL with Minimal Downtime

This article introduces the steps to migrate a self-built MySQL database to Alibaba Cloud RDS running MySQL with DTS utility to minimize the downtime.

Preparations

Before performing your database migration, some preparation work is required to be conducted first on the source database to ensure migration goes smoothly.

# Item Expectation DB Restart Involved? Remark
1 server_id Any integer greater than "1" Yes To enable change data replication
2 log_bin Having a value, e.g. "binlog" or "mysql-bin", so that binary log is enabled Yes To enable change data replication
3 binlog_format Having the value of "row" No To enable change data replication
4 binlog_row_image Having the value of "full" No When the source database is MySQL 5.6.2 or later
5 Migration Account An account with select, replication slave, replication client privilege granted No A DB account for migration
6 Primary Key Every table has a primary key defined No To prevent the table from getting locked

Ensure Binary Log is Properly Configured

In order to migrate the change data during the migration, the items 1 to 4 as per the above table should be configured on the source database.

Check Current Settings of the Source Database

Check the current settings with the below commands:

# Login the source database with mysql utility
mysql -u user_name -p -h Source_DB_IP -P Source_DB_PORT

# Check the variable server_id
show global variables like 'server_id';

# Check the variable log_bin
show global variables like 'log_bin';

# Check the variable binlog_format
show global variables like 'binlog_format';

# Check the variable binlog_row_image when DB version is MySQL 5.6.2 or later
show global variables like 'binlog_row_image';

Here is an output example that matches the migration requirements.

1

Reconfigure the Source Database

If server_id or log_bin does not match the requirement, a DB restart must be involved during reconfiguration.

# Modify DB configuration file
vi my.cnf
######## Add below lines ########
# Replication
server-id                = 300

# Binary Logging
log_bin                    = binlog

After that, restart the source database in an appropriate time window.

# Stop the source database
service mysql stop

# Start the source database
start mysql start

Variables binlog_format and binlog_row_image can be changed online; so, that will be much more convenient to adjust them.

Log in to the source database with root or any other account who has "super" privilege assigned, then execute below commands.

# Login the source database with mysql utility
mysql -u user_name -p -h Source_DB_IP -P Source_DB_PORT

# Set the binary log format to row
set global binlog_format='row';

# Set the binary log row image to full
set global binlog_row_image='full';
# Note: In order to let above change take effect, after modifying the binlog_row_image parameter, you need to terminate all connected sessions in case long connection mechanism is introduced by Application and let the application re-establish the connection to the source database instance.

Change the configuration file as well to ensure the settings remain the same, even if the source database experiences a restart.

# Modify DB configuration file
vi my.cnf

######## Add below lines ########
binlog_format                = row
binlog_row_image            = FULL

Here is an example of the variables in the configuration file.

2

Create a Migration Account on the Source Database

A database account with select, replication slave and replication client privilege granted should be created on the source database.

Log in to the source database with root or any other account that has "super" privilege assigned, then execute below commands.

# Login the source database with mysql utility
mysql -u user_name -p -h Source_DB_IP -P Source_DB_PORT

# Create migration account
grant select, replication slave, replication client on *.* to  'macc'@'%' identified by 'm2RDSmysql';
flush privileges;

# Check the privilege
show grants for macc;

3

Check Whether Any Table Has No Primary Key Defined

Since change data replication is selected, the non-transactional table without primary key defined is to be locked during the data migration to ensure data consistency.

Write operation is not allowed against the locked table during the locking period, which depends on the table data volume.

The locked table will be released only after it has been migrated.

Check the tables with the following SQL in non-business hours.

select 
    table_schema, table_name
from
    information_schema.tables t
where
    not exists( select 
            table_schema, table_name
        from
            information_schema.key_column_usage k
        where
            t.table_schema = k.table_schema
                and t.table_name = k.table_name
                and k.constraint_name = 'primary')
        and t.table_schema not in ('performance_schema' , 'information_schema', 'mysql');

Conduct primary key modifications during non-business hours.

Here is an example of adding a primary key to an existing table.

# Table definition

CREATE TABLE `my_pk` (
  `id` bigint(20) NOT NULL,
  `areaid` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

# Add primary key when MySQL 5.6 or later
alter table my_pk algorithm=inplace, lock=none, add primary key(id);

# Add primary key 
alter table my_pk add primary key(id);

Migration

Now, let's see how to access the Data Transmission Service (DTS) console and start the migration.

Configure Migration Task

Create Migration Task

Open the DTS console and click the "Create Migration Task" button at the top-right corner of the Data Migration page.

4

Configure Task Name and Connection Information

Configure the connection information of the self-built MySQL instance and the target RDS instance.

5

Then click the "Authorize whitelist and enter into next step" button at the lower right corner to open the next page.

6

Select Migration Type and To-be-migrated Objects

Select all the "Migrate object structure", "Migrate existing data", and "Replicate data changes" migration types, then choose the "To-be-migrated objects"; when finished, click the "Pre-check and start" button.

7

Fix Any Error Found in Pre-check

If any check fails during pre-check, click the "Information" button and get the details about the failure and solution.

8

In our case, since we have tables without a defined primary key, reload and lock tables privileges should be granted to the migration account.

9

10

After the solution has been applied, cancel the pre-check and click the "Revise task configuration" of the migration task.

11

Then rerun the pre-check. Repeat this circle if needed until it gets 100% success, then click "Next".

12

Purchase and Start the Migration

Choose an appropriate migration specification based on the data volume, then agree with the terms and start the migration.

13

Cut-over

Once the target RDS for MySQL instance is synchronized ("Without delay") with the source self-built MySQL instance, we can make a decision when to conduct the cut-over.

14

Cut-over Preparations

First, stop the application in the chosen cut-over window (obviously in non-business hours) so that no incremental data will be written into the source MySQL instance.

Then, insert a table row as the "finish tag" manually into the source instance.

15

Check if it has been replicated to the target RDS for MySQL instance.

16

Once the "finish tag" data is found on the target RDS instance, which means all data is synchronized between the source instance and the target instance, the preparation work is done.

Cut-over Steps

Select the migration task and click the "Finish" button on the DTS console page and agree to stop the migration.

17

18

After the migration task is finished, direct the application to use the target RDS for MySQL instance by modifying the application DB connection string.

Finally, start the application and perform the verification.

Learn more about how Alibaba Cloud can help you simplify your database migration process through the Data Transmission Service

MySQL Database dts Cloud Database Data Transmission Service database migration ApsaraDB RDS for MySQL
0 0 0
Share on

Read previous post:

Restoring Backup Data from an ApsaraDB RDS for MySQL 5.7 Instance to a User-created Database Instance

Jack008

2 posts | 0 followers

You may also like

Comments

Jack008

2 posts | 0 followers

Related Products

More Posts by Jack008

See All