mysql导入csv centos_CentOS7安装mysql并导入csv文件问题汇总

梦里梦外; 2023-01-11 13:22 190阅读 0赞

一、mysql安装之后启动失败，通过查看/var/log/mysqld.log日志文件报如下错误：

2018-10-17T04:29:31.727009Z 0 \[System\] \[MY-010116\] \[Server\] /usr/sbin/mysqld (mysqld 8.0.12) starting as process 1654

2018-10-17T04:29:31.738339Z 1 \[ERROR\] \[MY-012592\] \[InnoDB\] InnoDB: Operating system error number 13 in a file operation.

2018-10-17T04:29:31.738373Z 1 \[ERROR\] \[MY-012595\] \[InnoDB\] InnoDB: The error means mysqld does not have the access rights to the directory.

2018-10-17T04:29:31.738386Z 1 \[ERROR\] \[MY-012270\] \[InnoDB\] InnoDB: os\_file\_get\_status() failed on './ibdata1'. Can't determine file permissions

2018-10-17T04:29:31.738406Z 1 \[ERROR\] \[MY-010334\] \[Server\] Failed to initialize DD Storage Engine

2018-10-17T04:29:31.738524Z 0 \[ERROR\] \[MY-010020\] \[Server\] Data Dictionary initialization failed.

2018-10-17T04:29:31.738549Z 0 \[ERROR\] \[MY-010119\] \[Server\] Aborting

查看数据库data目录权限：

\-rw-rw----. mysql mysql unconfined\_u:object\_r:var\_lib\_t:s0 ibdata1

drwx--x--x. mysql mysql unconfined\_u:object\_r:var\_lib\_t:s0 mysql

drwx------. mysql mysql unconfined\_u:object\_r:var\_lib\_t:s0 performance\_schema

\-rw-r--r--. mysql mysql unconfined\_u:object\_r:var\_lib\_t:s0 RPM\_UPGRADE\_MARKER-LAST

drwxr-xr-x. mysql mysql unconfined\_u:object\_r:var\_lib\_t:s0 test

需要修改目录权限解决

chcon -R -t mysqld\_db\_t mysql

问题原因是selinux的权限控制问题，更改下就可以了。

二、导入csv文件一直报错：

开启该参数，因为是全局参数，所以需要加上global； SET GLOBAL local\_infile=1

再次导入数据，导入成功！

load data local infile '/opt/data-set/1\_train\_19\_Run\_or\_walk\_information.csv' into table \`test\`.\`19\_normal\` character set utf8 fields terminated by ',' enclosed by '"' lines terminated by '\\r\\n';

另外注意LINES TERMINATED BY '\\r\\n'，在linux上需要用'\\r\\n',windows上使用'\\n'