MySQLオンラインマイグレーションツールgh-ostで深夜メンテナンスを無くした話

.Z42-ؔٝٓ؎ٝو؎ؚٖ٦ءّٝخ٦ٕ HIPTUד帾㢸ًٝذشٝأ׾搀ֻ׃׋鑧䎃剢傈 "5&".5&$)㣐ꢻ 吳䒭⠓爡ؒ؎ث٦يٓ؎ؿأة؎ٕ ꈿ加㽟修

h"UFBN *OD 4FMG *OUSPEVDUJPO ꈿ加㽟修 (JU)VC!TUFSNJOBM 2JJUB !TV[VLJ@TI IUUQTXXXTUFSNJOBMDPN
8FC &OHJOFFS!/BHPZB0TBLB 1)13VCZ+BWB4DSJQU .JDSPTPGU"[VSF"NB[PO8FC4FSWJDFT

h"UFBN *OD 䬐䔲؟٦ؽأ稱➜

h"UFBN *OD ٕٓ٦ٝחאְג J04"OESPJE"QQ 8FC⩎ך⢪欽䪮遭 1)1 3VCZ .Z42- .BTUFS4MBWFךٖفٔ؛٦ءّٝ圓䧭
%PXOMPBET ♰⟃♳ %BJMZ "DUJWF 6TFS 侧⼧♰ .POUIMZ 1BHF 7JFX ⭙⟃♳ %BUBCBTF*03FRVFTUT 侧⼧⟃♳猱 ˟䎃剢儗挿

h"UFBN *OD ٕٓ٦ٝפך堣腉鷄⸇ չ䝢׫湱锑պפך堣腉鷄⸇ .Z42- "-5&3 5"#-& 侧⼧ⴓ։侧儗꟦ ׉ך꟦ծ%#ך剅ֹ鴥׫ָדֹזְ
ِ٦ؠָ乼⡲ׅ׷ذ٦ـٕד֮׸ל 䔲搫ⵃ欽דֹזֻז׷ ؟٦ؽأָ䧭׶甧׋זְ

h"UFBN *OD 帾㢸ًٝذشٝأכ׃׋ֻזְ˘ 歗⫷ך⳿Ⱙ IUUQTXXXQFYFMTDPNQIPUPHSBZTDBMFQIPUPXPNBO 1IPUPCZ&VHFOFGSPN1FYFMT

h"UFBN *OD .Z42- 0OMJOF %%-ך涫㜥 .Z42- ַ׵ծ0OMJOF %%-ָⵃ欽〳腉חז׏׋ "-5&3
5"#-&⚥ח61%"5&*/4&35%&-&5&ָ〳腉ח ٖفٔ؛٦ءّٝך42- 5ISFBEכءؚٕٝأٖحسד֮׶ծ "-5&35"#-&俑ָٖفٔ؛٦ءّٝך42-5ISFBE׾䲕׿׌ תתחז׷կ "-5&35"#-&ָ㸣✪ׅ׷תדך꟦ٖفٔ؛٦ءّٝ鹼䒀ָ 涪欰ׅ׷ .Z42- 0OMJOF %%-ך㉏겗挿

h"UFBN *OD װ׏ם׶帾㢸ًٝذشٝأָ䗳銲זךַ˘ 歗⫷ך⳿Ⱙ IUUQTXXXQFYFMTDPNQIPUPHSBZTDBMFQIPUPXPNBO 1IPUPCZ&VHFOFGSPN1FYFMT

h"UFBN *OD 0OMJOF.JHSBUJPO5PPMך涫㜥 QUPOMJOFTDIFNBDIBOHFծ-).ծPBLPOMJOFBMUFSUBCMF ֿ׸׵ךخ٦ٕכծֶֶ׬י♴鎸ך⹛⡲׾ׅ׷կ ⯋ךذ٦ـٕה겲⡂׃׋չ倜ذ٦ـٕպ׾⡲䧭 ⯋ךذ٦ـַٕ׵չ倜ذ٦ـٕպחծر٦ة׾䖚ղח؝ؾ٦
׉ך꟦חչ倜ذ٦ـٕպפ.JHSBUJPO׾⿾僥 ر٦ةָず劍׃׋׵ծ⯋ךذ٦ـٕ׾չ倜ذ٦ـٕպח縧䳔 ֿך倯岀ד֮׸לծ然ַחتؐٝة؎ي搀׃דذ٦ـٕך 㢌刿׾黝欽דֹ׷կ

h"UFBN *OD 0OMJOF.JHSBUJPO5PPMך㉏겗挿 زٔؖ٦׾⢪׏גず劍涸חر٦ة׾醱醡׃גְ׷ չزٔؖ٦׾⢪׻׆պչꬊず劍דպ㢌刿׾⿾僥דֹ׸לծ ֿ׸׵ך㉏겗כ饯ֿ׵זְկ زٔؖ٦כⳢ椚ָꅾֻծ頾蚚ַַָ׷ زٔؖ٦כ⯋ךؙؒٔהٗحָؙ畸さׅ׷ 頾蚚ָ넝ֻז׏ג׮ծ鷿⚥ד♧儗⨡姺׃׋׶כדֹזְ
زٔؖ٦׾⢪׏׋ず劍ך㉏겗挿

h"UFBN *OD 穠㽷帾㢸ًٝذشٝأָ䗳銲זךַ˘ .Z42-ד չذ٦ـٕ׾ꬊず劍ד醱醡דֹ׷➬穈׫պִׁ֮׸ל˘ 歗⫷ך⳿Ⱙ IUUQTXXXQFYFMTDPNQIPUPHSBZTDBMFQIPUPXPNBO 1IPUPCZ&VHFOFGSPN1FYFMT

h"UFBN *OD HIPTU (JU)VCT0OMJOF4DIFNB.JHSBUJPOTGPS.Z42- ذ٦ـٕ㢌刿ך⿾僥חٖفٔ؛٦ءّٝך➬穈׫׾⢪ֲ הְֲ暴䗙׾䭯׏׋ؔٝٓ؎ٝو؎ؚٖ٦ءّٝخ٦ٕկ ٖفٔ؛٦ءّٝ׾⢪ֲךדծꬊず劍

h"UFBN *OD HIPTUךאך⹛⡲ٌ٦س

h"UFBN *OD 㹋ꥷך⹛⡲ך⢽ $ ./gh-ost --user="root" --password="" ¥ --host="127.0.0.1"
--port="3306" --database="dbname" ¥ --table="table_name" ¥ --alter="ADD COLUMN created_at DATETIME" ¥ --test-on-replica ¥ --execute .Z42-פך䱸竲䞔㜠 "-5&35"#-&㼎韋ךذ٦ـٕせה 㢌刿ⰻ㺁 ٌ٦سך䭷㹀㢩ׅהչDPOOFDUUPSFQMJDBպ FYFDVUF׾➰ֽזְהծESZSVOהז׷

h"UFBN *OD HIPTUך暴䗙鯪ְ زٔؖ٦דכזֻٖفٔ؛٦ءّٝ׾⢪ֲ׋׭ծCJOMPH׾剅ֻך׫ ⡚ٔأؙ 㛇劤4MBWFד⹛ֻ׋׭ծ4MBWFָ⡚頾蚚ⱔꞿ⻉ׁ׸׋圓䧭׌הٔأָؙ㼰זְ
䩛⹛ⵖ䖴〳腉醱醡ך♧儗⨡姺ⱄꟚ׾䩛⹛דⵖ䖴דֹ׷կ ذ٦ـٕⴖ׶剏ִة؎ىؚٝ׮ծ䩛⹛ⵖ䖴דֹ׷կ 㹋遤⚥כծ鏩㺁ٖفٔ؛٦ءّٝ鹼䒀眔㔲ח　ת׷״ֲ荈⹛ⵖ䖴׃גֻ׸׷ ذأزדֹ׷ ⯓鶢ך$ٌ٦سדչ4MBWFד㹋遤׃ג4MBWFח黝欽ׅ׷պذأزָ〳腉 ׅץגךٌ٦سדESZSVO׮〳腉

h"UFBN *OD 㹋ꥷך麊欽穠卓㼎韋 .BTUFS4MBWF圓䧭 *BB4♳ח圓眠ׁ׸׋ .Z42-
%# ٕٓ٦ٝך侧涰♰ٖ؝٦س玎䏝ך鋉垷ךذ٦ـٕծⰋ俑嗚稊؎ٝرحؙأ֮׶ 穠卓侧⼧ⴓ玎䏝דծ"-5&3 5"#-&ךַַ׏׋ذ٦ـٕ׾醱醡㸣✪ծ ا؛حزؿ؋؎ٕחVOQPTUQPOF׾鷏׷✲דծ䩛⹛חגⴖ׶剏ִ㸣✪ ⹛⡲⚥4MBWFךTFDPOET@CFIJOE@NBTUFSח♧儗涸ז♳傻ָ鋅׵׸׋ָծ HIPTUָ荈⹛涸חأٗحزؚٔٝ׃גⳢ椚׾箢ㄤ ذ٦ـٕך醱醡כծ帾㢸瘝ח荈⹛ד㹋遤ׇׁ׷✲ָדֹ׷ ⴖ׶剏ִة؎ىؚٝכծ㌀噟儗꟦ⰻח䩛⹛דⵖ䖴דֹ׷ ̔帾㢸ًٝذشٝأָ♶銲ח HIPTUךⵃ挿

h"UFBN *OD HIPTUך铬겗挿 ،فٔ؛٦ءّٝ⩎ך%#أؗ٦وך؝٦س盖椚ח⛦׵זְ 3VCZPO3BJMTך03وحػ٦ח窟さׅ׷倯岀חאְגծ(JU)VC♳ד陽锷ׁ׸ גְ׷ IUUQTHJUIVCDPNHJUIVCHIPTUJTTVFT IUUQTHJUIVCDPNQMBZFSGN(IPTU.JHSBUJPO
䞔㜠ָ㼰׃㼰זְ Ⱅ䒭ך3&"%.&כHJUIVCDPN♳ד傈ղ䭁⯍ׁ׸גְ׷ (JU)VC爡כ"84דךⵃ欽㹋籐ָ搀ַ׏׋״ֲ׌ָծ044חז׏׋✲ד 剣䘊ח״׏ג"84欽ךسًُؗٝزָ鷄⸇ׁ׸׷瘝׃גְ׷ IUUQTHJUIVCDPNHJUIVCHIPTUCMPCNBTUFSEPDSETNE 傈劤铂ך䞔㜠כקר搀ְ QPTUEח״׷ㄤ鏬鎸✲הծ2JJUBך䬴珲ּ׵ְ˘

h"UFBN *OD תה׭ .Z42- "-5&3 5"#-&כ 帾㢸ًٝذشٝأ׾׃זֻג׮幥׬״ֲח 歗⫷ך⳿Ⱙ IUUQTXXXQFYFMTDPNQIPUPBDIJFWFNFOUBEVMUBHSFFNFOUBSNT

չ׫׿זד䎊ׇחז׸׷⠓爡חׅ׷ֿהպ չ➙ַ׵䎃竲ֻ⠓爡חׅ׷ֿהպ

MySQLオンラインマイグレーションツールgh-ostで深夜メンテナンスを無くした話

MySQLオンラインマイグレーションツールgh-ostで深夜メンテナンスを無くした話

s2terminal

More Decks by s2terminal

Other Decks in Technology

Featured

Transcript

.Z42-ؔٝٓ؎ٝو؎ؚٖ٦ءّٝخ٦ٕ HIPTUד帾㢸ًٝذشٝأ׾搀ֻ׃׋鑧䎃剢傈 "5&".5&$)㣐ꢻ 吳䒭⠓爡ؒ؎ث٦يٓ؎ؿأة؎ٕ ꈿ加㽟修

