first_mysql_performance_tuning_at_builderscon2017

by mamy1326

Slide 1

Slide 1 text

ॳΊͯͷ .Z42-αʔόʔ νϡʔχϯά σʔλϕʔε͸ා͘ͳ͍ʂ ·Έ΍ͳ͓͖ !NBNZ "VH!CVJMEFSTDPO

Slide 2

Slide 2 text

ࠓ೥ॳΊͯ NZDOGΛ৮ͬͨ๻͕

Slide 3

Slide 3 text

िؒͰ

Slide 4

Slide 4 text

αʔϏεΛ يಓʹ৐ͤͨ࿩Λ௨͠

Slide 5

Slide 5 text

ʮσʔλϕʔε͸ ා͘ͳ͍ʯΛ ͓࿩͠·͢

Slide 6

Slide 6 text

͓࿩͢Δ͜ͱ ▶︎ ࣗݾ঺հ ▶︎ ఻͍͑ͨ͜ͱ ▶︎ ෛՙঢ়گͱԾઆɺ਍அ ▶︎ my.cnf ߲໨ղઆͱ൓өɾ݁Ռ ▶︎ ϨϓϦέʔγϣϯͷ࣮ࢪ ▶︎ ӡ༻ɾҡ࣋͸େࣄ ▶︎ ͓ΘΓʹ

Slide 7

Slide 7 text

͓࿩͢Δ͜ͱ ▶︎ ࣗݾ঺հ ▶︎ ఻͍͑ͨ͜ͱ ▶︎ ෛՙঢ়گͱԾઆɺ਍அ ▶︎ my.cnf ߲໨ղઆͱ൓өɾ݁Ռ ▶︎ ϨϓϦέʔγϣϯͷ࣮ࢪ ▶︎ ӡ༻ɾҡ࣋͸େࣄ ▶︎ ͓ΘΓʹ

Slide 8

Slide 8 text

ࣗݾ঺հ ▶︎ ΦϥΫΕͳ͍ ▶︎ ϙεάΕͳ͍ ϫλγνϣοτμέ ϚΠΤεΩϡʔΤΔ

Slide 9

Slide 9 text

ࣗݾ঺հ Name ɿ·Έ΍ͳ͓͖ Twitterɿ@mamy1326 - ීஈ͸PHPॻ͍ͯ·͢ - 2017೥1݄ʹॳΊͯmy.cnfΛ৮Δ - ࠓ೥1೥ MySQLΛझຯʹͯ͠ΈΔ

Slide 10

Slide 10 text

ɹ૝ఆΦʔσΟΤϯε ɾΞϓϦέʔγϣϯΤϯδχΞ ɾ%#͸ීஈ͸πʔϧͰࢀরɾૢ࡞ ɹˠίϚϯυϥΠϯͰ%#ૢ࡞͠ͳ͍ ɾίϯιʔϧ͸͋Μ·Γ࢖Θͳ͍

Slide 11

Slide 11 text

͓࿩͢Δ͜ͱ ▶︎ ࣗݾ঺հ ▶︎ ఻͍͑ͨ͜ͱ ▶︎ ෛՙঢ়گͱԾઆɺ਍அ ▶︎ my.cnf ߲໨ղઆͱ൓өɾ݁Ռ ▶︎ ϨϓϦέʔγϣϯͷ࣮ࢪ ▶︎ ӡ༻ɾҡ࣋͸େࣄ ▶︎ ͓ΘΓʹ

Slide 12

Slide 12 text

σʔλϕʔε͸ ා͍Ͱ͔͢ʁ

Slide 13

Slide 13 text

ਖ਼௚๻͸ ා͔ͬͨͰ͢

Slide 14

Slide 14 text

ϫλγ.Z42- μΠϒίϫΠ

Slide 15

Slide 15 text

ɹ఻͍͑ͨ͜ͱ ⾣.Z42-ͱ਌͠Ή ⾣αʔόʔ͕ා͘ͳ͘ͳΔ ⾣ӡ༻Λ஌Δ ⾣ΠϯϑϥΛࢧ͑Δٕज़Λ஌Δ ⾣ΈΜͳνʔϜͰ͋Δ

Slide 16

Slide 16 text

μΠϒίϫΠ ˣ ΍ͬͯΈΑ͏ʂ

Slide 17

Slide 17 text

ɹͦΜͳΘ͚Ͱ θϩ͔Β࢝Ίͨ .Z42-νϡʔχϯά

Slide 18

Slide 18 text

͓࿩͢Δ͜ͱ ▶︎ ࣗݾ঺հ ▶︎ ఻͍͑ͨ͜ͱ ▶︎ ෛՙঢ়گͱԾઆɺ਍அ ▶︎ my.cnf ߲໨ղઆͱ൓өɾ݁Ռ ▶︎ ϨϓϦέʔγϣϯͷ࣮ࢪ ▶︎ ӡ༻ɾҡ࣋͸େࣄ ▶︎ ͓ΘΓʹ

Slide 19

Slide 19 text

ϓϩϩʔά

Slide 20

Slide 20 text

ɹͱ͋ΔαʔϏεͷӡ༻ঢ়گ िʹҰ౓ %#αʔόʔμ΢ϯ ೾͕ऩ·ΔͷΛ ͻͨ͢Β଴ͭ

Slide 21

Slide 21 text

ɹͱ͋ΔαʔϏεͷӡ༻ঢ়گ %#ͷ -"͕௓Ͷ্͕ͬͯΔΜͰ ͱΓ͋͑ͣ XFCͷ઀ଓ਺Λ੍ݶͯ͠ Ͳ͏ʹ͔͍ͯͨ͠

Slide 22

Slide 22 text

ɹӡ༻͔Βಀ͍͛ͯͨ ⾣ௐ΂Δ͜ͱΛ์ஔͯ͠ ⾣αʔϏεΛ൒୺ʹѻ͍ͬͯͨ ⾣΍ΕΔͷʹ΍Βͳ͍ ⾣ཧ༝Λ͚ͭͯઌԆ͹͠

Slide 23

Slide 23 text

ɹͱ͋ΔαʔϏεͷӡ༻ঢ়گ %#ͷ -"͕௓Ͷ্͕ͬͯΔΜͰ ͱΓ͋͑ͣ 8ͷ઀ଓ਺੍ݶͯ͠ Ͳ͏ʹ͔͍ͯͨ͠ ౖ

Slide 24

Slide 24 text

ঢ়گ̍ ϊϯνϡʔχϯά .Z42- ઐ༻αʔόʔ

Slide 25

Slide 25 text

ঢ়گ̎ ࢭ·Βͳ͍ )551ϦΫΤετ Ͱ΋ඵؒ̑

Slide 26

Slide 26 text

ঢ়گ̏ ڊେͳϩάςʔϒϧ ϑϧεΩϟϯ º̎ ֤ສϨίʔυ

Slide 27

Slide 27 text

ঢ়گ̐ ࿈ൃ͢ΔεϩʔΫΤϦ ϩά͸औ͍ͬͯͳ͍

Slide 28

Slide 28 text

ঢ়گ̑ ࢮΜͩΒऴΘΔϚελʔ εϨʔϒ͕ͳ͍

Slide 29

Slide 29 text

ঢ়گ̒ .Z42-ܥ ੓࣏తʹ όʔδϣϯΞοϓͰ͖ͳ͍

Slide 30

Slide 30 text

໨ࢦ͢ .Z42-ͷઃఆΛ ͪΌΜͱཧղͯ͠ ࠷దͳύϑΥʔϚϯεΛɻ

Slide 31

Slide 31 text

ෛՙঢ়گ

Slide 32

Slide 32 text

ɹɹ.Z42-αʔόʔͷෛՙঢ়گ ⾣-"͕ಥ͖ൈ͚͍ͯΔ ⾣ϦΫΤετ਺͸ৗࣝత ⾣ϝϞϦͷ࢖༻ྔʹมԽ͕ͳ͍ ⾣TXBQ͸͍ͯ͠ͳ͍ ⾣ετϨʔδ༰ྔ͕ṧഭ͍ͯ͠Δ

Slide 33

Slide 33 text

Ծઆ

Slide 34

Slide 34 text

ɹԾઆ ⾣εϩʔΫΤϦ ɹˠසൃ͍ͯ͠ΔͷͰ͸ʁ ⾣ϝϞϦ ɹˠਖ਼ׂ͘͠Γ౰ͯΒΕ͍ͯͳ͍ʁ ⾣֤छϩά ɹˠઃఆ͕ద੾Ͱ͸ͳ͍ʁ

Slide 35

Slide 35 text

.Z42- ਍அπʔϧΛ ࢖ͬͯΈΔ

Slide 36

Slide 36 text

