mysql问题记录

写入导出文件报错

错误提示：The MySQL server is running with the --secure-file-priv option so it cannot execute

出现这个问题的原因是因为启动MySQL的时候使用了--secure-file-priv这个参数，这个参数的主要目的就是限制LOAD DATA INFILE或者SELECT INTO OUTFILE之类文件的目录位置，我们可以使用  SELECT @@global.secure_file_priv;  查询到你当前设置的路径，默认应该是/var/lib/mysql-files

如果要解决这个问题，我们可以通过下面2种方式：

1. 将你要导入或导出的文件位置指定到你设置的路径里

2. 由于不能动态修改，我们可以修改my.cnf里关于这个选项的配置（secure关键字），然后重启即可。

导入导出数据记录：
1.查询导入导出目录。 SELECT @@global.secure_file_priv; 
2.导出数据备份 select * from tb_produceentity into outfile '/var/lib/mysql-files/my.txt'; 
导出的数据是用tab制表符分开的
LOAD DATA INFILE '/var/lib/mysql-files/my.txt' REPLACE INTO TABLE tb_my_test FIELDS TERMINATED BY "\t"; 可以用制表符再插回去
3.复制表结构 CREATE TABLE newuser LIKE user; 
4.备份数据 INSERT INTO newuser SELECT * FROM user;
5.将导出数据的制表符替换为@（没用到）
替换一行中的tab符 sed -r -i 's/\t+/@/g' 1.txt
替换一行中多个空格sed -r 's/ +/@/g' 1.txt
6.awk -F "\t" '{$4=0}1' my.txt > a.txt
7.cat a.txt my.txt
8.LOAD DATA INFILE '/var/lib/mysql-files/my.txt' REPLACE INTO TABLE tb_my_test FIELDS TERMINATED BY " ";

在使用LOAD DATA到MySQL的时候，有2种情况

（1）在远程客户端（需要添加选项：--local-infile=1）导入远程客户端文本到MySQL，需指定LOCAL（默认就是ignore）,加ignore选项会放弃数据，加replace选项会更新数据，都不会出现唯一性约束问题。

       local_infile服务器变量指示能否使用load data local infile命令（在远程客户端导入远程客户端文本）--local-infile=0禁用LOCAL 功能 1 为开启

       sqlfile="activetiflow.sql" ${line} 为ip

       ret=`ssh root@${line} "mysql -utomcat --local-infile=1 <${sqlfile}"`

       mysql -uzhuxu -pzhuxu test -h10.254.5.151 --local-infile=1  （先登录指定--local-infile=1） 

       LOAD DATA LOCAL INFILE '/tmp/2.txt' INTO TABLE tmp_loaddata FIELDS TERMINATED BY ','              

       FIELDS TERMINATED BY ','  的意思为使用 ',' 分隔

       LOAD DATA LOCAL INFILE '/tmp/2.txt' IGNORE INTO TABLE tmp_loaddata FIELDS TERMINATED BY ','

       LOAD DATA LOCAL INFILE '/tmp/2.txt' REPLACE INTO TABLE tmp_loaddata FIELDS TERMINATED BY ','

       错误实例：

       LOAD DATA INFILE '/tmp/2.txt' INTO TABLE tmp_loaddata FIELDS TERMINATED BY ',';

       ERROR 13 (HY000): Can't get stat of '/tmp/2.txt' (Errcode: 2) #由于数据库服务器没有对应的文本文件，所以报错。

       LOAD DATA LOCALINFILE '/tmp/2.txt' INTO TABLE tmp_loaddata FIELDS TERMINATED BY ',';

       ERROR 1148 (42000): The used command is not allowed with this MySQL version #进去mysql远程客户端，还需要加--local-infile=1参数指定

（2）在本地服务器导入本地服务器文本到MySQL，不指定LOACL，出现唯一性约束冲突，会失败回滚，数据导入不进去，这个时候就需要加ignore或者replace来导入数据。

       LOAD DATA INFILE '/home/zhuxu/1.txt' IGNORE INTO TABLE tmp_loaddata FIELDS TERMINATED BY ',';  使用IGNORE对于冲突的数据丢弃掉

       LOAD DATA INFILE '/home/zhuxu/1.txt' REPLACE INTO TABLE tmp_loaddata FIELDS TERMINATED BY ',';  使用REPLACE对于冲突的数据进行更新

       错误实例：

       LOAD DATA INFILE '/home/zhuxu/1.txt' INTO TABLE tmp_loaddata FIELDS TERMINATED BY ',';

       ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' #出现唯一性约束冲突，会失败回滚

