Upgrade to Pro — share decks privately, control downloads, hide ads and more …

first_mysql_performance_tuning_at_builderscon2017

 first_mysql_performance_tuning_at_builderscon2017

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

3c9c5f9a91cac73073db8bb2903bd968?s=128

mamy1326

August 04, 2017
Tweet

Transcript

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

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

  3. िؒͰ

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

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

  6. ͓࿩͢Δ͜ͱ ▶︎ ࣗݾ঺հ ▶︎ ఻͍͑ͨ͜ͱ ▶︎ ෛՙঢ়گͱԾઆɺ਍அ ▶︎ my.cnf ߲໨ղઆͱ൓өɾ݁Ռ

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

    ▶︎ ϨϓϦέʔγϣϯͷ࣮ࢪ ▶︎ ӡ༻ɾҡ࣋͸େࣄ ▶︎ ͓ΘΓʹ
  8. ࣗݾ঺հ ▶︎ ΦϥΫΕͳ͍ ▶︎ ϙεάΕͳ͍ ϫλγνϣοτμέ ϚΠΤεΩϡʔΤΔ

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

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

  11. ͓࿩͢Δ͜ͱ ▶︎ ࣗݾ঺հ ▶︎ ఻͍͑ͨ͜ͱ ▶︎ ෛՙঢ়گͱԾઆɺ਍அ ▶︎ my.cnf ߲໨ղઆͱ൓өɾ݁Ռ

    ▶︎ ϨϓϦέʔγϣϯͷ࣮ࢪ ▶︎ ӡ༻ɾҡ࣋͸େࣄ ▶︎ ͓ΘΓʹ
  12. σʔλϕʔε͸ ා͍Ͱ͔͢ʁ

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

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

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

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

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

  18. ͓࿩͢Δ͜ͱ ▶︎ ࣗݾ঺հ ▶︎ ఻͍͑ͨ͜ͱ ▶︎ ෛՙঢ়گͱԾઆɺ਍அ ▶︎ my.cnf ߲໨ղઆͱ൓өɾ݁Ռ

    ▶︎ ϨϓϦέʔγϣϯͷ࣮ࢪ ▶︎ ӡ༻ɾҡ࣋͸େࣄ ▶︎ ͓ΘΓʹ
  19. ϓϩϩʔά

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

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

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

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

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

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

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

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

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

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

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

  31. ෛՙঢ়گ

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

  33. Ծઆ

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

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

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

  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'
  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
  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' ղౚͱ ద੾ͳ࣮ߦݖݶ
  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ࢦఆ͠ɺ࣮ߦ
  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. ⾣࣮ߦ݁Ռ
  42. ਍அ݁Ռ ⾣&OBCMFUIFTMPXRVFSZMPHUP USPVCMFTIPPUCBERVFSJFT ɹεϩʔΫΤϦ͕ઃఆ͞Εͯͳ͍ ɹɹˠ#"%ͳΫΤϦʔΛ ɹɹɹɹɹɹτϥϒϧγϡʔτͰ͖ͳ͍ͧˑ

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

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

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

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

  47. ͓࿩͢Δ͜ͱ ▶︎ ࣗݾ঺հ ▶︎ ఻͍͑ͨ͜ͱ ▶︎ ෛՙঢ়گͱԾઆɺ਍அ ▶︎ my.cnf ߲໨ղઆͱ൓өɾ݁Ռ

    ▶︎ ϨϓϦέʔγϣϯͷ࣮ࢪ ▶︎ ӡ༻ɾҡ࣋͸େࣄ ▶︎ ͓ΘΓʹ
  48. NZDOG શ߲໨ͷ ௐࠪɾઃఆ

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

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

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

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

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

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

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

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

  58. ɹ݁Ռ EBUBCBTF UBCMFSPXTTJ[F WBSMJCNZTRMJCEBUB WBSMJCNZTRMNBNZ

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

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

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

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

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

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

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

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

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

    εϩʔΫΤϦͷ৔ॴ log-slow-queries=/var/log/slow.log
  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⊠
  69. ൓өखॱ

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

    ɹˠEVNQͨ͠಺༰ΛϦετΞ ⾣ಈ࡞ݕূɾϝϯςφϯεϞʔυղআɾܦա؍࡯ ɹˠςετ߲໨Λ࣮ߦɺϩά౳Λ֬ೝ
  71. NZDOGઃఆ݁Ռ ⾣ඵؒϦΫΤετʂ ⾣ϝϞϦ͕ੵ·Εͨ෼͚ͩՔಇʂ ⾣ςʔϒϧݸผͰߋ৽ܥ͕ૣ͘ʂ ⾣ΫΤϦΩϟογϡώοτʂ ⾣JOEFYݟ௚͠ͰεϩʔΫΤϦղফʂ

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

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

  74. ͓࿩͢Δ͜ͱ ▶︎ ࣗݾ঺հ ▶︎ ఻͍͑ͨ͜ͱ ▶︎ ෛՙঢ়گͱԾઆɺ਍அ ▶︎ my.cnf ߲໨ղઆͱ൓өɾ݁Ռ

    ▶︎ ϨϓϦέʔγϣϯͷ࣮ࢪ ▶︎ ӡ༻ɾҡ࣋͸େࣄ ▶︎ ͓ΘΓʹ
  75. ϨϓϦέʔγϣϯ ࣮ࢪ

  76. .Z42- Λ ෳ ੡ ͠ αʔόʔ ࢲ͕ ˓ΜͰ΋ ୅ΘΓ͸ ུ

    ͢Δ ͓࿩ ·Έ΍ͳϮ͖ !NBNZ
  77. ɹϨϓϦέʔγϣϯ ࢲͷ୅ΘΓ͸ ͍͘ΒͰ΋͍ΔΘ ͱݴͬͯ

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

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

  80. ੢ྐྵ೥݄

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

  82. NZDOG͕ σϑΥϧτ

  83. ੢ྐྵ೥݄

  84. ηΧϯυ ΠϯύΫτ

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  102. Ͱ΋

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

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

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

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

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

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

  109. ๻͕ ΍Γ·͢ʂ

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

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

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

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

    *%&/5*'*&%#:1"44803%` `
  114. ɹɹϨϓϦέʔγϣϯϢʔβʔ࡞੒ ⾣ݖݶίϚϯυ NZTRM NBTUFS (3"/53&1-*$"5*0/4-"7& NZTRM NBTUFS 0/  50

    NZTRM NBTUFS `SFQM`!`<εϨʔϒͷϗετ໊>`
  115. ࣄલ४උ ⾣ϨϓϦέʔγϣϯϢʔβʔ࡞੒ ⾣ϚελʔՔಇঢ়ଶͷ֬ೝ ⾣ϚελʔόΠφϦϩάͷ֬ೝ ⾣εϨʔϒͷઃఆ֬ೝ

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

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

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

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

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

  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
  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͕࣮ߦ͞Εͨ ϙδγϣϯ
  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) εϨʔϒઃఆͰ࢖༻͢ΔͨΊ ϝϞ͓͖ͯ͠·͠ΐ͏
  124. ⾣εϨʔϒΛ࡞Δࡍͷݪଇ ɹϚελʔΛϩοΫͯ͠EVNQ͠ ɹಉҰͷঢ়ଶͰϦετΞͯ͠ ɹϑΝΠϧɾϙδγϣϯΛ߹ΘͤΔ ࠩ෼ɾൈ͚࿙Εͳ͘ ϨϓϦέʔγϣϯ։࢝ ɹɹϚελʔόΠφϦϩάͷ֬ೝ

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

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

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

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

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

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

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

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

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

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

  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)
  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) εϨʔϒઃఆͰ࢖༻͢ΔͨΊ ϝϞ͓͖ͯ͠·͠ΐ͏
  138. ɹɹϚελʔͷEVNQΛऔಘ ⾣EVNQऔಘ $ mysqldump -u root -p mamy1326 > gzip

    mamy1326.dump.gz ɾผίϯιʔϧ͔Β࣮ࢪ ɹˠRVJU͢ΔͱϩοΫ͕֎Ε·͢ ɾετϨʔδ༰ྔΛߟྀͯ͠H[JQ ɾTDQίϚϯυ౳ͰεϨʔϒʹసૹ
  139. ɹɹϚελʔͷEVNQΛऔಘ ⾣ϚελʔͷϩοΫΛղআ mysql(master)> UNLOCK TABLES; Query OK, 0 rows affected

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

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

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

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

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

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

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

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

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

    -u root -p mamy1326 163(&."45&3-0(450 bNZTRMCJOYYYYYY` ద੾ʹύʔδ͠·͠ΐ͏
  149. ɹϨϓϦέʔγϣϯखॱ ⾣ϚελʔͷEVNQΛऔಘ ⾣εϨʔϒͷNZDOGઃఆ ⾣εϨʔϒʹϦετΞ ⾣εϨʔϒઃఆมߋ ⾣ϨϓϦέʔγϣϯ࣮ߦ ⾣࣮ߦঢ়گΛ؂ࢹ

  150. mysql> STOP SLAVE; mysql> RESET SLAVE; mysql> CHANGE MASTER TO

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

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

    -> master_log_file = ’mysql-bin.000075’, -> master_log_pos = 588201136; ɹεϨʔϒઃఆมߋ ⾣ϚελʔͰϝϞͨ͠಺༰Λઃఆ ϚελʔͷόΠφϦϩάɺ ϙδγϣϯΛઃఆ
  153. ɹϨϓϦέʔγϣϯखॱ ⾣ϚελʔͷEVNQΛऔಘ ⾣εϨʔϒͷNZDOGઃఆ ⾣εϨʔϒʹϦετΞ ⾣εϨʔϒઃఆมߋ ⾣ϨϓϦέʔγϣϯ࣮ߦ ⾣࣮ߦঢ়گΛ؂ࢹ

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

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

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

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

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

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

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

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

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

  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 ⾣ಉظ׬ྃ
  165. ॠؒɺ৺ɺॏͶͯ

  166. γϯΫϩ཰ ˋ

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

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

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

  170. σʔλ͸ ෳ੡໋͕

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

  172. ৼΓฦΓ

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

  174. ՝୊

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

  176. ͓࿩͢Δ͜ͱ ▶︎ ࣗݾ঺հ ▶︎ ఻͍͑ͨ͜ͱ ▶︎ ෛՙঢ়گͱԾઆɺ਍அ ▶︎ my.cnf ߲໨ղઆͱ൓өɾ݁Ռ

    ▶︎ ϨϓϦέʔγϣϯͷ࣮ࢪ ▶︎ ӡ༻ɾҡ࣋͸େࣄ ▶︎ ͓ΘΓʹ
  177. ӡ༻ɾҡ࣋͸ େࣄͩΑ

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

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

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

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

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

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

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

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

  186. ਌͠Ήͱ

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

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

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

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

  191. ͔͠͠ӡ༻͸

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

  193. ٯʹɾɾɾ

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

  195. ಓ͸ ੾Γ୓͔ΕΔ

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

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

  198. Ͱ͸ ҡ࣋ͱ͸Կ͔

  199.  Ϗοάόϯ

  200.  খӉ஦೩ম

  201.  Ի଎͔Βޫ଎

  202. ʜ ʁʁʁʁʁ

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

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

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

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

  207. ͭ·Γ

  208. ΈΜͳ νʔϜͰ͢ʂ

  209. ͦͯ͠ɾɾɾ

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

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

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

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

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

  215. ͓࿩͢Δ͜ͱ ▶︎ ࣗݾ঺հ ▶︎ ఻͍͑ͨ͜ͱ ▶︎ ෛՙঢ়گͱԾઆɺ਍அ ▶︎ my.cnf ߲໨ղઆͱ൓өɾ݁Ռ

    ▶︎ ϨϓϦέʔγϣϯͷ࣮ࢪ ▶︎ ӡ༻ɾҡ࣋͸େࣄ ▶︎ ͓ΘΓʹ
  216. ΞϓϦΤϯδχΞ͔ͩΒͬͯ ΠϯϑϥΛ ଞਓ೚ͤʹ͠ͳ͍

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

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

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

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

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

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

  223. .Z42-  ઃఆྫ

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

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

  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
  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 ໌֬ʹ ΤϥʔϩάͰ͋Δ͜ͱΛ ࢦఆ͢Δ
  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 ͜͜Ͱઃఆ͍ͯ͠Ε͹ %#ɺςʔϒϧɺΧϥϜͰ ࢦఆ͢Δඞཁ͕ͳ͍
  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ಉ༷ ͜͜Ͱઃఆ͓ͯ͘͠
  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͢Δͱ ແࢹ͞ΕΔͷͰ஫ҙ
  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 ͜͜Ͱઃఆ͍ͯ͠Ε͹ ௨৴࣌ʹ΋ จࣈԽ͚Λ๷ࢭͰ͖Δ
  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֬ೝ
  233. ɹઃఆྫ ⾣$IBSBTFU $PMMBUJPO ⾣TMPX@MPHؔ࿈ ⾣҉໧తͳଐੑ෇༩͸ඇਪ঑ ⾣JOOPECͷ಺෦σʔλอ࣋ྖҬ

  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
  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ͷΫΤϦΛ ϩάʹग़͢
  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ͷΫΤϦΛ ϩάʹग़ִؒ͢ʢ෼ʣ
  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ͷ ߦ਺ࢦఆ
  238. ɹઃఆྫ ⾣$IBSBTFU $PMMBUJPO ⾣TMPX@MPHؔ࿈ ⾣҉໧తͳଐੑ෇༩͸ඇਪ঑ ⾣JOOPECͷ಺෦σʔλอ࣋ྖҬ

  239. ɹ҉໧తͳଐੑ෇༩͸ඇਪ঑ ⾣UJNFTUBNQܕ [Warning] TIMESTAMP with implicit DEFAULT value is deprecated.

    Please use —explicit_defaults_for_timestamp server option (see documentation for more details).
  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’
  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’ ҉໧తͳଐੑ෇༩Ͱ ҙਤ͠ͳ͍஋ʹͳΔ
  242. ɹ҉໧తͳଐੑ෇༩͸ඇਪ঑ ⾣ݟͨ͜ͱ͋Γ·ͤΜ͔ʁ mysql> SELECT cuntomer_name -> FROM customers -> WHERE

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

    -> WHERE create_at != ‘0000-00-00 00:00:00’ -> AND create_at < NOW();
  244. ɹ҉໧తͳଐੑ෇༩͸ඇਪ঑ ⾣΍Ήͳ͘ରԠ ɹ΍Ήͳ͘ϓϩάϥϜଆͰରԠ ɹ΍Ήͳ͘8)&3&۟ͰରԠ

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

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

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

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

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