h"UFBN OD 4FMG OUSPEVDUJPO ꈿ加㽟修 (JU)VC!TUFSNJOBM 2JJUB !TV[VLJ@TI IUUQTXXXTUFSNJOBMDPN

h"UFBN *OD 䬐䔲؟٦ؽأ稱➜

h"UFBN *OD ٕٓ٦ٝחאְג J04"OESPJE"QQ 8FC⩎ך⢪欽䪮遭 1)1 3VCZ .Z42- .BTUFS4MBWFךٖفٔ؛٦ءّٝ圓䧭

h"UFBN *OD ٕٓ٦ٝפך堣腉鷄⸇ չ䝢׫湱锑պפך堣腉鷄⸇ .Z42- "-5&3 5"#-& 侧⼧ⴓ։侧儗꟦ ׉ך꟦ծ%#ך剅ֹ鴥׫ָדֹזְ

h"UFBN *OD 帾㢸ًٝذشٝأכ׃׋ֻזְ˘ 歗⫷ך⳿Ⱙ IUUQTXXXQFYFMTDPNQIPUPHSBZTDBMFQIPUPXPNBO 1IPUPCZ&VHFOFGSPN1FYFMT

h"UFBN *OD .Z42- 0OMJOF %%-ך涫㜥 .Z42- ַ׵ծ0OMJOF %%-ָⵃ欽〳腉חז׏׋ "-5&3