mysqldump 导入和导出数据库

mysqldump 是 mysql 用于转存储数据库的实用程序。它主要产生一个 SQL 脚本，其中包含从头重新创建数据库所必需的命令 CREATE TABLE INSERT 等。

使用 mysqldump 导出数据。如：将cdkeys数据库整体导入cdkeys.txt文件，需要有cdkeys.txt文件的权限。

mysqldump -u root -p cdkeys > /data/mysql-files/cdkeys.txt

或者将cdkeys的giftinfo 表导入txt中

mysqldump -u root -p cdkeys giftinfo > /data/mysql-files/cdkeys.txt

如果需要备份所有数据库，可以使用以下命令，--all-databases 选项在 MySQL 3.23.12 及以后版本加入

mysqldump -u root -p --all-databases > database_dump.txt

如果你需要将备份的数据库导入到MySQL服务器中，可以使用以下命令，使用以下命令你需要确认数据库已经创建。

mysql -uroot -p accountgifts < accountgifts.txt

如果你需要将远程服务器的数据拷贝到本地，你也可以在 mysqldump 命令中指定远程服务器的IP、端口及数据库名。请确保两台服务器是相通的.在源主机上执行以下命令，将数据备份到 dump.txt 文件中：

mysqldump -h IP -P port -u root -p database_name > dump.txt

password ****

MySQL 5.0.51中包含的mysqldump命令（根据自4.1.1以来的更改日志版本）确实关闭了外键检查。默认情况下，mysqldump在转储文件的顶部包含以下行：

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

该/*!40014 ... */语法是有条件的评论将在MySQL 4.0.14和以后执行。旧的外键检查设置将在转储文件的末尾恢复：

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

或者另一种操作：

导入批量数据 可以先 copy到一个txt文件里 然后awk ‘{printf xxx}’> xx.sql 重定向到一个文件 然后再sql文件里加参数 再执行即可。

use accountgifts; 使用数据库

SET collation_connection = utf8_bin;  collation_connection对比较文字字符串是重要的。对于列值的字符串比较，它不重要，因为列具有更高的 校对规则优先级

// utf8_bin 是将字符串每个字符串用二进制数据编译存储

SET character_set_client = utf8;  客户端使用的编码，如GBK, UTF8 比如你写的sql语句是什么编码的    //还有一个是 character_set_results 查询返回的结果集的编码(从数据库读取的数据是什么编码的)。

SET character_set_connection = utf8;  连接使用的编码

SET autocommit=0; //将autocommit设置为OFF之后，系统默认开始了事务，但是并没有默认帮你提交了事务，因此如果我们在客户端执行insert之后需要提交事务：

INSERT INTO accountinfo(bid, uid, arg) VALUES(0, 25241070, 370);

或者 INSERT INTO employees VALUES(224, 'Linus', 'Torvalds', '2002-05-01', '2004-10-12', 42, 21), (225, 'Linus', 'Torvalds', '2002-05-01', '2004-10-12', 42, 21);

SET AUTOCOMMIT=1;//打开自动提交。这样中间的所有操作算作一个事务，如果有一个没成功 不会进行提交操作。

Insert into select

需求：需要将表A的数据迁移到表B中去做一个备份。本想通过程序先查询查出来然后批量插入。但xxx觉得这样有点慢，需要耗费大量的网络I/O，决定采取别的方法进行实现。

通过在Baidu的海洋里遨游，他发现了可以使用insert into select实现，这样就可以避免使用网络I/O，直接使用SQL依靠数据库I/O完成，这样简直不要太棒了。然后就出问题了。

由于数据数据库中order_today数据量过大，当时好像有700W了并且每天在以30W的速度增加。

所以上司命令xxx将order_today内的部分数据迁移到order_record中，并将order_today中的数据删除。

这样来降低order_today表中的数据量。

由于考虑到会占用数据库I/O，为了不影响业务，计划是9:00以后开始迁移，但是xxx在8:00的时候，尝试迁移了少部分数据(1000条)，觉得没啥问题，就开始考虑大批量迁移。

