MySQL导入导出

MySQL 数据导入导出

配置

/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 为八进制 --> ':'

本地文件名必须与表名一致

# 此时的默认导入目录：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_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