ɹ.Z42-5VOFS ⾣.Z42-5VOFSͱ͸ ɹ.Z42-ͷνϡʔχϯά߲໨Λ ɹ਍அͯ͘͠ΕΔΞϓϦέʔγϣϯ ⾣πʔϧຊମ ɹIUUQTHJUIVCDPNSBDLFSIBDLFS.Z42-5VOFSQFSM

Slide 37

Slide 37 text

ɹ.Z42-5VOFS $ wget -O mysqltuner.zip https://github.com/rackerhacker/ MySQLTuner-perl/archive/master.zip $ unzip mysqltuner.zip $ cd MySQLTuner-perl-master $ chmod 755 mysqltuner.pl $ perl mysqltuner.pl --user root --pass='mamy1326'

Slide 38

Slide 38 text

Slide 39

Slide 39 text

Slide 40

Slide 40 text

Slide 41

Slide 41 text

ɹ.Z42-5VOFS -------- Recommendations ----------------------------- General recommendations: Enable the slow query log to troubleshoot bad queries Set thread_cache_size to 4 as a starting value Variables to adjust: query_cache_size (>= 8M) thread_cache_size (start at 4) innodb_file_per_table=ON innodb_buffer_pool_size (>= 12G) if possible. innodb_log_file_size should be equals to 1/4 of buffer pool size (=128M) if possible. ⾣࣮ߦ݁Ռ

Slide 42

Slide 42 text

਍அ݁Ռ ⾣&OBCMFUIFTMPXRVFSZMPHUP USPVCMFTIPPUCBERVFSJFT ɹεϩʔΫΤϦ͕ઃఆ͞Εͯͳ͍ ɹɹˠ#"%ͳΫΤϦʔΛ ɹɹɹɹɹɹτϥϒϧγϡʔτͰ͖ͳ͍ͧˑ

Slide 43

Slide 43 text

਍அ݁Ռ ⾣JOOPEC@pMF@QFS@UBCMF0/ ɾ*OOP%#ͷσʔλΛ ɹˠςʔϒϧ୯ҐͰϑΝΠϧ෼͚ͨ ɹɹɹɹɹɹɹɹɹ΄͏͕͍͍Αˑ

Slide 44

Slide 44 text

