MySQL – How to Achieve Zero Downtime When Upgrading Database Schema

migrationMySQLschema

I am looking for a solution in achieving zero downtime schema upgrade with the assumption that every schema upgrade is backwards-compatible with the previous application version which makes things easier. Since the database is already huge, the upgrade may take more than 2 days to complete and the business cannot afford to stop their operations that long.

I found a candidate solution in
https://stackoverflow.com/a/40365479
where its steps are comprise of:

Create a temp table
Creates triggers on the first table (for inserts, updates, deletes) so that they are replicated to the temp table
In small batches, migrate data
When done, rename table to new table, and drop the other table

But my concern is would not it cause a potential race condition problem in the last step where for instance, we have the original table 'users' and the new table 'users_new', and our goal is to replace the original table with the new table? Because to accomplish that, won't it be needed to execute two separate DDL operations such as

DROP TABLE users;
ALTER TABLE users_new RENAME users

And by doing so, what if a query was executed against the users table during the time between the execution of 1st and 2nd DDL commands?

Best Answer

pt-online-schema-change in the Percona Toolkit is used in production in many companies for many years. It's more than a candidate solution.

It avoids the race condition by dropping old table later.

RENAME TABLE users TO user_old, users_new TO users;
DROP TABLE users_old;

Check out the source code around "Step 5: Rename tables: orig -> old, new -> orig"

pt-osc creates three triggers - on update, on delete and on insert. So when any changes are made during copying from users to users_new, the triggers reflect the change in users_new. So by a moment right before the RENAME, users and users_new are identical.

Related Solutions

Mysql – How to check the consistency of tables in thesql

Run CHECKSUM TABLE command against a table.

There is a caveat for this. According to the MySQL Documentation on CHECKSUM TABLE:

If the checksums for two tables are different, then it is almost certain that the tables are different in some way. However, because the hashing function used by CHECKSUM TABLE is not guaranteed to be collision-free, there is a slight chance that two tables which are not identical can produce the same checksum.

OK, CHECKSUM TABLE is 99.99%, not 100%.

How can you aggressively check all the items of a table for matches and mismatches? You can actually run a great tool from Percona Tools called pt-table-sync. It will check for differences in tables between Master and Slave, as long as the table structure is identical.

For example, to sync the table db.tb1 on a Slave in relation to its Master, run pt-table-sync as follows:

pt-table-sync --print --sync-to-master h=SlaveIP,D=db,t=tbl > SQLtoFixDifferences.sql

When you run this, the table db.tb1 on the Slave is compared to that of its Master using the Primary Key (or Unique Key is there is no Primary Key) of the Slave's db.tb1. The output will be a series of SQL commands, usually REPLACE INTO and DELETE FROM. The script SQLtoFixDifferences.sql is to be executed on the Slave. When done, the data in db.tb1 should be identical to its counterpart on the Master.

To test it then, run these commands:

CHECKSUM TABLE db.tb1;
SELECT COUNT(1) FROM db.tb1;

The results should be identical on Master and Slave.

CAVEAT

I have never used pt-table-sync. I have been using mk-table-sync (MAATKIT tool from Percona) for years and I know it has always worked for me. I fully trust Percona's pt-table-sync is at the least the same, if not superior, quality.

PostgreSQL – Best Practices for Schema Changes and Data Migrations Without Downtime

You almost have your answer already:

Create the new structure in parallel
Start writing to both structures
Migrate old data to the new structure
Only write and read new structure
Delete old columns

As for step 3, use something like this (in one transaction):

Insert what is not there yet:

INSERT INTO new_tbl (old_id, data)
SELECT old_id, data
FROM   old_tbl
WHERE  NOT EXISTS (SELECT * FROM new_tbl WHERE new_tbl.old_id = old_tbl.old_id);

Update what has changed in the meantime:

UPDATE new_tbl
SET    data  = old.data
USING  old_tbl
WHERE  new_tbl.old_id = old_tbl.old_id
AND    new_tbl.data IS DISTINCT FROM old_tbl.data;

New data will not be touched, because it is identical in both places.

Best Answer

Related Solutions

Mysql – How to check the consistency of tables in thesql

PostgreSQL – Best Practices for Schema Changes and Data Migrations Without Downtime

Related Question