开发者学堂课程【上云迁移实战:使用 DTS 迁移结构化数据】学习笔记,与课程紧密联系,让用户快速学习知识。
课程地址:https://developer.aliyun.com/learning/course/514/detail/6856
使用 DTS 迁移结构化数据
MySQL 到 RDS MySQL
1、MySQL 到 RDS MySQL:DTS 不停机迁云
前提:完成 RDS 实例的准备,连接模式,数据库和账号等
权限:
迁移类型 |
结构迁移 |
全量迁移 |
增量迁移 |
本地数据库 |
select |
select |
Select super replication slave |
RDS MySQL |
读写权限 |
读写权限 |
读写权限 |
2、MySQL DTS 迁云步骤
(1)创建数据库账号
本地数据库创建迁移账号
CREATE USER 'username'@'host’IDENTIFIED BY 'password';
示例: CREATE USER 'dts user @'%’IDENTIFIED BY 'dts1234’;
本地数据库中给迁移账号授权
GRANT privileges ON databasenametablename TO'username @host’wiTH
GRANT OPTION;
示例: GRANT ALL ON *.* TO 'dts user'@'%’;
(2)开启 binlog
查看是否开启 binlog
show global variables like "log_bin“
开启 binlog
log bin=mysql_bin
binlog_format=row
server_id=大于1的整数
Binlog_row_image=full// 当本地 MySQL 版本大于56时,则需设置该项
修改完成后,重启 MySQL 进程
mysql dir bin mysgladmin-u root-p shutdown
mysql dir/bin/safe mysqld &
(3)迁移任务配置
创建迁移任务
配置源和目标数据库
(4)选择迁移类型及预检查
(5)
(6)启动迁移任务并等待迁移任务完成
3、演示
Xshell for Xmanager Enterprise 5(Build 0973)
Copyright (c)2002-2016 NetSarang Computer, Inc. All rights reserved
.
Type `help' to learn how to use Xshell prompt.
[d:\~]$
Connecting to 47.94.225.213:22...
Connection established.
To escape to local shell, press 'ctrl+Alt+]’.
WARNING! The remote SSH server rejected X11 forwarding request.
Last login: Mon Nov 6 01:19:24 2017 from 111.162.45.152
Welcome to Alibaba Cloud Elastic Compute Service!
[root@iZsldoo8zx7zc9Z ~]# vi/etc/my.cnf
[client]
port =3306
socket =/tmp/mysql.sock
[mysqld]
port =3306
socket =/tmp/mysgl.sock
skip-external-locking
log-error=/data/mysql/error.log
key buffer size=256M
max allowed packet =1M
table open cache =256 s
ort buffer size=1M
read buffer size =1M
read rnd buffer size=4M
myisam sort buffer size =64M
thread cache size=8
query cache size= 16M
log-bin=mysql-bin
b
inlog
_
format=
row
server-id =
2
Sgl
_
mode=NO
_
ENGINE
_
SUBSTITUTION
,
STRICT
_
TRANS
_
TABLES
保存退出,重启 mysql
Xshell for Xmanager Enterprise 5(Build 0973)
Copyright (c)2002-2016 NetSarang Computer, Inc. All rights reserved
.
Type `help' to learn how to use Xshell prompt.
[d:\~]$
Connecting to 47.94.225.213:22...
Connection established.
To escape to local shell, press 'ctrl+Alt+]’.
WARNING! The remote SSH server rejected X11 forwarding request.
Last login: Mon Nov 6 01:19:24 2017 from 111.162.45.152
Welcome to Alibaba Cloud Elastic Compute Service!
[root@iZsldoo8zx7zc9Z ~]# vi/etc/my.cnf
[root@iZsldoo8zx7zc9Z ~]# service mysqld restart
Shutting down MySQL... [ OK ]
Starting MySQL. [
O
K ]
[root@iZsldoo8zx7zc9Z ~]#
本地配置完成,配置 RDS
(1)创建数据库账户:
(2)配置白名单
(3)创建 DTS 迁移任务
选择华北2,将本地自建 IP 地址填好
测试链接,链接通过
测试都通过后点击授权白名单并进入下一步
选择增量迁移和迁移的对象
预检查并启动,需要等待一段时间
检测成功之后开启任务,根据数据量选择规格
启动,DTS 处于迁移中
等待迁移完成即可,增量迁移会一直保持数据同步