在迁移的过程中，应急群是先反应有小部分用户出现支付失败，随后反应大批用户出现支付失败的情况，以及初始化订单失败的情况，同时腾讯也开始报警。

立即停止了迁移。本以为停止迁移就就可以恢复了，但是并没有。后面发生的你们可以脑补一下。

事故还原

在本地建立一个精简版的数据库，并生成了100w的数据。模拟线上发生的情况。

建立表结构

订单表

CREATE TABLE `order_today` (  
  `id` varchar(32) NOT NULL COMMENT '主键',  
  `merchant_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '商户编号',  
  `amount` decimal(15,2) NOT NULL COMMENT '订单金额',  
  `pay_success_time` datetime NOT NULL COMMENT '支付成功时间',  
  `order_status` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '支付状态  S：支付成功、F：订单支付失败',  
  `remark` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '备注',  
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',  
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间 -- 修改时自动更新',  
  PRIMARY KEY (`id`) USING BTREE,  
  KEY `idx_merchant_id` (`merchant_id`) USING BTREE COMMENT '商户编号'  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;  
 

订单记录表

CREATE TABLE order_record like order_today;  
 

今日订单表数据

模拟迁移：把8号之前的数据都迁移到order_record表中去。

INSERT INTO order_record SELECT  
    *   
FROM  
    order_today   
WHERE  
    pay_success_time < '2020-03-08 00:00:00';  

在navicat中运行迁移的sql,同时开另个一个窗口插入数据，模拟下单。这篇《如何快速安全的插入千万条数据？》推荐看下。

从上面可以发现一开始能正常插入，但是后面突然就卡住了，并且耗费了23s才成功，然后才能继续插入。这个时候已经迁移成功了，所以能正常插入了。

出现的原因

在默认的事务隔离级别下：insert into order_record select * from order_today 加锁规则是：order_record表锁，order_today逐步锁（扫描一个锁一个）。MySQL 四种隔离级别，推荐看下。

分析执行过程。

通过观察迁移sql的执行情况你会发现order_today是全表扫描，也就意味着在执行insert into select from 语句时，mysql会从上到下扫描order_today内的记录并且加锁，这样一来不就和直接锁表是一样了。

这也就可以解释，为什么一开始只有少量用户出现支付失败，后续大量用户出现支付失败，初始化订单失败等情况，因为一开始只锁定了少部分数据，没有被锁定的数据还是可以正常被修改为正常状态。

由于锁定的数据越来越多，就导致出现了大量支付失败。最后全部锁住，导致无法插入订单，而出现初始化订单失败。

解决方案

由于查询条件会导致order_today全表扫描，什么能避免全表扫描呢，很简单嘛，给pay_success_time字段添加一个idx_pay_suc_time索引就可以了，由于走索引查询，就不会出现扫描全表的情况而锁表了，只会锁定符合条件的记录。

关于 MySQL 索引的详细用法有实战，大家可以关注公众号Java技术栈在后台回复mysql获取系列干货文章。

最终的sql

INSERT INTO order_record SELECT  
    *   
FROM  
    order_today FORCE INDEX (idx_pay_suc_time)  
WHERE  
    pay_success_time <= '2020-03-08 00:00:00';  

执行过程

总结

使用insert into tablA select * from tableB语句时，一定要确保tableB后面的where，order或者其他条件，都需要有对应的索引，来避免出现tableB全部记录被锁定的情况。注意：force index强制使用指定索引，mysql优化器会计算出一个合适的索引，但是这个索引不一定是最好的。force index()指令可以避免MySql优化器用到了一个低效的索引。上面的索引idx_pay_suc_time是自己创建的。

mysql建表Row size too large. The maximum row size for the used table type, not counting BLOBs, is 6553

mysql建表有个长度限制：MySQL要求一个行的定义长度不能超过65535。

（1）单个字段如果大于65535，则转换为TEXT 。

（2）单行最大限制为65535，这里不包括TEXT、BLOB。

所谓单行最大限制指的就是一张表中所有字段的所设置的长度不得超过65535字节

数据库编码为utf8mb4，数据库设定的varchar长度 需要乘以*4，如果是utf8，则是设定的varchar或者其他类型长度乘以3。

CREATE TABLE aaa(id VARCHAR(1000),name VARCHAR(10000),age VARCHAR(5000),gender VARCHAR(390))

（1000 + 10000 + 5000 + 390） * 4 》 65535