MySQL 如何跨机器加载数据(附脚本)?

作者:温开源

近期有同事需要做跨机器将一个数据文件导入到MySQL的需求,所以将以前做的笔记及随带脚本分享一下。

跨机器 load data

若本机有一个文件: /tmp/load.txt,需要导入到远端的 mysqlxxx_table里,可以用如下命令:

mysql -hx.x.x.x -uxxxx -pxxxx db -BN --local-infile=1 
      -e "load data local infile '/tmp/load.txt' into table xxx_table;"
  • 解析: \--local-infile=1 参数: 打开远端服务器的 local-infile 开关,允许 local infile 操作。 SQL 中:local infile 语法不同于 infile,前者从客户端机器读取文件,后者从服务器读取文件。 官方参考:http://dev.mysql.com/doc/refman/5.7/en/load-data.html

问题解决

如果服务器端(mysqld) 启动时指定了 \--local-infile=0,则 local infile中的 local 不会生效,即使在 mysql 命令中指定 \--local-infile=1,也无用。

这个限制为了避免一些安全问题:http://dev.mysql.com/doc/refman/5.7/en/load-data-local.html

附:通用脚本

通用的一个脚本,改改其中的这些参数:-uUSER -pPASSWD -hHOST DB 即可使用,load 的文本数据必须是 utf8 编码的,若是其他编码,修改脚本中 sql 中的 CHARACTER SET 部分。

  • load_data.sh
#!/bin/bash

MYSQL='mysql -uUSER -pPASSWD -hHOST DB --default-character-set=utf8 --local-infile=1 '

function LOG()
{
    echo "[`date  "%Y-%m-%d %H:%M:%S"` $$] $*"
}

if [[ -z "$2" ]]
then
    echo "Usage:"
    echo "  $0 <file> <table> [cols] [create_sql]"
    exit 1
fi

FILE=`readlink -m "$1"`
TABLE=$2
COLS=$3
CREATE_SQL=$4

sql="${CREATE_SQL}load data local infile '${FILE}' into table ${TABLE} CHARACTER SET utf8 ${COLS};"

LOG "${sql}"

if ${MYSQL} -e "${sql}"
then
    LOG "Load ${FILE} into table ${TABLE} success."
    exit 0
else
    LOG "Load ${FILE} into table ${TABLE} fail."
    exit 1
fi
  • 调用样例 ./load_data.sh test_data.txt test_data '(hash, cnt, url)' 'create table if not exists test_data( hash bigint(20) unsigned, cnt int(10) unsigned, url text, ) ENGINE = MyISAM DEFAULT CHARSET = utf8;'

如果不需要自动创建数据表,最后这个参数可以不要。 如果不指定列名,倒数第二个参数也可以不要。

即下面两种写法都可以:

  ./load_data.sh test_data.txt test_data '(hash, cnt, url)'
  ./load_data.sh test_data.txt test_data

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

编辑于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏idba

MySQL 5.7 新特性之三

本系列文章基于5.7.20 版本讲述MySQL的新特性,从安装,文件结构,SQL,优化,复制等几个方面展开介绍5.7 的新特性和功能,同时也建议大家跟踪官方bl...

822
来自专栏程序猿

mysql 命令大全

1、连接到本机上的MYSQL。 首先打开DOS窗口,然后进入目录mysql\bin,再键入命令mysql -u root -p,回车后提示你输密码.注意用户名前...

34311
来自专栏乐沙弥的世界

启用 Oracle 10046 调试事件

    Oracle 10046是一个Oracle内部事件。最常用的是在Session级别设置sql_trace(alter session set sql_t...

392
来自专栏james大数据架构

SQL之收集SQL Server线程等待信息

要知道线程等待时间是制约SQL Server效率的重要原因,这一个随笔中将学习怎样收集SQL Server中的线程等待时间,类型等信息,这些信息是进行数据库优化...

1837
来自专栏逸鹏说道

程序猿是如何解决SQLServer占CPU100%的

文章目录 遇到的问题 使用SQLServer Profiler监控数据库 SQL1:查找最新的30条告警事件 SQL2:获取当前的总报警记录数 有哪些SQL语句...

3378
来自专栏杨建荣的学习笔记

一个SQL性能问题的优化探索(一)(r11笔记第33天)

今天同事问我一个问题,看起来比较常规,但是仔细分析了一圈,发现实在是有些晕,我隐隐感觉这是一个bug,但是有感觉问题还有很多需要确认和理解的细节。 同事...

3469
来自专栏杨建荣的学习笔记

基于时间点的不完全恢复的例子(r6笔记第9天)

说到不完全恢复,一般有三种场景,基于时间点的不完全恢复,基于scn的不完全恢复,基于cancel的不完全恢复。 三种情况都是不完全恢复采用的方式,而不完全恢复都...

2675
来自专栏芋道源码1024

数据库中间件 MyCAT 源码分析 —— 【单库单表】查询

本文主要基于 MyCAT 1.6.5 正式版 1. 概述 2. 接收请求,解析 SQL 3. 获得路由结果 4. 获得 MySQL 连接,执行 SQL 5. 响...

5299
来自专栏数据存储

十分钟包会MySQL插件开发

请看下面的MySQL UDF插件模版,通过C执行shell语句。所以只要替换shell语句的位置,保存并编译。duang的一下,你的MySQL插件就成功出炉。

2768
来自专栏python3

MySQL审计

线上的数据库,开发可以直接navicat软件直接操作。一旦发生数据泄露,后果严重。需要禁止使用navicat,使用命令行操作,并且能记录每个开发执行的SQL语句...

1151

扫码关注云+社区