Microsoft’s MySQL Consistent Backup has been in preview mode for several months now, allowing you to make a consistent backup of a server from the open-source SQL database during offline migration to Azure. But how does this new tool work? What are its capabilities? And why do you need a Microsoft partner to help you implement it? Read more about MySQL Consistent Backup below.
MySQL Consistent Backup, Explained
Microsoft describes MySQL Consistent Backup as a new feature that “allows users to take a Consistent Backup of a MySQL server without losing data integrity at source because of ongoing CRUD (Create, Read, Update, and Delete) operations.”
Here are some of the benefits of MySQL Consistent Backup:
- Achieve transactional consistency without setting the source server to read mode
- Maintain data integrity between the source and the target server
- Use MySQL Consistent Backup alongside Data-in Replication or third-party tools such as Striim and Qlik to execute online migration with limited downtime
- Undo logs allow for repeatable reads and produce a snapshot for data migration
- Each log record includes information about undoing a modification to a clustered index record by a transaction
- Binary (or bin) logs maintain archives of CRUD operations in the source server and report to users after offline migration has been achieved
In the current implementation of MySQL Consistent Backup, you can enable “Make Source Server Read Only” when performing offline migration. Doing so preserves the data integrity of the target database and stops Write/Delete operations on the source server.
Making the source server “read only” prevents your team from altering data and ensures the database remains unavailable for updates. That can improve the consistency of migrated data because you can read database snapshots at the same time. MySQL Consistent Backup improves data consistency regardless of whether you set the source server as “read only.”
How Does MySQL Consistent Backup Work?
When performing a migration from the open-source SQL database MySQL to Azure, Consistent Backup flushes a table on the source service with a read lock to generate a data snapshot. That’s more reliable than locking individual tables and databases.
The rest of the process happens like this:
- The migration service starts a repeatable read and incorporates the contents of the undo log with the current table state for the snapshot
- After creating connections for migration, MySQL Consistent Backup releases the locks on the table
- Migration threads perform the migration with repeatable reads for transactions, while the source server conceals new modifications from offline migration
You can click on the specific database in Azure Database Migration Service Portal UI to learn the status of in-progress or completed tables during migration.
The Future of MySQL Consistent Backup
At the time of writing, MySQL Consistent Backup is still in preview mode, and Microsoft hasn’t announced a date for a general release of this feature. There are still kinks Microsoft needs to iron out. These issues concern locks and retries. For example, obtaining a lock on the source server can fail in certain scenarios.
How to Set Up MySQL Consistent Backup
You can turn on MySQL Consistent Backup by selecting “[Preview] Enable Transactional Consistency” in Azure Database Migration Service.
However, there are several prerequisites you might struggle with when using this feature. For example, users who flush tables with a read lock have “reload” and “flush” permissions for successful migration with Consistent Backup/
Additionally, users must utilize the MySQL client tool to enable log_bin on the source server, which isn’t always turned on by default. This process can be complicated and involves promoting and deleting a read replica and configuring the binlog_expire_logs_seconds parameter.
How to Get Value From MySQL Consistent Backup
As you can see, working with MySQL Consistent Backup requires a steep learning curve. Enlisting the services of a qualified, reputable Microsoft partner can remove the pain points associated with this new feature and provide more value when performing a consistent backup of a server from the MySQL SQL database.
Dynamic Consultants Group (DCG) is a Microsoft partner that can help you implement MySQL Consistent Backup faster than other companies. DCG takes a unique approach to implementation by taking into account your specific data migration use case and business requirements for more effective results.
Final Word
MySQL Consistent Backup is a new Azure feature that lets you consistently back up a MySQL server during Azure Database offline migration. While this feature is still in preview mode, you can use it to achieve transactional consistency, maintain data integrity, and generate snapshots for migration. Working with a Microsoft partner like DCG helps you navigate MySQL Consistent Backup and achieve your data migration objectives.
Talk to an expert to learn more about MySQL Consistent Backup implementation and SQL migration.
