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

by ichirin2501

Slide 1

Slide 1 text

MySQLӡ༻  ͱΒͿΔ͢ͱʔΓʙ^2 גࣜձࣾ͸ͯͳ  id: ichirin2501 2016/8/31 Hatena Engineer Seminar #6 ʙΠϯϑϥฤʙ @ Tokyo

Slide 2

Slide 2 text

ࣗݾ঺հ • ੢઒ ݩߊ ( @ichirin2501 ) • ͸ͯͳͷWebΦϖϨʔγϣϯΤϯδχΞ(໿1೥)  ݩΞϓϦέʔγϣϯΤϯδχΞ(໿2೥൒)͔Βస৬ ڵຯ

Slide 3

Slide 3 text

΋͘͡ • ςʔϒϧͷߦ਺੍ݶΛ໨ʹͨ͠࿩ • binlogഁଛ͔Βٹ͍ΛٻΊͨ࿩

Slide 4

Slide 4 text

ʰςʔϒϧͷߦ਺੍ݶΛ໨ʹͨ͠࿩ʱ

Slide 5

Slide 5 text

(͋Δ࣌)DBΤϥʔग़ͯΔ & (ൃੜ௚ޙ͔Β)ۮવൃݟ ෺ޠ։࢝

Slide 6

Slide 6 text

The table 'relword' is full ෆཁͳσʔλ(޾͍)Λ࡟আͯ͠Ԇ໋(Ұϲ݄)

Slide 7

Slide 7 text

full?, σʔλαΠζ͔ͳʁ • DB͸MySQL-4.0.27(Senna, ಠࣗbuild) • MyISAMςʔϒϧͷ্ݶ͸4GB  (Φϓγϣϯࢦఆͳ͠Ͱ࡞੒ͨ͠৔߹) • ALTERͰαΠζ্ݶΛมߋͯ͠ରॲՄ

Slide 8

Slide 8 text

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 ..................... ݏͳ༧ײɺߦ਺ʂʁ

Slide 9

Slide 9 text

conﬁgureΦϓγϣϯʹ --with-big-tables ͕ ݟ౰ͨΒͳ͍

Slide 10

Slide 10 text

ݹͷύοέʔδΛ  ࠶Ϗϧυʁ....

Slide 11

Slide 11 text

ͳΜ͔͏·͍͔͘ͳ͍

Slide 12

Slide 12 text

͜ͷ··Ͱ͸Ұϲ݄ޙʹࢮ͵

Slide 13

Slide 13 text

΋͏όʔδϣϯ͋͛Α͏ʂʂ ͍ͭͰʹInnoDBԽ

Slide 14

Slide 14 text

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  ͭΒ͍ɺ͕Μ͹Ζ͏

Slide 15

Slide 15 text

ΞοϓάϨʔυͷ४උͩʂ

Slide 16

Slide 16 text

5.0 4.0 5.1 Slave Slave VIP Standby Standby Master Master client ߋ৽ܥ ࢀরܥ

Slide 17

Slide 17 text

5.0 4.0 5.1 Slave Slave VIP Standby Standby Master Master client ߋ৽ܥ ࢀরܥ

Slide 18

Slide 18 text

5.0 4.0 5.1 Slave Slave VIP Master Master Standby Standby client ࢀরܥ

Slide 19

Slide 19 text

5.0 4.0 5.1 Slave Slave VIP Master Master Standby Standby client ߋ৽ܥ ࢀরܥ

Slide 20

Slide 20 text

μ΢ϯλΠϜ਺ඵ ߦ਺໰୊΋(ڧҾ)ղܾ InnoDBͰฏ࿨

Slide 21

Slide 21 text

ʰbinlogഁଛ͔Βٹ͍ΛٻΊͨ࿩ʱ

Slide 22

Slide 22 text

DBߏ੒ Master Standby Master Slave VIP

Slide 23

Slide 23 text

VIP Master Standby Master Slave DiskFull !! ෺ޠ։࢝

Slide 24

Slide 24 text

Master Standby Master Slave ϨϓϦΤϥʔʂʂ x x x ᄉᅀʹMasterͷ༰ྔΛۭ͚Δ͕... x VIP

Slide 25

Slide 25 text

Master Standby Master Slave Master͚ͩਐΉ x x x ߋ৽ΫΤϦ͸ྲྀΕଓ͚ͯΔ x VIP

Slide 26

Slide 26 text

Error reading packet from server: log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master (server_errno=1236) max_allowed_packet ??? Τϥʔϩά ͓લ͸ԿΛݴ͍ͬͯΔΜͩ

Slide 27

Slide 27 text

binlog͕։͚ͳ͍ $ mysqlbinlog --no-defaults antennadb08-bin.4406 > dump.bin mysqlbinlog: Error reading ﬁle '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ഁଛ

Slide 28

Slide 28 text

ୈҰʹαʔϏε෮چ  ࣍ͷbinlogʹมߋ  => ϨϓϦ஗Ԇճආ = αʔϏεDBͷੑ࣭తʹηʔϑ

Slide 29

Slide 29 text

Master Slave Error Pos New binlog Pos MasterͷΈͷߋ৽෼ Master/Slaveͷߋ৽ঢ়ଶ

Slide 30

Slide 30 text

Master͚ͩͷߋ৽෼Λൈ͖͍ͨ Error Posͷ࣍ͷPos৘ใ͕ඞཁ mysqlbinlog͸ػೳ͠ͳ͍

Slide 31

Slide 31 text

ͦ͏ͩɺόΠφϦͰݟΑ͏ʂ

Slide 32

Slide 32 text

timestamp magic type server_id length next pos ﬂag change-masterͱ͔Ͱࢦఆ͢Δ࣍ͷpos data v3 event structure

Slide 33

Slide 33 text

549168893 = 20BBA6FD ͷ৔ॴΛݟΔͱtimestampͰ͸ͳ͍ ͜ͷपล͸ഁଛͯ͠ϑΥʔϚοτ่͕ΕͯΔ

Slide 34

Slide 34 text

पลͷ bc 56 ͷσʔλྻΛ୳͢ 20bba7d5 = 549169109 ʹ timestamp master_log_ﬁle = "antennadb08-bin.4406" master_log_pos = 549169109 ΤϥʔͰϨϓϦఀࢭͯͨ͠backupDB͕͋ͬͨͷͰ  CHANGE MASTER TO͢Ε͹ಈ͖ͦ͏

Slide 35

Slide 35 text

ਖ਼ৗʹಈ͍ͨ ॿ͔ͬͨ ࠔͬͨΒόΠφϦΛݟΑ͏

Slide 36

Slide 36 text

·ͱΊ • ݹ͍DBͷ࠷େߦ|σʔλ਺͸ݱ࣮తͳ੍ݶ஋ • binlogഁଛͯ͠΋௚઀όΠφϦΛݟΕ͹ྑ͍  ௥هܗࣜͳͷͰޙଓ͸յΕͯͳ͍Մೳੑߴ