使用datax实现将mysql一张表的指定字段的数据抽取出来，并打印出来
1）第一步：创建mysql数据库以及向mysql当中插入数据执行以下命令创建mysql表数据
在 192.168.1.6 数据库中操作
# mysql -u root -h 192.168.1.6 -P 3306 -p'123456'
mysql> CREATE DATABASE `userdb`;
mysql> USE `userdb`;
mysql> DROP TABLE IF EXISTS `emp`;
mysql> CREATE TABLE `emp` (
`id` int(11) DEFAULT NULL,
`name` varchar(100) DEFAULT NULL,
`deg` varchar(100) DEFAULT NULL,
`salary` int(11) DEFAULT NULL,
`dept` varchar(10) DEFAULT NULL,
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`is_delete` bigint(20) DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
mysql> insert into `emp`(`id`,`name`,`deg`,`salary`,`dept`,`create_time`,`update_time`,`is_delete`) values (1201,'gopal','manager',50000,'TP','2018-06-17 18:54:32','2019-01-17 11:19:32',1),(1202,'manishahello','Proof reader',50000,'TPP','2018-06-15 18:54:32','2018-06-17 18:54:32',0),(1203,'khalillskjds','php dev',30000,'AC','2018-06-17 18:54:32','2019-03-14 09:18:27',1),(1204,'prasanth_xxx','php dev',30000,'AC','2018-06-17 18:54:32','2019-04-07 09:09:24',1),(1205,'kranthixxx','admin',20000,'TP','2018-06-17 18:54:32','2018-12-08 11:50:33',0),(1206,'garry','manager',50000,'TPC','2018-12-10 21:41:09','2018-12-10 21:41:09',1),(1207,'oliver','php dev',2000,'AC','2018-12-15 13:49:13','2018-12-15 13:49:13',1),(1208,'hello','phpDev',200,'TP','2018-12-16 09:41:48','2018-12-16 09:41:48',1),(1209,'ABC','HELLO',300,NULL,'2018-12-16 09:42:04','2018-12-16 09:42:24',1),(1210,'HELLO','HELLO',5800,'TP','2019-01-24 09:02:43','2019-01-24 09:02:43',1),(1211,'WORLD','TEST',8800,'AC','2019-01-24 09:03:15','2019-01-24 09:03:15',1),(1212,'sdfs','sdfsdf',8500,'AC','2019-03-13 22:01:38','2019-03-13 22:01:38',1),(1213,NULL,'sdfsdf',9800,'sdfsdf','2019-03-14 09:08:31','2019-03-14 09:08:54',1),(1214,'xxx','sdfsdf',9500,NULL,'2019-03-14 09:13:32','2019-03-14 09:13:44',0),(1215,'sdfsf','sdfsdfsdf',9870,'TP','2019-04-07 09:10:39','2019-04-07 09:11:18',0),(1216,'hello','HELLO',5600,'AC','2019-04-07 09:37:05','2019-04-07 09:37:05',1),(1217,'HELLO2','hello2',7800,'TP','2019-04-07 09:37:40','2019-04-07 09:38:17',1);
查看数据
node03执行以下命令查看帮助文档
# cd /data/datax
# python bin/datax.py -r mysqlreader -w streamwriter
node03执行以下命令开发datax配置文件，根据实际情况填写用户名和密码等。
# cd /data/datax/job
# vim mysql2stream.json
{
"job": {
"setting": {
"speed": {
"channel": 3
},
"errorLimit": {
"record": 0,
"percentage": 0.02
}
},
"content": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
"username": "root",
"password": "123456",
"column": [
"id",
"name"
],
"connection": [
{
"table": [
"emp"
],
"jdbcUrl": [
"jdbc:mysql://192.168.1.6:3306/userdb"
]
}
]
}
},
"writer": {
"name": "streamwriter",
"parameter": {
"encoding":"GBK",
"print":true
}
}
}
]
}
}
3）第三步：启动datax实现数据同步
node03执行以下命令实现datax数据同步
# cd /data/datax
# python /data/datax/bin/datax.py /data/datax/job/mysql2stream.json
报错：
2023-02-27 10:49:31.382 [main] WARN ConfigParser - 插件[mysqlreader,streamwriter]加载失败，1s后重试... Exception:Code:[Common-00], Describe:[您提供的配置文件存在错误信息，请检查您的作业配置 .] - 配置信息错误，您提供的配置文件[/home/data/datax/plugin/reader/._drdsreader/plugin.json]不存在. 请检查您的配置文件.
2023-02-27 10:49:32.389 [main] ERROR Engine -
经DataX智能分析,该任务最可能的错误原因是:
com.alibaba.datax.common.exception.DataXException: Code:[Common-00], Describe:[您提供的配置文件存在错误信息，请检查您的作业配置 .] - 配置信息错误，您提供的配置文件[/home/data/datax/plugin/reader/._drdsreader/plugin.json]不存在. 请检查您的配置文件.
at com.alibaba.datax.common.exception.DataXException.asDataXException(DataXException.java:26)
at com.alibaba.datax.common.util.Configuration.from(Configuration.java:95)
at com.alibaba.datax.core.util.ConfigParser.parseOnePluginConfig(ConfigParser.java:153)
at com.alibaba.datax.core.util.ConfigParser.parsePluginConfig(ConfigParser.java:125)
at com.alibaba.datax.core.util.ConfigParser.parse(ConfigParser.java:63)
at com.alibaba.datax.core.Engine.entry(Engine.java:137)
at com.alibaba.datax.core.Engine.main(Engine.java:204)
部署datax到本地后首次执行任务报错
分别进入到 reader和 writer 目录,删除掉这类型文件
cd /data/datax/plugin/reader
rm -rf ./._*
cd /data/datax/plugin/writer
rm -rf ./._*
再次执行
# python /data/datax/bin/datax.py /data/datax/job/mysql2stream.json
使用datax实现增量数据同步打印到控制台
1）第一步：开发datax的配置文件node03执行以下命令开发datax配置文件
# cd /data/datax/job
# vim mysql2streamadd.json
{
"job": {
"setting": {
"speed": {
"channel": 3
},
"errorLimit": {
"record": 0,
"percentage": 0.02
}
},
"content": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
"username": "root",
"password": "123456",
"column": [
"id",
"name"
],
"where": "create_time > '${start_time}' and create_time < '${end_time}'",
"connection": [
{
"table": [
"emp"
],
"jdbcUrl": [
"jdbc:mysql://192.168.1.6:3306/userdb"
]
}
]
}
},
"writer": {
"name": "streamwriter",
"parameter": {
"encoding":"GBK",
"print":true
}
}
}
]
}
}
2）第二步：启动datax实现数据同步
# cd /data/datax
# /data/datax/bin/datax.py /data/datax/job/mysql2streamadd.json -p "-Dstart_time='2018-06-15 00:00:00' -Dend_time='2023-06-15 23:59:59'"
SQL：
select id,name from emp where (create_time > '2018-06-15 00:00:00' and create_time < '2023-06-15 23:59:59'
案例四：使用datax实现mysql2mysql
使用datax实现将数据从mysql当中读取，并且通过sql语句实现数据的过滤，并且将数据写入到mysql另外一张表当中去
1）第一步：创建mysql另外一张表# mysql -u root -h 192.168.1.6 -P 3306 -p
mysql> USE userdb;
mysql> CREATE TABLE `emp2` (
`id` INT(11) DEFAULT NULL,
`name` VARCHAR(100) DEFAULT NULL,
`deg` VARCHAR(100) DEFAULT NULL,
`salary` INT(11) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=latin1;
2）第二步：开发datax的配置文件
查看帮助文档
# cd /data/datax
# python /data/datax/bin/datax.py -r mysqlreader -w mysqlwriter
node03执行以下命令开发datax配置文件
# cd /data/datax/job/
# vim mysql2mysql.json
{
"job": {
"setting": {
"speed": {
"channel":1
}
},
"content": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
"username": "root",
"password": "123456",
"connection": [
{
"querySql": [
"select id,name,deg,salary from emp where id < 1208;"
],
"jdbcUrl": [
"jdbc:mysql://192.168.1.6:3306/userdb"
]
}
]
}
},
"writer": {
"name": "mysqlwriter",
"parameter": {
"writeMode": "insert",
"username": "root",
"password": "123456",
"column": [
"id",
"name",
"deg",
"salary"
],
"session": [
"set session sql_mode='ANSI'"
],
"preSql": [
"delete from emp2"
],
"connection": [
{
"jdbcUrl": "jdbc:mysql://192.168.1.6:3306/userdb?useUnicode=true&characterEncoding=utf-8",
"table": [
"emp2"
]
}
]
}
}
}
]
}
}
3）第三步：启动datax实现数据同步
node03执行以下命令实现datax数据同步
cd /data/datax
python /data/datax/bin/datax.py /data/datax/job/mysql2mysql.json
完成
2023-02-27 15:57:43.684 [job-0] INFO JobContainer - PerfTrace not enable!
2023-02-27 15:57:43.685 [job-0] INFO StandAloneJobContainerCommunicator - Total 7 records, 177 bytes | Speed 17B/s, 0 records/s | Error 0 records, 0 bytes | All Task WaitWriterTime 0.000s | All Task WaitReaderTime 0.000s | Percentage 100.00%
2023-02-27 15:57:43.687 [job-0] INFO JobContainer -
任务启动时刻 : 2023-02-27 15:57:33
任务结束时刻 : 2023-02-27 15:57:43
任务总计耗时 : 10s
任务平均流量 : 17B/s
记录写入速度 : 0rec/s
读出记录总数 : 7
读写失败总数 : 0
4）查看数据
MysqlReader 插件文档： https://github.com/alibaba/DataX/blob/master/mysqlreader/doc/mysqlreader.md
DataX MysqlWriter https://github.com/alibaba/DataX/blob/master/mysqlwriter/doc/mysqlwriter.md
# python /data/datax/bin/datax.py -r mysqlreader -w mysqlwriter
{
"job": {
"content": [
{
"reader": {
"name": "mysqlreader", # 读取端
"parameter": {
"column": [], # 需要同步的列 (* 表示所有的列)
"connection": [
{
"jdbcUrl": [], # 连接信息
"table": [] # 连接表
}
],
"password": "", # 连接用户
"username": "", # 连接密码
"where": "" # 描述筛选条件
}
},
"writer": {
"name": "mysqlwriter", # 写入端
"parameter": {
"column": [], # 需要同步的列
"connection": [
{
"jdbcUrl": "", # 连接信息
"table": [] # 连接表
}
],
"password": "", # 连接密码
"preSql": [], # 同步前. 要做的事
"session": [],
"username": "", # 连接用户
"writeMode": "" # 操作类型
}
}
}
],
"setting": {
"speed": {
"channel": "" # 指定并发数
}
}
}
}
