How to deploy on-line after the "original" Sub-Library table

Recently busy moving, so stop for a while, today to fill up.

Introduction

Let's start with a joke.

Interviewer: Do you have any experience of concurrency? ”
Candidate: "A little. ”
Interviewer: "What optimizations have you made to deal with concurrency?" ”
Candidate: "Front and back end separation Ah, limit flow ah, sub-Library table AH." ”
Interviewer: "What about the sub-database?" "
Candidate: "Bala." Bala Bala. ”
Interviewer psychological activities: This guy how so like online blog copy, let me ask again.
Interviewer: "How do you deploy on-line after you divide the tables?" ”
Candidate: "This!!!!!! ”

Do not be surprised, I wrote this article, I deliberately went to the Internet to read the next sub-list of the article, it is amazing, are talking about how to carry out the sub-database table, but do not say after the completion, how to deploy on-line. This is more embarrassing during the interview.
You feel your own conscience, if you really do too much of the table, you will not know how to deploy it? So let's learn how to deploy it.

ps:I found a very magical phenomenon. Because many companies use the technology is relatively low, then some job seekers in order to improve their competitiveness, will be some of the high technology to write into their low project. And then, he went out to interview people who are afraid to come out from this company, after all, from the company out of the people, must know their previous company's project situation. So in order to lie, he would say: "They are engaged in the company's old project transformation work, with a lot of new technology in!" ”
Well, please think about how your old system has been smoothly upgraded to a new system!

How to deploy the outage deployment method

The general idea is, hang an announcement, midnight downtime upgrade, and then midnight Service stopped, run data migration program, data migration.
The steps are as follows:
(1) A notice, such as "Tonight 00:00~6:00 maintenance, suspension service"
(2) Write a migration program, read the db-old database, write the new library through the middleware db-new1 and db-new2 , as shown in

(3) Check the consistency before and after the migration, no problem to cut the part of the business to the new library.

By the way, this middleware. There are now two types of middleware for the popular Library sub-table, one is proxy form, for example mycat , it is necessary to deploy an additional server. There is also a client form of, for example, when the out Sharding-JDBC , is a jar package, the use of very light. I'm biased in Sharding-JDBC this way, no additional deployment, no other dependencies, and no need for DBAs to change the original operations.

Evaluation:
People do not think this method is low, I have always felt that this method of reliability is very strong. And I'm sure your readers ' company is not a very good Internet company, if you have more than 1000 users active in the product 1 o'clock in the morning, you hold a claw! After all, not everyone in what e-commerce company, most of the products in the middle of the night there is no traffic. Therefore, this scheme is not without merit.
But this program has a disadvantage, tired! not only the body tired, the heart is also tired! If you think about it, it's going to be six o'clock, you've migrated the database five points, but somehow, the program is a bit of a problem to cut the new library. So, the eyes look at the day will be bright, hurriedly put the database back to the old library. The second night to continue to do so, is almost physically and mentally exhausted.

ps：Here to teach you some skills ah, if you really did not do too much of the table, and want to blow a wave, raise wages, suggest a solution. Because this scheme compares low,low to have nothing to dig deep, so answer this plan, more reliable.
Also, if the interviewer's question is

How do you carry out the sub-database table?

The question is very broad, so answering this question suggests that you take the initiative to tell the strategy of the table and how to deploy it. Because this answer, appear rigorous.
However, many interviewers like to ask questions in order to show off their skills.

What are the strategies of the table? What kind do you use?

Ok.. This question points to a particular direction of the sub-database, and you should not take the initiative to deploy it. When the interviewer asks you, you answer again. If the interviewer doesn't ask, at the end of the interview, the interviewer asks you to ask a few questions. You just ask

You just happened to mention the problem of the sub-database table, we were deployed at the time, the first stop. Then in the middle of the night to migrate data, and then the next day to cut traffic to the new library, this scheme is too tired, do not know your company have any better plan?

In this case, the interviewer will have two answers. First, the interviewer has to bite the bullet. The second kind, the interviewer really did, answer truthfully. Remember, it doesn't matter how the interviewer answers. The point is, you go out and give the interviewer an illusion: "This guy really does too much of a library." "

If you're worried about going in, do you really want to do the sub-list? OK, don't be afraid. I bet you can't touch this job in probation. Because it is possible to carry out a sub-database, it must be very familiar to the business. Still in the probation period you, must be not familiar with the business, if the leader gives you this kind of work, I can only say that he has a big heart.

OK, pointing here. The interview was a battle of wits, pulled away and returned to our subject.

Double Write deployment Method (i)

