Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to achieve seamless migration of MySQL database, the article is very detailed, has a certain reference value, interested friends must read it!

Seamless migration of MySQL databases?

Q: in the usual starting process, due to many reasons, such as "requirements understanding, architecture design, requirements change" and so on, the table structure of the database needs to be changed after the system has been running for a period of time. How to achieve as short a downtime as possible? achieve seamless migration?

I joked that the best solution was not to move. It is definitely best not to migrate, which depends on very good design. Possible changes in requirements can be taken into account in the early architecture design, and database design can also be abstracted to a certain extent according to the business. This may be a bit too ideal, but migrating data is always an inevitable problem. Here are the general migration scenarios.

Fixed-point downtime migration

As the friend said, on a dark and windy night, stop the application and use the pre-written migration program to migrate the MySQL database data to the new structure MySQL database. When you are finished, switch applications. The biggest disadvantage is that the downtime will become very long as the amount of data increases.

MySQL binlog scheme

The migration of MySQL can consider the characteristics of MySQL master-slave replication replication, parse the binlog log, and then design the database structure according to the new business characteristics, write the data to the new database, and run the migration without downtime. When the data migration is almost complete, stop the previous application, wait for the migration to be completed, and switch to the new library. Downtime is very short, almost 1-2 minutes or less.

Trigger scheme

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, create a trigger, and let the data be written to the new MySQL table at the same time. Dump the old MySQL data, import into the new MySQL, this is the new MySQL table structure of the table should already have the corresponding data. Then turn on master-slave replication to make it consistent with the master database data. Switch applications and migrate to the solution. Downtime is very short, almost 1-2 minutes or less.

MySQL udf scheme

MySQL's udf allows you to develop your own functions and integrate them into MySQL so that you can easily write to other places as the data is written. The disadvantage is that the development cost is high, and you need to have an understanding of MySQL udf. You can also implement it with off-the-shelf memcached_functions_MySQL and lib_MySQLudf_json, so you don't need to write udf functions, you just need to implement a memcached server to accept the data, and then parse the json to the new database OK. The memcached protocol is very simple and easy to implement by yourself. The migration time of this scheme will also be very short.

Middleware scheme

This solution requires that your application connect data using a scheme similar to the middle tier, you just need to add to the middle tier while writing data to the new database to OK. This kind of scheme depends heavily, and it is believed that smaller companies may not have the conditions.

The above is all the contents of the article "how to achieve seamless migration of MySQL database". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.