MySQL导入导出：修订间差异
（创建页面，内容为“== MySQL 数据批量IO == ==== /etc/my.cnf ==== [mysqld] secure_file_priv= （1）'NULL'，表示禁止导入 （2）非空，只允许服务端该目录下文件（不含子目录） （3）如果如上为空，则表示不限制目录 <nowiki>#</nowiki> 需要重启 [client] local_infile=1 <nowiki>#</nowiki> 是否支持本地文件，=0只允许服务端目录下文件 <nowiki>#</nowiki> ERROR 3948 (42000): Loading local data is disabled; this mus…”） |
无编辑摘要 |
||
| 第1行： | 第1行： | ||
== MySQL 数据批量IO == | == MySQL 数据批量IO == | ||
=== 配置 === | |||
==== /etc/my.cnf ==== | ==== /etc/my.cnf ==== | ||
| 第11行： | 第13行： | ||
（3）如果如上为空，则表示不限制目录 | （3）如果如上为空，则表示不限制目录 | ||
[client] | [client] | ||
| 第18行： | 第18行： | ||
local_infile=1 | local_infile=1 | ||
<nowiki>#</nowiki> 是否支持本地文件，= | <nowiki>#</nowiki> 是否支持本地文件，=0只允许服务端目录下文件（ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides） | ||
<nowiki>#</nowiki> 需要重启 | |||
==== 变量方式配置 ==== | |||
<nowiki>#</nowiki> mysql> | <nowiki>#</nowiki> mysql> | ||
| 第39行： | 第38行： | ||
+---------------+-------+ | +---------------+-------+ | ||
| 第54行： | 第52行： | ||
+------------------+-----------------------+ | +------------------+-----------------------+ | ||
==== | ===== 定义后（& restart）： ===== | ||
+------------------+-------+ | +------------------+-------+ | ||
| 第64行： | 第62行： | ||
+------------------+-------+ | +------------------+-------+ | ||
==== # 权限 ==== | ==== # 权限 ==== | ||
| 第75行： | 第72行： | ||
super, system_variables_admin, session_variables_admin | super, system_variables_admin, session_variables_admin | ||
=== mysqlimport Ver 8.0.19 === | |||
-d --delete First delete all rows from table. | -d --delete First delete all rows from table. | ||
| 第88行： | 第84行： | ||
-c --columns=name Use only these columns to import the data | -c --columns=name Use only these columns to import the data | ||
2022年12月28日 (三) 15:12的版本
MySQL 数据批量IO
配置
/etc/my.cnf
[mysqld]
secure_file_priv=
（1）'NULL'，表示禁止导入
（2）非空，只允许服务端该目录下文件（不含子目录）
（3）如果如上为空，则表示不限制目录
[client]
local_infile=1
# 是否支持本地文件，=0只允许服务端目录下文件（ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides）
# 需要重启
变量方式配置
# mysql>
SET GLOBAL local_infile=1; #若 my.cnf 无配置
SHOW GLOBAL VARIABLES LIKE 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | ON |
+---------------+-------+
SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-----------------------+
| Variable_name | Value |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
定义后（& restart）：
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| secure_file_priv | |
+------------------+-------+
# 权限
# LOAD DATA
secure_file_priv
# mysqlimport
super, system_variables_admin, session_variables_admin
mysqlimport Ver 8.0.19
-d --delete First delete all rows from table.
-L --local Read all files through the client.
--ignore-lines=# Ignore first n lines of data infile.
-l --lock-tables Lock all tables for write (this disables threads).
-c --columns=name Use only these columns to import the data
若不指定 local，则只能从服务端 datadir 定义的目录下读取文件。
\072 为八进制 --> ':'
本地文件名必须与表名一致
# dump.txt --> psmm.mytbl
# secure_file_priv=
# 此时的默认导入目录：ERROR 13 (HY000): Can't get stat of '/var/lib/mysql/psmm/dump.txt' (OS errno 2 - No such file or directory)
# mytbl.sql
LOAD DATA INFILE 'dump.txt' INTO TABLE mytbl FIELDS TERMINATED BY ':' LINES TERMINATED BY '\n';
mysql -u root -Dpsmm < mytbl.sql
# 将 dump.txt 改名为 mytbl
mysqlimport -u root -p --fields-terminated-by="$(echo -ne '\072')" --lines-terminated-by="\n" psmm mytbl
# local file --> remote ip
# local_infile=1
mysqlimport -h n10 -u root -p --local psmm /root/mytbl.sql --fields-terminated-by="$(echo -ne '\072')" --lines-terminated-by="\n"
# 日期字段为空时，要么报错(load data方式)，要么置成'0000-00…'
# 以上都是垃圾
# Export Data from oracle
select PKID ||chr(5)||
CUST_CODE ||chr(5)||
to_char(RMD_CUST_TIME, 'yyyy-mm-dd hh24:mi:ss') ||chr(5)||
PLAN_DIRECTION ||chr(5)||
to_char(START_DATE, 'yyyy-mm-dd hh24:mi:ss') ||chr(5)||
to_char(END_DATE, 'yyyy-mm-dd hh24:mi:ss') ||chr(5)||
IMP_INFO ||chr(5)||
CREATED_BY ||chr(5)||
GRP_ID
from scsales_prd.t_cm_look_plan
where 1=1
and rownum < 10
;
# test.sql
# '\n' = X'0A'
LOAD DATA LOCAL
INFILE '/u01/tmp/test.dat'
INTO TABLE T_CM_CUST_HOLDER_TEST_1
FIELDS TERMINATED BY X'05'
LINES TERMINATED BY '\n'
(@PKID, CUST_CODE, AGENT_ID, GROUP_ID, AREA_ID, BIG_AREA_ID, POOL_LEVEL, @CREATE_TIME, CREATE_BY, WARZONE_ID, SWZ_ID, @INPOOL_TIME, MARK, @UPDATE_TIME, UPDATE_BY, COMPANY_ID)
SET PKID = IF(@PKID = '', NULL, @PKID),
CREATE_TIME = IF(@CREATE_TIME = '', NULL, @CREATE_TIME),
INPOOL_TIME = IF(@INPOOL_TIME = '', NULL, @INPOOL_TIME),
update_time = IF(@update_time = '', NULL, @update_time)
;
mysql -h n10 -Dpsmm -u root < test.sql
# pkid 为 int，当其为空时，默认置0.可以指定为空，但如果字段有非空限制，则为0
# varchar 字段，不需要指定
# LOG
-rw-r----- 1 mysql mysql 114688 May 18 14:28 T_CM_CUST_HOLDER_TEST_1.ibd
[root@m01 tmp]# time mysql -h n10 -Dpsmm -u root < test.sql
real 1m29.941s
user 0m0.147s
sys 0m0.565s
-rw-r----- 1 mysql mysql 1275068416 May 18 14:32 T_CM_CUST_HOLDER_TEST_1.ibd