实践练习四

最新推荐文章于 2024-09-28 13:51:46 发布

wanxxan

最新推荐文章于 2024-09-28 13:51:46 发布

阅读量40

点赞数

文章标签： mysql 数据库 oracle

本文链接：https://blog.csdn.net/u011355991/article/details/130214794

版权

一、环境准备

[root@localhost mysql]# mysql -h 127.1 -P 3308 -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.33 MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>

使用sysbench初始化数据

[root@localhost mysql]# mysql -h 127.1 -P 3308 -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.33 MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>
mysql> create database sbtest ;
Query OK, 1 row affected (0.00 sec)

初始化数据

[root@localhost sysbench]# sysbench /usr/local/share/sysbench/oltp_common.lua --time=300 --mysql-host=127.0.0.1 --mysql-port=3308 --mysql-user=root --mysql-password=royce --mysql-db=sbtest --table-size=100000 --tables=10 --threads=16 --events=999999999 prepare
sysbench 1.1.0-df89d34 (using bundled LuaJIT 2.1.0-beta3)

Initializing worker threads…

Creating table ‘sbtest4’…
Creating table ‘sbtest7’…
Creating table ‘sbtest2’…
Creating table ‘sbtest3’…
Creating table ‘sbtest1’…
Creating table ‘sbtest5’…
Creating table ‘sbtest9’…
Creating table ‘sbtest6’…
Creating table ‘sbtest10’…
Creating table ‘sbtest8’…
Inserting 100000 records into ‘sbtest4’
Inserting 100000 records into ‘sbtest6’
Inserting 100000 records into ‘sbtest9’
Inserting 100000 records into ‘sbtest1’
Inserting 100000 records into ‘sbtest7’
Inserting 100000 records into ‘sbtest10’
Inserting 100000 records into ‘sbtest3’
Inserting 100000 records into ‘sbtest5’
Inserting 100000 records into ‘sbtest8’
Inserting 100000 records into ‘sbtest2’
Creating a secondary index on ‘sbtest2’…
Creating a secondary index on ‘sbtest1’…
Creating a secondary index on ‘sbtest3’…
Creating a secondary index on ‘sbtest7’…
Creating a secondary index on ‘sbtest5’…
Creating a secondary index on ‘sbtest9’…
Creating a secondary index on ‘sbtest4’…
Creating a secondary index on ‘sbtest6’…
Creating a secondary index on ‘sbtest8’…
Creating a secondary index on ‘sbtest10’…

mysql> use sbtest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

二、迁移数据
使用mysqldump导出

[root@localhost sysbench]# mysqldump -uroot -proyce -h127.0.0.1 -P3308 --single-transaction --hex-blob --routines --events --triggers --master-data=2 --set-gtid-purged=OFF --databases sbtest --default-character-set=utf8 --max_allowed_packet=512M > /tmp/sbtest.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

导入数据

obclient [test]> source /tmp/sbtest.sql
Query OK, 0 rows affected (0.002 sec)

三、使用DATAX离线迁移数据
下载datax

[root@localhost ~]# wget http://datax-opensource.oss-cn-hangzhou.aliyuncs.com/datax.tar.gz
–2022-10-23 22:09:24-- http://datax-opensource.oss-cn-hangzhou.aliyuncs.com/datax.tar.gz
Resolving datax-opensource.oss-cn-hangzhou.aliyuncs.com (datax-opensource.oss-cn-hangzhou.aliyuncs.com)… 47.110.23.138
Connecting to datax-opensource.oss-cn-hangzhou.aliyuncs.com (datax-opensource.oss-cn-hangzhou.aliyuncs.com)|47.110.23.138|:80… connected.
HTTP request sent, awaiting response… 200 OK
Length: 853734462 (814M) [application/gzip]
Saving to: ‘datax.tar.gz’

100%[====================================================================================>] 853,734,462 1.35MB/s in 15m 32s

2022-10-23 22:25:02 (894 KB/s) - ‘datax.tar.gz’ saved [853734462/853734462]

删除隐藏文件

[root@localhost ~]# tar -xf datax.tar.gz -C /usr/local/
[root@localhost ~]# cd /usr/local/datax
[root@localhost datax]# ls
bin conf job lib log log_perf plugin script tmp
[root@localhost datax]# find /usr/local/datax/plugin -name “.*” | xargs rm -f
[root@localhost datax]# yum install -y python2 java
生成模板配置文件并修改

[root@localhost datax]# python2 /usr/local/datax/bin/datax.py -r mysqlreader -w oceanbasev10writer > /usr/local/datax/job/my2ob.json
[root@localhost datax]# vi /usr/local/datax/job/my2ob.json

配置文件示例
{
“job”: {
“content”: [
{
“reader”: {
“name”: “mysqlreader”,
“parameter”: {
“column”: [““],
“connection”: [
{
“jdbcUrl”: [“jdbc:mysql://127.0.0.1:3308/sbtest”],
“table”: [“sbtest1”]
}
],
“password”: “royce”,
“username”: “root”,
“where”: “”
}
},
“writer”: {
“name”: “oceanbasev10writer”,
“parameter”: {
“column”: [””],
“connection”: [
{
“jdbcUrl”: “||dsc_ob10_dsc||obdemo:obmysql||dsc_ob10_dsc||jdbc:oceanbase://127.0.0.1:2883/sbtest?useUnicode=true&characterEncoding=utf-8”,
“table”: [“sbtest1”]
}
],
“obWriteMode”: “insert”,
“password”: “royce”,
“username”: “root”
}
}
}
],
“setting”: {
“speed”: {
“channel”: “2”
}
}
}
}

启动同步任务

[root@localhost job]# python2 /usr/local/datax/bin/datax.py /usr/local/datax/job/my2ob.json
…
任务启动时刻 : 2022-10-24 01:23:08
任务结束时刻 : 2022-10-24 01:23:30
任务总计耗时 : 22s
任务平均流量 : 916.88KB/s
记录写入速度 : 5000rec/s
读出记录总数 : 100000
读写失败总数 : 0

验证数据

obclient [test]> use sbtest
Database changed
obclient [sbtest]>
obclient [sbtest]>
obclient [sbtest]> select count(1) from sbtest1;
±---------+
| count(1) |
±---------+
| 100000 |
±---------+
1 row in set (0.118 sec)