h"UFBN *OD װ׏ם׶帾㢸ًٝذشٝأָ䗳銲זךַ˘ 歗⫷ך⳿Ⱙ IUUQTXXXQFYFMTDPNQIPUPHSBZTDBMFQIPUPXPNBO 1IPUPCZ&VHFOFGSPN1FYFMT

h"UFBN *OD 0OMJOF.JHSBUJPO5PPMך涫㜥 QUPOMJOFTDIFNBDIBOHFծ-).ծPBLPOMJOFBMUFSUBCMF ֿ׸׵ךخ٦ٕכծֶֶ׬י♴鎸ך⹛⡲׾ׅ׷կ ⯋ךذ٦ـٕה겲⡂׃׋չ倜ذ٦ـٕպ׾⡲䧭 ⯋ךذ٦ـַٕ׵չ倜ذ٦ـٕպחծر٦ة׾䖚ղח؝ؾ٦

h"UFBN *OD 穠㽷帾㢸ًٝذشٝأָ䗳銲זךַ˘ .Z42-ד չذ٦ـٕ׾ꬊず劍ד醱醡דֹ׷➬穈׫պִׁ֮׸ל˘ 歗⫷ך⳿Ⱙ IUUQTXXXQFYFMTDPNQIPUPHSBZTDBMFQIPUPXPNBO 1IPUPCZ&VHFOFGSPN1FYFMT