This is the non-stop deployment method, where I need to first introduce two concepts: historical data and incremental data .
Let's say that we're splitting a test_tb table called, because you're going to double-write, the system inside and the test_tb table-related business is bound to add a pair of code, write to the old library and the new library, and then deploy, then
Historical Data : Before the deployment, the data for the database tables test_tb , which we call historical data.
Incremental Data : After that deployment, the newly generated data for the database tables test_tb , which we call incremental data.
Then the migration process is as follows
(1) Calculate the table you want to migrate first max(主键) . During the migration process, only the db-old middle table is migrated, and the test_tb primary key is equal to the value, which is max(主键) known as the historical data.
Here's a special case, if your table is using a UUID and cannot be found max(主键) , it is based on the creation time as the basis for dividing historical data and incremental data. If your table uses UUID, and there is no time to create this field, I believe that wit you, there must be a way to distinguish between historical data and incremental data.
(2) in the code, with the test_tb business concerned, add a message queue to send messages to the code, the operation of SQL sent to the message queue, as to how the message body assembly, we consider. note that only the SQL that writes the request is sent, only the SQL that writes the request, and only the SQL that writes the request. Important thing to say three times!
There are two reasons:

• (1) Only SQL that writes requests is useful for recovering data.
• (2) in the system, most of the business needs are read requests, writing requests are relatively small.

Note that at this stage, we do not consume the data in the message queue. We only send write requests, Message queue message stacking situation is not too serious!
(3) System on-line. In addition, write a migration program, the migration db-old of the test_tb table, the primary key is less than the max(主键) data, that is, the so-called historical data.
The process of the above step (1) ~ Step (3) is as follows

When db-old the historical data in the migration is complete, the migration of the incremental data, which is the data in the message queue, begins.
(4) The Migration program offline, write a subscription program subscription message queue data
(5) Subscribers will subscribe to the data and write to the new library via middleware
(6) New and old library consistency verification, remove the code of the double write code, will involve test_tb the table read and write operations, point to the new library.
The process of the above step (4) ~ Step (6) is as follows

Here you may have a problem, in step (1) ~ Step (3), the system to the historical data operation, will cause inconsistencies in the problem?
OK, No. Here we delete analyze the operation and update operation, because only these two operations will cause historical data changes, insert in the data are the incremental data.
(1) The db-old test_tb historical data of the table is issued delete operation, the data has not been deleted, the migration program to move away. At this point the delete operation has a record in the message queue, and the latter subscribes to the delete operation and can be deleted.
(2) The db-old test_tb historical data of the table is issued delete operation, the data has been deleted, the migration program can not move the data. At this point the delete operation has a record in the message queue, and the latter subscribes to the delete operation and executes again delete without affecting the consistency.
updatesimilar to the operation, do not repeat.

Double Write Deployment Method (II.)

The method above has a mishap, note I have a word

(2) in the code, TEST_TB-related business, add a message queue to send messages in the code, the operation of SQL sent to the message queue, as to how the message body assembly, we consider.

Let's think about it, this is not causing a serious code intrusion. Embed non-business code into your business code, so you are particularly tired when you delete code later.
Is there no way to avoid this problem?
Yes, subscribe to the binlog log. About the binlog log, I try to write next week, "research and development should be mastered binlog knowledge", here I will introduce the role

Record all database table structure changes (for example, create, ALTER table ...) ) and table data modifications (INSERT, UPDATE, DELETE ...). Binary log. Binlog does not record select and show operations because such operations do not modify the data itself.

Remember what we described in the double-write deployment Method (a) , the message sent to the message queue, is a write operation message. binloglogging is also a write operation. So subscribe to this log, also can meet our needs.
So the steps are as follows
(1) Open Binlog Log, the system is up and running.
(2) or write a migration program to migrate historical data. The steps are similar to the above, not verbose.
Step (1) ~ Step (2) flowchart is as follows

(3) Write a subscription program, subscribe to Binlog (MySQL has canal . As for Oracle, you should write it yourself. It then subscribes to the data through the middleware and writes to the new library.
(4) Inspection consistency, no problem on the cut library.
Step (3) ~ Step (4) flowchart is as follows

How to test data consistency

Here is a brief introduction, the length of this article is too long, everyone in the heart of the line.
(1) The prior quantity is consistent, because the number of tests is relatively fast.
There are two ways to verify a specific field:
(2.1) One way is to verify that only the key fields are consistent.
(2.2) Another is, take 50 at a time (not necessarily 50, specific, I just for example), and then like a spelling string, together. Encrypt with MD5 to get a string of values. The new library does the same thing, and gets a string of values that compares the two values. If consistent, continue to compare the next 50 data. If inconsistencies are found, the dichotomy is used to determine the inconsistent data in 0-25, or 26-50. And so on, find inconsistent data, to record.
OK, verbose finished.