mysql恢复数据数据出现Variable sql_mode cant 解决方法

用mysqldump导出的数据文件,再用source导进去的时候常常有一些报错 ariable sql_mode cant be set to the value of NULL,百度了好几回,终于找到是mysql导出的注释语句问题,导出的文件常常如下:

1. /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;   
2. /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;   
3. /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;   
4. /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;   
5. /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;   
6. /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;   
7. /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; 

解决方法:删除注释语句后再执行批量SQL语句操作.

利用二进制日志恢复报错？Variable 'sql_mode' can't be set to the value of '1344274432'.

1. Server version          5.0.18-log 
2. Protocol version        10 
3. Connection              localhost via TCP/IP 

操作系统:windowsXp,mysql为InnoDB存储引擎,数据库为utf.

win.ini中配置,代码如下:

1. [mysql] 
2. default-character-set=utf8 
3.  
4. [mysqld] 
5. basedir="c:/MySQL5/" 
6. datadir="c:/MySQL5/Data/" 
7.  
8. log-bin="d:/log/log"  
9. binlog-do-db=test 
10. innodb_data_home_dir = 
11. innodb_data_file_path=e:/MySQL Datafiles/ibdata1:10M;e:/MySQL Datafiles/ibdata2:1M:autoextend 
12.  
13. innodb_log_group_home_dir="d:/log/binlog/" 
14. default-character-set=utf8 
15. default-storage-engine=INNODB 
16.  
17. sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" 
18.  
19. ............... 

恢复冷备份后,执行日志恢复报错:

C:＼mysqlbinlog d:/log/log.000002|mysql -uroot -pa

ERROR 1231 (42000) at line 10: Variable 'sql_mode' can't be set to the value of

显示日志如下:

1. C:＼mysqlbinlog d:/log/log.000002 
2.  
3. /*!40019 SET @@session.max_insert_delayed_threads=0*/; 
4. /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; 
5. # at 4 
6. #070423  1:10:40 server id 1  end_log_pos 98    Start: binlog v 4, server v 5.0. 
7. 18-log created 070423  1:10:40 
8. # at 98 
9. #070423  1:10:46 server id 1  end_log_pos 93    Query   thread_id=9     exec_tim 
10. e=0     error_code=0 
11. use iservice; 
12. SET TIMESTAMP=1177261846; 
13. SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.uniq 
14. ue_checks=1; 
15. SET @@session.sql_mode=1344274432; 
16. SET @@session.character_set_client=33,@@session.collation_connection=33,@@sessio 
17. n.collation_server=33; 
18. insert into test values(3); 
19. # at 191 
20. #070423  1:10:46 server id 1  end_log_pos 218   Xid = 27 
21. COMMIT;  --phpfensi.com 
22. # at 218 
23. #070423  1:11:02 server id 1  end_log_pos 237   Stop 
24. # End of log file 
25. ROLLBACK /* added by mysqlbinlog */; 
26. /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; 

解决方法

mysqlbinlog:用于处理二进制日志文件的实用工具

服务器生成的二进制日志文件写成二进制格式,要想检查这些文本格式的文件,应使用mysqlbinlog实用工具.

应这样调用mysqlbinlog:shell> mysqlbinlog [options] log-files...

例如,要想显示二进制日志binlog.000003的内容,使用下面的命令:

shell> mysqlbinlog binlog.0000003

输出包括在binlog.000003中包含的所有语句，以及其它信息例如每个语句花费的时间、客户发出的线程ID、发出线程时的时间戳等等。

通常情况，可以使用mysqlbinlog直接读取二进制日志文件并将它们用于本地MySQL服务器。也可以使用--read-from-remote-server选项从远程服务器读取二进制日志。

当读取远程二进制日志时，可以通过连接参数选项来指示如何连接服务器，但它们经常被忽略掉，除非你还指定了--read-from-remote-server选项。这些选项是--host、--password、--port、--protocol、--socket和--user。

还可以使用mysqlbinlog来读取在复制过程中从服务器所写的中继日志文件。中继日志格式与二进制日志文件相同。

mysqlbinlog支持下面的选项:

·---help，-？

显示帮助消息并退出。

·---database=db_name，-d db_name

只列出该数据库的条目(只用本地日志)。

·--force-read，-f

使用该选项,如果mysqlbinlog读它不能识别的二进制日志事件,它会打印警告,忽略该事件并继续,没有该选项,如果mysqlbinlog读到此类事件则停止.

·--hexdump,-H

在注释中显示日志的十六进制转储,该输出可以帮助复制过程中的调试,在MySQL 5.1.2中添加了该选项.

·--host=host_name,-h host_name

获取给定主机上的MySQL服务器的二进制日志。

·--local-load=path,-l pat

为指定目录中的LOAD DATA INFILE预处理本地临时文件。

·--offset=N，-o N

跳过前N个条目。

·--password[=password]，-p[password]

当连接服务器时使用的密码。如果使用短选项形式(-p)，选项和 密码之间不能有空格。如果在命令行中--password或-p选项后面没有 密码值，则提示输入一个密码。

·--port=port_num,-P port_num

