mysql恢複資料資料出現Variable sql_mode cant 解決方案

01./*!40101 SET SQL_MODE=@OLD_SQL_MODE */; 
02./*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; 
03./*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; 
04./*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; 
05./*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; 
06./*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; 
07./*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

解決辦法

解決方案：刪除備註陳述式後再執行批量SQL語句操作

利用二進位日誌恢複報錯？Variable 'sql_mode' can't be set to the value of '1344274432'

Server version          5.0.18-log
Protocol version        10
Connection              localhost via TCP/IP

作業系統: windowsXp

mysql為InnoDB儲存引擎

資料庫為utf

win.ini中配置

[mysql]
default-character-set=utf8

[mysqld]
basedir="c:/MySQL5/"
datadir="c:/MySQL5/Data/"

log-bin="d:/log/log"
binlog-do-db=test
innodb_data_home_dir =
innodb_data_file_path=e:/MySQL Datafiles/ibdata1:10M;e:/MySQL Datafiles/ibdata2:1M:autoextend

innodb_log_group_home_dir="d:/log/binlog/"
default-character-set=utf8
default-storage-engine=INNODB

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

...............

恢複冷備份後，執行日誌恢複報錯
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
'1344274432'

------------------------

顯示日誌如下：
C:＼mysqlbinlog d:/log/log.000002

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
# at 4
#070423  1:10:40 server id 1  end_log_pos 98    Start: binlog v 4, server v 5.0.
18-log created 070423  1:10:40
# at 98
#070423  1:10:46 server id 1  end_log_pos 93    Query   thread_id=9     exec_tim
e=0     error_code=0
use iservice;
SET TIMESTAMP=1177261846;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.uniq
ue_checks=1;
SET @@session.sql_mode=1344274432;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@sessio
n.collation_server=33;
insert into test values(3);
# at 191
#070423  1:10:46 server id 1  end_log_pos 218   Xid = 27
COMMIT;
# at 218
#070423  1:11:02 server id 1  end_log_pos 237   Stop
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!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

另一個方法是：

代碼如下	複製代碼
shell> mysqlbinlog hostname-bin.000001 >  /tmp/statements.sql shell> mysqlbinlog hostname-bin.000002 >> /tmp/statements.sql shell> mysql -e "source /tmp/statements.sql"

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-#-#。