first_mysql_performance_tuning_at_builderscon2017

builderscon 2017 8/4(Fri) 11:00 から登壇した際のスライドです。

Transcript

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

   View Slide

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

   View Slide

3. िؒͰ
   

   View Slide

4. αʔϏεΛ
   يಓʹ৐ͤͨ࿩Λ௨͠
   

   View Slide

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

   View Slide

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

   View Slide

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

   View Slide

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

   View Slide

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

   View Slide

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

    View Slide

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

    View Slide

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

    View Slide

13. ਖ਼௚๻͸
    ා͔ͬͨͰ͢
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

19. ϓϩϩʔά
    

    View Slide

20. ɹͱ͋ΔαʔϏεͷӡ༻ঢ়گ
    िʹҰ౓
    %#αʔόʔμ΢ϯ
    ೾͕ऩ·ΔͷΛ
    ͻͨ͢Β଴ͭ
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

25. ঢ়گ̎
    ࢭ·Βͳ͍
    )551ϦΫΤετ
    Ͱ΋ඵؒ̑
    
    

    View Slide

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

    View Slide

27. ঢ়گ̐
    ࿈ൃ͢ΔεϩʔΫΤϦ
    ϩά͸औ͍ͬͯͳ͍
    
    

    View Slide

28. ঢ়گ̑
    ࢮΜͩΒऴΘΔϚελʔ
    εϨʔϒ͕ͳ͍
    
    

    View Slide

29. ঢ়گ̒
    .Z42-ܥ
    ੓࣏తʹ
    όʔδϣϯΞοϓͰ͖ͳ͍
    
    

    View Slide

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

    View Slide

31. ෛՙঢ়گ
    

    View Slide

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

    View Slide

33. Ծઆ
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

37. ɹ.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'
    

    View Slide

38. ɹ.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'
    HJUIVC͔Β
    [JQΛXHFU
    

    View Slide

39. ɹ.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' ղౚͱ
    ద੾ͳ࣮ߦݖݶ
    

    View Slide

40. ɹ.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'
    Ϣʔβʔɺ
    18ࢦఆ͠ɺ࣮ߦ
    

    View Slide

41. ɹ.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.
    ⾣࣮ߦ݁Ռ
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

48. NZDOG
    શ߲໨ͷ
    ௐࠪɾઃఆ
    

    View Slide

49. ɹ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
    
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

58. ɹ݁Ռ
    EBUBCBTF
    UBCMFSPXTTJ[F
    WBSMJCNZTRMJCEBUB
    WBSMJCNZTRMNBNZ
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

68. ͦͷଞͷઃఆ
    ⾣ΫΤϦΩϟογϡؔ࿈
    [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⊠
    

    View Slide

69. ൓өखॱ
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

75. ϨϓϦέʔγϣϯ
    ࣮ࢪ
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

80. ੢ྐྵ೥݄
    

    View Slide

81. ϑΝʔετ
    ΠϯύΫτ
    

    View Slide

82. NZDOG͕
    σϑΥϧτ
    

    View Slide

83. ੢ྐྵ೥݄
    

    View Slide

84. ηΧϯυ
    ΠϯύΫτ
    

    View Slide

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

    View Slide

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

    View Slide

87. ෳ੡
    Ϛελʔ͔͠ͳ͍
    ݱঢ়ʹ
    εϨʔϒΛ࡞Γ
    

    View Slide

88. ෳ੡
    ϨϓϦέʔγϣϯ
    ΛՔಇ͠·͢
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

99. ɹϨϓϦέʔγϣϯͷঢ়گ
    ͋·Γʹ΋໘౗Ͱ
    εϨʔϒΛ
    

    View Slide

100. ɹϨϓϦέʔγϣϯͷঢ়گ
     ͋·Γʹ΋໘౗Ͱ
     εϨʔϒΛ
     4501ʂ
     

     View Slide

101. ͭ·Γ
     ӡ༻͔Β
     ಀ͍͛ͯͨ
     

     View Slide

102. Ͱ΋
     

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

109. ๻͕
     ΍Γ·͢ʂ
     

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

121. ɹɹϚελʔόΠφϦϩάͷ֬ೝ
     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
     
     

     View Slide

122. ɹɹϚελʔόΠφϦϩάͷ֬ೝ
     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͕࣮ߦ͞Εͨ
     ϙδγϣϯ
     

     View Slide

123. ɹɹϚελʔόΠφϦϩάͷ֬ೝ
     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)
     εϨʔϒઃఆͰ࢖༻͢ΔͨΊ
     ϝϞ͓͖ͯ͠·͠ΐ͏
     

     View Slide

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

     View Slide

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

     View Slide

126. ɹɹεϨʔϒͷઃఆ֬ೝ
     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)
     

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

136. ɹɹϚελʔͷ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)
     

     View Slide

137. ɹɹϚελʔͷ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)
     εϨʔϒઃఆͰ࢖༻͢ΔͨΊ
     ϝϞ͓͖ͯ͠·͠ΐ͏
     

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

144. ɹɹεϨʔϒͷNZDOGઃఆ
     εϨʔϒͰ΋
     όΠφϦϩάΛग़ྗ͠
     Ϛελʔঢ֨Մೳʹ
     

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

155. ɹϨϓϦέʔγϣϯ࣮ߦ
     ⾣ϨϓϦέʔγϣϯͷ֬ೝ
     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
     

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

160. ɹϨϓϦέʔγϣϯͷ֬ೝ
     ⾣4FDPOET@#FIJOE@.BTUFSɿ
     
     εϨʔϒ͕Ϛελʔʹൺ΂
     ஗Ԇ͍ͯ͠Δඵ਺
     

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

