MySQL导入导出实践

最近一次数据迁移,需要将MySQL的数据导出、处理后导入到新表和ES。这里做个简单记录,方便后续查询。

注: 为了写文章方便及隐私安全,实际内容会有所简化。例如表结构简化、数据库连接部分全部用 xxx 表示、目录及文件名均为化名等。

实践过程

原表:

book_db 库
  - b_book(id,create_time,update_time,price,title,intro)

新表:

book 库
  - book(id,price,title,create_time,update_time)
  - book_ext(id,book_id,intro,create_time)

MySQL导出

mkdir -p /tmp/

# 导出原始数据
mysql -hxxx -uxxx -pxxx book_db  --default-character-set=utf8  -e 'select id,create_time,update_time,price,title,intro from b_book' | sed 's/NULL//g'   > /tmp/b_book.csv

sed 's/NULL//g'是因为导出的数据有些字段存的NULL,新表不需要存储NULL,所以去掉。

导出的数据每行默认以\t分隔,第一行包含字段名。这里我们删掉第一行:

sed -i '1d' /tmp/b_book.csv

数据处理

cd /tmp/

# 处理create_time,update_time,price,并生成文件 book.csv
cat b_book.csv | awk -F '\t' -v OFS=' @@@ ' '{gsub(/[-:]/," ",$2); $2=mktime($2);gsub(/[-:]/,"",$3);$3=mktime($3);$4=$4*100;$6="";print $0}' > book.csv

# 生成文件 book_ext.csv
cat b_book.csv | awk -F '\t' -v OFS=' @@@ ' '{print $1,$6}' > book_ext.csv

# 生成文件 book_es.csv
cat b_book.csv | awk -F '\t' -v OFS=' @@@ ' '{$4=$4*100;print $0}' > book_es.csv

因为原表里时间都是datetime格式,新表是时间戳格式,这里处理成时间戳格式。价格原表是以元为单位,这里*100是为了处理成以分为单位。

-v OFS=' @@@ '表示输出的时候每列以@@@为分隔符。原因是原表里的intro字段存储的是html,可能包含常用转义字符,这里使用@@@确保能正确分隔每列。

导入到MySQL

mysql -hxxx -uxxx -pxxx book
Load Data LOCAL InFile '/tmp/book.csv' Into Table book 
character set utf8 
Fields Terminated By ' @@@ ' Enclosed By '' Escaped By '' Lines Terminated By '\n'
(id,create_time,update_time,price,title);
 
Load Data LOCAL InFile '/tmp/book_ext.csv' Into Table book_ext 
character set utf8 
Fields Terminated By ' @@@ ' Enclosed By '' Escaped By '' Lines Terminated By '\n'
(book_id,intro);

说明:

  • Terminated 字段分隔符(列分隔符)。一般是空格或者\t
  • Enclosed 字段括起字符。没有为空字符即可
  • Escaped 转义字符。没有为空字符即可
  • Terminated 记录分隔符(行结束符)

Into Table 代表插入,记录已存在(唯一键约束)则失败不再往下执行。Replace Into Table 代表覆盖,记录已存在则覆盖(是整条记录覆盖,没有列出的字段给默认值)。Ignore Into Table 遇到已存在直接跳过。

导入到ES

由于生产的book_es.csv文件比较大,所以这里按20000条生成一个文件,防止文件过大,ES导入失败。

cd /tmp/

awk '{filename = "book_es.csv." int((NR-1)/20000) ".csv"; print >> filename}' book_es.csv

ConvertBookToEs.php是PHP脚本,生成ES批量导入的文件。见附录。执行后生成很多book_es.csv.*.csv.json文件。

php ConvertBookToEs.php

importToEs.sh是ES批量导入脚本,如下:

#!/bin/bash
for file in `ls /tmp/book_es.csv.*.csv.json` 
do
   echo $file;
   curl -XPOST http://xxx:9200/book/doc/_bulk -H "Content-Type: application/json" --data-binary "@$file"  >> importToEs.log
done

执行脚本:

sh importToEs.sh

等待数分钟,便执行完毕