Database Schema Migrations with Zero Downtime (DevOpsDays Copenhagen 2019)

Database Schema Migrations with Zero Downtime (DevOpsDays Copenhagen 2019)

Does your application use a database, and have you ever changed the schema? Then you know these database migrations can lead to downtime.

This talk discusses non-destructive changes, rollbacks, large data sets, tools and strategies to migrate data safely, with minimum disruption to production.

2f4800411154a8c66dde489448a044d2?s=128

Michiel Rook

April 03, 2019
Tweet

Transcript

  1. DATABASE SCHEMA MIGRATIONS WITHOUT DOWNTIME MICHIEL ROOK @michieltcs

  2. @michieltcs https://speakerdeck.com/mrook/the-road-to-continuous-deployment-devopsdays-riga-2017

  3. @michieltcs CONTINUOUS DEPLOYMENT

  4. @michieltcs 50+ DEPLOYS PER DAY

  5. @michieltcs HIGH AVAILABILITY

  6. @michieltcs DATABASE MIGRATIONS

  7. @michieltcs WITHOUT DOWNTIME?

  8. @michieltcs DATABASE SCHEMA MIGRATIONS

  9. @michieltcs DATABASE SCHEMA

  10. @michieltcs

  11. @michieltcs

  12. @michieltcs DATABASE SCHEMA MIGRATIONS

  13. @michieltcs SQL

  14. @michieltcs UP

  15. @michieltcs UP CREATE TABLE addresses(id INT NOT NULL,
 street VARCHAR(255)

    NOT NULL, PRIMARY KEY('id'));
  16. @michieltcs UP DOWN

  17. @michieltcs UP DOWN DROP TABLE addresses;

  18. @michieltcs TRANSACTIONS

  19. @michieltcs

  20. @michieltcs FLYWAY

  21. @michieltcs V20170228150619__create_addresses_table.sql
 
 CREATE TABLE addresses (id INT NOT NULL,

    street VARCHAR(255) NOT NULL, PRIMARY KEY(id));
  22. @michieltcs $ flyway migrate
 Flyway Community Edition 5.0.7 by Boxfuse


    
 Database: jdbc:mysql://localhost:3306/migrate (MySQL 5.7)
 Successfully validated 1 migration (execution time 00:00.018s)
 Creating Schema History table: `migrate`.`flyway_schema_history`
 Current version of schema `migrate`: << Empty Schema >>
 Migrating schema `migrate` to version 20170228150619 - create addresses table
 Successfully applied 1 migration to schema `migrate` (execution time 00:00.086s)
  23. @michieltcs mysql> select * from flyway_schema_history;
 +----------------+----------------+------------------------+------+---------------------- | installed_rank |

    version | description | type | script +----------------+----------------+------------------------+------+---------------------- | 1 | 20170228150619 | create addresses table | SQL | V20170228150619__crea +----------------+----------------+------------------------+------+---------------------- 1 row in set (0.00 sec) --------------+-----------+--------------+---------------------+----------------+---------+
 | checksum | installed_by | installed_on | execution_time | success |
 --------------+-----------+--------------+---------------------+----------------+---------+
 ses_table.sql | 671780326 | root | 2018-04-10 17:37:50 | 10 | 1 |
 --------------+-----------+--------------+---------------------+----------------+---------+

  24. @michieltcs LEGACY APPLICATIONS

  25. @michieltcs V1__base_version.sql
 
 < SNAPSHOT OF EXISTING DATABASE SCHEMA >

  26. @michieltcs $ flyway baseline
 Flyway Community Edition 5.0.7 by Boxfuse


    
 Database: jdbc:mysql://localhost:3306/migrate (MySQL 5.7)
 Creating Schema History table: `migrate`.`flyway_schema_history`
 Successfully baselined schema with version: 1
  27. @michieltcs --------------+----------+--------------+---------------------+----------------+---------+
 | checksum | installed_by | installed_on | execution_time

    | success |
 --------------+----------+--------------+---------------------+----------------+---------+
 y Baseline >> | NULL | root | 2018-04-11 08:43:14 | 0 | 1 |
 --------------+----------+--------------+---------------------+----------------+---------+
 mysql> select * from flyway_schema_history;
 +----------------+---------+-----------------------+----------+-----------------------+- | installed_rank | version | description | type | script | +----------------+---------+-----------------------+----------+-----------------------+- | 1 | 1 | << Flyway Baseline >> | BASELINE | << Flyway Baseline >> | +----------------+---------+-----------------------+----------+-----------------------+- 1 row in set (0.00 sec)
  28. @michieltcs REPEATABLE MIGRATIONS 1

  29. @michieltcs REPEATABLE MIGRATIONS JAVA MIGRATIONS 1 2

  30. @michieltcs REPEATABLE MIGRATIONS JAVA MIGRATIONS CALLBACKS 1 2 3

  31. @michieltcs UNDOING MIGRATIONS

  32. @michieltcs ROLL BACK
 OR
 ROLL FORWARD?

  33. @michieltcs DESTRUCTIVE CHANGES 1

  34. @michieltcs DESTRUCTIVE CHANGES 1 LONG-LIVED LOCKS

  35. @michieltcs DESTRUCTIVE CHANGES 1 LONG-LIVED LOCKS RENAMES

  36. @michieltcs DESTRUCTIVE CHANGES 1 LONG-LIVED LOCKS RENAMES DELETES

  37. @michieltcs DESTRUCTIVE CHANGES IMPLICIT COMMITS 1 2

  38. @michieltcs DESTRUCTIVE CHANGES IMPLICIT COMMITS SHARDING 1 2 3

  39. @michieltcs DESTRUCTIVE CHANGES IMPLICIT COMMITS SHARDING MULTIPLE DATABASES 1 2

    3 4
  40. @michieltcs RESTORING FROM BACKUPS?

  41. @michieltcs ROLLBACKS

  42. @michieltcs DOWNTIME?

  43. @michieltcs SIMPLE DEPLOYMENT FLOW

  44. @michieltcs BUILD IMAGE @michieltcs

  45. @michieltcs BUILD IMAGE STOP
 CONTAINER @michieltcs

  46. @michieltcs BUILD IMAGE STOP
 CONTAINER RUN
 MIGRATIONS @michieltcs

  47. @michieltcs BUILD IMAGE STOP
 CONTAINER RUN
 MIGRATIONS START
 CONTAINER @michieltcs

  48. @michieltcs DOWNTIME?

  49. @michieltcs CONTINUOUS DELIVERY

  50. @michieltcs DEV BUILD / TEST ACCEPTANCE PRODUCTION CONTINUOUS DELIVERY @michieltcs

  51. @michieltcs CONTINUOUS DEPLOYMENT

  52. @michieltcs DEV BUILD / TEST STAGING / ACCEPTANCE PRODUCTION CONTINUOUS

    DEPLOYMENT @michieltcs
  53. @michieltcs ZERO DOWNTIME DEPLOYS

  54. @michieltcs ZERO DOWNTIME DEPLOYMENT LOAD BALANCER APP 1.0 @michieltcs

  55. @michieltcs LOAD BALANCER APP 1.0 APP 2.0 ZERO DOWNTIME DEPLOYMENT

    @michieltcs
  56. @michieltcs LOAD BALANCER APP 1.0 APP 2.0 ZERO DOWNTIME DEPLOYMENT

    @michieltcs
  57. @michieltcs LOAD BALANCER APP 1.0 APP 2.0 ZERO DOWNTIME DEPLOYMENT

    @michieltcs
  58. @michieltcs LOAD BALANCER APP 2.0 ZERO DOWNTIME DEPLOYMENT @michieltcs

  59. @michieltcs DOWNTIME

  60. @michieltcs NO MIGRATIONS!

  61. @michieltcs DOWNTIME

  62. @michieltcs DATABASE STATE?

  63. @michieltcs DATABASE VERSION LOAD BALANCER APP 1.0 DATABASE 1.0 expects

    1.0 @michieltcs
  64. @michieltcs DATABASE VERSION LOAD BALANCER APP 1.0 DATABASE 1.0 expects

    1.0 @michieltcs Migration
  65. @michieltcs LOAD BALANCER APP 1.0 DATABASE 2.0 expects 1.0 DATABASE

    VERSION @michieltcs
  66. @michieltcs LOAD BALANCER APP 1.0 DATABASE 2.0 expects 1.0 DATABASE

    VERSION @michieltcs
  67. @michieltcs LOAD BALANCER APP 1.0 APP 2.0 DATABASE 2.0 expects

    1.0 expects 2.0 DATABASE VERSION @michieltcs
  68. @michieltcs OLD APP
 SHOULD WORK WITH
 NEW STATE

  69. @michieltcs ONE DIFF BACK

  70. @michieltcs ONE BUILD BACK

  71. @michieltcs ONE BUILD BACK (git revert HEAD)

  72. @michieltcs DECOUPLE

  73. @michieltcs MIGRATION DECOUPLE DEPLOYMENT

  74. @michieltcs NON-DESTRUCTIVE CHANGES

  75. @michieltcs BACKWARDS COMPATIBILITY

  76. @michieltcs ADDING TABLES

  77. @michieltcs ADDING TABLES ADDING COLUMNS

  78. @michieltcs ADDING TABLES ADDING COLUMNS CREATING INDEXES

  79. @michieltcs EXAMPLE:
 RENAMING A COLUMN

  80. @michieltcs surname last_name

  81. @michieltcs CREATE COLUMN WITH NEW NAME 1

  82. @michieltcs CREATE COLUMN WITH NEW NAME 1 ALTER TABLE person


    ADD last_name VARCHAR(255);
  83. @michieltcs CREATE COLUMN WITH NEW NAME WRITE TO OLD &

    NEW COLUMNS 1 2
  84. @michieltcs CREATE COLUMN WITH NEW NAME WRITE TO OLD &

    NEW COLUMNS 1 2 public void setLastName(String lastName) {
 this.lastName = lastName;
 this.surname = lastName;
 }
  85. @michieltcs CREATE COLUMN WITH NEW NAME WRITE TO OLD &

    NEW COLUMNS 1 2 public String getLastName() {
 return (lastName ? lastName : surname);
 }
  86. @michieltcs CREATE COLUMN WITH NEW NAME WRITE TO OLD &

    NEW COLUMNS MIGRATE OLD RECORDS 1 2 3
  87. @michieltcs CREATE COLUMN WITH NEW NAME WRITE TO OLD &

    NEW COLUMNS MIGRATE OLD RECORDS 1 2 3 UPDATE person SET person.last_name = person.surname
 WHERE person.last_name IS NULL;
  88. @michieltcs CREATE COLUMN WITH NEW NAME WRITE TO OLD &

    NEW COLUMNS MIGRATE OLD RECORDS READ FROM NEW COLUMN 1 2 3 4
  89. @michieltcs CREATE COLUMN WITH NEW NAME WRITE TO OLD &

    NEW COLUMNS MIGRATE OLD RECORDS READ FROM NEW COLUMN 1 2 3 4 public String getLastName() {
 return lastName;
 }
  90. @michieltcs CREATE COLUMN WITH NEW NAME WRITE TO OLD &

    NEW COLUMNS MIGRATE OLD RECORDS READ FROM NEW COLUMN 1 2 3 4 remove old column and code
  91. @michieltcs

  92. @michieltcs CHALLENGES

  93. @michieltcs LONG MIGRATIONS

  94. @michieltcs LONG MIGRATIONS FAILURES & RESTARTS

  95. @michieltcs LONG MIGRATIONS MEMORY USAGE FAILURES & RESTARTS

  96. @michieltcs LONG MIGRATIONS MEMORY USAGE CLEANING UP FAILURES & RESTARTS

  97. @michieltcs LONG MIGRATIONS MEMORY USAGE LOGGING & PROGRESS FAILURES &

    RESTARTS CLEANING UP
  98. @michieltcs LONG MIGRATIONS MEMORY USAGE (NON-)NULLABLE TYPES LOGGING & PROGRESS

    FAILURES & RESTARTS CLEANING UP
  99. @michieltcs STRATEGIES

  100. @michieltcs LOCKLESS ALTER

  101. @michieltcs ALTER TABLE … ALGORITHM INPLACE LOCK NONE;

  102. @michieltcs REPLICATION

  103. @michieltcs RUN MIGRATION ON FAILOVER

  104. @michieltcs MASTER SLAVE 1.0 1.0

  105. @michieltcs MASTER SLAVE 1.0 1.0 STOP SLAVE SQL_THREAD;

  106. @michieltcs MASTER SLAVE 1.0 2.0 ALTER TABLE person
 DROP COLUMN

    surname;
  107. @michieltcs MASTER SLAVE 1.0 2.0 START SLAVE SQL_THREAD;

  108. @michieltcs MASTER SLAVE 1.0 2.0 CHANGE MASTER TO ...;

  109. @michieltcs SLAVE MASTER 1.0 2.0

  110. @michieltcs SLAVE MASTER 1.0 2.0 STOP SLAVE SQL_THREAD;

  111. @michieltcs SLAVE MASTER 2.0 2.0 ALTER TABLE person
 DROP COLUMN

    surname;
  112. @michieltcs SLAVE MASTER 2.0 2.0 START SLAVE SQL_THREAD;

  113. @michieltcs SLAVE MASTER 2.0 2.0

  114. @michieltcs PT ONLINE SCHEMA CHANGE

  115. @michieltcs CREATE SHADOW COPY (TARGET TABLE) 1

  116. @michieltcs CREATE SHADOW COPY (TARGET TABLE) ADD TRIGGERS TO FORWARD

    UPDATES 1 2
  117. @michieltcs CREATE SHADOW COPY (TARGET TABLE) ADD TRIGGERS TO FORWARD

    UPDATES COPY SOURCE DATA IN SMALL CHUNKS 1 2 3
  118. @michieltcs CREATE SHADOW COPY (TARGET TABLE) ADD TRIGGERS TO FORWARD

    UPDATES COPY SOURCE DATA IN SMALL CHUNKS RENAME TARGET TABLE 1 2 3 4
  119. @michieltcs pt-online-schema-change --execute
 --alter "add index name_idx (name)" D=test,t=test1,h=localhost

  120. @michieltcs AND THERE'S MORE https://github.com/soundcloud/lhm https://github.com/facebookincubator/OnlineSchemaChange https://github.com/freels/table_migrator

  121. @michieltcs RECAP

  122. @michieltcs PLANNING

  123. @michieltcs MULTIPLE STEPS

  124. @michieltcs NO BREAKING CHANGES

  125. THANK YOU
 FOR LISTENING! @michieltcs / michiel@michielrook.nl www.michielrook.nl