jack
Forum Moderator
Forum Moderator
  • UID539
  • Fans2
  • Follows0
  • Posts19
Reads:2477Replies:0

Data migration

Created#
More Posted time:Jul 18, 2016 15:38 PM


Data migration

MySQL data migration: MySQLdump tool is used.

This solution is advantageous in its easy operation, but it requires long downtime.  So it is suitable for scenarios with small data sizes, or where enough downtime is allowed.

The ApsaraDB for RDS service and the original database service are fully compatible, so for users, migrating data from the original databases to ApsaraDB for RDS is similar to the process of migrating data from a MySQL server to another. The specific process is as follows:



1. Open ECS service.

2. Open ApsaraDB for RDS service. The connection address and ports will be returned to the user, such as cloudcc.mysql.rds.aliyuncs.com: 3306.

3. Stop all services first, use MySQLdump to export data from the original database as data files (this step exports data only and doesn’t include stored procedure, trigger or function).Specific commands are as follows:

mysqldump -h local_ip -u user_name -p --opt --default-character-set=utf8   --hex-blob db_name --skip-triggers  > /tmp/db_name.sql

Among them,

local_ip should be replaced by your real offline MySQL server IP address

user_name should be replaced by your real database user

db_name should be replaced by your real database name

/tmp/db_name.sql is the file name for you to fill out at will.

4. Backup the stored procedure, trigger and function of the original database (this step can be skipped if no stored procedure, trigger and function is used in the database). When stored procedure, trigger and function are being exported, delete definer to make them compatible with ApsaraDB for RDS.Specific commands are as follows:

mysqldump -h local_ip -u user_name -p --opt --default-character-set=utf8   --hex-blob db_name --triggers --no-data --no-create-info -R | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/'  > /tmp/trigger_procedure.sql

Among them,

local_ip should be replaced by your real offline MySQL server IP address

user_name should be replaced by your real database user

db_name should be replaced by your real database name

/tmp/trigger_procedure.sql is the file name for you to fill out at will.

5. Upload the data files and stored procedure files to the purchased ECS.

6. Log into ECS remotely and import the uploaded data file to cloudcc.mysql.rds.aliyuncs.com: 3306 instance. Specific commands are as follows:

mysql -h cloudcc.mysql.rds.aliyuncs.com -u user_name -p db_name< /tmp/dbName.sql

Among them,

cloudcc.mysql.rds.aliyuncs.com should be replaced by your real ApsaraDB for RDS instance address

user_name should be replaced by your real ApsaraDB for RDS database user

db_name should be replaced by your real ApsaraDB for RDS database name

/tmp/db_name.sql is the name of the data file you just exported

7. Log into ECS remotely and import the uploaded stored procedure files (including stored procedure, trigger and function. Skip this step if they are not used) into the cloudcc.mysql.rds.aliyuncs.com: 3306 instance.Specific commands are as follows:

mysql -h cloudcc.mysql.rds.aliyuncs.com -u user_name -p db_name< /tmp/triggerProcedure.sql

Among them,

cloudcc.mysql.rds.aliyuncs.com should be replaced by your real ApsaraDB for RDS instance address

user_name should be replaced by your real ApsaraDB for RDS database user

db_name should be replaced by your real ApsaraDB for RDS database name

/tmp/trigger_procedure.sql is the name of the stored procedure file you just exported.

By now the data migration has been complete and you can use ApsaraDB for RDS normally. Users can connect the database with the database client tool (e.g., MySQL-Front) or third-party database management tool (e.g., Phpmyadmin), or applications through the domain name and ports. They can log into the database with the correct user name and password for operation and development.
Guest