h"UFBN *OD HIPTU (JU)VCT0OMJOF4DIFNB.JHSBUJPOTGPS.Z42- ذ٦ـٕ㢌刿ך⿾僥חٖفٔ؛٦ءّٝך➬穈׫׾⢪ֲ הְֲ暴䗙׾䭯׏׋ؔٝٓ؎ٝو؎ؚٖ٦ءّٝخ٦ٕկ ٖفٔ؛٦ءّٝ׾⢪ֲךדծꬊず劍

h"UFBN *OD HIPTUךאך⹛⡲ٌ٦س

h"UFBN *OD 㹋ꥷך⹛⡲ך⢽ $ ./gh-ost --user="root" --password="" ¥ --host="127.0.0.1"

h"UFBN *OD HIPTUך暴䗙鯪ְ زٔؖ٦דכזֻٖفٔ؛٦ءّٝ׾⢪ֲ׋׭ծCJOMPH׾剅ֻך׫ ⡚ٔأؙ 㛇劤4MBWFד⹛ֻ׋׭ծ4MBWFָ⡚頾蚚ⱔꞿ⻉ׁ׸׋圓䧭׌הٔأָؙ㼰זְ

h"UFBN OD 㹋ꥷך麊欽穠卓㼎韋 .BTUFS4MBWF圓䧭 BB4♳ח圓眠ׁ׸׋ .Z42-

h"UFBN *OD HIPTUך铬겗挿 ،فٔ؛٦ءّٝ⩎ך%#أؗ٦وך؝٦س盖椚ח⛦׵זְ 3VCZPO3BJMTך03وحػ٦ח窟さׅ׷倯岀חאְגծ(JU)VC♳ד陽锷ׁ׸ גְ׷ IUUQTHJUIVCDPNHJUIVCHIPTUJTTVFT IUUQTHJUIVCDPNQMBZFSGN(IPTU.JHSBUJPO

h"UFBN *OD תה׭ .Z42- "-5&3 5"#-&כ 帾㢸ًٝذشٝأ׾׃זֻג׮幥׬״ֲח 歗⫷ך⳿Ⱙ IUUQTXXXQFYFMTDPNQIPUPBDIJFWFNFOUBEVMUBHSFFNFOUBSNT

չ׫׿זד䎊ׇחז׸׷⠓爡חׅ׷ֿהպ չ➙ַ׵䎃竲ֻ⠓爡חׅ׷ֿהպ