MySQL運用とらぶるすとーり〜^2 / MySQL-Troubleshooting-Story

Transcript

1. MySQLӡ༻
 ͱΒͿΔ͢ͱʔΓʙ^2 גࣜձࣾ͸ͯͳ
 id: ichirin2501 2016/8/31 Hatena Engineer Seminar #6 ʙΠϯϑϥฤʙ @ Tokyo 
2. ࣗݾ঺հ • ੢઒ ݩߊ ( @ichirin2501 ) • ͸ͯͳͷWebΦϖϨʔγϣϯΤϯδχΞ(໿1೥)
 ݩΞϓϦέʔγϣϯΤϯδχΞ(໿2೥൒)͔Βస৬ ڵຯ 
3. ΋͘͡ • ςʔϒϧͷߦ਺੍ݶΛ໨ʹͨ͠࿩ • binlogഁଛ͔Βٹ͍ΛٻΊͨ࿩ 
4. ʰςʔϒϧͷߦ਺੍ݶΛ໨ʹͨ͠࿩ʱ 
5. (͋Δ࣌)DBΤϥʔग़ͯΔ & (ൃੜ௚ޙ͔Β)ۮવൃݟ  ෺ޠ։࢝
6. The table 'relword' is full ෆཁͳσʔλ(޾͍)Λ࡟আͯ͠Ԇ໋(Ұϲ݄) 
7. full?, σʔλαΠζ͔ͳʁ • DB͸MySQL-4.0.27(Senna, ಠࣗbuild) • MyISAMςʔϒϧͷ্ݶ͸4GB
 (Φϓγϣϯࢦఆͳ͠Ͱ࡞੒ͨ͠৔߹) • ALTERͰαΠζ্ݶΛมߋͯ͠ରॲՄ 
8. mysql> show table status like 'relword' \G *************************** 1. row *************************** Name: relword Type: MyISAM Row_format: Fixed Rows: 4294967295 Avg_row_length: 9 Data_length: 38654705655 Max_data_length: 38654705663 Index_length: 106203639808 Data_free: 0 ..................... ݏͳ༧ײɺߦ਺ʂʁ 
9.  configureΦϓγϣϯʹ --with-big-tables ͕ ݟ౰ͨΒͳ͍
10. ݹͷύοέʔδΛ
 ࠶Ϗϧυʁ.... 
11. ͳΜ͔͏·͍͔͘ͳ͍ 
12. ͜ͷ··Ͱ͸Ұϲ݄ޙʹࢮ͵ 
13. ΋͏όʔδϣϯ͋͛Α͏ʂʂ  ͍ͭͰʹInnoDBԽ
14. MySQL 4.0 -> 5.1 • (ඇޓ׵)char,varcharͷ௕͞ఆ͕ٛ
 όΠτ਺͔Βจࣈ਺΁มߋ(4.1 ~ • (ඇޓ׵)timestamp(n)ഇࢭ(4.1 ~
 "20160830133257" -> "2016-08-30 13:32:57"
 ԼهͷΑ͏ʹจࣈྻͷ··ΞϓϦଆͰѻ͍ͬͯΔͱࢮ
 ྫ: http://anond.hatelabo.jp/20160830133257
 ͭΒ͍ɺ͕Μ͹Ζ͏ 
15. ΞοϓάϨʔυͷ४උͩʂ 
16. 5.0 4.0 5.1 Slave Slave VIP Standby Standby Master Master  client ߋ৽ܥ ࢀরܥ
17. 5.0 4.0 5.1 Slave Slave VIP Standby Standby Master Master  client ߋ৽ܥ ࢀরܥ
18. 5.0 4.0 5.1 Slave Slave VIP Master Master Standby Standby  client ࢀরܥ
19. 5.0 4.0 5.1 Slave Slave VIP Master Master Standby Standby  client ߋ৽ܥ ࢀরܥ
20. μ΢ϯλΠϜ਺ඵ ߦ਺໰୊΋(ڧҾ)ղܾ InnoDBͰฏ࿨ 
21. ʰbinlogഁଛ͔Βٹ͍ΛٻΊͨ࿩ʱ 
22. DBߏ੒ Master Standby Master Slave VIP 
23. VIP Master Standby Master Slave DiskFull !!  ෺ޠ։࢝
24. Master Standby Master Slave ϨϓϦΤϥʔʂʂ x x x ᄉᅀʹMasterͷ༰ྔΛۭ͚Δ͕... x VIP 
25. Master Standby Master Slave Master͚ͩਐΉ x x x ߋ৽ΫΤϦ͸ྲྀΕଓ͚ͯΔ x VIP 
26. Error reading packet from server: log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master (server_errno=1236) max_allowed_packet ??? Τϥʔϩά ͓લ͸ԿΛݴ͍ͬͯΔΜͩ 
27. binlog͕։͚ͳ͍ $ mysqlbinlog --no-defaults antennadb08-bin.4406 > dump.bin mysqlbinlog: Error reading file 'antennadb08-bin.4406' (Errcode: 22 - Invalid argument) ERROR: Error in Log_event::read_log_event(): 'read error', data_len: 2964698656, event_type: -51 ERROR: Could not read entry at offset 549168893: Error in log format or read error. ERROR: Could not read entry at offset 549168893: Error in log format or read error. શεϨʔϒ͸549168893ͷϙδγϣϯͰఀࢭ DiskFullͰbinlogഁଛ 
28. ୈҰʹαʔϏε෮چ
 ࣍ͷbinlogʹมߋ
 => ϨϓϦ஗Ԇճආ = αʔϏεDBͷੑ࣭తʹηʔϑ 
29. Master Slave Error Pos New binlog Pos MasterͷΈͷߋ৽෼ Master/Slaveͷߋ৽ঢ়ଶ 
30. Master͚ͩͷߋ৽෼Λൈ͖͍ͨ Error Posͷ࣍ͷPos৘ใ͕ඞཁ mysqlbinlog͸ػೳ͠ͳ͍ 
31. ͦ͏ͩɺόΠφϦͰݟΑ͏ʂ 
32. timestamp magic type server_id length next pos flag change-masterͱ͔Ͱࢦఆ͢Δ࣍ͷpos data v3 event structure 
33. 549168893 = 20BBA6FD ͷ৔ॴΛݟΔͱtimestampͰ͸ͳ͍ ͜ͷपล͸ഁଛͯ͠ϑΥʔϚοτ่͕ΕͯΔ 
34. पลͷ bc 56 ͷσʔλྻΛ୳͢ 20bba7d5 = 549169109 ʹ timestamp master_log_file = "antennadb08-bin.4406" master_log_pos = 549169109 ΤϥʔͰϨϓϦఀࢭͯͨ͠backupDB͕͋ͬͨͷͰ
 CHANGE MASTER TO͢Ε͹ಈ͖ͦ͏ 
35. ਖ਼ৗʹಈ͍ͨ ॿ͔ͬͨ ࠔͬͨΒόΠφϦΛݟΑ͏ 
36. ·ͱΊ • ݹ͍DBͷ࠷େߦ|σʔλ਺͸ݱ࣮తͳ੍ݶ஋ • binlogഁଛͯ͠΋௚઀όΠφϦΛݟΕ͹ྑ͍
 ௥هܗࣜͳͷͰޙଓ͸յΕͯͳ͍Մೳੑߴ