mysqlbinlog 导出 语法错误 mysql导入出错

今天同事导入MYSQL的时候遇到错误 导出文件大约200G，在大约1.8w行出错。文件太大用SED读取指定行的时候命令报错，

sed -n '18032,$p' sql.sql >sqlnew.sql

如果查看任何信息都非常麻烦，但是

MYSQL报错的时候出现了一个行号，然后大概推算了一下得出了开始的行号，所以使用C写了一个小程序，记录下来

i==18031 是你确定的行号-1 开始。

#include
#include
#include 
int main(void)
{
FILE* fd1;
FILE* fd2;
int i=0;
char m;
char test[4096];
if(!(fd1=fopen("reslut.sql","r")))
{
printf("file 1 open error!\n");
exit(10);
}
if(!(fd2=fopen("reslutnew.sql","w+")))
{
printf("file 2 open error!\n");
exit(10);
}
while(1)
{
if(fgetc(fd1) == '\n')
{
i++;
if(i==18031)
{
break;
}
}
}
while(!(feof(fd1)))
{
memset(test,0,4096);
fread(test,1000,4,fd1);
fwrite(test,strlen(test),1,fd2);
}
fclose(fd1);
fclose(fd2);
}

速度还行。

实际上就是根据换行符确定行号然后接着写入。

写入完成后使用sed替换了某些字符

sed -i 's/\*\/\;\;/\*\/\$\$/g'  reslutnew.sql

可以完成，sed取行的时候应该是OOM了。

注意：

1、这样截取没有 use database信息需要自己写一下然后source

2、这样截取MYSQLDUMP语句的头信息肯定没有了要自己写一下

3、注意关闭binlog set sql_bin_log=0;

4、注意设置innodb_flush_log_at_trx_commit= 0

关于分开导出表结构和数据 注意事项：

1、表结构中使用--skip-triggers不要导出trigger，因为触发器可能导致数据变化，同时trigger是随表导出的一定要--skip-triggers

在某些跨版本的情况下routine都不要导出，只要建表建库等语句，注意导出表结构和导出表数据都要--skip-trigger 因为不管

导出数据还是表结构trigger都会导出

2、MYSQL库会在导入结构的时候插入字典信息，导出数据同样包含了MYSQL的信息，这个时候再往MYSQL插入数据就会报错

处理就是删除MYSQL数据库

3、在存储过程和触发替换 ;;为$$的过程中因为担心数据中也有这种信息

我们使用如下几次替换：

sed -i 's/\*\/\;\;/\*\/\$\$/g'   替换*/;; 为*/$$

sed -i 's/DELIMITER ;;/DELIMITER $$/g' 替换 DELIMITER ;; 为 DELIMITER $$

sed -i 's/end ;;/end $$/g' 替换 end ;; 为 end $$

4、如果截断了行进行再次导入一定注意加上MYSQLDUMP文件的一些初始的变量设置如：

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

5、如果用MYSQL界面导入设置一个 --tee log.log 用于记录插入日志

关于trigger 还有一点要说明：

trigger本生是随表导出的。

也就是不管是after还是before的trigger某个表的触发器都是在

这个表数据insert以后建立，这样既保证了trigger的建立也保证了

数据不会变化，但是如果先导入的是表结构带了trigger那么就打破了

这个原则导致数据乱掉。

看如下dump：

LOCK TABLES `mmmm` WRITE;
/*!40000 ALTER TABLE `mmmm` DISABLE KEYS */;
INSERT INTO `mmmm` VALUES (1);
/*!40000 ALTER TABLE `mmmm` ENABLE KEYS */;
UNLOCK TABLES;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 trigger tri_bb after insert on mmmm
for each row
begin
insert into mmmm1 select count(*) from mmmm;
end */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
--
-- Dumping data for table `mmmm2`
--
LOCK TABLES `mmmm2` WRITE;
/*!40000 ALTER TABLE `mmmm2` DISABLE KEYS */;
INSERT INTO `mmmm2` VALUES (1);
/*!40000 ALTER TABLE `mmmm2` ENABLE KEYS */;
UNLOCK TABLES;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 trigger tri_bb1 after insert on mmmm2
for each row
begin
insert into mmmm3 select count(*) from mmmm;
end */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;

可以看到trigger的建立是在表insert后下一个表以前