Database migrations with zero downtime (DevOpsDays Riga 2018)

2f4800411154a8c66dde489448a044d2?s=47 Michiel Rook
September 27, 2018

Database migrations with zero downtime (DevOpsDays Riga 2018)

Does your application or service use a database? When that application changes because of new business requirements, you may need to make changes to the database schema. These database migrations could lead to downtime and can be an obstacle to implementing continuous delivery/deployment.

How can we deal with database migrations when we don’t want our end-users to experience downtime, and want to keep releasing?

In this talk we’ll discuss non-destructive changes, rollbacks, large data sets, useful tools and a few strategies to migrate our data safely, with minimum disruption to production.

2f4800411154a8c66dde489448a044d2?s=128

Michiel Rook

September 27, 2018
Tweet

Transcript

  1. DATABASE SCHEMA MIGRATIONS WITHOUT DOWNTIME MICHIEL ROOK @michieltcs

  2. ▸ Freelance Developer & Coach ▸ @michieltcs

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

  4. CONTINUOUS DEPLOYMENT

  5. 50+ DEPLOYS PER DAY

  6. HIGH AVAILABILITY

  7. DATABASE MIGRATIONS

  8. WITHOUT DOWNTIME?

  9. DATABASE SCHEMA MIGRATIONS

  10. DATABASE SCHEMA

  11. None
  12. None
  13. DATABASE SCHEMA MIGRATIONS

  14. SQL

  15. UP @michieltcs

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

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

  18. UP DOWN @michieltcs DROP TABLE addresses;

  19. TRANSACTIONS

  20. @michieltcs

  21. FLYWAY

  22. V20170228150619__create_addresses_table.sql
 
 CREATE TABLE addresses (id INT NOT NULL, street

    VARCHAR(255) NOT NULL, PRIMARY KEY(id)); @michieltcs
  23. $ 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) @michieltcs
  24. 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) @michieltcs --------------+-----------+--------------+---------------------+----------------+---------+
 | checksum | installed_by | installed_on | execution_time | success |
 --------------+-----------+--------------+---------------------+----------------+---------+
 ses_table.sql | 671780326 | root | 2018-04-10 17:37:50 | 10 | 1 |
 --------------+-----------+--------------+---------------------+----------------+---------+

  25. LEGACY APPLICATIONS

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

  27. $ 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 @michieltcs
  28. 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) @michieltcs --------------+----------+--------------+---------------------+----------------+---------+
 | checksum | installed_by | installed_on | execution_time | success |
 --------------+----------+--------------+---------------------+----------------+---------+
 y Baseline >> | NULL | root | 2018-04-11 08:43:14 | 0 | 1 |
 --------------+----------+--------------+---------------------+----------------+---------+

  29. REPEATABLE MIGRATIONS 1 @michieltcs

  30. REPEATABLE MIGRATIONS JAVA MIGRATIONS 1 2 @michieltcs

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

  32. UNDOING MIGRATIONS

  33. ROLL BACK
 OR
 ROLL FORWARD?

  34. DESTRUCTIVE CHANGES 1 @michieltcs

  35. DESTRUCTIVE CHANGES 1 LONG-LIVED LOCKS @michieltcs

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

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

  38. DESTRUCTIVE CHANGES IMPLICIT COMMITS 1 2 @michieltcs

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

  40. DESTRUCTIVE CHANGES IMPLICIT COMMITS SHARDING MULTIPLE DATABASES 1 2 3

    4 @michieltcs
  41. RESTORING FROM BACKUPS?

  42. ROLLBACKS

  43. DOWNTIME?

  44. SIMPLE DEPLOYMENT FLOW

  45. BUILD IMAGE @michieltcs

  46. BUILD IMAGE STOP
 CONTAINER @michieltcs

  47. BUILD IMAGE STOP
 CONTAINER RUN
 MIGRATIONS @michieltcs

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

  49. DOWNTIME?

  50. CONTINUOUS DELIVERY

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

  52. CONTINUOUS DEPLOYMENT

  53. DEV BUILD / TEST STAGING / ACCEPTANCE PRODUCTION CONTINUOUS DEPLOYMENT

    @michieltcs
  54. ZERO DOWNTIME DEPLOYS

  55. ZERO DOWNTIME DEPLOYMENT LOAD BALANCER APP 1.0 @michieltcs

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

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

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

  59. LOAD BALANCER APP 2.0 ZERO DOWNTIME DEPLOYMENT @michieltcs

  60. DOWNTIME

  61. NO MIGRATIONS!

  62. DOWNTIME

  63. DATABASE STATE?

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

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

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

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

    @michieltcs
  68. LOAD BALANCER APP 1.0 APP 2.0 DATABASE 2.0 expects 1.0

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

  70. ONE DIFF BACK

  71. ONE BUILD BACK

  72. ONE BUILD BACK (git revert HEAD)

  73. DECOUPLE

  74. MIGRATION DECOUPLE DEPLOYMENT

  75. NON-DESTRUCTIVE CHANGES

  76. BACKWARDS COMPATIBILITY

  77. ADDING TABLES @michieltcs

  78. ADDING TABLES ADDING COLUMNS @michieltcs

  79. ADDING TABLES ADDING COLUMNS CREATING INDEXES @michieltcs

  80. EXAMPLE:
 RENAMING A COLUMN

  81. surname last_name

  82. CREATE COLUMN WITH NEW NAME 1 @michieltcs

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


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

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

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

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

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

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

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

    COLUMNS MIGRATE OLD RECORDS READ FROM NEW COLUMN 1 2 3 4 @michieltcs public String getLastName() {
 return lastName;
 }
  91. 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 @michieltcs
  92. None
  93. CHALLENGES

  94. LONG MIGRATIONS

  95. LONG MIGRATIONS FAILURES & RESTARTS

  96. LONG MIGRATIONS MEMORY USAGE FAILURES & RESTARTS

  97. LONG MIGRATIONS MEMORY USAGE CLEANING UP FAILURES & RESTARTS

  98. LONG MIGRATIONS MEMORY USAGE LOGGING & PROGRESS FAILURES & RESTARTS

    CLEANING UP
  99. LONG MIGRATIONS MEMORY USAGE (NON-)NULLABLE TYPES LOGGING & PROGRESS FAILURES

    & RESTARTS CLEANING UP
  100. STRATEGIES

  101. LOCKLESS ALTER

  102. ALTER TABLE … ALGORITHM INPLACE LOCK NONE;

  103. REPLICATION

  104. RUN MIGRATION ON FAILOVER

  105. MASTER SLAVE 1.0 1.0

  106. MASTER SLAVE 1.0 1.0 STOP SLAVE SQL_THREAD;

  107. MASTER SLAVE 1.0 2.0 ALTER TABLE person
 DROP COLUMN surname;

  108. MASTER SLAVE 1.0 2.0 START SLAVE SQL_THREAD;

  109. MASTER SLAVE 1.0 2.0 CHANGE MASTER TO ...;

  110. SLAVE MASTER 1.0 2.0

  111. SLAVE MASTER 1.0 2.0 STOP SLAVE SQL_THREAD;

  112. SLAVE MASTER 2.0 2.0 ALTER TABLE person
 DROP COLUMN surname;

  113. SLAVE MASTER 2.0 2.0 START SLAVE SQL_THREAD;

  114. SLAVE MASTER 2.0 2.0

  115. PT ONLINE SCHEMA CHANGE

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

  117. CREATE SHADOW COPY (TARGET TABLE) ADD TRIGGERS TO FORWARD UPDATES

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

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

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

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

  122. RECAP

  123. PLANNING

  124. MULTIPLE STEPS

  125. NO BREAKING CHANGES

  126. THANK YOU! @michieltcs / michiel@michielrook.nl www.michielrook.nl