错误出现情景

  在cmd中使用mysql命令,学生信息表添加数据。使用load
data方式简单批量导入数据。

  准备好文本数据: xueshengxinxi.txt 文件  数据之间以tab键进行分割

  执行 “load data infile 文本数据路径  into table
tab_load_data”
  弹出错误。

澳门皇冠金沙网站 1澳门皇冠金沙网站 2

#load data (载入数据)语法,要载入数据如下:
    1    张三            男        江西    1
    2    李四            男        四川    2
    3    王五            男        上海    1
    4    赵六            女        湖北    3
    5    孙七            女        湖北    3
    6    周八            男        湖南    1    

#测试数据表
    create table tab_load_data (
        id int auto_increment primary key,
        name varchar(10),
        sex enum('男','女'),
        native varchar(10),
        f5 int
    );    

【点击查看】测试数据Demo

 

在使用mysqdump导出的sql时,1G文件大约导入了20分钟,需要频繁测试,这个不能忍,查询后得到以下优化方法,基本在2分钟左右。

1、数据文件格式(位于C:\test.bat):

mysql在通过导入sql文件可能会出现下面二个问题:

错误描述

  使用load data 导入文件数据总是弹出错误(Error 1290…..)如下:

  ERROR 1290 (HY000): The
MySQL server is running with the –secure-file-priv option so it
cannot execute this statement

    澳门皇冠金沙网站 3

导出文件太慢

mysqldump -uxxxx -pxxxx -e --max_allowed_packet=1048576 --net_buffer_length=16384 > backup.sql

说明:
-e 生成insert语句时,使用多行插入的模式,这个对大数据性能影响非常大
max_allowed_packet和net_buffer_length是说同行缓存区的大小,这个值不能比服务器的大,服务器上这样查看

mysql> show variables like 'max_allowed_packet'; 
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 16777216 |
+--------------------+----------+
1 row in set (0.06 sec)
mysql> show variables like 'net_buffer_length'; 
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| net_buffer_length | 16384 |
+-------------------+-------+
1 row in set (0.00 sec)
Name, Age, IncomeRange, Education, Skills, Social, isPaid
Albert, 23, 0, 0, 3, 3, 0
Alexandra, 25, 1, 2, 4, 2, 0
Athena, 24, 0, 1, 3, 4, 0
Aurora, 23, 1, 2, 5, 2, 0
Babis, 21, 0, 0, 3, 4, 0
Bill, 31, 1, 2, 4, 2, 0
Bob, 32, 1, 1, 3, 1, 1
Carl, 30, 0, 2, 4, 2, 0
Catherine, 31, 1, 1, 3, 3, 0
Charlie, 30, 1, 2, 3, 2, 0
Constantine, 37, 1, 1, 3, 2, 0
Dmitry, 35, 2, 2, 1, 1, 1
Elena, 38, 1, 1, 3, 2, 0
Eric, 37, 2, 2, 2, 2, 0
Frank, 39, 3, 1, 3, 1, 1
George, 42, 2, 2, 2, 1, 1
Jack, 43, 3, 1, 1, 1, 1
John, 45, 4, 2, 1, 1, 1
Maria, 43, 2, 1, 3, 1, 0
Lukas, 45, 3, 2, 1, 1, 1

 

解决思路(过程)

  1)
既然错误提示说明运行了secure-file-priv这个选项,不能执行load服务,那就找到MYSQL的配置文件”my.ini”
中该配置项。

澳门皇冠金沙网站 ,  2) 配置前,先关闭mysql服务

  3)
打开my.ini,搜索关键字“secure-file-priv”找到这个选项后,直接注释掉这个选项,保存后,启动MYSQL服务

      澳门皇冠金沙网站 4

  4) 结果还是一样的错误。说明注释没有效果。这是怎么回事了?

    执行命令 “show variables like
‘%secure%’”发现原来我注释掉后,secure-file-priv的值为NULL说明禁止导入导出。        

    这说明注释掉是不行的。不删配置文件条目,那只好修改目录好了。

  5)修改路径,执行命令 “show
variables like ‘%secure%’” 查看 , 重启MYSQL。

    澳门皇冠金沙网站 5
    澳门皇冠金沙网站 6

  6)再执行load data, 执行成功

  澳门皇冠金沙网站 7

  更复杂的load用法可以查看mysql手册。 到此,该问题解决。

