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

つらくないマルチテナンシーを求めて: 全て見せます! SmartHR データベース移行プロジェクトの裏側 / builderscon 2018

Purintai
September 08, 2018

つらくないマルチテナンシーを求めて: 全て見せます! SmartHR データベース移行プロジェクトの裏側 / builderscon 2018

Purintai

September 08, 2018
Tweet

More Decks by Purintai

Other Decks in Programming

Transcript

  1. BQBSUNFOUͷڍಈ 4NBSU)3ͷϚϧνςφϯγʔ ೥౰࣌ Amazon RDS Amazon ElastiCache AWS Elastic Beanstalk

    Amazon S3 BQBSUNFOU %#ίωΫγϣϯΛׂΓ౰ͯΒΕΔ <ύϒϦοΫ%#>
  2. BQBSUNFOUͷڍಈ 4NBSU)3ͷϚϧνςφϯγʔ ೥౰࣌ Amazon RDS Amazon ElastiCache AWS Elastic Beanstalk

    Amazon S3 BQBSUNFOU ϦΫΤετϔομʔͷ)PTU͔ΒαϒυϝΠϯΛநग़ )PTUFYBNQMFTNBSUISKQ͔ΒFYBNQMFΛऔΓग़͢ <ύϒϦοΫ%#>
  3. BQBSUNFOUͷڍಈ 4NBSU)3ͷϚϧνςφϯγʔ ೥౰࣌ Amazon RDS Amazon ElastiCache AWS Elastic Beanstalk

    Amazon S3 BQBSUNFOU αϒυϝΠϯFYBNQMFʹରԠ͢Δ ɹςφϯτͷଘࡏΛύϒϦοΫ%#্Ͱ֬ೝ͢Δ
  4&-&$5 '30.AUFOBOUTA 8)&3&ATVCEPNBJOAFYBNQMF <ύϒϦοΫ%#>
  4. BQBSUNFOUͷڍಈ 4NBSU)3ͷϚϧνςφϯγʔ ೥౰࣌ Amazon RDS Amazon ElastiCache AWS Elastic Beanstalk

    Amazon S3 BQBSUNFOU αϒυϝΠϯFYBNQMFʹରԠ͢ΔϨίʔυΛฦ͢ <ύϒϦοΫ%#>
  5. BQBSUNFOUͷڍಈ 4NBSU)3ͷϚϧνςφϯγʔ ೥౰࣌ Amazon RDS Amazon ElastiCache AWS Elastic Beanstalk

    Amazon S3 BQBSUNFOU Ϩίʔυʹσʔλϕʔεؚ໊͕·Ε͍ͯΔͷͰ ɹݱࡏ௫ΜͰ͍Δ%#઀ଓͷσʔλϕʔεΛ੾Γସ͑Δ
  VTFQSPEVDUJPO@FYBNQMF <FYBNQMF>
  6. BQBSUNFOUͷڍಈ 4NBSU)3ͷϚϧνςφϯγʔ ೥౰࣌ Amazon RDS Amazon ElastiCache AWS Elastic Beanstalk

    Amazon S3 BQBSUNFOU BQBSUNFOU͸ϦΫΤετΛॲཧ͢Δؒ ੾Γସ͑தͷςφϯτΛ͓֮͑ͯ͘
  "QBSUNFOU5FOBOUDVSSFOUFYBNQMF <FYBNQMF> <FYBNQMF>
  7. <FYBNQMF> BQBSUNFOUͷڍಈ 4NBSU)3ͷϚϧνςφϯγʔ ೥౰࣌ Amazon RDS Amazon ElastiCache AWS Elastic

    Beanstalk Amazon S3 BQBSUNFOU FY3FEJTͱ4͸੾Γସ͑தͷςφϯτ*%Λ ͦΕͧΕOBNFTQBDFσΟϨΫτϦύεʹར༻͠෼཭ <FYBNQMF>
  8. BQBSUNFOUͷڍಈ 4NBSU)3ͷϚϧνςφϯγʔ ೥౰࣌ Amazon RDS Amazon ElastiCache AWS Elastic Beanstalk

    Amazon S3 BQBSUNFOU ϦΫΤετΛॲཧ͢Δ  ϓϥΠϕʔτ%#্ͰϨίʔυૢ࡞ <FYBNQMF> <FYBNQMF>
  9. BQBSUNFOUͷڍಈ 4NBSU)3ͷϚϧνςφϯγʔ ೥౰࣌ Amazon RDS Amazon ElastiCache AWS Elastic Beanstalk

    Amazon S3 BQBSUNFOU ݱࡏ௫ΜͰ͍Δ%#ίωΫγϣϯͷ ࢀরઌΛݩʹ໭͢
  VTFTNBSUIS@QSPEVDUJPO <ύϒϦοΫ%#>
  10. BQBSUNFOUͷڍಈ 4NBSU)3ͷϚϧνςφϯγʔ ೥౰࣌ Amazon RDS Amazon ElastiCache AWS Elastic Beanstalk

    Amazon S3 BQBSUNFOU %#ίωΫγϣϯΛฦ٫ऴྃ͠ ࣍ͷ)551ϦΫΤετΛ଴ͭʢऴʣ
  11. 8):

  12. 4NBSU)3Ϛϧνςφϯτ೥ද ܧଓར༻཰        

           ࣾ ར༻اۀ਺
  13. 4NBSU)3Ϛϧνςφϯτ೥ද         

        ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ར༻اۀ਺
  14. 4NBSU)3Ϛϧνςφϯτ೥ද         

        ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ར༻اۀ਺ 4NBSU)3ެ։ ೥݄
  15. 4NBSU)3Ϛϧνςφϯτ೥ද         

        ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ར༻اۀ਺ 4NBSU)3ެ։ ೥݄ JOJUJBMDPNNJU ೥݄
  16. 4NBSU)3Ϛϧνςφϯτ೥ද         

        ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ར༻اۀ਺ 4NBSU)3ެ։ ೥݄ JOJUJBMDPNNJU ೥݄ ฒྻNJHSBUF ࢼ࡞൛
  17. 4NBSU)3Ϛϧνςφϯτ೥ද         

        ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ར༻اۀ਺ 4NBSU)3ެ։ ೥݄ JOJUJBMDPNNJU ೥݄ ฒྻNJHSBUF ࢼ࡞൛ σϓϩΠ໰୊͕ ঃʑʹݦࡏԽ
  18. 4NBSU)3Ϛϧνςφϯτ೥ද         

        ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ར༻اۀ਺ 4NBSU)3ެ։ ೥݄ JOJUJBMDPNNJU ೥݄ ฒྻNJHSBUF ࢼ࡞൛ σϓϩΠ໰୊͕ ঃʑʹݦࡏԽ QVSJOUBJೖࣾ
  19. 4NBSU)3Ϛϧνςφϯτ೥ද         

        ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ར༻اۀ਺ 4NBSU)3ެ։ ೥݄ JOJUJBMDPNNJU ೥݄ ฒྻNJHSBUF ࢼ࡞൛ σϓϩΠ໰୊͕ ঃʑʹݦࡏԽ QVSJOUBJೖࣾ ଟஈσϓϩΠͷಋೖ
  20. 4NBSU)3Ϛϧνςφϯτ೥ද         

        ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ར༻اۀ਺ 4NBSU)3ެ։ ೥݄ JOJUJBMDPNNJU ೥݄ ฒྻNJHSBUF ࢼ࡞൛ σϓϩΠ໰୊͕ ঃʑʹݦࡏԽ QVSJOUBJೖࣾ ଟஈσϓϩΠͷಋೖ ৳ͼଓ͚Δ σϓϩΠ࣌ؒ
  21. 4NBSU)3Ϛϧνςφϯτ೥ද         

        ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ར༻اۀ਺ 4NBSU)3ެ։ ೥݄ JOJUJBMDPNNJU ೥݄ ฒྻNJHSBUF ࢼ࡞൛ σϓϩΠ໰୊͕ ঃʑʹݦࡏԽ QVSJOUBJೖࣾ ଟஈσϓϩΠͷಋೖ ৳ͼଓ͚Δ σϓϩΠ࣌ؒ ʁʁʁ
  22. ϚΠάϨʔγϣϯ ׬ྃςφϯτ܈ ༏ઌ౓தɾ௿܈ͷਓ͕ΞΫηεͯ͘͠Δࠒʹ͸ ϚΠάϨʔγϣϯ͸ऴΘ͍ͬͯΔʢϋζʣ ༏ઌϚΠάϨʔγϣϯ ະ ׬ NJHSBUJPO ίϯςφ ༏ઌ౓ߴ

    ༏ઌ౓த ༏ઌ౓௿ ৽͍͠ 8FCίϯςφ DPNNFOUT ςʔϒϧ͋Δ ৽͍͠ 8FCίϯςφ DPNNFOUT ςʔϒϧ͋Δ
  23. 4NBSU)3Ϛϧνςφϯτ೥ද         

        ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ར༻اۀ਺ 4NBSU)3ެ։ ೥݄ JOJUJBMDPNNJU ೥݄ ฒྻNJHSBUF ࢼ࡞൛ σϓϩΠ໰୊͕ ঃʑʹݦࡏԽ QVSJOUBJೖࣾ ଟஈσϓϩΠͷಋೖ ৳ͼଓ͚Δ σϓϩΠ࣌ؒ αʔϏεো֐ൃੜ ఆظϝϯςମ੍ ελʔτ
  24. 4NBSU)3Ϛϧνςφϯτ೥ද         

        ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ར༻اۀ਺ 4NBSU)3ެ։ ೥݄ JOJUJBMDPNNJU ೥݄ ฒྻNJHSBUF ࢼ࡞൛ σϓϩΠ໰୊͕ ঃʑʹݦࡏԽ QVSJOUBJೖࣾ ଟஈσϓϩΠͷಋೖ ৳ͼଓ͚Δ σϓϩΠ࣌ؒ αʔϏεো֐ൃੜ ఆظϝϯςମ੍ ελʔτ %JTDPWFS 4PNFUIJOH/FX
  25. Ҡߦલ ҠߦલͱҠߦޙͷࠩ෼ 1PTUHSF42- $JUVT$MPVE QHHFN BDUJWFSFDPSENVMUJUFOBOUHFN ୯ҰεΩʔϚ ओΩʔ66*% 4ͷΩʔ66*% Ҡߦޙ

    .Z42- "843%4 NZTRMHFN BQBSUNFOUHFN ෼ࢄεΩʔϚ ओΩʔ࿈൪ 4ͷΩʔ࿈൪
  26. ετΞυϑΝϯΫγϣϯ࡞ͬͨΒ UFYUܥΛͻͨ͢ΒғΉ EPVCMFRVPUFͷΤεέʔϓ໰୊ SELECT t.uuid AS id, '<%= tenant_id %>'

    AS tenant_id, CONCAT('"', t.id, '"') AS old_id, CONCAT('"', DATE_FORMAT(t.created_at, '%Y-%m-%d %T'), '"') AS created_at, CONCAT('"', DATE_FORMAT(t.updated_at, '%Y-%m-%d %T'), '"') AS updated_at, sys.CSVSTR(t.country_number) AS country_number, sys.CSVSTR(t.zip_code) AS zip_code, sys.CSVSTR(t.pref) AS pref, sys.CSVSTR(t.city) AS city, sys.CSVSTR(t.street) AS street, sys.CSVSTR(t.building) AS building, sys.CSVSTR(t.literal_yomi) AS literal_yomi FROM <%= dbname %>.addresses AS t ORDER BY t.id ASC;
  27. ফ͑ͨࢀরઌͷςʔϒϧ໊JEςφϯτJEΛ౉͢ͱ 66*%Λฦͯ͘͠ΕΔϋογϡؔ਺ ϦϨʔγϣϯઌͷϨίʔυ͕ͳ͍ DELIMITER // CREATE FUNCTION sys.GHOSTUUID(table_name TEXT, id

    BIGINT, tenant_id TEXT) RETURNS TEXT CHARSET utf8mb4 DETERMINISTIC BEGIN SET @sha2 = SHA2(CONCAT('table_name:',table_name,'::id:',id,'::tenant_id:',tenant_id), 512); SET @uuid = CONCAT( SUBSTR(@sha2 from 10 for 8), '-', SUBSTR(@sha2 from 18 for 4), '-4', SUBSTR(@sha2 from 22 for 3), '-', LOWER(CONV(CONV(SUBSTR(@sha2 from 26 for 1), 16, 10) & 3 | 8, 10, 16)), SUBSTR(@sha2 from 27 for 3), '-', SUBSTR(@sha2 from 40 for 12) ); RETURN @uuid; END; // DELIMITER ;
  28. ଘࡏ͠ͳ͍͔΋͠Εͳ͍΍ͭ͸ ͱΓ͋͑ͣғΜͰ͓͜͏ʂ ϦϨʔγϣϯઌͷϨίʔυ͕ͳ͍ SELECT t.uuid AS id, '<%= tenant_id %>'

    AS tenant_id, CONCAT('"', t.id, '"') AS old_id, CONCAT('"', DATE_FORMAT(t.created_at, '%Y-%m-%d %T'), '"') AS created_at, CONCAT('"', DATE_FORMAT(t.updated_at, '%Y-%m-%d %T'), '"') AS updated_at, COALESCE(s.uuid, sys.GHOSTUUID('Payslip', t.payslip_id, '<%= tenant_id %>')) AS payslip_id, sys.CSVSTR(t.name) AS name, t.amount AS amount FROM <%= dbname %>.deductions AS t LEFT OUTER JOIN <%= dbname %>.payslips AS s ON t.payslip_id = s.id ORDER BY t.id ASC ;
  29. 4NBSU)3Ϛϧνςφϯτ೥ද         

        ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ར༻اۀ਺ 4NBSU)3ެ։ ೥݄ JOJUJBMDPNNJU ೥݄ ฒྻNJHSBUF ࢼ࡞൛ σϓϩΠ໰୊͕ ঃʑʹݦࡏԽ QVSJOUBJೖࣾ ଟஈσϓϩΠͷಋೖ ৳ͼଓ͚Δ σϓϩΠ࣌ؒ αʔϏεো֐ൃੜ ఆظϝϯςମ੍ ελʔτ $JUVTͱͷग़ձ͍ $JUVT$MPVE Ҡߦ1+ελʔτ ίʔυϑϦʔζ❄ $JUVT$MPVE Ҡߦ੒ޭʂ ͝ਗ਼ௌ ͋Γ͕ͱ͏͍͟͝·ͨ͠