਍அ݁Ռ ⾣JOOPEC@CV⒎FS@QPPM@TJ[F ( JGQPTTJCMF σʔλͱJOEFYΛ ϝϞϦʹΩϟογϡ͓ͯ͘͠ αΠζΛઃఆ͠ͳ͍͞Αˑ

Slide 45

Slide 45 text

਍அ݁Ռ ⾣JOOPEC@MPH@pMF@TJ[FTIPVMECF FRVBMTUPPGCV⒎FSQPPMTJ[F . JGQPTTJCMF *OOP%#ͷߋ৽ϩάϑΝΠϧΛ JOOPEC@CV⒎FS@QPPM@TJ[Fͷ ෼ͷʹͯ͠Ͷˑ

Slide 46

Slide 46 text

ɹԾઆͱ਍அ݁Ռ·ͱΊ ⾣εϩʔΫΤϦ ɹˠه࿥͞Ε͍ͯͳ͔ͬͨ ⾣ϝϞϦ ɹˠద੾ʹׂΓ౰ͯΒΕ͍ͯͳ͔ͬͨ ⾣֤छϩά ɹˠߋ৽ϩάઃఆ͕ద੾Ͱ͸ͳ͔ͬͨ ⾣ςʔϒϧσʔλͷϑΝΠϧ ɹˠݸผྖҬʹͳ͍ͬͯͳ͔ͬͨ

Slide 47

Slide 47 text

͓࿩͢Δ͜ͱ ▶︎ ࣗݾ঺հ ▶︎ ఻͍͑ͨ͜ͱ ▶︎ ෛՙঢ়گͱԾઆɺ਍அ ▶︎ my.cnf ߲໨ղઆͱ൓өɾ݁Ռ ▶︎ ϨϓϦέʔγϣϯͷ࣮ࢪ ▶︎ ӡ༻ɾҡ࣋͸େࣄ ▶︎ ͓ΘΓʹ

Slide 48

Slide 48 text

NZDOG શ߲໨ͷ ௐࠪɾઃఆ

Slide 49

Slide 49 text

ɹNZDOG֬ೝ [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock symbolic-lincs=0 expire_logs_days=3 sort_buffer=4M log-bin=mysql-bin server-id=1001 ΄΅σϑΥϧτ ɾТɾA

Slide 50

Slide 50 text

ɹNZDOGઃఆํ਑ ɾڞ༗ྖҬ͔Β ɹɹɹςʔϒϧݸผྖҬ΁ ɾద੾ͳϝϞϦׂΓ౰ͯ

Slide 51

Slide 51 text

ɹNZDOGઃఆํ਑ ɾڞ༗ྖҬ͔Β ɹɹɹςʔϒϧݸผྖҬ΁ ɾద੾ͳϝϞϦׂΓ౰ͯ

Slide 52

Slide 52 text

ɹڞ༗ྖҬͱ͸ ɾ.Z42-ͷσϑΥϧτ͸ɺ͓͖͍ͬശͷத ɹͰશςʔϒϧΛऔΓѻ͍ͬͯΔ ɾେ͖ͳςʔϒϧ΋খ͞ͳςʔϒϧ΋ಉډ ɾϨίʔυͰ΋ߋ৽ʹ͕͔͔࣌ؒΔ

Slide 53

Slide 53 text

ɹڞ༗ྖҬͷ࣮ྫ EBUBCBTF UBCMFSPXTTJ[F WBSMJCNZTRMJCEBUB

Slide 54

Slide 54 text

ɹڞ༗ྖҬͷ࣮ྫ EBUBCBTF UBCMFSPXTTJ[F WBSMJCNZTRMJCEBUB ͜ͷϑΝΠϧʹ શͯͷςʔϒϧͷσʔλ͕ ه࿥͞Ε͍ͯΔ

Slide 55

Slide 55 text

ɹςʔϒϧݸผྖҬͱ͸ ɾςʔϒϧΛ̍ͭͣͭ෼͚ͯऔΓѻ͏ ɾେ͖ͳςʔϒϧ΋খ͞ͳςʔϒϧ΋ݸผ ɾߋ৽ର৅ͷςʔϒϧͷྖҬʹͷΈߋ৽͕ ɹ͔͔ΔͨΊଞʹӨڹΛ༩͑ͳ͍

Slide 56

Slide 56 text

ɹઃఆ಺༰ ͜Ε͚ͩʂ [mysqld] innodb_file_per_table=1

Slide 57

Slide 57 text

ͨͩ͠ɾɾɾ ɾNZTRM࠶ىಈ͚ͩͰ͸ ɹطଘͷςʔϒϧʹରͯ͠͸ద༻͞Εͳ͍ ɾ࠶ىಈޙʹ$3&"5& ɹ͞ΕΔςʔϒϧʹͷΈ༗ޮ EVNQϦετΞ͕ඞཁ

Slide 58

Slide 58 text

ɹ݁Ռ EBUBCBTF UBCMFSPXTTJ[F WBSMJCNZTRMJCEBUB WBSMJCNZTRMNBNZ

Slide 59

Slide 59 text

ɹ݁Ռ EBUBCBTF UBCMFSPXTTJ[F WBSMJCNZTRMJCEBUB WBSMJCNZTRMNBNZ EBUBCBTF໊Ͱ σΟϨΫτϦ͕࡞੒͞Ε ςʔϒϧ͝ͱʹ ϑΝΠϧ͕࡞ΒΕ͍ͯΔ

Slide 60

Slide 60 text

ɹ݁Ռ EBUBCBTF UBCMFSPXTTJ[F WBSMJCNZTRMJCEBUB WBSMJCNZTRMNBNZ EBUBCBTF໊Ͱ σΟϨΫτϦ͕࡞੒͞Ε ςʔϒϧ͝ͱʹ ϑΝΠϧ͕࡞ΒΕ͍ͯΔ Ώɹ͑ɹʹ

Slide 61

Slide 61 text

ɹ݁Ռ EBUBCBTF UBCMFSPXTTJ[F WBSMJCNZTRMJCEBUB WBSMJCNZTRMNBNZ SFQPSUςʔϒϧɹ͕ ߋ৽͞Εͨ৔߹ ֘౰ͷϑΝΠϧͷΈߋ৽

Slide 62

Slide 62 text

ɹNZDOGઃఆํ਑ ɾڞ༗ྖҬ͔Β ɹɹɹςʔϒϧݸผྖҬ΁ ɾద੾ͳϝϞϦׂΓ౰ͯ

Slide 63

Slide 63 text

ɹϝϞϦׂΓ౰ͯͱ͸ ɾσʔλͱJOEFY͸ ɹɹɹɹϝϞϦʹΩϟογϡ ɾద੾ͳαΠζ͸ ɹɹɹɹ෺ཧϝϞϦͷׂ

Slide 64

Slide 64 text

ɹઃఆ಺༰ ͜Ε͚ͩʂ [mysqld] # InnoDBͷσʔλͱindexΛΩϟογϡ͢ΔόοϑΝαΠζ # (ਪ঑͸෺ཧϝϞϦ8ׂ) innodb_buffer_pool_size=6G # InnoDBͷߋ৽ϩάΛه࿥͢ΔσΟεΫ্ͷϑΝΠϧαΠζ innodb_log_file_size=1G

Slide 65

Slide 65 text

ղઆ ⾣JOOPEC@CV⒎FS@QPPM@TJ[F( ɾσʔλͱJOEFYΛΩϟογϡ͓ͯ͘͠αΠζ ɾ%#αʔόʔͷ෺ཧϝϞϦ(ʹର͢Δ͓Αׂͦ ɾNZTRM࠶ىಈ࣌ʹׂΓ౰ͯΒΕ·͢ ɾॻ͖ࠐΈ࣌ʹ΋ύϑΥʔϚϯε޲্͕ݟࠐ·ΕΔ ɹˏ.Z42-ެࣜ

Slide 66

Slide 66 text

ղઆ ⾣JOOPEC@MPH@pMF@TJ[F( ɾ*OOP%#ͷߋ৽ϩάαΠζ ɾDPNNJUཤྺΛه࿥͢Δ ɹˠ.Z42-͸DPNNJU࣌ɺ͍ͬͨΜϩάʹه࿥͞ΕΔ ɾϑΝΠϧ͸γʔέϯγϟϧͳͷͰɺߋ৽͕଎͍ ɾੵΜͩDPNNJUΛॱ࣮࣍σʔλʹ൓ө͢Δ

Slide 67

Slide 67 text

ͦͷଞͷઃఆ ⾣εϩʔΫΤϦϩάؔ࿈ [mysqld] # εϩʔΫΤϦͷग़ྗઃఆ slow_query_log=ON # εϩʔΫΤϦͱ൑ఆ͢Δඵ਺ long_query_time=3 # εϩʔΫΤϦͷ৔ॴ log-slow-queries=/var/log/slow.log

Slide 68

Slide 68 text

ͦͷଞͷઃఆ ⾣ΫΤϦΩϟογϡؔ࿈ [mysqld] # ΫΤϦΩϟογϡ࠷େαΠζ query_cache_limit=16M # ΫΤϦΩϟογϡͰ࢖༻͢ΔϝϞϦαΠζ query_cache_size=512M # ΫΤϦΩϟογϡͷλΠϓ # 0:off, 1:ON SELECT SQL_NO_CACHE, 2:DEMAND SELECT SQL_CACHE query_cache_type=1⊠

Slide 69

Slide 69 text

൓өखॱ

Slide 70

Slide 70 text

൓өखॱ ⾣%#΁ͷΞΫηεΛఀࢭ ɹˠϝϯςφϯεϞʔυʹ ⾣EVNQऔಘ ɹˠεΩʔϚશମΛEVNQ͢ΔͷͰɺετϨʔδ༰ྔʹ஫ҙ ⾣ߋ৽ޙͷNZDOGʹࠩ͠ସ͑ ɹˠεςʔδϯάͰݕূࡁΈ ⾣.Z42-࠶ىಈ ɹˠTUPQTUBSUΤϥʔͳͲͰ͍ͯͳ͍͔֬ೝ ⾣%#ϦετΞ ɹˠEVNQͨ͠಺༰ΛϦετΞ ⾣ಈ࡞ݕূɾϝϯςφϯεϞʔυղআɾܦա؍࡯ ɹˠςετ߲໨Λ࣮ߦɺϩά౳Λ֬ೝ

Slide 71

Slide 71 text

NZDOGઃఆ݁Ռ ⾣ඵؒϦΫΤετʂ ⾣ϝϞϦ͕ੵ·Εͨ෼͚ͩՔಇʂ ⾣ςʔϒϧݸผͰߋ৽ܥ͕ૣ͘ʂ ⾣ΫΤϦΩϟογϡώοτʂ ⾣JOEFYݟ௚͠ͰεϩʔΫΤϦղফʂ

Slide 72

Slide 72 text

ɹNZDOGઃఆ·ͱΊ ⾣αʔϏεʹԠͯ͡ద੾ʹઃఆʂ ⾣σϑΥϧτμϝʂθολΠʂ ⾣ઃఆޙͷ਺஋͸ఆظతʹ؂ࢹʂ ɹˠνϡʔχϯά͸ܧଓ໋͕ʂ ⾣ςʔϒϧઃܭ͸ܭըతʹʂ

Slide 73

Slide 73 text

ແࣄʹɾɾɾ αʔϏε͕ ӡ༻ʹ଱͑ΒΕΔΑ͏ʹ

Slide 74

Slide 74 text

͓࿩͢Δ͜ͱ ▶︎ ࣗݾ঺հ ▶︎ ఻͍͑ͨ͜ͱ ▶︎ ෛՙঢ়گͱԾઆɺ਍அ ▶︎ my.cnf ߲໨ղઆͱ൓өɾ݁Ռ ▶︎ ϨϓϦέʔγϣϯͷ࣮ࢪ ▶︎ ӡ༻ɾҡ࣋͸େࣄ ▶︎ ͓ΘΓʹ

Slide 75

Slide 75 text

ϨϓϦέʔγϣϯ ࣮ࢪ

Slide 76

Slide 76 text

.Z42- Λ ෳ ੡ ͠ αʔόʔ ࢲ͕ ˓ΜͰ΋ ୅ΘΓ͸ ུ ͢Δ ͓࿩ ·Έ΍ͳϮ͖ !NBNZ

Slide 77

Slide 77 text

ɹϨϓϦέʔγϣϯ ࢲͷ୅ΘΓ͸ ͍͘ΒͰ΋͍ΔΘ ͱݴͬͯ

Slide 78

Slide 78 text

ɹϨϓϦέʔγϣϯ ࣮ࡍ͸ ࢮΜͩΒऴΘΔ .Z42-Λ

Slide 79

Slide 79 text

ɹϨϓϦέʔγϣϯ ෳ੡ ϨϓϦέʔγϣϯ ͢Δ

Slide 80

Slide 80 text

੢ྐྵ೥݄

Slide 81

Slide 81 text

ϑΝʔετ ΠϯύΫτ

Slide 82

Slide 82 text

NZDOG͕ σϑΥϧτ

Slide 83

Slide 83 text

੢ྐྵ೥݄

Slide 84

Slide 84 text

ηΧϯυ ΠϯύΫτ

Slide 85

Slide 85 text

՝୊ Ϛελʔ͕ࢮ͵ͱ σʔλ͕શ෦ࢮ͵

Slide 86

Slide 86 text

՝୊ Ϛελʔ͕ࢮ͵ͱ σʔλ͕શ෦ࢮ͵ ·͔͞ͷ େരൃ

Slide 87

Slide 87 text

ෳ੡ Ϛελʔ͔͠ͳ͍ ݱঢ়ʹ εϨʔϒΛ࡞Γ

Slide 88

Slide 88 text

ෳ੡ ϨϓϦέʔγϣϯ ΛՔಇ͠·͢

Slide 89

Slide 89 text

ɹϨϓϦέʔγϣϯͱ͸ ⾣σʔλϕʔεશମͷෳ੡ ϨϓϦΧ ⾣ผͷαʔόʔʹෳ੡ʢεϨʔϒʣ ⾣.Z42-ͷඪ४ػೳͰ͓खܰ ⾣΄΅࣌ؒࠩͳ͠ʹಉظ

Slide 90

Slide 90 text

ߏ੒ Ϛελʔ ʢ୯Ұʣ εϨʔϒ ʢʣ εϨʔϒ ʢOʣ ɾɾɾ

Slide 91

Slide 91 text

Ϛελʔ ⾣σʔλΛߋ৽͢Δଆ ⾣ߋ৽಺༰ΛεϨʔϒ͕औΓʹདྷΔ ɹˠεϨʔϒʹର͠ߋ৽ΠϕϯτΛ௨஌ ⾣ෳ਺ͷεϨʔϒΛ࣋ͯΔ

Slide 92

Slide 92 text

εϨʔϒ ⾣ߋ৽༻Ͱ͸ͳ͘ࢀর༻ ⾣Ϛελʔͷߋ৽಺༰Λड͚औΔ ⾣ϚελʔͷόΠφϦϩάΛऔಘ ⾣ߋ৽಺༰Λ%#ʹ൓ө ⾣ͭͷϚελʔͷΈ࣋ͯΔ

Slide 93

Slide 93 text

ϝϦοτ ⾣ࢀর 4&-&$5 ੑೳͷ޲্ ݕࡧॲཧ͕ॏ͍৔߹ εϨʔϒΛࢀরܥʹͯ͠ ෛՙ෼ࢄ

Slide 94

Slide 94 text

ϝϦοτ ⾣Մ༻ੑͷߴ͍ߏ੒ͷ࣮ݱ Ϛελʔো֐ͷ৔߹ εϨʔϒΛϚελʔʹঢ֨ μ΢ϯλΠϜ୹ॖ

Slide 95

Slide 95 text

ϝϦοτ ⾣όοΫΞοϓαʔόʔ εϨʔϒͰόοΫΞοϓ ϚελʔʹӨڹͳ͘ όοΫΞοϓऔಘ

Slide 96

Slide 96 text

ɹϨϓϦέʔγϣϯͷঢ়گ νϡʔχϯάલ͸ िʹҰ౓ Ϛελʔ͕μ΢ϯ͍ͯͨ͠

Slide 97

Slide 97 text

ɹϨϓϦέʔγϣϯͷঢ়گ Ϛελʔ࠶ىಈͷ౎౓ εϨʔϒͷ ઃఆΛ͠௚͍ͯͨ͠

Slide 98

Slide 98 text

ɹϨϓϦέʔγϣϯͷঢ়گ Ϛελʔ࠶ىಈͷ౎౓ εϨʔϒͷ ઃఆΛ͠௚͍ͯͨ͠ ͭΒ͍

Slide 99

Slide 99 text

ɹϨϓϦέʔγϣϯͷঢ়گ ͋·Γʹ΋໘౗Ͱ εϨʔϒΛ

Slide 100

Slide 100 text

ɹϨϓϦέʔγϣϯͷঢ়گ ͋·Γʹ΋໘౗Ͱ εϨʔϒΛ 4501ʂ

Slide 101

Slide 101 text

ͭ·Γ ӡ༻͔Β ಀ͍͛ͯͨ

Slide 102

Slide 102 text

Ͱ΋

Slide 103

Slide 103 text

Ͱ΋ ಀ͛ͪΌବ໨ͩɺ ಀ͛ͪΌବ໨ͩɺ ಀ͛ͪΌବ໨ͩɺ ಀ͛ͪΌବ໨ͩɺ ಀ͛ͪΌବ໨ͩɺ ಀ͛ͪΌବ໨ͩɻ

Slide 104

Slide 104 text

Ͱ΋ ಀ͛ͪΌବ໨ͩɺ ಀ͛ͪΌବ໨ͩɺ ಀ͛ͪΌବ໨ͩɺ ಀ͛ͪΌବ໨ͩɺ ಀ͛ͪΌବ໨ͩɺ ಀ͛ͪΌବ໨ͩɻ

Slide 105

Slide 105 text

Ͱ΋ ಀ͛ͪΌବ໨ͩɺ ಀ͛ͪΌବ໨ͩɺ ಀ͛ͪΌବ໨ͩɺ ಀ͛ͪΌବ໨ͩɺ ಀ͛ͪΌବ໨ͩɺ ಀ͛ͪΌବ໨ͩɻ

Slide 106

Slide 106 text

Ͱ΋ ಀ͛ͪΌବ໨ͩɺ ಀ͛ͪΌବ໨ͩɺ ಀ͛ͪΌବ໨ͩɺ ಀ͛ͪΌବ໨ͩɺ ಀ͛ͪΌବ໨ͩɺ ಀ͛ͪΌବ໨ͩɻ

Slide 107

Slide 107 text

Ͱ΋ ಀ͛ͪΌବ໨ͩɺ ಀ͛ͪΌବ໨ͩɺ ಀ͛ͪΌବ໨ͩɺ ಀ͛ͪΌବ໨ͩɺ ಀ͛ͪΌବ໨ͩɺ ಀ͛ͪΌବ໨ͩɻ

Slide 108

Slide 108 text

Ͱ΋ ಀ͛ͪΌବ໨ͩɺ ಀ͛ͪΌବ໨ͩɺ ಀ͛ͪΌବ໨ͩɺ ಀ͛ͪΌବ໨ͩɺ ಀ͛ͪΌବ໨ͩɺ ಀ͛ͪΌବ໨ͩɻ

Slide 109

Slide 109 text

๻͕ ΍Γ·͢ʂ

Slide 110

Slide 110 text

ࣄલ४උ ⾣ϨϓϦέʔγϣϯϢʔβʔ࡞੒ ⾣ϚελʔՔಇঢ়ଶͷ֬ೝ ⾣ϚελʔόΠφϦϩάͷ֬ೝ ⾣εϨʔϒͷઃఆ֬ೝ

Slide 111

Slide 111 text

ࣄલ४උ ⾣ϨϓϦέʔγϣϯϢʔβʔ࡞੒ ⾣ϚελʔՔಇঢ়ଶͷ֬ೝ ⾣ϚελʔόΠφϦϩάͷ֬ೝ ⾣εϨʔϒͷઃఆ֬ೝ

Slide 112

Slide 112 text

ɹɹϨϓϦέʔγϣϯϢʔβʔ࡞੒ ⾣ఆٛ ɹɾϚελʔଆʹ࡞੒ ɹɾεϨʔϒ͔Β઀ଓ͢Δ ɹɾϨϓϦέʔγϣϯઐ༻Ϣʔβʔ

Slide 113

Slide 113 text

ɹɹϨϓϦέʔγϣϯϢʔβʔ࡞੒ ⾣࡞੒ίϚϯυ NZTRM NBTUFS $3&"5&64&3 NZTRM NBTUFS `SFQM`!`<εϨʔϒͷϗετ໊>` NZTRM NBTUFS *%&/5*'*&%#:1"44803%``

Slide 114

Slide 114 text

ɹɹϨϓϦέʔγϣϯϢʔβʔ࡞੒ ⾣ݖݶίϚϯυ NZTRM NBTUFS (3"/53&1-*$"5*0/4-"7& NZTRM NBTUFS 0/50 NZTRM NBTUFS `SFQM`!`<εϨʔϒͷϗετ໊>`

Slide 115

Slide 115 text

ࣄલ४උ ⾣ϨϓϦέʔγϣϯϢʔβʔ࡞੒ ⾣ϚελʔՔಇঢ়ଶͷ֬ೝ ⾣ϚελʔόΠφϦϩάͷ֬ೝ ⾣εϨʔϒͷઃఆ֬ೝ

Slide 116

Slide 116 text

ϚελʔͷNZDOG DPNNJUΛ௨஌ޙ εϨʔϒ͕઀ଓͯ͠औಘ WBSMJCNZTRM NZTRMCJO999999

Slide 117

Slide 117 text

ϚελʔͷNZDOG εϨʔϒ͔Βݟͨ Ϛελʔͷ*%

Slide 118

Slide 118 text

ϚελʔͷNZDOG ଟ͗͘͢͠Δͱ ετϨʔδѹഭ

Slide 119

Slide 119 text

ࣄલ४උ ⾣ϨϓϦέʔγϣϯϢʔβʔ࡞੒ ⾣ϚελʔՔಇঢ়ଶͷ֬ೝ ⾣ϚελʔόΠφϦϩάͷ֬ೝ ⾣εϨʔϒͷઃఆ֬ೝ

Slide 120

Slide 120 text

ɹɹϚελʔόΠφϦϩάͷ֬ೝ ⾣όΠφϦϩάͱҐஔ৘ใ ɹɾόΠφϦϩάϑΝΠϧͱ͸ ɹɹɹˠϚελʔ͕DPNNJUͨ͠ཤྺ ɹɾϩʔςʔτ͞ΕΔ ɹɹɹˠݱࡏͷϑΝΠϧ໊Λ֬ೝ ɹɾͲ͜·ͰDPNNJUͰ͖ͯΔ͔ ɹɹɹˠҐஔ৘ใΛ֬ೝ

Slide 121

Slide 121 text

ɹɹϚελʔόΠφϦϩάͷ֬ೝ mysql> SHOW MASTER STATUS\G ********* 1. row ********* File: mysql-bin.000075 Position: 588201136 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.01 sec) DPNNJUΛ௨஌ޙ εϨʔϒ͕઀ଓͯ͠औಘ WBSMJCNZTRM NZTRMCJO999999

Slide 122

Slide 122 text

ɹɹϚελʔόΠφϦϩάͷ֬ೝ mysql> SHOW MASTER STATUS\G ********* 1. row ********* File: mysql-bin.000075 Position: 588201136 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.01 sec) ϚελʔͰ DPNNJU͕࣮ߦ͞Εͨ ϙδγϣϯ

Slide 123

Slide 123 text

ɹɹϚελʔόΠφϦϩάͷ֬ೝ mysql> SHOW MASTER STATUS\G ********* 1. row ********* File: mysql-bin.000075 Position: 588201136 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.01 sec) εϨʔϒઃఆͰ࢖༻͢ΔͨΊ ϝϞ͓͖ͯ͠·͠ΐ͏

Slide 124

Slide 124 text

⾣εϨʔϒΛ࡞Δࡍͷݪଇ ɹϚελʔΛϩοΫͯ͠EVNQ͠ ɹಉҰͷঢ়ଶͰϦετΞͯ͠ ɹϑΝΠϧɾϙδγϣϯΛ߹ΘͤΔ ࠩ෼ɾൈ͚࿙Εͳ͘ ϨϓϦέʔγϣϯ։࢝ ɹɹϚελʔόΠφϦϩάͷ֬ೝ

Slide 125

Slide 125 text

ࣄલ४උ ⾣ϨϓϦέʔγϣϯϢʔβʔ࡞੒ ⾣ϚελʔՔಇঢ়ଶͷ֬ೝ ⾣ϚελʔόΠφϦϩάͷ֬ೝ ⾣εϨʔϒͷઃఆ֬ೝ

Slide 126

Slide 126 text

ɹɹεϨʔϒͷઃఆ֬ೝ mysql> SHOW SLAVE STATUS\G **************** 1. row **************** Slave_IO_State: Master_Host: [Ϛελʔͷϗετ໊] Master_User: repl Master_Log_File: mysql-bin.000009 Read_Master_Log_Pos: 303456264 Slave_IO_Running: No Slave_SQL_Running: No Seconds_Behind_Master: NULL Master_Server_Id: 0 1 row in set (0.01 sec)

Slide 127

Slide 127 text

ɹɹεϨʔϒͷઃఆ֬ೝ ⾣4MBWF@*0@4UBUFɿۭจࣈ ɹεϨʔϒͷݱࡏͷεςʔλε ɹϨϓϦέʔγϣϯ్͕੾Ε͍ͯΔͨΊ ɹۭจࣈͱͳ͍ͬͯΔ ࣮ߦ͞Ε͍ͯΕ͹ 8BJUJOHGPSNBTUFSUPTFOEFWFOU

Slide 128

Slide 128 text

ɹɹεϨʔϒͷઃఆ֬ೝ ⾣.BTUFS@6TFSɿSFQM εϨʔϒ͔ΒϚελʔʹ ઀ଓ͢ΔϢʔβʔ໊ ɹ ϚελʔͰ࡞੒ͨ͠ઐ༻Ϣʔβʔ

Slide 129

Slide 129 text

ɹɹεϨʔϒͷઃఆ֬ೝ ⾣.BTUFS@-PH@'JMFɿNZTRMCJO εϨʔϒ͕Ϛελʔ͔Β औಘ͢ΔόΠφϦϩά ɹ Քಇ͍ͯ͠ͳ͔ͬͨͷͰ ζϨ͍ͯΔ

Slide 130

Slide 130 text

ɹɹεϨʔϒͷઃఆ֬ೝ ⾣[email protected]@-PH@1PTɿ εϨʔϒͷ*0εϨου͕ ࠷ޙʹಡΈऔͬͨҐஔ ɹ ͔͜͜ΒϨϓϦέʔγϣϯ࠶։͕ͩ େ͖͘ζϨ͍ͯΔ

Slide 131

Slide 131 text

ɹɹεϨʔϒͷઃఆ֬ೝ ⾣4MBWF@*0@3VOOJOHɿ/0 εϨʔϒͷ*0εϨου͕ ࣮ߦ͞Ε͍ͯͳ͍ ɹ ϚελʔͷόΠφϦϩά͕ҧ͏ͷͰ ઀ଓͰ͖͍ͯͳ͍

Slide 132

Slide 132 text

ɹɹεϨʔϒͷઃఆ֬ೝ ⾣4MBWF@42-@3VOOJOHɿ/0 εϨʔϒͷ42-εϨου͕ ࣮ߦ͞Ε͍ͯͳ͍ ɹ εϨʔϒͷ%#͕ ߋ৽͞Ε͍ͯͳ͍

Slide 133

Slide 133 text

ɹɹεϨʔϒͷઃఆ֬ೝ ⾣4FDPOET@#[email protected]ɿ/6-- Ϛελʔʹൺ΂ͯ ஗Ԇ͍ͯ͠Δඵ਺ ɹ ϨϓϦέʔγϣϯ࣮ߦ͞Ε͍ͯͳ͍

Slide 134

Slide 134 text

ɹϨϓϦέʔγϣϯखॱ ⾣ϚελʔͷEVNQΛऔಘ ⾣εϨʔϒͷNZDOGઃఆ ⾣εϨʔϒʹϦετΞ ⾣εϨʔϒઃఆมߋ ⾣ϨϓϦέʔγϣϯ࣮ߦ ⾣࣮ߦঢ়گΛ؂ࢹ

Slide 135

Slide 135 text

ɹϨϓϦέʔγϣϯखॱ ⾣ϚελʔͷEVNQΛऔಘ ⾣εϨʔϒͷNZDOGઃఆ ⾣εϨʔϒʹϦετΞ ⾣εϨʔϒઃఆมߋ ⾣ϨϓϦέʔγϣϯ࣮ߦ ⾣࣮ߦঢ়گΛ؂ࢹ

Slide 136

Slide 136 text

ɹɹϚελʔͷEVNQΛऔಘ ⾣ϚελʔΛϩοΫ mysql(master)> FLUSH TABLES WITH READ LOCK; Query OK, 0 rows affected (0.00 sec) ⾣όΠφϦϩάͱϙδγϣϯऔಘ mysql(master)> SHOW MASTER STATUS; File: mysql-bin.000075 Position: 588201136 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.01 sec)

Slide 137

Slide 137 text

Slide 138

Slide 138 text

ɹɹϚελʔͷEVNQΛऔಘ ⾣EVNQऔಘ $ mysqldump -u root -p mamy1326 > gzip mamy1326.dump.gz ɾผίϯιʔϧ͔Β࣮ࢪ ɹˠRVJU͢ΔͱϩοΫ͕֎Ε·͢ ɾετϨʔδ༰ྔΛߟྀͯ͠H[JQ ɾTDQίϚϯυ౳ͰεϨʔϒʹసૹ

Slide 139

Slide 139 text

ɹɹϚελʔͷEVNQΛऔಘ ⾣ϚελʔͷϩοΫΛղআ mysql(master)> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec)

Slide 140

Slide 140 text

ɹϨϓϦέʔγϣϯखॱ ⾣ϚελʔͷEVNQΛऔಘ ⾣εϨʔϒͷNZDOGઃఆ ⾣εϨʔϒʹϦετΞ ⾣εϨʔϒઃఆมߋ ⾣ϨϓϦέʔγϣϯ࣮ߦ ⾣࣮ߦঢ়گΛ؂ࢹ

Slide 141

Slide 141 text

ɹɹεϨʔϒͷNZDOGઃఆ ϚελʔɺεϨʔϒ શͯ߹Θͤͯ Ұҙͷ*%

Slide 142

Slide 142 text

ɹɹεϨʔϒͷNZDOGઃఆ όΠφϦϩάઃఆ Ϛελʔʹঢ֨ͨ͠৔߹ εϨʔϒʹ௨஌͢Δ

Slide 143

Slide 143 text

ɹɹεϨʔϒͷNZDOGઃఆ ༰ྔѹഭ͠ͳ͍Α͏ʹ

Slide 144

Slide 144 text

ɹɹεϨʔϒͷNZDOGઃఆ εϨʔϒͰ΋ όΠφϦϩάΛग़ྗ͠ Ϛελʔঢ֨Մೳʹ

Slide 145

Slide 145 text

ɹɹεϨʔϒͷͦͷଞͷ࡞ۀ ⾣NZDOGΛຊ൪ʹ߹ΘͤΔ ⾣NZTRM࠶ىಈ ⾣ϦετΞର৅ͷ ɹɹɹσʔλϕʔε࡟আɾ࠶࡞੒

Slide 146

Slide 146 text

ɹϨϓϦέʔγϣϯखॱ ⾣ϚελʔͷEVNQΛऔಘ ⾣εϨʔϒͷNZDOGઃఆ ⾣εϨʔϒʹϦετΞ ⾣εϨʔϒઃఆมߋ ⾣ϨϓϦέʔγϣϯ࣮ߦ ⾣࣮ߦঢ়گΛ؂ࢹ

Slide 147

Slide 147 text

ɹɹεϨʔϒʹϦετΞ ⾣ϦετΞ࣮ߦ ɾετϨʔδ༰ྔʹ஫ҙʂ ɹɹˠαΠζେ͖ΊͷόΠφϦϩά͕ ɹɹɹɹͲΜͲΜͰ͖ͯ༰ྔΛѹഭ $ zcat mamy1326.dump.gz | mysql -u root -p mamy1326

Slide 148

Slide 148 text

ɹɹεϨʔϒʹϦετΞ ⾣ϦετΞ࣮ߦ ɾετϨʔδ༰ྔʹ஫ҙʂ ɹɹˠαΠζେ͖ΊͷόΠφϦϩά͕ ɹɹɹɹͲΜͲΜͰ͖ͯ༰ྔΛѹഭ $ zcat mamy1326.dump.gz | mysql -u root -p mamy1326 163(&."45&3-0(450 bNZTRMCJOYYYYYY` ద੾ʹύʔδ͠·͠ΐ͏

Slide 149

Slide 149 text

ɹϨϓϦέʔγϣϯखॱ ⾣ϚελʔͷEVNQΛऔಘ ⾣εϨʔϒͷNZDOGઃఆ ⾣εϨʔϒʹϦετΞ ⾣εϨʔϒઃఆมߋ ⾣ϨϓϦέʔγϣϯ࣮ߦ ⾣࣮ߦঢ়گΛ؂ࢹ

Slide 150

Slide 150 text

mysql> STOP SLAVE; mysql> RESET SLAVE; mysql> CHANGE MASTER TO -> master_log_file = ’mysql-bin.000075’, -> master_log_pos = 588201136; ɹεϨʔϒઃఆมߋ ⾣ϚελʔͰϝϞͨ͠಺༰Λઃఆ εϨʔϒΛࢭΊ·͢

Slide 151

Slide 151 text

mysql> STOP SLAVE; mysql> RESET SLAVE; mysql> CHANGE MASTER TO -> master_log_file = ’mysql-bin.000075’, -> master_log_pos = 588201136; ɹεϨʔϒઃఆมߋ ⾣ϚελʔͰϝϞͨ͠಺༰Λઃఆ εϨʔϒ͕อ͍࣋ͯͨ͠ ϨϓϦέʔγϣϯҐஔΛ Ϧηοτ͠·͢

Slide 152

Slide 152 text

mysql> STOP SLAVE; mysql> RESET SLAVE; mysql> CHANGE MASTER TO -> master_log_file = ’mysql-bin.000075’, -> master_log_pos = 588201136; ɹεϨʔϒઃఆมߋ ⾣ϚελʔͰϝϞͨ͠಺༰Λઃఆ ϚελʔͷόΠφϦϩάɺ ϙδγϣϯΛઃఆ

Slide 153

Slide 153 text

ɹϨϓϦέʔγϣϯखॱ ⾣ϚελʔͷEVNQΛऔಘ ⾣εϨʔϒͷNZDOGઃఆ ⾣εϨʔϒʹϦετΞ ⾣εϨʔϒઃఆมߋ ⾣ϨϓϦέʔγϣϯ࣮ߦ ⾣࣮ߦঢ়گΛ؂ࢹ

Slide 154

Slide 154 text

ɹϨϓϦέʔγϣϯ࣮ߦ ⾣ϨϓϦέʔγϣϯͷ։࢝ mysql> START SLAVE; ઃఆͨ͠ϙδγϣϯ͔Β ಉظ͕࣮ߦ͞Ε·͢

Slide 155

Slide 155 text

ɹϨϓϦέʔγϣϯ࣮ߦ ⾣ϨϓϦέʔγϣϯͷ֬ೝ mysql> SHOW SLAVE STATUS\G ***************** 1. row ***************** Slave_IO_State: Waiting for master to send event Master_Log_File: mysql-bin.000075 Read_Master_Log_Pos: 878873047 Slave_IO_Running: Yes Slave_SQL_Running: Yes Exec_Master_Log_Pos: 605504154 Seconds_Behind_Master: 244615 Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Master_Server_Id: 1001

Slide 156

Slide 156 text

ɹϨϓϦέʔγϣϯͷ֬ೝ ⾣4MBWF@*0@4UBUFɿ 8BJUJOHGPSNBTUFSUPTFOEFWFOU εϨʔϒͷݱࡏͷεςʔλε ϨϓϦέʔγϣϯ͕։࢝͞Ε Ϛελʔ͔ΒͷΠϕϯτ଴ͪ

Slide 157

Slide 157 text

ɹϨϓϦέʔγϣϯͷ֬ೝ ⾣.BTUFS@-PH@'JMFɿ NZTRMCJO ϚελʔͷόΠφϦϩάϑΝΠϧ ݱࡏϚελͰߋ৽͞Ε͍ͯΔ ϑΝΠϧ໊ʹͳ͍ͬͯΔ

Slide 158

Slide 158 text

ɹϨϓϦέʔγϣϯͷ֬ೝ ⾣[email protected]@-PH@1PTɿ ϚελʔͷόΠφϦϩά͔Β *0εϨου͕ಡΈऔͬͨҐஔ ˞42-࣮ߦ͞Εͨͱ͸ݶΒͳ͍

Slide 159

Slide 159 text

ɹϨϓϦέʔγϣϯͷ֬ೝ ⾣&[email protected]@-PH@1PTɿ *0εϨου͕ಡΈࠐΜͩϩά͔Β 42-εϨου͕ ࣮ߦͨ͠ΫΤϦͷҐஔ

Slide 160

Slide 160 text

ɹϨϓϦέʔγϣϯͷ֬ೝ ⾣4FDPOET@#[email protected]ɿ εϨʔϒ͕Ϛελʔʹൺ΂ ஗Ԇ͍ͯ͠Δඵ਺

Slide 161

Slide 161 text

ɹϨϓϦέʔγϣϯͷ֬ೝ ⾣4MBWF@*0@3VOOJOHɿ:&4 ϚελʔͷόΠφϦϩάΛ औಘͰ͖͍ͯΔ

Slide 162

Slide 162 text

ɹϨϓϦέʔγϣϯͷ֬ೝ ⾣4MBWF@42-@3VOOJOHɿ:&4 औಘͨ͠όΠφϦϩάͷ 42-࣮ߦͰ͖͍ͯΔ

Slide 163

Slide 163 text

ɹϨϓϦέʔγϣϯखॱ ⾣ϚελʔͷEVNQΛऔಘ ⾣εϨʔϒͷNZDOGઃఆ ⾣εϨʔϒʹϦετΞ ⾣εϨʔϒઃఆมߋ ⾣ϨϓϦέʔγϣϯ࣮ߦ ⾣࣮ߦঢ়گΛ؂ࢹ

Slide 164

Slide 164 text

ɹ࣮ߦঢ়گΛ؂ࢹ mysql> SHOW SLAVE STATUS\G **************** 1. row ************** Read_Master_Log_Pos: 878873047 Exec_Master_Log_Pos: 605504154 Seconds_Behind_Master: 244615 ⾣࣮ߦ௚ޙ mysql> SHOW SLAVE STATUS\G **************** 1. row ************** Read_Master_Log_Pos: 879066686 Exec_Master_Log_Pos: 879066686 Seconds_Behind_Master: 0 ⾣ಉظ׬ྃ

Slide 165

Slide 165 text

ॠؒɺ৺ɺॏͶͯ

Slide 166

Slide 166 text

γϯΫϩ཰ ˋ

Slide 167

Slide 167 text

݁Ռ ࢲ Ϛελʔ ͕ མͪͯ΋

Slide 168

Slide 168 text

݁Ռ ୅ΘΓ ϨϓϦΧ ͕͋Δ΋ͷʂ

Slide 169

Slide 169 text

ɹϨϓϦέʔγϣϯ·ͱΊ ⾣ෳ੡࡞੒ɾӡ༻׬ྃʂ ⾣ࢀরܥͷαʔόʔ४උ׬ྃʂ ⾣όοΫΞοϓαʔόʔ࡞੒׬ྃ

Slide 170

Slide 170 text

σʔλ͸ ෳ੡໋͕

Slide 171

Slide 171 text

Մ༻ੑΛߴΊ γεςϜΛ αʔϏεαʔϏε͎ ͍͖ͯ͠·͠ΐ͏ʂ

Slide 172

Slide 172 text

ৼΓฦΓ

Slide 173

Slide 173 text

શମͷৼΓฦΓ ⾣NZDOGઃఆ ຊདྷͷύϑΥʔϚϯεΛಘͨ ⾣ϨϓϦέʔγϣϯ࣮ࢪ ৑௕Խͷ४උ͕Ͱ͖ͨ

Slide 174

Slide 174 text

՝୊

Slide 175

Slide 175 text

՝୊ ⾣ܧଓ؂ࢹͷ࢓૊Έ ֤छγΣϧˠ.BDLFSFMͱ͔ ⾣ࢀরܥΛεϨʔϒʹ εϨʔϒ࡞੒ˠϓϩάϥϜվम ⾣ෆཁϨίʔυͷ࡟আ ϩάܥ ઃܭˠୀආˠ࡟আ

Slide 176

Slide 176 text

͓࿩͢Δ͜ͱ ▶︎ ࣗݾ঺հ ▶︎ ఻͍͑ͨ͜ͱ ▶︎ ෛՙঢ়گͱԾઆɺ਍அ ▶︎ my.cnf ߲໨ղઆͱ൓өɾ݁Ռ ▶︎ ϨϓϦέʔγϣϯͷ࣮ࢪ ▶︎ ӡ༻ɾҡ࣋͸େࣄ ▶︎ ͓ΘΓʹ

Slide 177

Slide 177 text

ӡ༻ɾҡ࣋͸ େࣄͩΑ

Slide 178

Slide 178 text

ɹӡ༻ɾҡ࣋͸େࣄͩΑ ⾣ා͍͔Βා͘ͳ͍΁ ⾣਌͠Ή·Ͱͷϓϩηε ⾣ӡ༻ҡ࣋ͷຊ౰ͷҙຯ ⾣ҡ࣋ͬͯେࣄ ⾣΍ͬͯΈΑ͏ʂ

Slide 179

Slide 179 text

ɹӡ༻ɾҡ࣋͸େࣄͩΑ ⾣ා͍͔Βා͘ͳ͍΁ ⾣਌͠Ή·Ͱͷϓϩηε ⾣ӡ༻ҡ࣋ͷຊ౰ͷҙຯ ⾣ҡ࣋ͬͯେࣄ ⾣΍ͬͯΈΑ͏ʂ

Slide 180

Slide 180 text

ɹ.Z42-ා͍ʜʜʜ ⾣ࠓ·Ͱ͸ʜ ਖ਼௚ܟԕͯ͠·ͨ͠ ࠇ͍ը໘ා͔ͬͨͰ͢

Slide 181

Slide 181 text

ɹ.Z42-ා͍ʜʜʁ ⾣ॳΊͯʜ ઃఆΛ৮Γ·ͨ͠ NZDOG͕ා͘ͳ͘ͳͬͨ

Slide 182

Slide 182 text

ɹ.Z42-ා͍ʜʁʁ ⾣ઃఆޙʜ ಓ۩ͷಈ͖Λ஌Γ ઃܭͷ෯͕޿͕Γ τϥϒϧͰ΋৺ʹ༨༟͕

Slide 183

Slide 183 text

͍ͭͷ·ʹ͔ ා͘ͳ͘ͳͬͨ

Slide 184

Slide 184 text

ɹӡ༻ɾҡ࣋͸େࣄͩΑ ⾣ා͍͔Βා͘ͳ͍΁ ⾣਌͠Ή·Ͱͷϓϩηε ⾣ӡ༻ҡ࣋ͷຊ౰ͷҙຯ ⾣ҡ࣋ͬͯେࣄ ⾣΍ͬͯΈΑ͏ʂ

Slide 185

Slide 185 text

ɹ਌͠Ή·Ͱͷϓϩηε ⾣·ͣઃఆΛ஌Δ ⾣൓өखॱΛߟ࣮͑ߦ ⾣࣮ࡍͷޮՌΛଌఆ ⾣ઃܭɾ࣮૷ͷߟྀ͕ਂ·Δ ⾣ଞͷίϚϯυૢ࡞ʹ׳Ε͍ͯ͘

Slide 186

Slide 186 text

਌͠Ήͱ

Slide 187

Slide 187 text

ӡ༻ҡ࣋ͷ ຊ౰ͷҙຯΛ஌Δ

Slide 188

Slide 188 text

ɹӡ༻ɾҡ࣋͸େࣄͩΑ ⾣ා͍͔Βා͘ͳ͍΁ ⾣਌͠Ή·Ͱͷϓϩηε ⾣ӡ༻ҡ࣋ͷຊ౰ͷҙຯ ⾣ҡ࣋ͬͯେࣄ ⾣΍ͬͯΈΑ͏ʂ

Slide 189

Slide 189 text

ɹӡ༻ҡ࣋ͷຊ౰ͷҙຯ ⾣࣮͸ʜ ΞϓϦέʔγϣϯΑΓ Πϯϑϥʢσʔλʣͷํ͕ ण໋͕௕͍

Slide 190

Slide 190 text

ɹӡ༻ҡ࣋ͷຊ౰ͷҙຯ ⾣ण໋͕௕͍ͱʜ Πϯϑϥͷӡ༻ҡ࣋ʹ͸ Ұఆͷίετ͕ ͔͔Γଓ͚Δ

Slide 191

Slide 191 text

͔͠͠ӡ༻͸

Slide 192

Slide 192 text

ಓΛ੾Γ୓͘ ਓ͕͍Δ͔Βͦ͜ ͋Δͱݴ͑Δ

Slide 193

Slide 193 text

ٯʹɾɾɾ

Slide 194

Slide 194 text

ӡ༻อक͕ ͋Δ͔Βͦ͜

Slide 195

Slide 195 text

ಓ͸ ੾Γ୓͔ΕΔ

Slide 196

Slide 196 text

૬ޓʹײँ ͜Ε͕νʔϜ

Slide 197

Slide 197 text

ɹҡ࣋͢ΔਓͷଘࡏΛ஌Δ ͋ͳ͕ͨ࡞ͬͨ ΞϓϦέʔγϣϯ ඞͣ୭͔͕ ҡ࣋ͯ͠·͢

Slide 198

Slide 198 text

Ͱ͸ ҡ࣋ͱ͸Կ͔

Slide 199

Slide 199 text

Ϗοάόϯ

Slide 200

Slide 200 text

খӉ஦೩ম

Slide 201

Slide 201 text

Ի଎͔Βޫ଎

Slide 202

Slide 202 text

ʜ ʁʁʁʁʁ

Slide 203

Slide 203 text

ޫ଎ͷ·· ਐΜͰ͍͘

Slide 204

Slide 204 text

͜Ε͕ ҡ࣋ɾӡ༻อक

Slide 205

Slide 205 text

ɹӡ༻ɾҡ࣋͸େࣄͩΑ ⾣ා͍͔Βා͘ͳ͍΁ ⾣਌͠Ή·Ͱͷϓϩηε ⾣ӡ༻ҡ࣋ͷຊ౰ͷҙຯ ⾣ҡ࣋ͬͯେࣄ ⾣΍ͬͯΈΑ͏ʂ

Slide 206

Slide 206 text

ɹҡ࣋ͬͯେࣄʂ ⾣͍͢͝଎͞ͰಓΛ୓͚͹ ⾣ಓΛ੔උ͍ͯ͘͠ਓ͕͍Δ ⾣੒௕ͱҡ࣋Λࢧ͑Δਓ͕͍Δ ⾣ͦ͏ͯ͠ੈք͕Ͱ͖͕͋Δ

Slide 207

Slide 207 text

ͭ·Γ

Slide 208

Slide 208 text

ΈΜͳ νʔϜͰ͢ʂ

Slide 209

Slide 209 text

ͦͯ͠ɾɾɾ

Slide 210

Slide 210 text

ɹӡ༻ɾҡ࣋͸େࣄͩΑ ⾣ා͍͔Βා͘ͳ͍΁ ⾣਌͠Ή·Ͱͷϓϩηε ⾣ӡ༻ҡ࣋ͷຊ౰ͷҙຯ ⾣ҡ࣋ͬͯେࣄ ⾣΍ͬͯΈΑ͏ʂ

Slide 211

Slide 211 text

ɹσʔλϕʔε͸ා͘ͳ͍ʂ ⾣ಈ͖Λ஌Δ ⾣࢖͍ํɾઃఆΛ஌Δ ⾣σʔλϕʔεΛ஌Δ

Slide 212

Slide 212 text

ɹσʔλϕʔε͸ා͘ͳ͍ʂ ⾣ಈ͖Λ஌Δ ⾣࢖͍ํɾઃఆΛ஌Δ ⾣σʔλϕʔεΛ஌Δ ා͘ͳ͍ʂ

Slide 213

Slide 213 text

΍Ζ͏ͱ ࢥ͍͚ͬͯͨͲʜ

Slide 214

Slide 214 text

ͦΜͳਓʹ ʮ΍ͬͯΈΑ͏ʯΛ ಧ͚͍ͨ

Slide 215

Slide 215 text

͓࿩͢Δ͜ͱ ▶︎ ࣗݾ঺հ ▶︎ ఻͍͑ͨ͜ͱ ▶︎ ෛՙঢ়گͱԾઆɺ਍அ ▶︎ my.cnf ߲໨ղઆͱ൓өɾ݁Ռ ▶︎ ϨϓϦέʔγϣϯͷ࣮ࢪ ▶︎ ӡ༻ɾҡ࣋͸େࣄ ▶︎ ͓ΘΓʹ

Slide 216

Slide 216 text

ΞϓϦΤϯδχΞ͔ͩΒͬͯ ΠϯϑϥΛ ଞਓ೚ͤʹ͠ͳ͍

Slide 217

Slide 217 text

ΞϓϦΤϯδχΞ͔ͩΒͬͯ ϑϩϯτΤϯυΛ ଞਓ೚ͤʹ͠ͳ͍

Slide 218

Slide 218 text

ੈքΛ޿͛Δ͜ͱ͸ ΤϯδχΞਓੜͷ޿͕Γ Λҙຯ͢Δ

Slide 219

Slide 219 text

஌Βͳ͍ΑΓ΋ ஌Δ΄͏ָ͕͍͠ʂ

Slide 220

Slide 220 text

ੈքΛ޿͛ͯ ͜Ε͔Β΋ ָ͍͠ΤϯδχΞϥΠϑΛʂ

Slide 221

Slide 221 text

͋Γ͕ͱ͏͍͟͝·ͨ͠ʂ

Slide 222

Slide 222 text

͕࣌ؒ ༨ͬͨΒ ࿩͢Α

Slide 223

Slide 223 text

.Z42- ઃఆྫ

Slide 224

Slide 224 text

ɹઃఆྫ ⾣$IBSBTFU $PMMBUJPO ⾣TMPX@MPHؔ࿈ ⾣҉໧తͳଐੑ෇༩͸ඇਪ঑ ⾣JOOPECͷ಺෦σʔλอ࣋ྖҬ

Slide 225

Slide 225 text

ɹઃఆྫ ⾣$IBSBTFU $PMMBUJPO ⾣TMPX@MPHؔ࿈ ⾣҉໧తͳଐੑ෇༩͸ඇਪ঑ ⾣JOOPECͷ಺෦σʔλอ࣋ྖҬ

Slide 226

Slide 226 text

ɹ$IBSBTFU $PMMBUJPO [mysqld] # mysqld ϩάग़ྗઃఆ log-error=/var/log/mysql-error.log # ৽ن࡞੒͢ΔσʔλϕʔεͷCharaset character-set-server = utf8mb4 # ৽ن࡞੒͢ΔσʔλϕʔεͷCharaset collation_server = utf8mb4_bin [client] # ΫϥΠΞϯτ಺ͷจࣈॲཧͱαʔόʔͱͷ઀ଓͷCharaset loose-default-character-set = utf8mb4

Slide 227

Slide 227 text

Slide 228

Slide 228 text

ɹ$IBSBTFU $PMMBUJPO [mysqld] # mysqld ϩάग़ྗઃఆ log-error=/var/log/mysql-error.log # ৽ن࡞੒͢ΔσʔλϕʔεͷCharaset character-set-server = utf8mb4 # ৽ن࡞੒͢ΔσʔλϕʔεͷCollation collation_server = utf8mb4_bin [client] # ΫϥΠΞϯτ಺ͷจࣈॲཧͱαʔόʔͱͷ઀ଓͷCharaset loose-default-character-set = utf8mb4 ͜͜Ͱઃఆ͍ͯ͠Ε͹ %#ɺςʔϒϧɺΧϥϜͰ ࢦఆ͢Δඞཁ͕ͳ͍

Slide 229

Slide 229 text

Slide 230

Slide 230 text

Slide 231

Slide 231 text

Slide 232

Slide 232 text

Slide 233

Slide 233 text

ɹઃఆྫ ⾣$IBSBTFU $PMMBUJPO ⾣TMPX@MPHؔ࿈ ⾣҉໧తͳଐੑ෇༩͸ඇਪ঑ ⾣JOOPECͷ಺෦σʔλอ࣋ྖҬ

Slide 234

Slide 234 text

ɹTMPX@MPHؔ࿈ [mysqld] # slow log ग़ྗઃఆ slow_query_log = ON # slow.log ͷ৔ॴ slow_query_log_file = /var/log/mysql/slow.log # ग़ྗର৅ඵ਺ long_query_time = 3 # no index ͷΫΤϦ΋ग़͔͢Ͳ͏͔ log_queries_not_using_indexes = 1 # no index Λϩάʹग़ྗ͢Δִؒʢ෼ʣ log_throttle_queries_not_using_indexes = 1 # εϩʔΫΤϦͰɺSELECTߦ਺͕ࢦఆ਺ҎԼͷΫΤϦ΋ϩάʹग़ྗ min_examined_row_limit = 0

Slide 235

Slide 235 text

Slide 236

Slide 236 text

Slide 237

Slide 237 text

Slide 238

Slide 238 text

ɹઃఆྫ ⾣$IBSBTFU $PMMBUJPO ⾣TMPX@MPHؔ࿈ ⾣҉໧తͳଐੑ෇༩͸ඇਪ঑ ⾣JOOPECͷ಺෦σʔλอ࣋ྖҬ

Slide 239

Slide 239 text

ɹ҉໧తͳଐੑ෇༩͸ඇਪ঑ ⾣UJNFTUBNQܕ [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use —explicit_defaults_for_timestamp server option (see documentation for more details).

Slide 240

Slide 240 text

Slide 241

Slide 241 text

ɹ҉໧తͳଐੑ෇༩͸ඇਪ঑ ⾣ྫɿUJNFTUBNQܕ [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use —explicit_defaults_for_timestamp server option (see documentation for more details). ͍ΘΏΔθϩλΠϜελϯϓ ‘0000-00-00 00:00:00’ ҉໧తͳଐੑ෇༩Ͱ ҙਤ͠ͳ͍஋ʹͳΔ

Slide 242

Slide 242 text

ɹ҉໧తͳଐੑ෇༩͸ඇਪ঑ ⾣ݟͨ͜ͱ͋Γ·ͤΜ͔ʁ mysql> SELECT cuntomer_name -> FROM customers -> WHERE create_at != ‘0000-00-00 00:00:00’ -> AND create_at < NOW();

Slide 243

Slide 243 text

ɹ҉໧తͳଐੑ෇༩͸ඇਪ঑ ⾣ݟͨ͜ͱ͋Γ·ͤΜ͔ʁ ҙਤ͠ͳ͍ͱ ΍Ήͳ͘ରԠ͕૿͑Δ mysql> SELECT cuntomer_name -> FROM customers -> WHERE create_at != ‘0000-00-00 00:00:00’ -> AND create_at < NOW();

Slide 244

Slide 244 text

ɹ҉໧తͳଐੑ෇༩͸ඇਪ঑ ⾣΍Ήͳ͘ରԠ ɹ΍Ήͳ͘ϓϩάϥϜଆͰରԠ ɹ΍Ήͳ͘8)&3&۟ͰରԠ

Slide 245

Slide 245 text

ɹ҉໧తͳଐੑ෇༩͸ඇਪ঑ ⾣΍Ήͳ͘ରԠ ɹ΍Ήͳ͘ϓϩάϥϜଆͰରԠ ɹ΍Ήͳ͘8)&3&۟ͰରԠ σϑΥϧτΛআ֎ͯ͠ ద੾ͳσʔλอ࣋ͷઃܭΛ

Slide 246

Slide 246 text

ɹ҉໧తͳଐੑ෇༩͸ඇਪ঑ [mysqld] # timestampܕͷ҉໧తͳσϑΥϧτ஋ explicit_defaults_for_timestamp = ON ⾣ઃఆ ⾣֬ೝ mysql> show variables like 'explicit_defaults_for_timestamp'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | explicit_defaults_for_timestamp | ON | +---------------------------------+-------+ 1 row in set (0.00 sec)

Slide 247

Slide 247 text

ɹઃఆྫ ⾣$IBSBTFU $PMMBUJPO ⾣TMPX@MPHؔ࿈ ⾣҉໧తͳଐੑ෇༩͸ඇਪ঑ ⾣JOOPECͷ಺෦σʔλอ࣋ྖҬ

Slide 248

Slide 248 text

ɹJOOPECؔ࿈ [ERROR] unknown variable 'innodb_additional_mem_pool_size=20M' ⾣*OOP%#ͷ಺෦σʔλอ࣋ྖҬ ɹˠͰഇࢭ͞Ε·ͨ͠ *OOP%#ಠࣗͷϝϞϦΞϩέʔλ ͔Β 04ͷϝϞϦΞϩέʔλ΁

Slide 249

Slide 249 text

ɹJOOPECؔ࿈ [ERROR] unknown variable 'innodb_additional_mem_pool_size=20M' ⾣*OOP%#ͷ಺෦σʔλอ࣋ྖҬ ɹˠͰഇࢭ͞Ε·ͨ͠ *OOP%#ಠࣗͷϝϞϦΞϩέʔλ ͔Β 04ͷϝϞϦΞϩέʔλ΁

Slide 250

Slide 250 text

ɹJOOPECؔ࿈ ⾣*OOP%#ͷ಺෦σʔλอ࣋ྖҬ ɹˠͰഇࢭ͞Ε·ͨ͠ *OOP%#ಠࣗͷϝϞϦΞϩέʔλ ͔Β 04ͷϝϞϦΞϩέʔλ΁ [ERROR] unknown variable 'innodb_additional_mem_pool_size=20M'