导出数据时出现can’t opne file ‘xx.frm’ (errno:24)错误

一开始以为是被锁了,后来发现lock列表是空的,重启服务后没有其他连接也不行,后来得知是一个叫open_files_limit的设置的问题。
open_files_limit的系统默认值为max_connections*5 或 max_connections

  • table_cache*2。
    如果open_files_limit设置过小,造成打开的文件过多,就会出现错误’xxx.frm’无法打开。

解决方案:命令行修改global
open_files_limit比要导出的表数量多即可,修改配置文件后重启也行。

2、执行导入语言:

1.如果sql文件过大,会出现”MySQL server has gone away”问题;
 

三, 小结说明

  从本错误中吸取:

  1) load data : 可以从一个外部的记事本文件中导入数据到某个表中。

    该记事本中的数据通常要求“比较整齐”,一行文本对应一行数据,一行中用某种字符(如用tab符)来分割一个一个字段的值

  2) secure-file-priv这个参数用来限制数据导入和导出操作的效果。

    如:执行LOAD,SELECT…INTO
OUTFILE语句和LOAD_FILE()函数。这些操作需要file权限。

  3)如果
secure-file-priv这个参数设为一个目录名,MYSQL服务只允许这个目录中执行文件的导入和导出操作。

    这个目录必须存在,MYSQL服务不会创建它。

  4)
如果secure-file-priv这个参数为NULL,MYSQL服务会禁止导入和导出操作。

 

 

 

导入mysql数据太慢

  1. 确保导入的SQL头部有如下设置

SET FOREIGN_KEY_CHECKS=0;
SET AUTO_COMMIT=0;
  1. mysql导入的参数设置max_allowed_packet大一些

mysql -uxxxx -pxxxx database --default-character-set=utf8 --max_allowed_packet=256M < backup.sql
CREATE DATABASE iweb2;

USE iweb2;

CREATE TABLE sf_users (Name VARCHAR(250) NOT NULL PRIMARY KEY, Age INT NOT NULL, IncomeRange INT NOT NULL, 
           Education INT NOT NULL, Skills INT NOT NULL, Social INT NOT NULL, isPaid INT NOT NULL);

LOAD DATA INFILE 'C:\\test.dat' INTO TABLE sf_users FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES;

2.如果sql文件数据有中文,会出现乱码  www.2cto.com  

3、导入结果:

 

澳门皇冠金沙网站 8

解决问题:

 

 

推荐一个自己业余时间开发的网盘搜索引擎,360盘搜(www.360panso.com)

问题1:出现MySQL server has gone
away”问题,是因为mysql默认的”max_allowed_packet”变量值过小.

查看目前配置
 

show VARIABLES like ‘%max_allowed_packet%’;
 

显示的结果为:

 

+——————–+———+

| Variable_name      | Value   |

+——————–+———+

| max_allowed_packet | 1048576 |

+——————–+———+
 

说明目前的配置是:1048576/1024/1024 = 1M

 

修改max_allowed_packet值:
 

方法1: SET GLOBAL max_allowed_packet = 500*1024*1024;(经测试无效)
 

方法2: 直接修改配置文件,重启mysql  www.2cto.com  

             windows中修改my.ini文件,在linux中修改my.cnf文件.

             C:\Documents and Settings\All Users\Application
Data\MySQL\MySQL Server 5.5\my.ini(安装mysql时的,指定的数据文件目录)

             
澳门皇冠金沙网站 9
 

            重启mysql后,在查看修改后的max_allowed_packet值

             

澳门皇冠金沙网站 10
 

问题2:登录时指定字符集编码

            mysql -uroot -P3308 -p123456  – -default-character-set=utf8
 (-P是指指定端口号)

            

澳门皇冠金沙网站 11        
 

最后通过source命令 即可成功导入:

              source  E:ydj\test.sql
 

   澳门皇冠金沙网站 12      
   

 

 

 

作者 LoveJavaYDJ

:
1.如果sql文件过大,会出现MySQL server has gone away问题;
2.如果sql文件数据有中文,会出现乱码…

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图