ããã¯ãUbuntu OnlineãFedora OnlineãWindowsãªã³ã©ã€ã³ãšãã¥ã¬ãŒã¿ãŒãMACOSãªã³ã©ã€ã³ãšãã¥ã¬ãŒã¿ãŒãªã©ã®è€æ°ã®ç¡æãªã³ã©ã€ã³ã¯ãŒã¯ã¹ããŒã·ã§ã³ã®XNUMXã€ã䜿çšããŠOnWorksç¡æãã¹ãã£ã³ã°ãããã€ããŒã§å®è¡ã§ããã³ãã³ãmysqlbinlogã§ãã
ããã°ã©ã ïŒ
NAME
mysqlbinlog-ãã€ããªãã°ãã¡ã€ã«ãåŠçããããã®ãŠãŒãã£ãªãã£
SYNOPSIS
mysqlbinlog [ãªãã·ã§ã³] ãã°ãã¡ã€ã« ...
DESCRIPTION
ãµãŒããŒã®ãã€ããªãã°ã¯ãå€æŽã説æãããã€ãã³ãããå«ããã¡ã€ã«ã§æ§æãããŠããŸã
ããŒã¿ããŒã¹ã®å 容ã«ã ãµãŒããŒã¯ãããã®ãã¡ã€ã«ããã€ããªåœ¢åŒã§æžã蟌ã¿ãŸãã ãããã衚瀺ããã«ã¯
ããã¹ã圢åŒã®ã³ã³ãã³ãã¯ã mysqlbinlog å¹çšã 䜿çšããããšãã§ããŸã mysqlbinlog ããž
ã¬ããªã±ãŒã·ã§ã³èšå®ã§ã¹ã¬ãŒããµãŒããŒã«ãã£ãŠæžã蟌ãŸãããªã¬ãŒãã°ãã¡ã€ã«ã®å 容ã衚瀺ããŸã
ãªã¬ãŒãã°ã¯ãã€ããªãã°ãšåã圢åŒã§ããããã§ãã ãã€ããªãã°ãšãªã¬ãŒãã°ã¯
ã»ã¯ã·ã§ã³5.2.4ããã€ããªãã°ãããã³ã»ã¯ã·ã§ã³17.2.2ãã¬ããªã±ãŒã·ã§ã³ãã§ããã«èª¬æããŸãã
ãªã¬ãŒããã³ã¹ããŒã¿ã¹ãã°ãã
åŒã³åºããŸã mysqlbinlog ãã®ãããªïŒ
ã·ã§ã«> mysqlbinlog [ãªãã·ã§ã³] ãã°ãã¡ã€ã« ...
ããšãã°ãbinlog.000003ãšããååã®ãã€ããªãã°ãã¡ã€ã«ã®å 容ã衚瀺ããã«ã¯ãããã䜿çšããŸã
ã³ãã³ãïŒ
ã·ã§ã«> mysqlbinlog binlog.0000003
åºåã«ã¯ãbinlog.000003ã«å«ãŸããã€ãã³ããå«ãŸããŸãã ã¹ããŒãã¡ã³ãããŒã¹ã®ãã®ã³ã°ã®å Žåãã€ãã³ã
æ å ±ã«ã¯ãSQLã¹ããŒãã¡ã³ãããããå®è¡ããããµãŒããŒã®IDã
ã¹ããŒãã¡ã³ããå®è¡ããããšãã®ã¿ã€ã ã¹ã¿ã³ããæèŠæéãªã©ã ã«ãšã£ãŠ
è¡ããŒã¹ã®ãã®ã³ã°ãã€ãã³ãã¯ãSQLã¹ããŒãã¡ã³ãã§ã¯ãªãè¡ã®å€æŽã瀺ããŸãã èŠã
ãã®ã³ã°ã¢ãŒãã«ã€ããŠã¯ãã»ã¯ã·ã§ã³17.1.2ãã¬ããªã±ãŒã·ã§ã³ãã©ãŒãããããåç §ããŠãã ããã
ã€ãã³ãã®åã«ã¯ãè¿œå æ å ±ãæäŸããããããŒã³ã¡ã³ãããããŸãã äŸãã°ïŒ
ïŒ141
ïŒ100309ïŒ9ïŒ28ãµãŒããŒID36 end_log_pos 123
ã¯ãšãªthread_id = 3350 exec_time = 11 error_code = 0
æåã®è¡ã®atã«ç¶ãæ°åã¯ããã¡ã€ã«ã®ãªãã»ãããŸãã¯éå§ã瀺ããŸã
ãã€ããªãã°ãã¡ã€ã«å ã®ã€ãã³ãã®äœçœ®ã
XNUMXè¡ç®ã¯ãã¹ããŒãã¡ã³ãããã€éå§ããããã瀺ãæ¥ä»ãšæå»ã§å§ãŸããŸãã
ã€ãã³ããçºçãããµãŒããŒã ã¬ããªã±ãŒã·ã§ã³ã®å Žåããã®ã¿ã€ã ã¹ã¿ã³ãã¯ã¹ã¬ãŒãã«äŒæãããŸã
ãµãŒããŒã server idã¯ãã€ãã³ããçºçãããµãŒããŒã®server_idå€ã§ãã
end_log_posã¯ã次ã®ã€ãã³ããéå§ããå Žæã瀺ããŸãïŒã€ãŸãã次ã®ã€ãã³ãã®çµäºäœçœ®ã§ãã
çŸåšã®ã€ãã³ã+1ïŒã thread_idã¯ãã©ã®ã¹ã¬ãããã€ãã³ããå®è¡ãããã瀺ããŸãã exec_timeã¯
ãã¹ã¿ãŒãµãŒããŒã§ã®ã€ãã³ãã®å®è¡ã«è²»ããããæéã 奎é·ã§ã¯ãããã¯éãã§ã
ã¹ã¬ãŒãã§ã®çµäºå®è¡æéãããã¹ã¿ãŒã§ã®éå§å®è¡æéãåŒãããã®ã
ãã®éãã¯ãã¬ããªã±ãŒã·ã§ã³ããã¹ã¿ãŒããã©ãã ãé ããŠãããã瀺ããã®ãšããŠæ©èœããŸãã
error_codeã¯ãã€ãã³ãã®å®è¡çµæã瀺ããŸãã ãŒãã¯ãšã©ãŒããªãããšãæå³ããŸã
çºçããŸããã
Note
ã€ãã³ãã°ã«ãŒãã䜿çšããå Žåãã€ãã³ãã®ãã¡ã€ã«ãªãã»ãããã°ã«ãŒãåããŠã
ã€ãã³ãã®ã³ã¡ã³ãã¯ã°ã«ãŒãåã§ããŸãã ãããã®ã°ã«ãŒãåãããã€ãã³ããééããªãã§ãã ãã
空çœã®ãã¡ã€ã«ãªãã»ããã
ããã®åºå mysqlbinlog åå®è¡ã§ããŸãïŒããšãã°ããžã®å ¥åãšããŠäœ¿çšããããšã«ããïŒ
mysqlã®ïŒãã°å ã®ã¹ããŒãã¡ã³ããããçŽããŸãã ããã¯ãåŸã®ãªã«ããªæäœã«åœ¹ç«ã¡ãŸãã
ãµãŒããŒãã¯ã©ãã·ã¥ããŸãã ãã®ä»ã®äœ¿çšäŸã«ã€ããŠã¯ããã®ã»ã¯ã·ã§ã³ã®åŸåããã³
ã»ã¯ã·ã§ã³7.5ããã€ããªãã°ã䜿çšãããã€ã³ãã€ã³ã¿ã€ã ïŒã€ã³ã¯ãªã¡ã³ã¿ã«ïŒãªã«ããªãã
éåžžãããªãã¯äœ¿çšããŸã mysqlbinlog ãã€ããªãã°ãã¡ã€ã«ãçŽæ¥èªã¿åããããããã«é©çšããã«ã¯
ããŒã«ã«MySQLãµãŒããŒã ã䜿çšããŠããªã¢ãŒããµãŒããŒãããã€ããªãã°ãèªã¿åãããšãã§ããŸãã
ã --ãªã¢ãŒããµãŒããŒããã®èªã¿åã ãªãã·ã§ã³ã ãªã¢ãŒããã€ããªãã°ãèªã¿åãã«ã¯ãæ¥ç¶ãã©ã¡ãŒã¿
ãµãŒããŒãžã®æ¥ç¶æ¹æ³ã瀺ããªãã·ã§ã³ãæå®ã§ããŸãã ãããã®ãªãã·ã§ã³ã¯ - ãã¹ã,
- ãã¹ã¯ãŒã, - 枯, - ãããã³ã«, - ãœã±ãã, - ãŠãŒã¶ãŒ; ããªããããå Žåãé€ããŠããããã¯ç¡èŠãããŸã
ãŸãã --ãªã¢ãŒããµãŒããŒããã®èªã¿åã ãªãã·ã§ã³ãéžæããŸãã
mysqlbinlog ã³ãã³ãã©ã€ã³ã§æå®ã§ãã次ã®ãªãã·ã§ã³ããµããŒãããŸãã
ãªãã·ã§ã³ãã¡ã€ã«ã®[mysqlbinlog]ããã³[client]ã°ã«ãŒãå ã ãªãã·ã§ã³ã«ã€ããŠã®æ å ±
MySQLããã°ã©ã ã§äœ¿çšããããã¡ã€ã«ã«ã€ããŠã¯ã4.2.6é ããªãã·ã§ã³ãã¡ã€ã«ã®äœ¿çšããåç §ããŠãã ããã
· - å©ããŠ, -?
ãã«ãã¡ãã»ãŒãžã衚瀺ããŠçµäºããŸãã
· --base64-output =å€
ãã®ãªãã·ã§ã³ã¯ãã€ãã³ããbase-64æååãšããŠãšã³ã³ãŒãããŠè¡šç€ºããã¿ã€ãã³ã°ã決å®ããŸã
BINLOGã¹ããŒãã¡ã³ãã䜿çšããŸãã ãªãã·ã§ã³ã«ã¯ã次ã®èš±å®¹å€ããããŸãïŒå€§æåãšå°æåã¯åºå¥ãããŸããïŒã
ã»AUTOïŒãèªåãïŒãŸãã¯UNSPECïŒãæªæå®ãïŒã¯ãBINLOGã¹ããŒãã¡ã³ãã衚瀺ããŸã
å¿ èŠã«å¿ããŠèªåçã«ïŒã€ãŸãããã©ãŒãããèšè¿°ã€ãã³ããšè¡ã®å ŽåïŒ
ã€ãã³ãïŒã ãããã®å Žå --base64-åºå ãªãã·ã§ã³ãäžããããå Žåãå¹æã¯ãšåãã§ã
--base64-output = AUTO.
Note
èªåBINLOG衚瀺ã¯ãã䜿çšããå Žåã®å¯äžã®å®å šãªåäœã§ãã
ã®åºå mysqlbinlog ãã€ããªãã°ãã¡ã€ã«ã®å 容ãåå®è¡ããŸãã ä»ã®ãªãã·ã§ã³
å€ã¯ããããã°ãŸãã¯ãã¹ãã®ç®çã§ã®ã¿äœ¿çšãããŸãã
å®è¡å¯èœåœ¢åŒã®ãã¹ãŠã®ã€ãã³ããå«ãŸãªãåºåãçæããŸãã
ã»BINLOGã¹ããŒãã¡ã³ãã衚瀺ãããªãããã«ããªãã§ãã ããã mysqlbinlog ã§çµäºããŸã
BINLOGã䜿çšããŠè¡šç€ºããå¿ èŠãããè¡ã€ãã³ããèŠã€ãã£ãå Žåã¯ãšã©ãŒã
ã»DECODE-ROWSã¯æ¬¡ã®ããã«æå®ããŸã mysqlbinlog è¡ã€ãã³ãããã³ãŒãããäºå®ã§ããããš
ãŸãããæå®ããããšã«ãããã³ã¡ã³ãä»ãSQLã¹ããŒãã¡ã³ããšããŠè¡šç€ºãããŸãã -詳现 ãªãã·ã§ã³ãéžæããŸãã
NEVERãšåæ§ã«ãDECODE-ROWSã¯BINLOGã¹ããŒãã¡ã³ãã®è¡šç€ºãæå¶ããŸãããNEVERãšã¯ç°ãªãã
è¡ã€ãã³ããèŠã€ãã£ãå Žåããšã©ãŒã§çµäºããããšã¯ãããŸããã
ã®å¹æã瀺ãäŸã«ã€ã㊠--base64-åºå & -詳现 è¡ã€ãã³ãã§
åºåã«ã€ããŠã¯ããMYSQLBINLOG ROWEVENTDISPLAYããšããã»ã¯ã·ã§ã³ãåç §ããŠãã ããã
· --bind-address =IPã¢ãã¬ã¹
è€æ°ã®ãããã¯ãŒã¯ã€ã³ã¿ãŒãã§ã€ã¹ãåããã³ã³ãã¥ãŒã¿ãŒã§ã¯ããã®ãªãã·ã§ã³ã䜿çšããŠã
MySQLãµãŒããŒãžã®æ¥ç¶ã«äœ¿çšããã€ã³ã¿ãŒãã§ãŒã¹ã
ãã®ãªãã·ã§ã³ã¯ãMySQL5.6.1以éã§ãµããŒããããŠããŸãã
· --binlog-row-event-max-size =N
ââââââââââââââââââââââ¬âââââââââââââââââââââââââââââ âââââââââââââââ
âã³ãã³ãã©ã€ã³ ãã©ãŒããã â--binlog-row-event-max-size=ïŒâ
ââââââââââââââââââââââŒâââââââââââââââââââââââââââââ âââââââââââââââ€
ââ èš±å¯ããã 䟡å€èŠ³ â
ââââââââââââââââââââââ¬âââââââââââââââââââââââ€
ââ ãã©ãããã©ãŒã ããã ãµã€ãº â64â
ââââââââââââââââââââââŒâââââââââââââââââââââââ€
ââ ã¿ã€ã âæ°å€â
ââââââââââââââââââââââŒâââââââââââââââââââââââ€
ââ ããã©ã«ã â4294967040â
ââââââââââââââââââââââŒâââââââââââââââââââââââ€
ââ æå°å€ å€ â256â
ââââââââââââââââââââââŒâââââââââââââââââââââââ€
ââ æå€§å€ å€ â18446744073709547520â
ââââââââââââââââââââââŽââââââââââââââââââââŽâââââââââ âââââââââââââââ
è¡ããŒã¹ã®ãã€ããªãã°ã€ãã³ãã®æ倧ãµã€ãºããã€ãåäœã§æå®ããŸãã è¡ã¯ã°ã«ãŒãåãããŸã
å¯èœã§ããã°ããã®ãµã€ãºãããå°ããã€ãã³ãã«ã å€ã¯256ã®åæ°ã§ããå¿ èŠããããŸãã
ããã©ã«ãã¯4GBã§ãã
· --character-sets-dir =path
æåã»ãããã€ã³ã¹ããŒã«ãããŠãããã£ã¬ã¯ããªã ã»ã¯ã·ã§ã³10.5ãæåã»ããããåç §ããŠãã ããã
æ§æ"ã
· --connection-server-id =ãµãŒããŒID
ãã®ãªãã·ã§ã³ã¯ãBINLOG_DUMP_NON_BLOCKã®ãµããŒãã«ã€ããŠMySQLãµãŒããŒããã¹ãããããã«äœ¿çšãããŸã
MySQL 5.6.5ã§èª€ã£ãŠåé€ãããMySQLã§åŸ©å ãããæ¥ç¶ãã©ã°
5.6.20ïŒBugïŒ18000079ãBugïŒ71178ïŒã éåžžã®æäœã«ã¯å¿ èŠãããŸããã
ãã®ãªãã·ã§ã³ã®æå¹ãªããã©ã«ãå€ãšæå°å€ã¯ã mysqlbinlog
ããããã³ã°ã¢ãŒããŸãã¯éããããã³ã°ã¢ãŒãã§å®è¡ãããŸãã ã〠mysqlbinlog ããããã³ã°ã§å®è¡ãããŸã
ã¢ãŒãã§ã¯ãããã©ã«ãïŒããã³æå°ïŒå€ã¯1ã§ãã éããããã³ã°ã¢ãŒãã§å®è¡ããå Žåãããã©ã«ã
ïŒããã³æå°ïŒå€ã¯0ã§ãã
ãã®ãªãã·ã§ã³ã¯MySQL5.6.20ã§è¿œå ãããŸããã
· -ããŒã¿ããŒã¹=ããŒã¿ããŒã¹å, -d ããŒã¿ããŒã¹å
ãã®ãªãã·ã§ã³ã«ããã mysqlbinlog ãã€ããªãã°ãããšã³ããªãåºåããïŒããŒã«ã«ãã°ã®ã¿ïŒ
ãã㯠ããŒã¿ããŒã¹å USEã«ãã£ãŠããã©ã«ãããŒã¿ããŒã¹ãšããŠéžæãããŠããŸãã
ã -ããŒã¿ããŒã¹ ã®ãªãã·ã§ã³ mysqlbinlog ã«äŒŒãŠããŸã --binlog-do-db ã®ãªãã·ã§ã³
mysqldãããã ããXNUMXã€ã®ããŒã¿ããŒã¹ã®ã¿ãæå®ããããã«äœ¿çšã§ããŸãã ããã -ããŒã¿ããŒã¹ è€æ°ãäžããããŸã
å Žåã«ãã£ãŠã¯ãæåŸã®ã€ã³ã¹ã¿ã³ã¹ã®ã¿ã䜿çšãããŸãã
ãã®ãªãã·ã§ã³ã®å¹æã¯ãã¹ããŒãã¡ã³ãããŒã¹ã®ãã®ã³ã°ãè¡ããŒã¹ã®ãã®ã³ã°ãã«ãã£ãŠç°ãªããŸãã
ãã©ãŒãããã¯ãã®å¹æãšåãããã«äœ¿çšãããŠããŸã --binlog-do-db ãã©ããã«äŸåããŸã
ã¹ããŒãã¡ã³ãããŒã¹ãŸãã¯è¡ããŒã¹ã®ãã®ã³ã°ã䜿çšãããŠããŸãã
ã¹ããŒãã¡ã³ãããŒã¹ ãã®ã³ã°ãéžæããŸãã -ããŒã¿ããŒã¹ ãªãã·ã§ã³ã¯æ¬¡ã®ããã«æ©èœããŸãã
ã» ãã®é ããŒã¿ããŒã¹å ã¯ããã©ã«ãã®ããŒã¿ããŒã¹ã§ãããã¹ããŒãã¡ã³ãã¯å€æŽãããã©ããã«é¢ä¿ãªãåºåãããŸã
ã®ããŒãã« ããŒã¿ããŒã¹å ãŸãã¯å¥ã®ããŒã¿ããŒã¹ã
ã» ããã§ããªããã° ããŒã¿ããŒã¹å ããã©ã«ãããŒã¿ããŒã¹ãšããŠãéžæãããŠããå Žåãã¹ããŒãã¡ã³ãã¯åºåãããŸããã
ã§ããŒãã«ãå€æŽããŠã ããŒã¿ããŒã¹å.
ã»CREATE DATABASEãALTER DATABASEãããã³DROPDATABASEã«ã¯äŸå€ããããŸãã ã¶
ããŒã¿ããŒã¹ã¯ äœæããã å€æŽãããŸããã or èœãšãã ããã©ã«ããšèŠãªãããŸã
ã¹ããŒãã¡ã³ããåºåãããã©ããã決å®ãããšãã®ããŒã¿ããŒã¹ã
ã䜿çšããŠãããã®ã¹ããŒãã¡ã³ããå®è¡ããããšã«ããããã€ããªãã°ãäœæããããšããŸãã
ã¹ããŒãã¡ã³ãããŒã¹ã®ãã®ã³ã°ïŒ
INSERT INTO test.t1ïŒiïŒ VALUESïŒ100ïŒ;
INSERT INTO db2.t2ïŒjïŒ VALUESïŒ200ïŒ;
USEãã¹ã;
INSERT INTO test.t1ïŒiïŒ VALUESïŒ101ïŒ;
INSERT INTO t1ïŒiïŒ VALUESïŒ102ïŒ;
INSERT INTO db2.t2ïŒjïŒ VALUESïŒ201ïŒ;
db2ã䜿çšããŸãã
INSERT INTO test.t1ïŒiïŒ VALUESïŒ103ïŒ;
INSERT INTO db2.t2ïŒjïŒ VALUESïŒ202ïŒ;
INSERT INTO t2ïŒjïŒ VALUESïŒ203ïŒ;
mysqlbinlog --database = test æåã®XNUMXã€ã®INSERTã¹ããŒãã¡ã³ãã¯åºåãããŸããã
ããã©ã«ãã®ããŒã¿ããŒã¹ã¯ãããŸããã USEã«ç¶ãXNUMXã€ã®INSERTã¹ããŒãã¡ã³ããåºåããŸã
ãã¹ãããŸãããUSEdb2ã«ç¶ãXNUMXã€ã®INSERTã¹ããŒãã¡ã³ãã¯ãã¹ãããŸããã
mysqlbinlog --database = db2 æåã®XNUMXã€ã®INSERTã¹ããŒãã¡ã³ãã¯åºåãããŸããã
ããã©ã«ãã®ããŒã¿ããŒã¹ã¯ãããŸããã 次ã®XNUMXã€ã®INSERTã¹ããŒãã¡ã³ãã¯åºåãããŸããã
USEãã¹ãã§ãããUSEdb2ã«ç¶ãXNUMXã€ã®INSERTã¹ããŒãã¡ã³ããåºåããŸãã
è¡ããŒã¹ ãã®ã³ã°. mysqlbinlog ã«å±ããããŒãã«ãå€æŽãããšã³ããªã®ã¿ãåºåããŸã
ããŒã¿ããŒã¹åã ããã©ã«ãã®ããŒã¿ããŒã¹ã¯ããã«åœ±é¿ãäžããŸããã ãã€ããªãã°ã
説æã¯ãã¹ããŒãã¡ã³ãããŒã¹ã®ãã®ã³ã°ã§ã¯ãªãè¡ããŒã¹ã®ãã®ã³ã°ã䜿çšããŠäœæãããŸããã
mysqlbinlog --database = test ãã¹ãã§t1ãå€æŽãããšã³ããªã®ã¿ãåºåããŸã
USEãçºè¡ããããã©ããããŸãã¯ããã©ã«ãã®ããŒã¿ããŒã¹ãäœã§ãããã«é¢ä¿ãªããããŒã¿ããŒã¹ã ãã
ãµãŒããŒã¯binlog_formatãMIXEDã«èšå®ããŠå®è¡ãããŠããã
ã€ãããŸã mysqlbinlog -ããŒã¿ããŒã¹ ãªãã·ã§ã³ã®å Žåã次ã®ããŒãã«ã確èªããå¿ èŠããããŸã
å€æŽããããã®ã¯ãUSEã«ãã£ãŠéžæãããããŒã¿ããŒã¹ã«ãããŸãã ïŒç¹ã«ãã¯ãã¹ããŒã¿ããŒã¹ã¯ãããŸãã
æŽæ°ã䜿çšããå¿ èŠããããŸããïŒ
MySQL 5.6.10ããåã§ã¯ã -ããŒã¿ããŒã¹ æžã蟌ãŸãããã°ã§ãªãã·ã§ã³ãæ£ããæ©èœããŸããã§ãã
GTID察å¿ã®MySQLãµãŒããŒã«ããã ïŒãã°ïŒ15912728ïŒ
· -ãããã°[=ãããã°ãªãã·ã§ã³], -# [ãããã°ãªãã·ã§ã³]
ãããã°ãã°ãæžã蟌ã¿ãŸãã å žåç㪠ãããã°ãªãã·ã§ã³ æååã¯dïŒtïŒoãfile_nameã ããã©ã«ã
dïŒtïŒoã/ tmp /mysqlbinlog.traceã§ãã
· --ãããã°ãã§ãã¯
ããã°ã©ã ã®çµäºæã«ãããã°æ å ±ãåºåããŸãã
· - ãããã°æ å ±
ããã°ã©ã æã«ãããã°æ å ±ãšã¡ã¢ãªããã³CPU䜿çšçã®çµ±èšãåºåãã
çµäºããŸãã
· --default-auth =ãã©ã°ã€ã³
䜿çšããã¯ã©ã€ã¢ã³ãåŽã®èªèšŒãã©ã°ã€ã³ã ã»ã¯ã·ã§ã³6.3.7ããã©ã¬ãã«ããåç §ããŠãã ããã
èªèšŒãã
ãã®ãªãã·ã§ã³ã¯MySQL5.6.2ã§è¿œå ãããŸããã
· --defaults-extra-file =file_name
ãã®ãªãã·ã§ã³ãã¡ã€ã«ã¯ãã°ããŒãã«ãªãã·ã§ã³ãã¡ã€ã«ã®åŸã§ãïŒUnixã§ã¯ïŒãŠãŒã¶ãŒã®åã«èªãã§ãã ããã
ãªãã·ã§ã³ãã¡ã€ã«ã ãã¡ã€ã«ãååšããªãããä»ã®æ¹æ³ã§ã¢ã¯ã»ã¹ã§ããªãå Žåããšã©ãŒãçºçããŸãã
file_name çžå¯Ÿãã¹ãšããŠæå®ãããŠããå Žåã¯ãçŸåšã®ãã£ã¬ã¯ããªãåºæºã«ããŠè§£éãããŸã
ãã«ãã¹åã§ã¯ãªãååã
· --defaults-file =file_name
æå®ããããªãã·ã§ã³ãã¡ã€ã«ã®ã¿ã䜿çšããŠãã ããã ãã¡ã€ã«ãååšããªãããããã§ãªãå Žå
ã¢ã¯ã»ã¹ã§ããªãå Žåããšã©ãŒãçºçããŸãã file_name çŸåšã®çžå¯Ÿçãªè§£é
ãã«ãã¹åã§ã¯ãªãçžå¯Ÿãã¹åãšããŠæå®ãããŠããå Žåã¯ããã£ã¬ã¯ããªã
· --defaults-group-suffix =STR
éåžžã®ãªãã·ã§ã³ã°ã«ãŒãã ãã§ãªããéåžžã®ååãš
ã®æ¥å°ŸèŸ STRã äŸãã°ã mysqlbinlog éåžžã[client]ãš[mysqlbinlog]ãèªã¿åããŸã
ã°ã«ãŒãã ã®å Žå --defaults-group-suffix = _other ãªãã·ã§ã³ãäžãããã mysqlbinlog ãŸãèªã
[client_other]ããã³[mysqlbinlog_other]ã°ã«ãŒãã
· --ãã°ãã³ãç¡å¹ã«ãã, -D
ãã€ããªãã°ãç¡å¹ã«ããŸãã ããã¯ãã䜿çšããå Žåã«ç¡éã«ãŒããåé¿ããã®ã«åœ¹ç«ã¡ãŸã
--æåŸã®ãã°ãŸã§ ãªãã·ã§ã³ã§ãåãMySQLãµãŒããŒã«åºåãéä¿¡ããŠããŸãã ãã®ãªãã·ã§ã³
ãŸããã¯ã©ãã·ã¥åŸã«åŸ©å ãããšãã«ãã¹ããŒãã¡ã³ãã®éè€ãé¿ããããã«åœ¹ç«ã¡ãŸãã
ãã°ã«èšé²ããŸããã
ãã®ãªãã·ã§ã³ã«ã¯ãSUPERç¹æš©ãå¿ èŠã§ãã ãããåå mysqlbinlog ããž
SET sql_log_bin = 0ã¹ããŒãã¡ã³ããåºåã«å«ããŠãã®ãã€ããªãã®ã³ã°ãç¡å¹ã«ããŸãã
æ®ãã®åºåã SUPERããªãéããSETã¹ããŒãã¡ã³ãã¯ç¡å¹ã§ãã
ç¹æš©ã
· --exclude-gtids =gtid_set
ã«ãªã¹ããããŠããã°ã«ãŒãã衚瀺ããªãã§ãã ãã gtid_setã MySQL5.6.5ã§è¿œå ãããŸããã
· --force-if-open, -F
ãã€ããªãã°ãã¡ã€ã«ãéããŠããããæ£ããéããããŠããªããŠããããããèªã¿åããŸãã
· --匷å¶èªã¿åã, -f
ãã®ãªãã·ã§ã³ã䜿çšãããšã mysqlbinlog èªèããªããã€ããªãã°ã€ãã³ããèªã¿åãã
èŠåãåºåããã€ãã³ããç¡èŠããŠç¶è¡ããŸãã ãã®ãªãã·ã§ã³ããªããšã
mysqlbinlog ãã®ãããªã€ãã³ããèªã¿åããšåæ¢ããŸãã
· --hexdump, -H
ãšåŒã°ããã»ã¯ã·ã§ã³ã§èª¬æãããŠããããã«ãã³ã¡ã³ãã§ãã°ã®XNUMXé²ãã³ãã衚瀺ããŸã
ãMYSQLBINLOGXNUMXé²ãã³ã圢åŒãã XNUMXé²åºåã¯ãã¬ããªã±ãŒã·ã§ã³ã«åœ¹ç«ã¡ãŸã
ãããã°ã
· --host =ãã¹ãå, -h ãã¹ãå
æå®ããããã¹ãã®MySQLãµãŒããŒãããã€ããªãã°ãååŸããŸãã
· --include-gtids =gtid_set
ãªã¹ããããŠããã°ã«ãŒãã®ã¿ã衚瀺ããŸã gtid_setã MySQL5.6.5ã§è¿œå ãããŸããã
· --local-load =path, -l path
æå®ããããã£ã¬ã¯ããªã«LOADDATAINFILEçšã®ããŒã«ã«äžæãã¡ã€ã«ãæºåããŸãã
éèŠ
ãããã®äžæãã¡ã€ã«ã¯ã«ãã£ãŠèªåçã«åé€ãããŸãã mysqlbinlog ãŸãã¯ãã®ä»ã®
MySQLããã°ã©ã ã
· --login-path =å
.mylogin.cnfãã°ã€ã³ãã¡ã€ã«ã®æå®ããããã°ã€ã³ãã¹ãããªãã·ã§ã³ãèªã¿åããŸãã ããã°ã€ã³ãã¹ã
éããããªãã·ã§ã³ã®ã»ããã®ã¿ãèš±å¯ãããªãã·ã§ã³ã°ã«ãŒãã§ãã host, user,
passwordã ãã°ã€ã³ãã¹ã¯ããµãŒããŒãã¹ããš
ãµãŒããŒã§èªèšŒããããã®è³æ Œæ å ±ã ãã°ã€ã³ãã¹ãã¡ã€ã«ãäœæããã«ã¯ã
ã mysql_config_editor å¹çšã èŠã mysql_config_editorïŒ1ïŒã ãã®ãªãã·ã§ã³ã¯ã§è¿œå ãããŸãã
MySQL5.6.6ã
· --no-ããã©ã«ã
ãªãã·ã§ã³ãã¡ã€ã«ãèªã¿åããªãã§ãã ããã äžæãªãªãã·ã§ã³ã®èªã¿åããåå ã§ããã°ã©ã ã®èµ·åã«å€±æããå Žå
ãªãã·ã§ã³ãã¡ã€ã«ããã --no-ããã©ã«ã ããããèªã¿åãããªãããã«ããããã«äœ¿çšã§ããŸãã
äŸå€ã¯ã.mylogin.cnfãã¡ã€ã«ãååšããå Žåã¯ããã¹ãŠã®å Žåã«èªã¿åãããããšã§ãã ãã®
次ã®å Žåã§ããã³ãã³ãã©ã€ã³ãããå®å šãªæ¹æ³ã§ãã¹ã¯ãŒããæå®ã§ããŸãã
--no-ããã©ã«ã 䜿çšãããŠããã ïŒ.mylogin.cnfã¯ã«ãã£ãŠäœæãããŸã mysql_config_editor ãŠãŒãã£ãªãã£ã
èŠã mysql_config_editorïŒ1ïŒãïŒ
· -ãªãã»ãã=N, -o N
æåãã¹ããã N ãã°ã®ãšã³ããªã
· -ãã¹ã¯ãŒã[=password], -NS[password]
ãµãŒããŒã«æ¥ç¶ãããšãã«äœ¿çšãããã¹ã¯ãŒãã ã·ã§ãŒããªãã·ã§ã³ãã©ãŒã ã䜿çšããå Žå
(-pïŒã å㯠ãªãã·ã§ã³ãšãã¹ã¯ãŒãã®éã«ã¹ããŒã¹ãå ¥ããŠãã ããã çç¥ããå Žå
password 次ã®å€ - ãã¹ã¯ãŒã or -p ã³ãã³ãã©ã€ã³ã®ãªãã·ã§ã³ã mysqlbinlog
XNUMXã€ã®ããã³ããã
ã³ãã³ãã©ã€ã³ã§ãã¹ã¯ãŒããæå®ããããšã¯å®å šã§ã¯ãªããšèŠãªãããã¹ãã§ãã èŠã
ã»ã¯ã·ã§ã³6.1.2.1ããã¹ã¯ãŒãã»ãã¥ãªãã£ã®ãšã³ããŠãŒã¶ãŒã¬ã€ãã©ã€ã³ãã ãªãã·ã§ã³ã䜿çšã§ããŸã
ã³ãã³ãã©ã€ã³ã§ãã¹ã¯ãŒããæå®ããªãããã«ããããã®ãã¡ã€ã«ã
· --plugin-dir =path
ãã©ã°ã€ã³ãæ¢ããã£ã¬ã¯ããªã ãã®ãªãã·ã§ã³ãæå®ããå¿ èŠãããå ŽåããããŸã
ãã --ããã©ã«ãèªèšŒ ãªãã·ã§ã³ã¯èªèšŒãã©ã°ã€ã³ãæå®ããããã«äœ¿çšãããŸãã
mysqlbinlog èŠã€ãããŸããã 6.3.7é ããã©ã¬ãã«èªèšŒããåç §ããŠãã ããã
ãã®ãªãã·ã§ã³ã¯MySQL5.6.2ã§è¿œå ãããŸããã
· --port =ããŒãçªå·, -P ããŒãçªå·
ãªã¢ãŒããµãŒããŒãžã®æ¥ç¶ã«äœ¿çšããTCP / IPããŒãçªå·ã
· --print-defaults
ããã°ã©ã åãšããªãã·ã§ã³ãã¡ã€ã«ããååŸãããã¹ãŠã®ãªãã·ã§ã³ãå°å·ããŸãã
· -ãããã³ã«= {TCP |ãœã±ãã|ãã€ã|ã¡ã¢ãª}
ãµãŒããŒãžã®æ¥ç¶ã«äœ¿çšããæ¥ç¶ãããã³ã«ã ããã¯ã
ä»ã®æ¥ç¶ãã©ã¡ãŒã¿ã¯éåžžããããã³ã«ä»¥å€ã®ãããã³ã«ã䜿çšãããåå ã«ãªããŸãã
ããªãã欲ãããã®ã 蚱容å€ã®è©³çŽ°ã«ã€ããŠã¯ã4.2.2é ããžã®æ¥ç¶ããåç §ããŠãã ããã
MySQLãµãŒããŒãã
· - ç
ããã©ã«ãã§ã¯ã mysqlbinlog ãã€ããªãã°ãã¡ã€ã«ãèªã¿åããã€ãã³ããããã¹ã圢åŒã§æžã蟌ã¿ãŸãã ã¶
- ç ãªãã·ã§ã³ã¯èšã mysqlbinlog ããããå ã®ãã€ããªåœ¢åŒã§èšè¿°ããŸãã ãã®äœ¿çš
ãããå¿ èŠã§ã --ãªã¢ãŒããµãŒããŒããã®èªã¿åã ãã¡ã€ã«ãèŠæ±ãããããã«ã䜿çšãããŸã
ãµãŒããŒããã mysqlbinlog ãµãŒããŒããèªã¿åããããã¡ã€ã«ããšã«XNUMXã€ã®åºåãã¡ã€ã«ãæžã蟌ã¿ãŸãã
ã - ç ãªãã·ã§ã³ã䜿çšããŠããµãŒããŒã®ãã€ããªãã°ã®ããã¯ã¢ãããäœæã§ããŸãã ãšãšãã«
-åæ¢-決ã㊠ãªãã·ã§ã³ã®å Žåãããã¯ã¢ããã¯ãã©ã€ããã§ãã mysqlbinlog æ¥ç¶ãç¶æããŸã
ãµãŒãã ããã©ã«ãã§ã¯ãåºåãã¡ã€ã«ã¯åããã£ã¬ã¯ããªã§çŸåšã®ãã£ã¬ã¯ããªã«æžã蟌ãŸããŸã
å ã®ãã°ãã¡ã€ã«ãšããŠã®ååã åºåãã¡ã€ã«åã¯ãã䜿çšããŠå€æŽã§ããŸãã
-çµæãã¡ã€ã« ãªãã·ã§ã³ã 詳现ã«ã€ããŠã¯ããMYSQLBINLOGã®äœ¿çšããšããã»ã¯ã·ã§ã³ãåç §ããŠãã ããã
ãã€ããªãã°ãã¡ã€ã«ãããã¯ã¢ããããã«ã¯ãã
ãã®ãªãã·ã§ã³ã¯MySQL5.6.0ã§è¿œå ãããŸããã
· --read-from-remote-master =type
COM_BINLOG_DUMPãŸãã¯COM_BINLOG_DUMP_GTIDã䜿çšããŠMySQLãµãŒããŒãããã€ããªãã°ãèªã¿åããŸã
ãªãã·ã§ã³å€ãBINLOG-DUMP-NON-GTIDSãŸãã¯
ãããããBINLOG-DUMP-GTIDSã ããã --read-from-remote-master = BINLOG-DUMP-GTIDS is
ãšçµã¿åãããããš --exclude-gtidsããã©ã³ã¶ã¯ã·ã§ã³ã¯ãã¹ã¿ãŒã§ãã£ã«ã¿ãŒã§é€å€ã§ããŸãã
äžèŠãªãããã¯ãŒã¯ãã©ãã£ãã¯ãåé¿ããŸãã
ã®èª¬æãåç §ããŠãã ãã --ãªã¢ãŒããµãŒããŒããã®èªã¿åã.
ãã®ãªãã·ã§ã³ã¯MySQL5.6.5ã§è¿œå ãããŸããã
· --ãªã¢ãŒããµãŒããŒããã®èªã¿åã, -R
ããŒã«ã«ãã°ãã¡ã€ã«ãèªã¿åãã®ã§ã¯ãªããMySQLãµãŒããŒãããã€ããªãã°ãèªã¿åããŸãã ã©ãã§ã
ãã®ãªãã·ã§ã³ãæå®ãããŠããªãéããæ¥ç¶ãã©ã¡ãŒã¿ãªãã·ã§ã³ã¯ç¡èŠãããŸãã ãããã¯
ãªãã·ã§ã³ã¯ - ãã¹ã, - ãã¹ã¯ãŒã, - 枯, - ãããã³ã«, - ãœã±ãã, - ãŠãŒã¶ãŒ.
ãã®ãªãã·ã§ã³ã§ã¯ããªã¢ãŒããµãŒããŒãå®è¡ãããŠããå¿ èŠããããŸãã ãã€ããªãã°ã§ã®ã¿æ©èœããŸã
ãªã¬ãŒãã°ãã¡ã€ã«ã§ã¯ãªãããªã¢ãŒããµãŒããŒäžã®ãã¡ã€ã«ã
MySQL 5.6.5以éããã®ãªãã·ã§ã³ã¯æ¬¡ã®ããã«ãªããŸãã
--read-from-remote-master = BINLOG-DUMP-NON-GTIDS.
· --result-file =å, -r å
ãªã - ç ãªãã·ã§ã³ããã®ãªãã·ã§ã³ã¯ã察象ã®ãã¡ã€ã«ã瀺ããŸã mysqlbinlog æžã蟌ã¿
ããã¹ãåºåã ãš - ç, mysqlbinlog ãã°ãã¡ã€ã«ããšã«XNUMXã€ã®ãã€ããªåºåãã¡ã€ã«ãæžã蟌ã¿ãŸã
ãµãŒããŒãã転éãããããã©ã«ãã§çŸåšã®ãã£ã¬ã¯ããªã«æ¬¡ã®ããã«æžã蟌ã¿ãŸãã
å ã®ãã°ãã¡ã€ã«ãšåãååã ãã®å Žåã -çµæãã¡ã€ã« ãªãã·ã§ã³å€
åºåãã¡ã€ã«åãå€æŽãããã¬ãã£ãã¯ã¹ãšããŠæ±ãããŸãã
· --ã»ãã¥ã¢èªèšŒ
å€ãïŒ4.1ããåã®ïŒåœ¢åŒã§ãµãŒããŒã«ãã¹ã¯ãŒããéä¿¡ããªãã§ãã ããã ããã«ããæ¥ç¶ã劚ããããŸã
æ°ãããã¹ã¯ãŒã圢åŒã䜿çšãããµãŒããŒãé€ããŸãã ãã®ãªãã·ã§ã³ã¯ã«ãã£ãŠæå¹ã«ãªããŸã
ãã£ãã©ã«ã; 䜿çšãã --skip-secure-auth ç¡å¹ã«ããŸãã ãã®ãªãã·ã§ã³ã¯ãMySQL5.6.17ã§è¿œå ãããŸããã
Note
4.1ããåã®ããã·ã¥æ¹åŒã䜿çšãããã¹ã¯ãŒãã¯ã次ã®ãããªãã¹ã¯ãŒããããå®å šæ§ãäœããªããŸãã
ãã€ãã£ããã¹ã¯ãŒãããã·ã¥æ¹åŒã䜿çšãããããé¿ããå¿ èŠããããŸãã 4.1ããåã®ãã¹ã¯ãŒã
ã¯éæšå¥šã§ããããããã®ãµããŒãã¯å°æ¥ã®MySQLãªãªãŒã¹ã§åé€ãããäºå®ã§ãã ã«ãšã£ãŠ
ã¢ã«ãŠã³ãã®ã¢ããã°ã¬ãŒãæé ã«ã€ããŠã¯ã6.3.8.3é ã4.1ããåã®ããŒãžã§ã³ããã®ç§»è¡ããåç §ããŠãã ããã
ãã¹ã¯ãŒãããã·ã¥ãšmysql_old_passwordãã©ã°ã€ã³ãã
· --server-id =id
æå®ããããµãŒããŒIDãæã€ãµãŒããŒã«ãã£ãŠäœæãããã€ãã³ãã®ã¿ã衚瀺ããŸãã
· --server-id-bits =N
æåã®ãã®ã ãã䜿çšããŠãã ãã N ãµãŒããŒãèå¥ããããã®server_idã®ãããã ãã€ããªãã°ã®å Žå
ã«ãã£ãŠæžãããŸãã mysqldã server-id-bitsã32æªæºã«èšå®ããããŠãŒã¶ãŒããŒã¿ãä¿åãããŠãã
æäžäœãããã§ãå®è¡äž mysqlbinlog ã --ãµãŒã㌠ID ããã 32ã«èšå®
ãã®ããŒã¿ã衚瀺ã§ããããã«ããŸãã
ãã®ãªãã·ã§ã³ã¯ãã®ããŒãžã§ã³ã§ã®ã¿ãµããŒããããŠããŸã mysqlbinlog MySQLã«ä»å±
ã¯ã©ã¹ã¿ãŒé åžããŸãã¯MySQLã¯ã©ã¹ã¿ãŒãœãŒã¹ããæ§ç¯ã
· --set-charset =æåã»ããå
ã»ããåãè¿œå ãã æåã»ããå æåã»ãããæå®ããåºåãžã®ã¹ããŒãã¡ã³ã
ãã°ãã¡ã€ã«ã®åŠçã«äœ¿çšãããŸãã
· --shared-memory-base-name =å
Windowsã§ã¯ãå ±æã¡ã¢ãªã䜿çšããŠè¡ãããæ¥ç¶ã«äœ¿çšããå ±æã¡ã¢ãªå
ããŒã«ã«ãµãŒããŒã ããã©ã«ãå€ã¯MYSQLã§ãã å ±æã¡ã¢ãªåã§ã¯å€§æåãšå°æåãåºå¥ãããŸãã
ãµãŒããŒã¯ã - å ±æã¡ã¢ãª å ±æã¡ã¢ãªãæå¹ã«ãããªãã·ã§ã³
æ¥ç¶ã
· - ã·ã§ãŒããã©ãŒã , -s
ãã°ã«å«ãŸããã¹ããŒãã¡ã³ãã®ã¿ã衚瀺ããè¿œå æ å ±ã
è¡ããŒã¹ã®ã€ãã³ãã ããã¯ãã¹ãå°çšã§ãããæ¬çªç°å¢ã§ã¯äœ¿çšããªãã§ãã ãã
ã·ã¹ãã ã
· --skip-gtids [=ïŒtrue | falseïŒ]
åºåã«GTIDã衚瀺ããªãã§ãã ããã ããã¯ããã³ããã¡ã€ã«ã«æžã蟌ããšãã«å¿ èŠã§ã
ãã®äŸã«ç€ºãããã«ãGTIDãå«ãXNUMXã€ä»¥äžã®ãã€ããªãã°ããïŒ
ã·ã§ã«> mysqlbinlog -ã¹ããã-gtids binlog.000001 > /tmp/ãã³ã.sql
ã·ã§ã«> mysqlbinlog -ã¹ããã-gtids binlog.000002 >> /tmp/ãã³ã.sql
ã·ã§ã«> mysqlã® -u ã«ãŒã -p -e "ãœãŒã¹ /tmp/dump.sql "
ãã以å€ã®å Žåããã®ãªãã·ã§ã³ã®äœ¿çšã¯ãéåžžãæ¬çªç°å¢ã§ã¯æšå¥šãããŸããã
ãã®ãªãã·ã§ã³ã¯MySQL5.6.5ã§è¿œå ãããŸããã
· -ãœã±ãã=path, -S path
localhostãžã®æ¥ç¶ã®å Žåã䜿çšããUnixãœã±ãããã¡ã€ã«ããŸãã¯Windowsã®å Žåã¯
䜿çšããååä»ããã€ãã
· --start-datetime =æ¥ä»æå»
ã¿ã€ã ã¹ã¿ã³ãããã以éã®æåã®ã€ãã³ãã§ãã€ããªãã°ã®èªã¿åããéå§ããŸã
ãã æ¥ä»æå» åŒæ°ã 㯠æ¥ä»æå» å€ã¯ãäžã®ããŒã«ã«ã¿ã€ã ãŸãŒã³ãåºæºã«ããŠããŸã
ããªããèµ°ããã·ã³ mysqlbinlogã å€ã¯ãåãå ¥ãããã圢åŒã§ããå¿ èŠããããŸã
DATETIMEãŸãã¯TIMESTAMPããŒã¿åã äŸãã°ïŒ
ã·ã§ã«> mysqlbinlog --start-datetime = "2005-12-25 11:25:56ã binlog.000003
ãã®ãªãã·ã§ã³ã¯ããã€ã³ãã€ã³ã¿ã€ã ãªã«ããªã«åœ¹ç«ã¡ãŸãã ã»ã¯ã·ã§ã³7.3ãããã¯ã¢ããã®äŸãš
å埩æŠç¥ãã
· --start-position =N, -j N
äœçœ®ãåãããã以äžã®æåã®ã€ãã³ãã§ãã€ããªãã°ã®èªã¿åããéå§ããŸã
ãã Nã ãã®ãªãã·ã§ã³ã¯ãã³ãã³ãã©ã€ã³ã§æå®ãããæåã®ãã°ãã¡ã€ã«ã«é©çšãããŸãã
ãã®ãªãã·ã§ã³ã¯ããã€ã³ãã€ã³ã¿ã€ã ãªã«ããªã«åœ¹ç«ã¡ãŸãã ã»ã¯ã·ã§ã³7.3ãããã¯ã¢ããã®äŸãš
å埩æŠç¥ãã
· --stop-datetime =æ¥ä»æå»
ã¿ã€ã ã¹ã¿ã³ãããã以éã®æåã®ã€ãã³ãã§ãã€ããªãã°ã®èªã¿åããåæ¢ããŸã
ãã æ¥ä»æå» å£è«ã ãã®ãªãã·ã§ã³ã¯ããã€ã³ãã€ã³ã¿ã€ã ãªã«ããªã«åœ¹ç«ã¡ãŸãã ãåç §ããŠãã ãã
ã®èª¬æ --éå§æ¥æ ã«é¢ããæ å ±ã®ãªãã·ã§ã³ æ¥ä»æå» ã®å€ã§ãã
ãã®ãªãã·ã§ã³ã¯ããã€ã³ãã€ã³ã¿ã€ã ãªã«ããªã«åœ¹ç«ã¡ãŸãã ã»ã¯ã·ã§ã³7.3ãããã¯ã¢ããã®äŸãš
å埩æŠç¥ãã
· -åæ¢-決ããŠ
ãã®ãªãã·ã§ã³ã¯ --ãªã¢ãŒããµãŒããŒããã®èªã¿åãã ããã¯äŒããŸã mysqlbinlog æ®ã
ãµãŒããŒã«æ¥ç¶ãããŠããŸãã ãããªããš mysqlbinlog æåŸã®ãã°ãã¡ã€ã«ãçµäºãããšçµäºããŸã
ãµãŒããŒãã転éãããŸããã -åæ¢-決ã㊠å«æãã --æåŸã®ãã°ãŸã§ãã ããæåã ã
転éãããã°ãã¡ã€ã«ã¯ãã³ãã³ãã©ã€ã³ã§ååãä»ããå¿ èŠããããŸãã
-åæ¢-決ã㊠ã§äžè¬çã«äœ¿çšãããŸã - ç ã©ã€ããã€ããªãã°ããã¯ã¢ãããäœæããã ãã§ãªãã
ãªãã§äœ¿çšã§ããŸã - ç ãã°ã€ãã³ãã®ç¶ç¶çãªããã¹ã衚瀺ãç¶æããããã«
ãµãŒããŒãããããçæããŸãã
ãã®ãªãã·ã§ã³ã¯MySQL5.6.0ã§è¿œå ãããŸããã
· --stop-never-slave-server-id =id
-åæ¢-決ããŠ, mysqlbinlog ãµãŒããŒã«æ¥ç¶ãããšã65535ã®ãµãŒããŒIDãå ±åããŸãã
ãµãŒããŒã --stop-never-slave-server-id ã¬ããŒããããµãŒããŒIDãæ瀺çã«æå®ããŸãã ãã
ã¹ã¬ãŒããµãŒããŒãŸãã¯å¥ã®ãµãŒããŒã®IDãšã®ç«¶åãåé¿ããããã«äœ¿çšã§ããŸã mysqlbinlog
åŠçããã ãMYSQLBINLOGãµãŒããŒIDã®æå®ããšããã»ã¯ã·ã§ã³ãåç §ããŠãã ããã
ãã®ãªãã·ã§ã³ã¯MySQL5.6.0ã§è¿œå ãããŸããã
· --stop-position =N
äœçœ®ãåãããã以äžã®æåã®ã€ãã³ãã§ããã€ããªãã°ã®èªã¿åããåæ¢ããŸã
ãã Nã ãã®ãªãã·ã§ã³ã¯ãã³ãã³ãã©ã€ã³ã§æå®ãããæåŸã®ãã°ãã¡ã€ã«ã«é©çšãããŸãã
ãã®ãªãã·ã§ã³ã¯ããã€ã³ãã€ã³ã¿ã€ã ãªã«ããªã«åœ¹ç«ã¡ãŸãã ã»ã¯ã·ã§ã³7.3ãããã¯ã¢ããã®äŸãš
å埩æŠç¥ãã
· --æåŸã®ãã°ãŸã§, -t
MySQLãµãŒããŒããèŠæ±ããããã€ããªãã°ã®æåŸã§åæ¢ããã®ã§ã¯ãªãã
æåŸã®ãã€ããªãã°ãçµäºãããŸã§å°å·ãç¶ããŸãã åºåãã«éä¿¡ããå Žå
åãMySQLãµãŒããŒã®å Žåãããã«ããç¡éã«ãŒããçºçããå¯èœæ§ããããŸãã ãã®ãªãã·ã§ã³ã«ã¯
--ãªã¢ãŒããµãŒããŒããã®èªã¿åã.
· --user =USER_NAME, -u USER_NAME
ãªã¢ãŒããµãŒããŒã«æ¥ç¶ãããšãã«äœ¿çšããMySQLãŠãŒã¶ãŒåã
· -詳现, -v
è¡ã€ãã³ããåæ§ç¯ããã³ã¡ã³ãä»ãã®SQLã¹ããŒãã¡ã³ããšããŠè¡šç€ºããŸãã ãã®ãªãã·ã§ã³ã
XNUMXåæå®ãããšãåºåã«ã¯åã®ããŒã¿åãšããã€ãã瀺ãã³ã¡ã³ããå«ãŸããŸã
ã¡ã¿ããŒã¿
ã®å¹æã瀺ãäŸã«ã€ã㊠--base64-åºå & -詳现 è¡ã€ãã³ãã§
åºåã«ã€ããŠã¯ããMYSQLBINLOG ROWEVENTDISPLAYããšããã»ã¯ã·ã§ã³ãåç §ããŠãã ããã
· --verify-binlog-ãã§ãã¯ãµã , -c
ãã€ããªãã°ãã¡ã€ã«ã®ãã§ãã¯ãµã ã確èªããŸãã ãã®ãªãã·ã§ã³ã¯MySQL5.6.1ã§è¿œå ãããŸããã
· - ããŒãžã§ã³, -V
ããŒãžã§ã³æ å ±ã衚瀺ããŠçµäºããŸãã
MySQL 5.6.11ããåã§ã¯ã mysqlbinlog 衚瀺ãããŠããããŒãžã§ã³çªå·ã¯3.3ã§ããã MySQL5.6.11ã§ã¯
以éãããã¯3.4ã§ãã ïŒãã°ïŒ15894381ããã°ïŒ67643ïŒ
次ã®å€æ°ã䜿çšããŠèšå®ããããšãã§ããŸã --å€æ°å=å€ æ§æïŒ
ã»open_files_limit
äºçŽããéããŠãããã¡ã€ã«èšè¿°åã®æ°ãæå®ããŸãã
ããªãã¯ã®åºåããã€ãããããšãã§ããŸã mysqlbinlog ã« mysqlã® ã€ãã³ããå®è¡ããã¯ã©ã€ã¢ã³ã
ãã€ããªãã°ã«å«ãŸããŠããŸãã ãã®ææ³ã¯ã次ã®å Žåã«ã¯ã©ãã·ã¥ããå埩ããããã«äœ¿çšãããŸã
å€ãããã¯ã¢ããïŒã»ã¯ã·ã§ã³7.5ããã€ããªã䜿çšãããã€ã³ãã€ã³ã¿ã€ã ïŒå¢åïŒãªã«ããªããåç §ïŒ
ãã°"ïŒã äŸãã°ïŒ
ã·ã§ã«> mysqlbinlog binlog.000001 | mysqlã® -u ã«ãŒã -p
ãŸãã¯ïŒ
ã·ã§ã«> mysqlbinlog binlogã[0-9] * | mysqlã® -u ã«ãŒã -p
ã«ãã£ãŠçæãããã¹ããŒãã¡ã³ãã®å Žå mysqlbinlog BLOBå€ãå«ãŸããŠããå¯èœæ§ããããããããåå ã§ããå¯èœæ§ããããŸã
ãšãã®åé¡ mysqlã® ããããåŠçããŸãã ãã®å ŽåãåŒã³åºããŸã mysqlã® -ãã€ããªã¢ãŒã
ãªãã·ã§ã³ãéžæããŸãã
ã®åºåããªãã€ã¬ã¯ãããããšãã§ããŸã mysqlbinlog å¿ èŠã«å¿ããŠã代ããã«ããã¹ããã¡ã€ã«ã«å€æããŸã
æåã«ã¹ããŒãã¡ã³ããã°ãå€æŽããŸãïŒããšãã°ãäžèŠãªã¹ããŒãã¡ã³ããåé€ããã«ã¯
äœããã®çç±ã§å®è¡ããŸãïŒã ãã¡ã€ã«ãç·šéããåŸããã¡ã€ã«ã«å«ãŸããã¹ããŒãã¡ã³ããå®è¡ããŸã
ããããžã®å ¥åãšããŠäœ¿çšããããšã«ãã£ãŠ mysqlã® ããã°ã©ã ïŒ
ã·ã§ã«> mysqlbinlog binlog.000001 > tmpãã¡ã€ã«
ã·ã§ã«>..ã edit tmpãã¡ã€ã« ...
ã·ã§ã«> mysqlã® -u ã«ãŒã -p < tmpãã¡ã€ã«
æ¥æ mysqlbinlog ã§åŒã³åºãããŸã -éå§äœçœ® ãªãã·ã§ã³ãããã¯ãããã ãã衚瀺ããŸã
ãã€ããªãã°å ã®ãªãã»ãããç¹å®ã®äœçœ®ä»¥äžã®ã€ãã³ãïŒ
æå®ãããäœçœ®ã¯ãXNUMXã€ã®ã€ãã³ãã®éå§ãšäžèŽããå¿ èŠããããŸãïŒã åæ¢ããã³éå§ãããªãã·ã§ã³ããããŸã
æå®ãããæ¥æã®ã€ãã³ãã衚瀺ããããšãã ããã«ãããå®è¡ããããšãã§ããŸã
ã䜿çšãããã€ã³ãã€ã³ã¿ã€ã ãªã«ã㪠--åæ¢æ¥æ ãªãã·ã§ã³ïŒããšãã°ã
ãããŒã¿ããŒã¹ãä»æ¥ã®åå10æ30åã«ããŒã«ãã©ã¯ãŒãããŸããïŒã
MySQLãµãŒããŒã§å®è¡ãããã€ããªãã°ãè€æ°ããå Žåãå®å šãªæ¹æ³ã¯æ¬¡ã®ãšããã§ãã
ãµãŒããŒãžã®åäžã®æ¥ç¶ã䜿çšããŠããããã¹ãŠãåŠçããŸãã ããããã®äŸã§ã
äœãã§ãããã瀺ããŠããŸã å®å šã§ãªã:
ã·ã§ã«> mysqlbinlog binlog.000001 | mysqlã® -u ã«ãŒã -p # å±éºïŒïŒ
ã·ã§ã«> mysqlbinlog binlog.000002 | mysqlã® -u ã«ãŒã -p # å±éºïŒïŒ
ãµãŒããŒãžã®è€æ°ã®æ¥ç¶ã䜿çšããŠãã®æ¹æ³ã§ãã€ããªãã°ãåŠçãããšãåé¡ãçºçããŸã
æåã®ãã°ãã¡ã€ã«ã«CREATETEMPORARYTABLEã¹ããŒãã¡ã³ããšXNUMXçªç®ã®ãã°ãå«ãŸããŠããå Žå
äžæããŒãã«ã䜿çšããã¹ããŒãã¡ã³ããå«ãŸããŠããŸãã æåã®ãšã mysqlã® ããã»ã¹
çµäºãããšããµãŒããŒã¯äžæããŒãã«ãåé€ããŸãã XNUMXçªç®ã®ãšã mysqlã® ããã»ã¹ã®è©Šè¡
ããŒãã«ã䜿çšããããã«ããµãŒããŒã¯ãäžæãªããŒãã«ããå ±åããŸãã
ãã®ãããªåé¡ãåé¿ããã«ã¯ã mysqlã® ãã¹ãŠã®ã³ã³ãã³ããå®è¡ããããã»ã¹
åŠçãããã€ããªãã°ã ãããè¡ãXNUMXã€ã®æ¹æ³ã¯æ¬¡ã®ãšããã§ãã
ã·ã§ã«> mysqlbinlog binlog.000001 binlog.000002 | mysqlã® -u ã«ãŒã -p
ããXNUMXã€ã®æ¹æ³ã¯ããã¹ãŠã®ãã°ãXNUMXã€ã®ãã¡ã€ã«ã«æžã蟌ãã§ããããã®ãã¡ã€ã«ãåŠçããããšã§ãã
ã·ã§ã«> mysqlbinlog binlog.000001 > /tmp/statements.sql
ã·ã§ã«> mysqlbinlog binlog.000002 >> /tmp/statements.sql
ã·ã§ã«> mysqlã® -u ã«ãŒã -p -e "ãœãŒã¹ /tmp/statements.sql "
mysqlbinlog LOAD DATAINFILEæäœãåçŸããåºåãçæã§ããŸãã
å ã®ããŒã¿ãã¡ã€ã«ã mysqlbinlog ããŒã¿ãäžæãã¡ã€ã«ã«ã³ããŒããLOADãæžã蟌ã¿ãŸã
ãã¡ã€ã«ãåç §ããDATALOCALINFILEã¹ããŒãã¡ã³ãã ãã£ã¬ã¯ããªã®ããã©ã«ãã®å Žæ
ãããã®ãã¡ã€ã«ãæžã蟌ãŸããå Žæã¯ã·ã¹ãã åºæã§ãã ãã£ã¬ã¯ããªãæ瀺çã«æå®ããã«ã¯ã
ã --ããŒã«ã«ããŒã ãªãã·ã§ã³ãéžæããŸãã
ãªããªã mysqlbinlog LOAD DATAINFILEã¹ããŒãã¡ã³ããLOADDATA LOCALINFILEã«å€æããŸã
ã¹ããŒãã¡ã³ãïŒã€ãŸããLOCALãè¿œå ããŸãïŒã䜿çšããã¯ã©ã€ã¢ã³ããšãµãŒããŒã®äž¡æ¹
ã¹ããŒãã¡ã³ããåŠçããã«ã¯ãLOCALæ©èœãæå¹ã«ããŠæ§æããå¿ èŠããããŸãã èŠã
6.1.6é ãLOADDATALOCALã®ã»ãã¥ãªãã£åé¡ãã
èŠå
LOAD DATALOCALã¹ããŒãã¡ã³ãçšã«äœæãããäžæãã¡ã€ã«ã¯æ¬¡ã®ãšããã§ãã èªåçã«
ãããã®ã¹ããŒãã¡ã³ããå®éã«å®è¡ãããŸã§å¿ èŠã«ãªããããåé€ãããŸããã å
ã¹ããŒãã¡ã³ããã°ãäžèŠã«ãªã£ãããäžæãã¡ã€ã«ãèªåã§åé€ããå¿ èŠããããŸãã
ãã¡ã€ã«ã¯äžæãã¡ã€ã«ãã£ã¬ã¯ããªã«ããã次ã®ãããªååãä»ããŠããŸãã
original_file_name-ïŒ-ïŒ.
MYSQLBINLOG HEX DUMP FORMAT
ã --hexdump ãªãã·ã§ã³ã®åå mysqlbinlog ãã€ããªãã°ã®å 容ã®XNUMXé²ãã³ããçæããã«ã¯ïŒ
ã·ã§ã«> mysqlbinlog --hexdump ãã¹ã¿ãŒãã³.000001
XNUMXé²åºåã¯ãïŒã§å§ãŸãã³ã¡ã³ãè¡ã§æ§æãããŠãããããåºåã¯æ¬¡ã®ããã«ãªããŸãã
åã®ã³ãã³ãã®å ŽåïŒ
/ *ïŒ40019 SET @@ session.max_insert_delayed_threads = 0 * /;
/ *ïŒ50003 SET @ OLD_COMPLETION_TYPE = @@ COMPLETION_TYPEãCOMPLETION_TYPE = 0 * /;
ïŒ4
ïŒ051024ïŒ17ïŒ24ãµãŒããŒID13 end_log_pos 1
ïŒäœçœ®ã¿ã€ã ã¹ã¿ã³ãã¿ã€ããã¹ã¿ãŒIDãµã€ãºãã¹ã¿ãŒäœçœ®ãã©ã°
ïŒ00000004 9d fc 5c 43 0f 01 00 00 00 5e 00 00 00 62 00 00 00 00 00
ïŒ00000017 04 00 35 2e 30 2e 31 35 2d 64 65 62 75 67 2d 6c | ..5.0.15.debug.l |
ïŒ00000027 6f 67 00 00 00 00 00 00 00 00 00 00 00 00 00 00 | og .............. |
ïŒ00000037 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 | ................ |
ïŒ00000047 00 00 00 00 9d fc 5c 43 13 38 0d 00 08 00 12 00 | ....... C.8 ...... |
ïŒ00000057 04 04 04 04 12 00 00 4b 00 04 1a | ....... K ... |
ïŒéå§ïŒbinlog v 4ãserver v5.0.15-debug-logcreated 051024 17:24:13
ïŒèµ·åæ
ããŒã«ããã¯;
XNUMXé²ãã³ãã®åºåã«ã¯ãçŸåšã次ã®ãªã¹ãã®èŠçŽ ãå«ãŸããŠããŸãã ãã®ãã©ãŒãããã¯
å€æŽã®äºå®ã ïŒãã€ããªãã°åœ¢åŒã®è©³çŽ°ã«ã€ããŠã¯ããåç §ããŠãã ããã MySQL å éšïŒ ã
ãã€ã㪠ãã°[1]ã
ã»äœçœ®ïŒãã°ãã¡ã€ã«å ã®ãã€ãäœçœ®ã
ã»ã¿ã€ã ã¹ã¿ã³ãïŒã€ãã³ãã®ã¿ã€ã ã¹ã¿ã³ãã 瀺ãããŠããäŸã§ã¯ãã9d fc5c43ãã¯
'051024ïŒ17ïŒ24'ã®13é²æ°ã§ã®è¡šçŸã
ã»ã¿ã€ãïŒã€ãã³ãã¿ã€ãã³ãŒãã 瀺ãããŠããäŸã§ã¯ãã0fãã¯
FORMAT_DESCRIPTION_EVENTã 次ã®è¡šã«ãå¯èœãªã¿ã€ãã³ãŒãã瀺ããŸãã
âââââââ¬âââââââââââââââââââââââââââ¬âââââââââââââââââ âââââââââââââââââââââââ
âã¿ã€ã â åå â æå³ â
âââââââŒâââââââââââââââââââââââââââŒâââââââââââââââââ âââââââââââââââââââââââ€
â00âUNKNOWN_EVENTâãã®ã€ãã³ãã¯çµ¶å¯Ÿã«è¡ããªãã§ãã ããâ
âââãã°ã«ååšããŸãã â
âââââââŒâââââââââââââââââââââââââââŒâââââââââââââââââ âââââââââââââââââââââââ€
â01âSTART_EVENT_V3âããã¯éå§ã瀺ããŸãâ
âââã«ãã£ãŠæžã蟌ãŸãããã°ãã¡ã€ã«ã®
âââMySQL4以åã â
âââââââŒâââââââââââââââââââââââââââŒâââââââââââââââââ âââââââââââââââââââââââ€
â02âQUERY_EVENTâæãäžè¬çãªã¿ã€ãã®â
âââã€ãã³ãã ãããã«ã¯âãå«ãŸããŠããŸã
âââã§å®è¡ãããã¹ããŒãã¡ã³ã
ââââ
âââãã¹ã¿ãŒã â
âââââââŒâââââââââââââââââââââââââââŒâââââââââââââââââ âââââââââââââââââââââââ€
â03âSTOP_EVENTâãã¹ã¿ãŒãæã£ãŠããããšã瀺ããŸãâ
âââåæ¢ããŸããã â
âââââââŒâââââââââââââââââââââââââââŒâââââââââââââââââ âââââââââââââââââââââââ€
â04âROTATE_EVENTâãã¹ã¿ãŒæã«æžãããâ
âââæ°ãããã°ãã¡ã€ã«ã«åãæ¿ããŸãã â
âââââââŒâââââââââââââââââââââââââââŒâââââââââââââââââ âââââââââââââââââââââââ€
â05âINTVAR_EVENTâAUTO_INCREMENTã«äœ¿çšâ
âââå€ãŸãã¯
âââLAST_INSERT_IDïŒïŒâ
âââæ©èœâ
âââã¯ã¹ããŒãã¡ã³ãã§äœ¿çšãããŸãã â
âââââââŒâââââââââââââââââââââââââââŒâââââââââââââââââ âââââââââââââââââââââââ€
â06âLOAD_EVENTâLOADDATAã«äœ¿çšâ
âââMySQLã®INFILEâ
âââ3.23ã â
âââââââŒâââââââââââââââââââââââââââŒâââââââââââââââââ âââââââââââââââââââââââ€
â07âSLAVE_EVENTâå°æ¥ã®äœ¿çšã®ããã«äºçŽãããŠããŸãã â
âââââââŒâââââââââââââââââââââââââââŒâââââââââââââââââ âââââââââââââââââââââââ€
â08âCREATE_FILE_EVENTâLOADDATAã«äœ¿çšâ
âââINFILEâ
âââã¹ããŒãã¡ã³ãã ããã¯ãâã瀺ããŠããŸã
âââéå§â
âââãã®ãããªã¹ããŒãã¡ã³ãã®å®è¡ã Aâ
âââäžæçâ
âââãã¡ã€ã«ãäœæãããŸãâ
âââã¹ã¬ãŒãäžã MySQL4ã§ã®ã¿äœ¿çšãããŸãã â
âââââââŒâââââââââââââââââââââââââââŒâââââââââââââââââ âââââââââââââââââââââââ€
â09âAPPEND_BLOCK_EVENTâã§äœ¿çšããããŒã¿ãå«ãŸããŠããŸãâ
âââããŒã¿ã®èªã¿èŸŒã¿â
âââINFILEâ
âââã¹ããŒãã¡ã³ãã ããŒã¿ã¯âã«ä¿åãããŸã
âââäžæçãªâ
âââã¹ã¬ãŒãäžã®ãã¡ã€ã«ã â
âââââââŒâââââââââââââââââââââââââââŒâââââââââââââââââ âââââââââââââââââââââââ€
â0aâEXEC_LOAD_EVENTâLOADDATAã«äœ¿çšâ
âââINFILEâ
âââã¹ããŒãã¡ã³ãã âã®å 容
âââäžæãã¡ã€ã«â
âââã¯ã¹ã¬ãŒãã®ããŒãã«ã«æ ŒçŽãããŸãã â
âââMySQL4ã§äœ¿çšâ
âââã®ã¿ã â
âââââââŒâââââââââââââââââââââââââââŒâââââââââââââââââ âââââââââââââââââââââââ€
â0bâDELETE_FILE_EVENTâããŒãããŒã¿ã®ããŒã«ããã¯â
âââINFILEâ
âââã¹ããŒãã¡ã³ãã äžæãã¡ã€ã«â
âââã¯âã§ããå¿ èŠããããŸã
âââã¹ã¬ãŒãã§åé€ãããŸããã â
âââââââŒâââââââââââââââââââââââââââŒâââââââââââââââââ âââââââââââââââââââââââ€
â0câNEW_LOAD_EVENTâLOADDATAã«äœ¿çšâ
âââMySQLã®INFILEâ
âââ4以åã â
âââââââŒâââââââââââââââââââââââââââŒâââââââââââââââââ âââââââââââââââââââââââ€
â0dâRAND_EVENTâã«é¢ããæ å ±ãéä¿¡ããããã«äœ¿çšãããŸãâ
âââã©ã³ãã ãªå€ã®å Žå
âââRANDïŒïŒé¢æ°â
âââã¯âã§ã
âââã§äœ¿çšãããâ
âââã¹ããŒãã¡ã³ãã â
âââââââŒâââââââââââââââââââââââââââŒâââââââââââââââââ âââââââââââââââââââââââ€
â0eâUSER_VAR_EVENTâãŠãŒã¶ãŒå€æ°ãè€è£œããããã«äœ¿çšãããŸãã â
âââââââŒâââââââââââââââââââââââââââŒâââââââââââââââââ âââââââââââââââââââââââ€
â0fâFORMAT_DESCRIPTION_EVENTâããã¯ãã°ã®éå§ã瀺ããŸãâ
âââMySQL5以éã§äœæããããã¡ã€ã«ã â
âââââââŒâââââââââââââââââââââââââââŒâââââââââââââââââ âââââââââââââââââââââââ€
â10âXID_EVENTâXAã®ã³ãããã瀺ãã€ãã³ãâ
âââãã©ã³ã¶ã¯ã·ã§ã³ã â
âââââââŒâââââââââââââââââââââââââââŒâââââââââââââââââ âââââââââââââââââââââââ€
â11âBEGIN_LOAD_QUERY_EVENTâLOADDATAã«äœ¿çšâ
âââINFILEâ
âââMySQL5以éã®ã¹ããŒãã¡ã³ãã â
âââââââŒâââââââââââââââââââââââââââŒâââââââââââââââââ âââââââââââââââââââââââ€
â12âEXECUTE_LOAD_QUERY_EVENTâLOADDATAã«äœ¿çšâ
âââINFILEâ
âââMySQL5以éã®ã¹ããŒãã¡ã³ãã â
âââââââŒâââââââââââââââââââââââââââŒâââââââââââââââââ âââââââââââââââââââââââ€
â13âTABLE_MAP_EVENTâããŒãã«ã«é¢ããæ å ±â
âââå®çŸ©ã MySQL5.1.5ããã³âã§äœ¿çš
âââåŸã§ã â
âââââââŒâââââââââââââââââââââââââââŒâââââââââââââââââ âââââââââââââââââââââââ€
â14âPRE_GA_WRITE_ROWS_EVENTâåäžã®ããŒãã«ã®è¡ããŒã¿
âââãäœæããå¿ èŠããããŸãã MySQLã§äœ¿çšãããâ
âââ5.1.5â
âââ5.1.17ãžã â
âââââââŒâââââââââââââââââââââââââââŒâââââââââââââââââ âââââââââââââââââââââââ€
â15âPRE_GA_UPDATE_ROWS_EVENTâåäžã®ããŒãã«ã®è¡ããŒã¿
âââæŽæ°ããå¿ èŠããããŸãã MySQLã§äœ¿çšãããâ
âââ5.1.5ã5.1.17ã â
âââââââŒâââââââââââââââââââââââââââŒâââââââââââââââââ âââââââââââââââââââââââ€
â16âPRE_GA_DELETE_ROWS_EVENTâåäžã®ããŒãã«ã®è¡ããŒã¿
âââåé€ããå¿ èŠããããŸãã MySQLã§äœ¿çšãããâ
âââ5.1.5â
âââ5.1.17ãžã â
âââââââŒâââââââââââââââââââââââââââŒâââââââââââââââââ âââââââââââââââââââââââ€
â17âWRITE_ROWS_EVENTâåäžã®ããŒãã«ã®è¡ããŒã¿
âââãäœæããå¿ èŠããããŸãã MySQLã§äœ¿çšãããâ
âââ5.1.18â
âââ以éã â
âââââââŒâââââââââââââââââââââââââââŒâââââââââââââââââ âââââââââââââââââââââââ€
â18âUPDATE_ROWS_EVENTâåäžã®ããŒãã«ã®è¡ããŒã¿
âââæŽæ°ããå¿ èŠããããŸãã MySQLã§äœ¿çšãããâ
âââ5.1.18ããã³â
âââåŸã§ã â
âââââââŒâââââââââââââââââââââââââââŒâââââââââââââââââ âââââââââââââââââââââââ€
â19âDELETE_ROWS_EVENTâåäžã®ããŒãã«ã®è¡ããŒã¿
âââåé€ããå¿ èŠããããŸãã MySQLã§äœ¿çšãããâ
âââ5.1.18â
âââ以éã â
âââââââŒâââââââââââââââââââââââââââŒâââââââââââââââââ âââââââââââââââââââââââ€
â1aâINCIDENT_EVENTâç°åžžãªäœãâ
âââèµ·ãã£ãã MySQL5.1.18ã§è¿œå ãããŸããã â
âââââââŽâââââââââââââââââââââââââââŽâââââââââââââââââ âââââââââââââââââââââââ
ã»ãã¹ã¿ãŒIDïŒã€ãã³ããäœæãããã¹ã¿ãŒã®ãµãŒããŒIDã
ã»ãµã€ãºïŒã€ãã³ãã®ãã€ãåäœã®ãµã€ãºã
ã»ãã¹ã¿ãŒäœçœ®ïŒå ã®ãã¹ã¿ãŒãã°ãã¡ã€ã«å ã®æ¬¡ã®ã€ãã³ãã®äœçœ®ã
ã»ãã©ã°ïŒ16åã®ãã©ã°ã çŸåšã以äžã®ãã©ã°ã䜿çšãããŠããŸãã ä»ã¯ã®ããã«äºçŽãããŠããŸã
å°æ¥ã®äœ¿çšã
âââââââ¬ââââââââââââââââââââââââââââââ¬ââââââââââââââ ââââââââââââââââââââââââââââââââââââ
âãã©ã° â åå â æå³ â
âââââââŒââââââââââââââââââââââââââââââŒââââââââââââââ ââââââââââââââââââââââââââââââââââââ€
â01âLOG_EVENT_BINLOG_IN_USE_Fâãã°ãã¡ã€ã«ãæ£ããâ
âââéããã ïŒâã§ã®ã¿äœ¿çš
âââFORMAT_DESCRIPTION_EVENTãïŒâ
âââIfâ
âââthisâ
âââãã©ã°ãèšå®ãããŠããïŒâã®å Žå
âââãã©ã°ã¯ãäŸãã°ãâã§ãã
âââ'01â
âââ00 'ïŒã®â
âââFORMAT_DESCRIPTION_EVENTãâ
âââãã°â
âââãã¡ã€ã«â
âââé©åã«ãããŠããŸããâ
âââéããã ããããâ
âââthisâ
âââã¯ãã¹ã¿ãŒã®ããã§ãâ
âââã¯ã©ãã·ã¥ïŒããšãã°ã
âââããâ
âââåé»ïŒã â
âââââââŒââââââââââââââââââââââââââââââŒââââââââââââââ ââââââââââââââââââââââââââââââââââââ€
â02ââå°æ¥ã®äœ¿çšã®ããã«äºçŽãããŠããŸãã â
âââââââŒââââââââââââââââââââââââââââââŒââââââââââââââ ââââââââââââââââââââââââââââââââââââ€
â04âLOG_EVENT_THREAD_SPECIFIC_Fâã€ãã³ããäŸåããŠããå Žåã«èšå®
âââãããå®è¡ãããæ¥ç¶ïŒ
âââäŸã'04 00 'ïŒãâã®å Žå
âââäŸãâ
âââã€ãã³ãã䜿çšããå Žåâ
âââäžæããŒãã«ã â
âââââââŒââââââââââââââââââââââââââââââŒââââââââââââââ ââââââââââââââââââââââââââââââââââââ€
â08âLOG_EVENT_SUPPRESS_USE_Fâã€ãã³ããã®å Žåã«èšå®ãããŸãâ
âââããã©ã«ãã«äŸåããªãâ
âââããŒã¿ããŒã¹ã â
âââââââŽââââââââââââââââââââââââââââââŽââââââââââââââ ââââââââââââââââââââââââââââââââââââ
MYSQLBINLOG è¡ EVENT DISPLAY
次ã®äŸã¯ããã®æ¹æ³ã瀺ããŠããŸã mysqlbinlog ããŒã¿ãæå®ããè¡ã€ãã³ãã衚瀺ããŸã
å€æŽã ãããã¯ãWRITE_ROWS_EVENTãUPDATE_ROWS_EVENTã
ããã³DELETE_ROWS_EVENTã¿ã€ãã³ãŒãã The --base64-output = DECODE-ROWS & -詳现 ãªãã·ã§ã³
è¡ã€ãã³ãã®åºåã«åœ±é¿ãäžããããã«äœ¿çšãããå ŽåããããŸãã
ãµãŒããŒãè¡ããŒã¹ã®ãã€ããªãã°ã䜿çšããŠããŠã
次ã®äžé£ã®ã¹ããŒãã¡ã³ãïŒ
ããŒãã«ã®äœæ t
(
id INT NOT NULLã
å VARCHARïŒ20ïŒNOT NULLã
æ¥ä»DATENULL
ïŒENGINE = InnoDB;
ãã©ã³ã¶ã¯ã·ã§ã³ãéå§ããŸãã
INSERT INTO t VALUESïŒ1ã 'apple'ãNULLïŒ;
UPDATE t SET name = 'pear'ãdate = '2009-01-01' WHERE id = 1;
t WHERE id = 1ããåé€;
COMMIT;
ããã©ã«ãã§ã¯ã mysqlbinlog BINLOGã䜿çšããŠbase-64æååãšããŠãšã³ã³ãŒããããè¡ã€ãã³ãã衚瀺ããŸã
ã¹ããŒãã¡ã³ãã ç¡é¢ä¿ãªè¡ãçç¥ããŠãã«ãã£ãŠçæãããè¡ã€ãã³ãã®åºå
åã®ã¹ããŒãã¡ã³ãã·ãŒã±ã³ã¹ã¯æ¬¡ã®ããã«ãªããŸãã
ã·ã§ã«> mysqlbinlog ãã°ãã¡ã€ã«
...
ïŒ218
ïŒ080828ïŒ15ïŒ03ãµãŒããŒID08 end_log_pos 1 Write_rowsïŒããŒãã«ID 258ãã©ã°ïŒSTMT_END_F
BINLOG '
fAS3SBMBAAAALAAAANoAAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ =
fAS3SBcBAAAAKAAAAAIBAAAQABEAAAAAAAEAA // 8AQAAAAVhcHBsZQ ==
'/ *ïŒ* /;
...
ïŒ302
ïŒ080828ïŒ15ïŒ03ãµãŒããŒID08 end_log_pos 1 Update_rowsïŒããŒãã«ID 356ãã©ã°ïŒSTMT_END_F
BINLOG '
fAS3SBMBAAAALAAAAC4BAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ =
fAS3SBgBAAAANgAAAGQBAAAQABEAAAAAAAEAA //// AEAAAAFYXBwbGX4AQAAAARwZWFyIbIP
'/ *ïŒ* /;
...
ïŒ400
ïŒ080828ïŒ15ïŒ03ãµãŒããŒID08 end_log_pos 1 Delete_rowsïŒããŒãã«ID 442ãã©ã°ïŒSTMT_END_F
BINLOG '
fAS3SBMBAAAALAAAAJABAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ =
fAS3SBkBAAAAKgAAALoBAAAQABEAAAAAAAEAA // 4AQAAAARwZWFyIbIP
'/ *ïŒ* /;
è¡ã€ãã³ãããç䌌SQLãã¹ããŒãã¡ã³ãã®åœ¢åŒã®ã³ã¡ã³ããšããŠè¡šç€ºããã«ã¯ã次ãå®è¡ããŸãã mysqlbinlog
-詳现 or -v ãªãã·ã§ã³ã åºåã«ã¯ã###ã§å§ãŸãè¡ãå«ãŸããŸãã
ã·ã§ã«> mysqlbinlog -v ãã°ãã¡ã€ã«
...
ïŒ218
ïŒ080828ïŒ15ïŒ03ãµãŒããŒID08 end_log_pos 1 Write_rowsïŒããŒãã«ID 258ãã©ã°ïŒSTMT_END_F
BINLOG '
fAS3SBMBAAAALAAAANoAAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ =
fAS3SBcBAAAAKAAAAAIBAAAQABEAAAAAAAEAA // 8AQAAAAVhcHBsZQ ==
'/ *ïŒ* /;
### test.t ã«æ¿å ¥
ïŒïŒïŒ ã»ãããã
### @ 1 = 1
### @ 2 = 'ã¢ããã«'
### @ 3 = NULL
...
ïŒ302
ïŒ080828ïŒ15ïŒ03ãµãŒããŒID08 end_log_pos 1 Update_rowsïŒããŒãã«ID 356ãã©ã°ïŒSTMT_END_F
BINLOG '
fAS3SBMBAAAALAAAAC4BAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ =
fAS3SBgBAAAANgAAAGQBAAAQABEAAAAAAAEAA //// AEAAAAFYXBwbGX4AQAAAARwZWFyIbIP
'/ *ïŒ* /;
### test.t ãæŽæ°
ïŒïŒïŒ ã©ã
### @ 1 = 1
### @ 2 = 'ã¢ããã«'
### @ 3 = NULL
ïŒïŒïŒ ã»ãããã
### @ 1 = 1
### @ 2 = '梚'
### @ 3 = '2009ïŒ01ïŒ01'
...
ïŒ400
ïŒ080828ïŒ15ïŒ03ãµãŒããŒID08 end_log_pos 1 Delete_rowsïŒããŒãã«ID 442ãã©ã°ïŒSTMT_END_F
BINLOG '
fAS3SBMBAAAALAAAAJABAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ =
fAS3SBkBAAAAKgAAALoBAAAQABEAAAAAAAEAA // 4AQAAAARwZWFyIbIP
'/ *ïŒ* /;
### test.t ããåé€
ïŒïŒïŒ ã©ã
### @ 1 = 1
### @ 2 = '梚'
### @ 3 = '2009ïŒ01ïŒ01'
æå® -詳现 or -v ããããã®ããŒã¿åãšããã€ãã®ã¡ã¿ããŒã¿ã衚瀺ããããã«XNUMXå
æ¡ã åºåã«ã¯ãååã®å€æŽã«ç¶ãè¿œå ã®ã³ã¡ã³ããå«ãŸããŸãã
ã·ã§ã«> mysqlbinlog -vv ãã°ãã¡ã€ã«
...
ïŒ218
ïŒ080828ïŒ15ïŒ03ãµãŒããŒID08 end_log_pos 1 Write_rowsïŒããŒãã«ID 258ãã©ã°ïŒSTMT_END_F
BINLOG '
fAS3SBMBAAAALAAAANoAAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ =
fAS3SBcBAAAAKAAAAAIBAAAQABEAAAAAAAEAA // 8AQAAAAVhcHBsZQ ==
'/ *ïŒ* /;
### test.t ã«æ¿å ¥
ïŒïŒïŒ ã»ãããã
### @ 1 = 1 / * INT meta = 0 nullable = 0 is_null = 0 * /
### @ 2 = 'ã¢ããã«' / * VASTRINGïŒ20ïŒmeta = 20 nullable = 0 is_null = 0 * /
### @ 3 = NULL / * VASTRINGïŒ20ïŒmeta = 0 nullable = 1 is_null = 1 * /
...
ïŒ302
ïŒ080828ïŒ15ïŒ03ãµãŒããŒID08 end_log_pos 1 Update_rowsïŒããŒãã«ID 356ãã©ã°ïŒSTMT_END_F
BINLOG '
fAS3SBMBAAAALAAAAC4BAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ =
fAS3SBgBAAAANgAAAGQBAAAQABEAAAAAAAEAA //// AEAAAAFYXBwbGX4AQAAAARwZWFyIbIP
'/ *ïŒ* /;
### test.t ãæŽæ°
ïŒïŒïŒ ã©ã
### @ 1 = 1 / * INT meta = 0 nullable = 0 is_null = 0 * /
### @ 2 = 'ã¢ããã«' / * VASTRINGïŒ20ïŒmeta = 20 nullable = 0 is_null = 0 * /
### @ 3 = NULL / * VASTRINGïŒ20ïŒmeta = 0 nullable = 1 is_null = 1 * /
ïŒïŒïŒ ã»ãããã
### @ 1 = 1 / * INT meta = 0 nullable = 0 is_null = 0 * /
### @ 2 = '梚' / * VASTRINGïŒ20ïŒmeta = 20 nullable = 0 is_null = 0 * /
### @ 3 = '2009ïŒ01ïŒ01' / * DATE meta = 0 nullable = 1 is_null = 0 * /
...
ïŒ400
ïŒ080828ïŒ15ïŒ03ãµãŒããŒID08 end_log_pos 1 Delete_rowsïŒããŒãã«ID 442ãã©ã°ïŒSTMT_END_F
BINLOG '
fAS3SBMBAAAALAAAAJABAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ =
fAS3SBkBAAAAKgAAALoBAAAQABEAAAAAAAEAA // 4AQAAAARwZWFyIbIP
'/ *ïŒ* /;
### test.t ããåé€
ïŒïŒïŒ ã©ã
### @ 1 = 1 / * INT meta = 0 nullable = 0 is_null = 0 * /
### @ 2 = '梚' / * VASTRINGïŒ20ïŒmeta = 20 nullable = 0 is_null = 0 * /
### @ 3 = '2009ïŒ01ïŒ01' / * DATE meta = 0 nullable = 1 is_null = 0 * /
æããŠãããã mysqlbinlog ã䜿çšããŠè¡ã€ãã³ãã®BINLOGã¹ããŒãã¡ã³ããæå¶ãã
--base64-output = DECODE-ROWS ãªãã·ã§ã³ã ããã¯ã«äŒŒãŠããŸã --base64-output = NEVER ããããããŸãã
è¡ã€ãã³ããèŠã€ãã£ãå Žåã¯ããšã©ãŒã§çµäºããŸãã ã®çµã¿åãã --base64-output = DECODE-ROWS
& -詳现 è¡ã€ãã³ããSQLã¹ããŒãã¡ã³ããšããŠã®ã¿è¡šç€ºãã䟿å©ãªæ¹æ³ãæäŸããŸãã
ã·ã§ã«> mysqlbinlog -v --base64-output = DECODE-ROWS ãã°ãã¡ã€ã«
...
ïŒ218
ïŒ080828ïŒ15ïŒ03ãµãŒããŒID08 end_log_pos 1 Write_rowsïŒããŒãã«ID 258ãã©ã°ïŒSTMT_END_F
### test.t ã«æ¿å ¥
ïŒïŒïŒ ã»ãããã
### @ 1 = 1
### @ 2 = 'ã¢ããã«'
### @ 3 = NULL
...
ïŒ302
ïŒ080828ïŒ15ïŒ03ãµãŒããŒID08 end_log_pos 1 Update_rowsïŒããŒãã«ID 356ãã©ã°ïŒSTMT_END_F
### test.t ãæŽæ°
ïŒïŒïŒ ã©ã
### @ 1 = 1
### @ 2 = 'ã¢ããã«'
### @ 3 = NULL
ïŒïŒïŒ ã»ãããã
### @ 1 = 1
### @ 2 = '梚'
### @ 3 = '2009ïŒ01ïŒ01'
...
ïŒ400
ïŒ080828ïŒ15ïŒ03ãµãŒããŒID08 end_log_pos 1 Delete_rowsïŒããŒãã«ID 442ãã©ã°ïŒSTMT_END_F
### test.t ããåé€
ïŒïŒïŒ ã©ã
### @ 1 = 1
### @ 2 = '梚'
### @ 3 = '2009ïŒ01ïŒ01'
Note
åå®è¡ããå Žåã¯ãBINLOGã¹ããŒãã¡ã³ããæå¶ããªãã§ãã ããã mysqlbinlog
åºåã
ã«ãã£ãŠçæãããSQLã¹ããŒãã¡ã³ã -詳现 è¡ã€ãã³ãã®å Žåã¯ã
察å¿ããBINLOGã¹ããŒãã¡ã³ãã ãã ããå ã®æåãšæ£ç¢ºã«äžèŽããŠããããã§ã¯ãããŸãã
ã€ãã³ããçæããSQLã¹ããŒãã¡ã³ãã 次ã®å¶éãé©çšãããŸãã
ã»å ã®ååã¯å€±ããã@ã«çœ®ãæããããŸãNããã§ã N åçªå·ã§ãã
ã»æåã»ããæ å ±ã¯ãæååã«åœ±é¿ãããã€ããªãã°ã§ã¯å©çšã§ããŸãã
å衚瀺ïŒ
ã»å¯Ÿå¿ãããã€ããªæååãšéãã€ããªæååã®åºå¥ã¯ãããŸãã
ã¿ã€ãïŒBINARYãšCHARãVARBINARYãšVARCHARãBLOBãšTEXTïŒã åºåã¯ã䜿çšããŸã
åºå®é·æååã®å Žåã¯STRINGã®ããŒã¿åãå¯å€é·æååã®å Žåã¯VARSTRINGã®ããŒã¿å
æååã
ã»ãã«ããã€ãæåã»ããã®å ŽåãXNUMXæåãããã®æ倧ãã€ãæ°ã¯
ãã€ããªãã°ã«ååšãããããæåååã®é·ãã¯ãã€ãåäœã§è¡šç€ºãããŸã
æåã§ã¯ãªãã äŸãã°ã STRINGïŒ4ïŒã¯ããŒã¿åãšããŠäœ¿çšãããŸã
ãããã®åã¿ã€ãã®ããããããã®å€ã®å ŽåïŒ
CHARïŒ4ïŒæåã»ããlatin1
CHARïŒ2ïŒæåã»ããucs2
ã»ã¿ã€ãUPDATE_ROWS_EVENTã®ã€ãã³ãã®ä¿å圢åŒã«ãããUPDATEã¹ããŒãã¡ã³ã
SETå¥ã®åã«WHEREå¥ãä»ããŠè¡šç€ºãããŸãã
è¡ã€ãã³ããé©åã«è§£éããã«ã¯ã圢åŒã®èª¬æããã®æ å ±ãå¿ èŠã§ã
ãã€ããªãã°ã®å é ã«ããã€ãã³ãã ãªããªã mysqlbinlog äºåã«ç¥ããªã
ãã°ã®æ®ãã®éšåã«è¡ã€ãã³ããå«ãŸããŠãããã©ãããããã©ã«ãã§ã¯æ¬¡ã®åœ¢åŒã§è¡šç€ºãããŸã
åºåã®æåã®éšåã§BINLOGã¹ããŒãã¡ã³ãã䜿çšããdescriptionã€ãã³ãã
ãã€ããªãã°ã«BINLOGã¹ããŒãã¡ã³ããå¿ èŠãšããã€ãã³ããå«ãŸããŠããªãããšãããã£ãŠããå ŽåïŒ
ã¯ãè¡ã€ãã³ããªãïŒã --base64-output = NEVER ãªãã·ã§ã³ã䜿çšããŠããã®ããããŒãé²ãããšãã§ããŸã
æžãããããšããã
䜿çšãã MYSQLBINLOG ã« BACK UP BINARY LOG ãã¡ã€ã«
ããã©ã«ãã§ã¯ã mysqlbinlog ãã€ããªãã°ãã¡ã€ã«ãèªã¿åãããã®å 容ãããã¹ã圢åŒã§è¡šç€ºããŸãã
ããã«ããããã¡ã€ã«å ã®ã€ãã³ããããç°¡åã«èª¿ã¹ãŠãããããåå®è¡ã§ããŸãã
ïŒããšãã°ãåºåããžã®å ¥åãšããŠäœ¿çšããããšã«ãã£ãŠ mysqlã®). mysqlbinlog ãã°ãã¡ã€ã«ãèªã¿åãããšãã§ããŸã
ããŒã«ã«ãã¡ã€ã«ã·ã¹ãã ããçŽæ¥ããŸãã¯ã --ãªã¢ãŒããµãŒããŒããã®èªã¿åã ãªãã·ã§ã³ãããã¯ã§ããŸã
ãµãŒããŒã«æ¥ç¶ãããã®ãµãŒããŒã«ãã€ããªãã°ã®å 容ãèŠæ±ããŸãã mysqlbinlog æžã蟌ã¿
æšæºåºåããŸãã¯ã®å€ãšããŠæå®ããããã¡ã€ã«ãžã®ããã¹ãåºå
--result-file =file_name ãã®ãªãã·ã§ã³ãæå®ãããŠããå Žåã¯ãªãã·ã§ã³ã
MySQL 5.6以éã mysqlbinlog ãã€ããªãã°ãã¡ã€ã«ãèªã¿åãããå«ãæ°ãããã¡ã€ã«ãæžã蟌ãããšãã§ããŸã
åãã³ã³ãã³ããã€ãŸããããã¹ã圢åŒã§ã¯ãªããã€ããªåœ¢åŒã ãã®æ©èœã«ããã
ãã€ããªãã°ãå ã®åœ¢åŒã§ç°¡åã«ããã¯ã¢ããã§ããŸãã mysqlbinlog éçã«ããããšãã§ããŸã
ããã¯ã¢ãããäžé£ã®ãã°ãã¡ã€ã«ã®ããã¯ã¢ãããããã³æåŸã®ãã¡ã€ã«ã®çµããã
ã«éããã ãŸãããµãŒããŒãžã®æ¥ç¶ãç¶æãããŸãŸãç¶ç¶çãªïŒãã©ã€ããïŒããã¯ã¢ãããäœæããããšãã§ããŸãã
æåŸã®ãã°ãã¡ã€ã«ã®æåŸã«å°éããæ°ããã€ãã³ãããã®ãŸãŸã³ããŒãç¶ããå Žå
çæãããŸãã é£ç¶ããã¯ã¢ããæäœã§ã¯ã mysqlbinlog æ¥ç¶ãçµäºãããŸã§å®è¡ãããŸãïŒ
ããšãã°ããµãŒããŒãçµäºãããšãïŒãŸã㯠mysqlbinlog 匷å¶çµäºããŸãã æ¥ç¶æ
çµããã mysqlbinlog ã¹ã¬ãŒãã¬ããªã±ãŒã·ã§ã³ãšã¯ç°ãªããæ¥ç¶ãåŸ æ©ããŠåè©Šè¡ããŸãã
ãµãŒãã ãµãŒããŒã®åèµ·ååŸã«ã©ã€ãããã¯ã¢ãããç¶è¡ããã«ã¯ã次ã®ããšãè¡ãå¿ èŠããããŸãã
åèµ·å mysqlbinlog.
ãã€ããªãã°ããã¯ã¢ããã§ã¯ããåŒã³åºãå¿ èŠããããŸã mysqlbinlog å°ãªããšãXNUMXã€ã®ãªãã·ã§ã³ããããŸãã
ã» --ãªã¢ãŒããµãŒããŒããã®èªã¿åã ïŒãŸã㯠-RïŒãªãã·ã§ã³ã¯ mysqlbinlog ãµãŒããŒã«æ¥ç¶ããã«ã¯
ãã€ããªãã°ãèŠæ±ããŸãã ïŒããã¯ãæ¥ç¶ããã¹ã¬ãŒãã¬ããªã±ãŒã·ã§ã³ãµãŒããŒã«äŒŒãŠããŸã
ãã®ãã¹ã¿ãŒãµãŒããŒã«ãïŒ
ã» - ç ãªãã·ã§ã³ã¯èšã mysqlbinlog ããã¹ãåºåã§ã¯ãªããçã®ïŒãã€ããªïŒåºåãæžã蟌ã¿ãŸãã
ã«å ã㊠--ãªã¢ãŒããµãŒããŒããã®èªã¿åããä»ã®ãªãã·ã§ã³ãæå®ããã®ãäžè¬çã§ãã - ãã¹ã
ãµãŒããŒãå®è¡ãããŠããå Žæã瀺ããŸããæ¥ç¶ãªãã·ã§ã³ãæå®ããå¿ èŠãããå ŽåããããŸã
ãªã© - ãŠãŒã¶ãŒ & - ãã¹ã¯ãŒã.
ä»ã®ããã€ãã®ãªãã·ã§ã³ã¯ã - ç:
· -åæ¢-決ããŠïŒæåŸã®ãã°ãã¡ã€ã«ã®æåŸã«éããåŸããµãŒããŒãžã®æ¥ç¶ãç¶æããŸã
ãããŠãæ°ããã€ãã³ããèªã¿ç¶ããŸãã
· --stop-never-slave-server-id =idïŒãµãŒããŒID mysqlbinlog ãµãŒããŒã«å ±åããŸã
ã〠-åæ¢-決ã㊠䜿çšãããŠããã ããã©ã«ãã¯65535ã§ããããã¯ã競åãåé¿ããããã«äœ¿çšã§ããŸã
ã¹ã¬ãŒããµãŒããŒãŸãã¯å¥ã®ãµãŒããŒã®ID mysqlbinlog åŠçããã ãšåŒã°ããã»ã¯ã·ã§ã³ãåç §ããŠãã ãã
ãMYSQLBINLOGãµãŒããŒIDã®æå®ãã
· -çµæãã¡ã€ã«ïŒåŸã§èª¬æããããã«ãåºåãã¡ã€ã«åã®ãã¬ãã£ãã¯ã¹ã
ãµãŒããŒã®ãã€ããªãã°ãã¡ã€ã«ãã§ããã¯ã¢ããããã«ã¯ mysqlbinlogããã¡ã€ã«åãæå®ããå¿ èŠããããŸã
å®éã«ã¯ãµãŒããŒäžã«ååšããŸãã ååãããããªãå Žåã¯ããµãŒããŒã«æ¥ç¶ããŠäœ¿çšããŠãã ãã
SHOW BINARY LOGSã¹ããŒãã¡ã³ãã䜿çšããŠãçŸåšã®ååã確èªããŸãã ã¹ããŒãã¡ã³ãã
ãã®åºåãçæããŸãïŒ
mysql> SHOW BINARY ãã°;
+ --------------- + ----------- +
| | ãã°å | ãã¡ã€ã«ãµã€ãº |
+ --------------- + ----------- +
| | binlog.000130 | 27459 |
| | binlog.000131 | 13719 |
| | binlog.000132 | 43268 |
+ --------------- + ----------- +
ãã®æ å ±ã§ãããªãã¯äœ¿ãããšãã§ããŸã mysqlbinlog ãã€ããªãã°ãçŸåšã®ãã°ã«ããã¯ã¢ããããŸã
次ã®ããã«ãã£ã¬ã¯ããªãäœæããŸãïŒåã³ãã³ããXNUMXè¡ã§å ¥åããŸãïŒã
ã»binlog.000130ããbinlog.000132ã®éçããã¯ã¢ãããäœæããã«ã¯ã次ã®ããããã䜿çšããŸãã
mysqlbinlog --read-from-remote-server --host =ãã¹ãå - ç
binlog.000130 binlog.000131 binlog.000132
mysqlbinlog --read-from-remote-server --host =ãã¹ãå - ç
--to-last-log binlog.000130
æåã®ã³ãã³ãã¯ããã¹ãŠã®ãã¡ã€ã«åãæ瀺çã«æå®ããŸãã XNUMXçªç®ã®ååã¯
æåã®ãã¡ã€ã«ãšäœ¿çš --æåŸã®ãã°ãŸã§ æåŸãèªã¿éãã ãããã®éã
ã³ãã³ãã¯ããµãŒããŒãåã«binlog.000133ãéããå Žåã§ã mysqlbinlog
binlog.000132ã®çµããã«éãããšãæåã®ã³ãã³ãã¯ãããèªã¿åããŸããããXNUMXçªç®ã®ã³ãã³ãã¯ãããèªã¿åããŸã
ã³ãã³ããããŸãã
ã»ã©ã€ãããã¯ã¢ãããäœæããã«ã¯ mysqlbinlog binlog.000130ã§å§ãŸããæ¢åã®ãã®ãã³ããŒããŸã
ãã°ãã¡ã€ã«ãäœæãããµãŒããŒãæ°ããã€ãã³ããçæãããšãã«ãæ¥ç¶ãç¶æããŠæ°ããã€ãã³ããã³ããŒããŸãã
mysqlbinlog --read-from-remote-server --host =ãã¹ãå - ç
--stop-決ããŠbinlog.000130
-åæ¢-決ããŠãæå®ããå¿ èŠã¯ãããŸãã --æåŸã®ãã°ãŸã§ æåŸãŸã§èªã
ãã®ãªãã·ã§ã³ãæ瀺ãããŠããããããã°ãã¡ã€ã«ã
åºåãã¡ã€ã«Naming.PPãªã - ç, mysqlbinlog ããã¹ãåºåãš
-çµæãã¡ã€ã« ãªãã·ã§ã³ãæå®ãããŠããå Žåããã¹ãŠã®åºåãè¡ãããåäžãã¡ã€ã«ã®ååãæå®ããŸã
æžãããã ãš - ç, mysqlbinlog ãã°ãã¡ã€ã«ããšã«XNUMXã€ã®ãã€ããªåºåãã¡ã€ã«ãæžã蟌ã¿ãŸã
ãµãŒããŒãã転éãããŸããã ããã©ã«ãã§ã¯ã mysqlbinlog çŸåšã®ãã¡ã€ã«ãæžã蟌ã¿ãŸã
å ã®ãã°ãã¡ã€ã«ãšåãååã®ãã£ã¬ã¯ããªã åºåãã¡ã€ã«åãå€æŽããã«ã¯ã
äœ¿çš -çµæãã¡ã€ã« ãªãã·ã§ã³ã ãšçµã¿åãã㊠- ç -çµæãã¡ã€ã« ãªãã·ã§ã³å€ã¯
åºåãã¡ã€ã«åãå€æŽãããã¬ãã£ãã¯ã¹ãšããŠæ±ãããŸãã
ãµãŒããŒã«çŸåšãbinlog.000999以éãšããååã®ãã€ããªãã°ãã¡ã€ã«ããããšããŸãã ããããããã
ã€ãããŸã mysqlbinlog - ç ãã¡ã€ã«ãããã¯ã¢ããããã«ã¯ã -çµæãã¡ã€ã« ãªãã·ã§ã³ã¯åºåãã¡ã€ã«ãçæããŸã
次ã®è¡šã«ç€ºãååã 次ã®æ¹æ³ã§ãã¡ã€ã«ãç¹å®ã®ãã£ã¬ã¯ããªã«æžã蟌ãããšãã§ããŸãã
å§ã㊠-çµæãã¡ã€ã« ãã£ã¬ã¯ããªãã¹ãå«ãå€ã ã®å Žå -çµæãã¡ã€ã« å€
ãã£ã¬ã¯ããªåã®ã¿ã§æ§æãããå€ã¯ãã¹ååºåãæåã§çµããå¿ èŠããããŸã
ãã£ã©ã¯ã¿ãŒã åºåãã¡ã€ã«ãååšããå Žåã¯äžæžããããŸãã
âââââââââââââââââââââââ¬ââââââââââââââââââââââââââââ ââ
â-çµæãã¡ã€ã« ãªãã·ã§ã³â åºå File å â
âââââââââââââââââââââââŒââââââââââââââââââââââââââââ ââ€
â--result-file = x âxbinlog.000999以äžâ
âââââââââââââââââââââââŒââââââââââââââââââââââââââââ ââ€
â--result-file =/ tmp / â/ tmp / binlog.000999以äžâ
âââââââââââââââââââââââŒââââââââââââââââââââââââââââ ââ€
â--result-file = / tmp / x â/ tmp / xbinlog.000999以äžâ
âââââââââââââââââââââââŽâââââââââââââââââââââââââââ ââ
äŸïŒBackup andRestore.PPã®mysqldump + mysqlbinlog次ã®äŸã§ã¯ã
䜿çšæ¹æ³ã瀺ãç°¡åãªã·ããªãª mysqldump & mysqlbinlog äžç·ã«ããã¯ã¢ãããã
ãµãŒããŒã®ããŒã¿ãšãã€ããªãã°ãããã³ããŒã¿ã倱ãããå Žåã«ããã¯ã¢ããã䜿çšããŠãµãŒããŒã埩å ããæ¹æ³
çºçããŸãã ãã®äŸã§ã¯ããµãŒããŒããã¹ãäžã§å®è¡ãããŠããããšãåæãšããŠããŸã ãã¹ãå ãããŠãã®æå
ãã€ããªãã°ãã¡ã€ã«ã®ååã¯binlog.000999ã§ãã åã³ãã³ããXNUMXè¡ã§å ¥åããŸãã
ã mysqlbinlog ãã€ããªãã°ã®ç¶ç¶çãªããã¯ã¢ãããäœæããã«ã¯ïŒ
mysqlbinlog --read-from-remote-server --host =ãã¹ãå - ç
--stop-決ããŠbinlog.000999
ã mysqldump ãµãŒããŒã®ããŒã¿ã®ã¹ãããã·ã§ãããšããŠãã³ããã¡ã€ã«ãäœæããŸãã ã€ãããŸã
-ãã¹ãŠã®ããŒã¿ããŒã¹, -ã€ãã³ã, -ã«ãŒãã³ ãã¹ãŠã®ããŒã¿ãããã¯ã¢ãããã --master-data = 2 ããž
çŸåšã®ãã€ããªãã°åº§æšããã³ããã¡ã€ã«ã«å«ããŸãã
mysqldump --host =ãã¹ãå --all-databases --events --routines --master-data = 2> ãã³ããã¡ã€ã«
å®è¡ãã mysqldump å®æçã«ã³ãã³ããå®è¡ããŠãå¿ èŠã«å¿ããŠæ°ããã¹ãããã·ã§ãããäœæããŸãã
ããŒã¿æ倱ãçºçããå ŽåïŒããšãã°ããµãŒããŒãã¯ã©ãã·ã¥ããå ŽåïŒãææ°ã®ãã³ããã¡ã€ã«ã䜿çšããŠ
ããŒã¿ã埩å ããŸãã
mysql --host =ãã¹ãå -uã«ãŒã-p ãã³ããã¡ã€ã«
次ã«ããã€ããªãã°ããã¯ã¢ããã䜿çšããŠãåŸã«æžã蟌ãŸããã€ãã³ããåå®è¡ããŸãã
ãã³ããã¡ã€ã«ã«ãªã¹ããããŠãã座æšã ãã¡ã€ã«å ã®åº§æšã次ã®ããã«ãªã£ãŠãããšããŸãã
ãã®ïŒ
--MASTERãMASTER_LOG_FILE = 'binlog.001002'ãMASTER_LOG_POS = 27284ã«å€æŽããŸãã
ææ°ã®ããã¯ã¢ãããã°ãã¡ã€ã«ã®ååãbinlog.001004ã®å Žåã¯ããã°ã€ãã³ããåå®è¡ããŠãã ãã
ãã®ãããªïŒ
mysqlbinlog --start-position = 27284 binlog.001002 binlog.001003 binlog.001004
| mysql --host =ãã¹ãå -uã«ãŒã-p
ããã¯ã¢ãããã¡ã€ã«ïŒãã³ããã¡ã€ã«ãšãã€ããªãã°ãã¡ã€ã«ïŒãã«ã³ããŒããæ¹ãç°¡åãªå ŽåããããŸãã
埩å æäœã®å®è¡ã容æã«ããããã®ãµãŒããŒãã¹ãããŸãã¯MySQLã§èš±å¯ãããŠããªãå Žå
ãªã¢ãŒãã«ãŒãã¢ã¯ã»ã¹ã
æå®ãã 㶠MYSQLBINLOG ãµãŒã ID
ã§åŒã³åºãããå Žå --ãªã¢ãŒããµãŒããŒããã®èªã¿åã ãªãã·ã§ã³ã mysqlbinlog MySQLã«æ¥ç¶ããŸã
serverã¯ãããèªäœãèå¥ããããã®ãµãŒããŒIDãæå®ãã
ãµãŒãã ããªãã䜿çšããããšãã§ããŸã mysqlbinlog ããã€ãã®æ¹æ³ã§ãµãŒããŒã«ãã°ãã¡ã€ã«ãèŠæ±ããã«ã¯ïŒ
ã»æ瀺çã«ååãä»ãããããã¡ã€ã«ã®ã»ãããæå®ããŸãããã¡ã€ã«ããšã«ã mysqlbinlog æ¥ç¶ããŠ
Binlogãã³ãã³ãã³ããçºè¡ããŸãã ãµãŒããŒã¯ãã¡ã€ã«ãéä¿¡ããŠåæããŸãã ããã«äžã€
ãã¡ã€ã«ããšã®æ¥ç¶ã
ã»éå§ãã¡ã€ã«ãæå®ãã --æåŸã®ãã°ãŸã§: mysqlbinlog æ¥ç¶ããŠBinlogãçºè¡ããŸã
ãã¹ãŠã®ãã¡ã€ã«ã®ãã³ãã³ãã³ãã ãµãŒããŒã¯ãã¹ãŠã®ãã¡ã€ã«ãéä¿¡ããŠåæããŸãã
ã»éå§ãã¡ã€ã«ãæå®ãã -åæ¢-決ã㊠ïŒãã㯠--æåŸã®ãã°ãŸã§): mysqlbinlog
æ¥ç¶ããŠããã¹ãŠã®ãã¡ã€ã«ã«å¯ŸããŠBinlogãã³ãã³ãã³ããçºè¡ããŸãã ãµãŒããŒã¯ãã¹ãŠã®ãã¡ã€ã«ãéä¿¡ãã
ãã ããæåŸã®ãã®ãéä¿¡ããåŸã¯åæãããŸããã
--ãªã¢ãŒããµãŒããŒããã®èªã¿åã ã®ã¿ã mysqlbinlog 0ã®ãµãŒããŒIDã䜿çšããŠæ¥ç¶ããŸãã
æåŸã«èŠæ±ããããã°ãã¡ã€ã«ãéä¿¡ããåŸã«åæããããã«ãµãŒããŒã«æ瀺ããŸãã
--ãªã¢ãŒããµãŒããŒããã®èªã¿åã & -åæ¢-決ããŠ, mysqlbinlog ãŒã以å€ã䜿çšããŠæ¥ç¶ããŸã
ãµãŒããŒIDãæåŸã®ãã°ãã¡ã€ã«ãéä¿¡ããåŸããµãŒããŒã¯åæãããŸããã ãµãŒããŒ
IDã¯ããã©ã«ãã§65535ã§ãããããã¯æ¬¡ã®ããã«å€æŽã§ããŸãã --stop-never-slave-server-id.
ãããã£ãŠããã¡ã€ã«ãèŠæ±ããæåã®XNUMXã€ã®æ¹æ³ã§ã¯ããµãŒããŒã¯æ¬¡ã®çç±ã§åæãããŸãã
mysqlbinlog ãµãŒããŒIDã0ã«æå®ããŸãã次ã®å Žåã¯åæãããŸãã -åæ¢-決ã㊠äžãããã
ãªããªã mysqlbinlog ãŒã以å€ã®ãµãŒããŒIDãæå®ããŸãã
COPYRIGHT
Copyright©1997ã2014ãOracleããã³/ãŸãã¯ãã®é¢é£äŒç€Ÿã å šèäœæš©ææã
ãã®ããã¥ã¡ã³ãã¯ããªãŒãœãããŠã§ã¢ã§ãã ããªãã¯ãããåé åžããã³/ãŸãã¯ãããå€æŽããããšãã§ããŸã
Free SoftwareFoundationã«ãã£ãŠçºè¡ãããGNUGeneral PublicLicenseã®æ¡ä»¶ã
ã©ã€ã»ã³ã¹ã®ããŒãžã§ã³2ã
ãã®ããã¥ã¡ã³ãã¯ã圹ç«ã€ããšãæåŸ ããŠé åžãããŠããŸãããäœããããŸãã
ä¿èšŒ; ååæ§ãŸãã¯ç¹å®ã®é©åæ§ã®é»ç€ºã®ä¿èšŒããããŸãã
ç®çã 詳现ã«ã€ããŠã¯ãGNU General PublicLicenseãåç §ããŠãã ããã
ããã°ã©ã ãšäžç·ã«GNUGeneral PublicLicenseã®ã³ããŒãåãåã£ãŠããã¯ãã§ãã
ããã§ãªãå Žåã¯ãFree Software FoundationãInc.ã51 Franklin StreetãFifth Floorã
ãã¹ãã³ãããµãã¥ãŒã»ãã02110-1301ç±³åœãŸãã¯åç § http://www.gnu.org/licenses/.
泚æäºé
1. MySQLå éšïŒãã€ããªãã°
http://dev.mysql.com/doc/internals/en/binary-log.html
onworks.netãµãŒãã¹ã䜿çšããŠmysqlbinlogããªã³ã©ã€ã³ã§äœ¿çšãã