Seamless MySQL database migration

On April 9, September 11, I attended the technical salon MySQL performance optimization and spatial database development practices jointly held by infoq and Baidu. the content shared by Liu Bin from Baidu is very helpful to those who are currently using ssd to improve MySQL performance. At the same time, I personally think ssd has a lot of space in other data storage. I have never been familiar with the spatial database mentioned by Yan Xun, but it seems to be very good. It should be helpful for friends who are doing geographic information. Two groups in the last open space environment discussed some questions, but there was no answer at the end. I shared some similar experiences.

How does one seamlessly migrate MySQL databases?

Q: during the normal process, the table structure of the database needs to be changed after the system runs for a period of time due to many reasons such as "requirement understanding, architecture design, and requirement change, how can we achieve minimal downtime and seamless migration?

I joked that the best solution is not to migrate. Migration is definitely the best. It depends on a very good design. In the early stage of architecture design, we can consider the possible changes to the demand. The database design can also be abstracted to a certain extent based on the business. This may be a bit too good, but data migration is always an inevitable problem. The general migration scheme is described below.

Migration with fixed point downtime

As the friend said, the application was stopped on a dark night, And the MySQL database data was migrated to the new MySQL database using a pre-written migration program. Switch the application. The biggest drawback is that the downtime will become very long as the data volume increases.

MySQL binlog Solution

MySQL migration can take into account the master-slave replication feature of MySQL, parse the binlog, and then design the database structure based on the new business characteristics to write data to the new database, the migration process does not require downtime. When data migration is basically completed, stop the previous application, wait until all the migration is completed, and switch the application to the new database. The downtime is very short. It only takes about 1-2 minutes or less.

Trigger plan

Back up the old MySQL Data table structure to the new MySQL database, create a new table structure in the new database, change the old database table, and create a trigger, the new MySQL table to which data is written at the same time. Dump the old MySQL Data and import it to the new MySQL. This is the table of the new MySQL table structure that should already have the corresponding data. Then enable master-slave replication to make it consistent with the data in the master database. Switch the application to the migration solution. The downtime is very short. It only takes about 1-2 minutes or less.

MySQL udf Solution

MySQL udf allows you to develop your own functions and integrate them into MySQL, so that you can easily write data to other places at the same time. The disadvantage is that the development costs are high and you need to understand the MySQL udf. You can also use the ready-made memcached_functions_MySQL and lib_MySQLudf_json functions. You don't need to write udf functions. You only need to implement a memcached server to accept data, and then parse the json to the new database. The memcached protocol is very simple and easy to implement. The migration time of this solution is also very short.

Middleware Solution

This solution requires your application to connect to data using a solution similar to the middle layer. You only need to add data in the middle layer and write data to the new database. The dependency on this solution is relatively large. I believe that smaller companies may not have any conditions.

Summary

To achieve seamless migration, the cost and difficulty will definitely increase. This needs to be implemented based on your business needs. The migration scheme should be fully tested, and an error rollback scheme should be considered. This is all I want to explain about the seamless migration of MySQL databases. I hope this will help you.