用于连接远程服务器的TCP/IP端口号。

·--position=N，-j N

不赞成使用,应使用--start-position.

·--protocol={TCP | SOCKET | PIPE | -position

使用的连接协议.

·--read-from-remote-server,-R

从MySQL服务器读二进制日志,如果未给出该选项,任何连接参数选项将被忽略,这些选项是--host、--password、--port、--protocol、--socket和--user.

·--result-file=name, -r name

将输出指向给定的文件.

·--short-form,-s

只显示日志中包含的语句,不显示其它信息.

·--socket=path,-S path

用于连接的套接字文件.

·--start-datetime=datetime

从二进制日志中第1个日期时间等于或晚于datetime参量的事件开始读取。datetime值相对于运行mysqlbinlog的机器上的本地时区。该值格式应符合DATETIME或TIMESTAMP数据类型,例如:

shell> mysqlbinlog --start-datetime="2004-12-25 11:25:56" binlog.000003

该选项可以帮助点对点恢复。

·--stop-datetime=datetime

从二进制日志中第1个日期时间等于或晚于datetime参量的事件起停止读。关于datetime值的描述参见--start-datetime选项。该选项可以帮助及时恢复。

·--start-position=N

从二进制日志中第1个位置等于N参量时的事件开始读。

·--stop-position=N

从二进制日志中第1个位置等于和大于N参量时的事件起停止读。

·--to-last-logs,-t

在MySQL服务器中请求的二进制日志的结尾处不停止,而是继续打印直到最后一个二进制日志的结尾,如果将输出发送给同一台MySQL服务器,会导致无限循环,该选项要求--read-from-remote-server.

·--disable-logs-bin，-D

禁用二进制日志。如果使用--to-last-logs选项将输出发送给同一台MySQL服务器，可以避免无限循环。该选项在崩溃恢复时也很有用，可以避免复制已经记录的语句。注释：该选项要求有SUPER权限。

·--user=user_name，-u user_name

连接远程服务器时使用的MySQL用户名。

·--version,-V

显示版本信息并退出.

还可以使用--var_name=value选项设置下面的变量:

·open_files_limit

指定要保留的打开的文件描述符的数量。

可以将mysqlbinlog的输出传到mysql客户端以执行包含在二进制日志中的语句。如果你有一个旧的备份，该选项在崩溃恢复时也很有用:

shell> mysqlbinlog hostname-bin.000001 | mysql

或：shell> mysqlbinlog hostname-bin.[0-9]* | mysql

如果你需要先修改含语句的日志,还可以将mysqlbinlog的输出重新指向一个文本文件,(例如,想删除由于某种原因而不想执行的语句),编辑好文件后,将它输入到mysql程序并执行它包含的语句.

mysqlbinlog有一个--position选项，只打印那些在二进制日志中的偏移量大于或等于某个给定位置的语句(给出的位置必须匹配一个事件的开始)。它还有在看见给定日期和时间的事件后停止或启动的选项。这样可以使用--stop-datetime选项进行点对点恢复(例如，能够说“将数据库前滚动到今天10:30 AM的位置”)。

如果MySQL服务器上有多个要执行的二进制日志,安全的方法是在一个连接中处理它们,下面是一个说明什么是不安全的例子:

shell> mysqlbinlog hostname-bin.000001 | mysql # DANGER!!

shell> mysqlbinlog hostname-bin.000002 | mysql # DANGER!!

使用与服务器的不同连接来处理二进制日志时，如果第1个日志文件包含一个CREATE TEMPORARY TABLE语句，第2个日志包含一个使用该临时表的语句，则会造成问题。当第1个mysql进程结束时，服务器撤销临时表。当第2个mysql进程想使用该表时，服务器报告 “不知道该表”.

要想避免此类问题，使用一个连接来执行想要处理的所有二进制日志中的内容。下面提供了一种方法:

shell> mysqlbinlog hostname-bin.000001 hostname-bin.000002 | mysql

另一个方法是:

1. shell> mysqlbinlog hostname-bin.000001 >  /tmp/statements.sql 
2. shell> mysqlbinlog hostname-bin.000002 >> /tmp/statements.sql 
3. shell> mysql -e "source /tmp/statements.sql"  --phpfensi.com 

mysqlbinlog产生的输出可以不需要原数据文件即可重新生成一个LOAD DATA INFILE操作。mysqlbinlog将数据复制到一个临时文件并写一个引用该文件的LOAD DATA LOCAL INFILE语句。由系统确定写入这些文件的目录的默认位置。要想显式指定一个目录，使用--local-load选项。

因为mysqlbinlog可以将LOAD DATA INFILE语句转换为LOAD DATA LOCAL INFILE语句(也就是说,它添加了LOCAL),用于处理语句的客户端和服务器必须配置为允许LOCAL操作。参见5.6.4节,“LOAD DATA LOCAL安全问题”。

警告:为LOAD DATA LOCAL语句创建的临时文件不会自动删除,因为在实际执行完那些语句前需要它们,不再需要语句日志后应自己删除临时文件,文件位于临时文件目录中,文件名类似original_file_name-#-#。