164. ɹ࣮ߦঢ়گΛ؂ࢹ
     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
     ⾣ಉظ׬ྃ
     

     View Slide

165. ॠؒɺ৺ɺॏͶͯ
     

     View Slide

166. γϯΫϩ཰
     ˋ
     

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

170. σʔλ͸
     ෳ੡໋͕
     

     View Slide

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

     View Slide

172. ৼΓฦΓ
     

     View Slide

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

     View Slide

174. ՝୊
     

     View Slide

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

     View Slide

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

     View Slide

177. ӡ༻ɾҡ࣋͸
     େࣄͩΑ
     

     View Slide

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

     View Slide

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

     View Slide

180. ɹ.Z42-ා͍ʜʜʜ
     ⾣ࠓ·Ͱ͸ʜ
     ਖ਼௚ܟԕͯ͠·ͨ͠
     ࠇ͍ը໘ා͔ͬͨͰ͢
     

     View Slide

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

     View Slide

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

     View Slide

183. ͍ͭͷ·ʹ͔
     ා͘ͳ͘ͳͬͨ
     

     View Slide

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

     View Slide

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

     View Slide

186. ਌͠Ήͱ
     

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

191. ͔͠͠ӡ༻͸
     

     View Slide

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

     View Slide

193. ٯʹɾɾɾ
     

     View Slide

194. ӡ༻อक͕
     ͋Δ͔Βͦ͜
     

     View Slide

195. ಓ͸
     ੾Γ୓͔ΕΔ
     

     View Slide

196. ૬ޓʹײँ
     ͜Ε͕νʔϜ
     

     View Slide

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

     View Slide

198. Ͱ͸
     ҡ࣋ͱ͸Կ͔
     

     View Slide

199. 
     Ϗοάόϯ
     

     View Slide

200. 
     খӉ஦೩ম
     

     View Slide

201. 
     Ի଎͔Βޫ଎
     

     View Slide

202. ʜ
     ʁʁʁʁʁ
     

     View Slide

203. ޫ଎ͷ··
     ਐΜͰ͍͘
     

     View Slide

204. ͜Ε͕
     ҡ࣋ɾӡ༻อक
     

     View Slide

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

     View Slide

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

     View Slide

207. ͭ·Γ
     

     View Slide

208. ΈΜͳ
     νʔϜͰ͢ʂ
     

     View Slide

209. ͦͯ͠ɾɾɾ
     

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

221. ͋Γ͕ͱ͏͍͟͝·ͨ͠ʂ
     

     View Slide

222. ͕࣌ؒ
     ༨ͬͨΒ
     ࿩͢Α
     

     View Slide

223. .Z42-
     
     ઃఆྫ
     

     View Slide

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

     View Slide

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

     View Slide

226. ɹ$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
     

     View Slide

227. ɹ$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
     ໌֬ʹ
     ΤϥʔϩάͰ͋Δ͜ͱΛ
     ࢦఆ͢Δ
     

     View Slide

228. ɹ$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
     ͜͜Ͱઃఆ͍ͯ͠Ε͹
     %#ɺςʔϒϧɺΧϥϜͰ
     ࢦఆ͢Δඞཁ͕ͳ͍
     

     View Slide

229. ɹ$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
     $IBSBTFUಉ༷
     ͜͜Ͱઃఆ͓ͯ͘͠
     

     View Slide

230. ɹ$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
     ͨͩ͠
     $)"3"$5&34&5͢Δͱ
     ແࢹ͞ΕΔͷͰ஫ҙ
     

     View Slide

231. ɹ$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
     ͜͜Ͱઃఆ͍ͯ͠Ε͹
     ௨৴࣌ʹ΋
     จࣈԽ͚Λ๷ࢭͰ͖Δ
     

     View Slide

232. ɹ$IBSBTFU $PMMBUJPO
     mysql> show variables like 'char%';
     +--------------------------+----------------------------+
     | Variable_name | Value |
     +--------------------------+----------------------------+
     | character_set_client | utf8mb4 |
     | character_set_connection | utf8mb4 |
     | character_set_database | utf8mb4 |
     | character_set_filesystem | binary |
     | character_set_results | utf8mb4 |
     | character_set_server | utf8mb4 |
     | character_set_system | utf8mb4 |
     | character_sets_dir | /usr/share/mysql/charsets/ |
     +--------------------------+----------------------------+
     ⾣$IBSBTFU֬ೝ
     

     View Slide

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

     View Slide

234. ɹ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
     

     View Slide

235. ɹ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
     OPJOEFYͷΫΤϦΛ
     ϩάʹग़͢
     

     View Slide

236. ɹ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
     OPJOEFYͷΫΤϦΛ
     ϩάʹग़ִؒ͢ʢ෼ʣ
     

     View Slide

237. ɹ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
     4&-&$5ͷ
     ߦ਺ࢦఆ
     

     View Slide

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

     View Slide

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

     View Slide

240. ɹ҉໧తͳଐੑ෇༩͸ඇਪ঑
     ⾣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’
     

     View Slide

241. ɹ҉໧తͳଐੑ෇༩͸ඇਪ঑
     ⾣ྫɿ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’
     ҉໧తͳଐੑ෇༩Ͱ
     ҙਤ͠ͳ͍஋ʹͳΔ
     

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

246. ɹ҉໧తͳଐੑ෇༩͸ඇਪ঑
     [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)
     

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide