Teach you to implement MySQL table data Migration automation

I. BACKGROUND

Before I wrote about SQL Server data Migration Automation article: SQL Server database Migration folk prescription, in the previous article designed a temporary table, this temporary table records the relocation configuration information, using a stored procedure to read this table for data migration, The stored procedure is called again by a job iteration.

In this MySQL actual combat, my database has done 4 slices, distributes in the different 4 machines, each machine data quantity has 170 million (1.7*4=6.8 billion), occupies the space 260G (260*4=1040g), this migration's goal is deletes some historical records, Reduce database pressure, some people say this why not use table partitioning it? This has something to do with our business logic. Cannot use table partitioning, as to why, reference reading: MySQL table partitioning combat, where the most important is the only index problem, extended reading: MySQL when a bulk insert encounters a unique index, This article needs to know some knowledge of the MySQL timer: the MySQL timer events

The most important difference between this article and SQL Server Database migration is that MySQL events are not serial, when the job call stored procedures have not been completed, but again to the scheduled time, MySQL will not wait for the last job completed before scheduling, As a result, repeated calls are read to the same data, and SQL Server does not have the above problem.

Second, design ideas

1. Create a temporary table Tempblog_log, which holds the ID starting and ending values for each transfer data, and the start and end times of the relocation; (this ID is the primary key for the table we want to migrate, the self-added field, the unique identifier)

2. Create a stored procedure InsertData (), which is used to insert records in the Tempblog_log table, which is created because MySQL is a little different from SQL Server, MySQL does not support anonymous stored procedures, SQL Server to execute SQL directly, no need to create a stored procedure for these SQL, this is the anonymous stored procedure;

3. Create a stored procedure moveblogdata (), which is used to read records in the Tempblog_log table, and then bulk transfer the Bloga data to the BLOGB; This is the core logic, which solves the problem of the timer repetitive dispatch, see the explanation of the code in detail.

4. Create a timer e_blog, this timer calls the stored procedure Moveblogdata (), but there are duplicate scheduling problems, only through the stored procedure Moveblogdata () control.

Iii. Characteristics of migration automation

1. The design is adapted to the migration of large data;

2. Can minimize downtime (in the process of transfer Bloga or has been in the data, but in the last part of the data need to be a short time to stop the storage operation);

3. Can prevent the MySQL timer repeated implementation of the problem;

4. Can monitor the progress of data transfer in real time;

5. Data migrations may take several days to ensure that BLOGB data is infinitely close to bloga data;

Four, code Analysis

(i) Create temporary tables Tempblog_log

--Create TABLE creation table
Tempblog_log (
    Beginid int not NULL,
    Endid int. NOT NULL,
    isdone BIT DEFAULT B ' 0 ' not NU LL,
    begintime datetime default NULL,
    endtime datetime default NULL,
PRIMARY KEY (Beginid) 
);

The following is a field explanation of the table structure:

1) Beginid, Endid are the primary key values of the ServerA migration table, Beginid represents the starting value of data migration, Endid represents the end of a data migration, the difference of two values is the data transfer data volume;

2) Isdone Indicates whether the data has been successfully transferred;

3) BeginTime Indicates the start time of the transfer, Endtime indicates the end time of the transfer, and these two fields set the default value NULL is critical, which is the basis for the following to determine whether to repeat the execution;

(ii) Create stored Procedures InsertData ()

--Stored procedure
DELIMITER $$ use
' DataBaseName ' $$
DROP PROCEDURE IF EXISTS ' insertdata ' $$
    
CREATE definer= ' root ' @ '% ' PROCEDURE ' InsertData ' ()
BEGIN
    DECLARE ids_begin,ids_end,ids_increment INT;
    Set ids_begin=130000000;--need to transfer the start ID value
    set ids_end=210000000;--need to transfer the end ID value
    set ids_increment=200000;--the number of IDs per transfer While
    Ids_begin < ids_end 
        do INSERT into Tempblog_log (beginid,endid) VALUES (ids_begin,ids_begin+ids_increment);
        SET Ids_begin = Ids_begin + ids_increment;
    End While; 
end$$
        
DELIMITER;

Anonymous stored procedures are not supported in MySQL, so in order to insert records in a temporary table, you can create only one stored procedure, which is a good example if you have not written a tempblog_log stored procedure for MySQL.

1) in order to be able to use the MySQL separator in the stored procedure ";", DELIMITER $$ says you use "$$" as a separator, you can also "//";

2 Define variables, you need to define all the variables, and then the assignment, otherwise it will be an error, which is different from SQL Server;

3 The while condition is followed by a do and ends with a while;

4 As the end of the stored procedure, again appear "$$" expression has ended, followed by a "$$" to form a whole, process, and reset ";" as a separator;

5 Execute Call InsertData (), invoke the above stored procedure, insert the data, the result of the call is as shown in the following figure Figure1:

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/

(Figure1: Pre-transfer State)