Migrating data from one shard to another can be a challenging task, especially when it comes to dealing with large volumes of data. In traditional approaches, database administrators would take the database offline and perform the migration process, causing downtime that could lead to loss of revenue, reputation, or even customers. However, downtime is not an option for many businesses that require 24/7 access to their databases. Fortunately, there are ways to perform a shard migration without any downtime. In this blog post, we will discuss how to migrate data between shards in a database without causing any downtime. We'll explore the different techniques, tools, and best practices to ensure a smooth and seamless migration experience. Whether you're a database administrator or a developer, this guide will help you successfully migrate your data without disrupting your business operations.
Understanding the problem statement
We have heard about hot shards while working on sharded databases. For people who dont know what sharded databases are, its a technique of horizontal sharding where the data is broken and split across multiple database instances to improve performance and reduce load on a single database instance.
Coming back to hot shards we have some part of our data which is heavily used and the other part which remains dormant. The shard containing this heavily used data is called a hot shard as it faces enormous request queries. To understand this better lets take the example of twitter. A shard can contain data of multiple twitter users. Lets say a user called Robert share the same shard as Selena Gomez. Here Selena Gomez has millions of followers and this shard remains continuosly hot due to huge traffic. But one day Robert became Iron Man and his followers suddenly got a huge spike from few hundred to few millions. The shard which was already under enormous load with one celebrity suddenly had to handle the traffic of two. This is the point where the shards tend to fail and the database crashes.
Solution to the problem
To overcome this issue we need to migrate the data of Robert to another shard. Naive approach of migrating data from one shard to another involves stopping the database transactions for some time until the data is migrated from one shard to another. This will affect the followers of both Selena and Robert as they wont be able to view the twitter account. So this is not a feasible option here. Lets discuss how we can move the data across shards without any down time.
Batch Copy and Binlog Tailing
We can begin with starting a batch copy of the user data with a specific select query. One thing we need to keep in mind while copying the data is that the data does not change in the source database. To ensure this we use MySQLs SELECT ... FOR UPDATE. This statement is a means of applying a lock on the selected rows such that no writes happen on these rows. MySQL also provides a binlog file which contains all the changes occuring in the database. As we batch copy the data we can simultaneously start reading this binlog file and start applying any changes to the new database. This way both the databases remain in sync with each other.
Pulling the plug
Once the batch copy is complete and the binlog changes are continuously syncing we choose a point in time when the writes are least. At this time we stop the requests on the source database and note the time point and let the transactions to get executed until this point in time from the binlog. Once the transactions are fully executed the traffic is routed to the new database. This ensures neglegible down time.
Conclusion
In conclusion, migrating data from one shard to another in a database can be a complex and challenging task. However, by following the steps outlined in this blog post, you can do it without any downtime. By using batch copying and binlog tailing, you can ensure that the data is transferred accurately and efficiently. With careful planning and execution, you can successfully migrate your data to a new shard, ensuring that your database is scalable, reliable, and performing optimally. Remember to always backup your data before any major changes, and seek the help of professionals if necessary.
Reference article:
Article title says "no downtime", while in reality there is downtime. Would be interested in absolutely zero downtime solution. It's possible, if you can impact application level