how MySQL engineering broke the backups

MySQL has exceptional track of record by introducing minor fixes that cause major breakages. Though usually I could blame naiveté of engineers, who did not really ever have to deal with production implications, but lately I can start sensing various business implications against open-source offerings.

As an original author of mydumper I really cannot get out of my mind that 5.5 and 5.6 metadata locking changes are there to screw with anyone who is building a backup solution using stable snapshot views of MySQL (for example, mysqldump –single-transaction, the golden standard of backing things up in MySQL world).

As seen in a bug #71017 (palindrome!) filed by my esteemed colleague Eric, newly introduced behaviors gobble all the locks possible, even if it makes absolutely no sense for backup/ETL/migration/etc scenarios. 

The only supported way out of that is using MySQL Enterprise Backup, which is proprietary software, and does not produce logical backups that allow selective data restores or ETL capabilities or anything else. You get complete vendor lock in where there is no way to get your data out of the system in a consistent manner, unless, of course, you restrict to “no metadata changes allowed in production” mode. 

11 thoughts on “how MySQL engineering broke the backups”

    1. You do realize that MariaDB IS MySQL right, I mean the EXACT SAME CODE with some changes. I don’t see major metadata changes from MySQL metadata as being in the list of changes from MySQL -> MariaDB. Perhaps I missed it. Can you point me to the changelog or launchpad commit?

      If you think MariaDB is solution to all MySQL problems, I have some snake oil and a bridge or two to sell you. Perhaps I can interest you in some prime waterfront property along the gila river in Arizona?

  3. Domas, what you’re really saying is that previously your backups were broken, and you were fine with that. It’s basic 2PL theorem. Now they’re fixed – and you want the old semantics back. Not a good way to file a feature request.

    1. This behavior does not guard forward-only march of backups, the fact that backups can hit new table versions is a different one.

  4. And yet somehow Percona XtraBackup (free software) manages just fine.

    1. Xtrabackup by default uses a FTWRL to consistently backup the MySQL metadata and MyISAM tables. Thus, Xtrabackup too can have issues with long running select statements. It includes an option to not take this lock, however, that could cause inconsistent backups.

      Regardless, mysqldump is important and only one example of the type of workload that causes this issue. ETL is another one.

  7. I find this interesting:

    select * from performance_schema.threads\G
    *************************** 1. row ***************************
    THREAD_ID: 1
    NAME: thread/sql/main
    TYPE: BACKGROUND
    PROCESSLIST_ID: NULL
    PROCESSLIST_USER: NULL
    PROCESSLIST_HOST: NULL
    PROCESSLIST_DB: NULL
    PROCESSLIST_COMMAND: NULL
    PROCESSLIST_TIME: 4970
    PROCESSLIST_STATE: System lock
    PROCESSLIST_INFO: INTERNAL DDL LOG RECOVER IN PROGRESS
    PARENT_THREAD_ID: NULL
    ROLE: NULL
    INSTRUMENTED: YES

Comments are closed.

%d bloggers like this: