GH-OST principle

GH-OST principle 1. Architecture diagram of three modes

1. Connect to the slave database and modify it on the master database

This is the default Operating Mode of GH-ost. It will check the slave database status, find the master database of the cluster, and connect to it. It has the least intrusion to the master database. The general steps are as follows:

Create _ xxx_gho and _ xxx_ghc on the master database and modify the _ xxx_gho table structure. Read Binary log events from the slave and apply the changes to the _ xxx_gho table on the master database; read data from the source table on the master database and write it to the _ xxx_gho table: insert into igore .... select; Table switching is completed on the master database;

2. directly modify the master database

Create _ xxx_gho and _ xxx_ghc on the master database and modify the _ xxx_gho table structure. Read Binary log events from the master database and apply the changes to the _ xxx_gho table on the master database; read data from the source table on the master database and write it to the _ xxx_gho table: insert into igore .... select; Table switching is completed on the master database;

3. Modify and test the slave Database

This mode will be modified on the slave database. GH-ost will still be connected to the master database, but all operations are performed on the slave database and will not affect the master database. During the operation, GH-ost will also pause from time to time so that the data in the slave database can be kept up to date. -- Migrate-on-replica option allows GH-Ost to directly modify the table on the slave database. The final switching process is also completed in the normal replication status of the slave database. -- Test-on-replica indicates that the operation is only for testing purpose. Before the final switchover, the replication will be stopped. The original and temporary tables will switch between each other and switch back. Eventually, the original table is not passive. When the master-slave replication is paused, you can check and compare the data in the two tables.

Ii. Principles

1. Check the Verification Environment

Test whether the database is connectable and whether the database exists. Check whether the connected instance has the correct permission. Verify the show grants for CURRENT_USER () BINLOG, including the row format verification and the restart of the replicate original table storage engine after modifying the BINLOG format, foreign keys, trigger check, and row estimation.

2. Create a BINLOG streamer to connect to or from the master database and add a BINLOG listener.

3. Create log table _ xxx_ghc and ghost table _ xxx_gho, and modify the ghost table structure to the latest one.

4. Start data migration: both row copy and BINLOG apply are performed.

1) Minimum value: Select 'id' from darren '. 't4 'order by id' ASC limit 1; 2) maximum value: Select 'id' from darren '. 't4 'order by id' DESC limit 1; 3) Calculate the first chunk: Select 'id' from 'darren '. 't4 'where' id'> = _ binary '1' and 'id' <= _ binary '000000' order by 'id' ASC limit 1 offset 58594 if the last chunk is insufficient 1000, if the preceding SQL query is empty, run: Select 'id' from (select 'id' from 'darren '. 't4 'where' id'> _ binary '123456' and 'id' <= _ binary '123456' order by 'id' ASC limit 58000) select_osc_chunk order by 'id' DESC limit 1; 4) insert data cyclically: insert ignore into 'darren '. '_ t4_gho' ('id', 'name', 'c1') (select 'id', 'name', 'c1' from 'darren '. 't4 'force index ('primary') Where 'id'> = _ binary '1' and 'id' <= _ binary '123' lock in share mode)

4.1. Does rowcopy data conflict with the BINLOG order of the application?

SQL ing during data migration:

Various permutation and combinations of rowcopy and BINLOG applications:

The data migration process involves three operations: A: rowcopy of the original table; B: DML of the application; C: BINLOG is applied to the new table, because the DML Operation Records binglog, therefore, the C operation must be followed by the B operation. There are several combinations:

1. insert operation

BINLOG is the most authoritative. The GH-OST principle is to give priority to BINLOG. Therefore, data is consistent with BINLOG in any order. If rowcopy is followed, ignore is inserted, if BINLOG apply is completed, it will replace.

2. Update/delete operations

For data that has already been rowcopied, the update/delete operation on the original table appears. At this time, all operations are performed through BINLOG apply. Note that the update of BINLOG apply overwrites all columns of a record, so there is no problem of accumulation.

For data that has not been migrated, the update/delete operation on the original table appears. At this time, BINLOG apply for the new table will be empty, and the specific data will be migrated by rowcopy.

In special cases:

After the original table is updated, rowcopy migrates the data before BINLOG apply. When BINLOG event comes, it will be applied again. Is there a problem here? In fact, we migrated the data before applying the BINLOG of GH-ost,

In the binlog SQL ing rule, the insert operation will be replaced by replace again, update will update all rows of the corresponding record, and delete will be empty. The final data is consistent.

4.2 When Will BINLOG Data Synchronization end?

After data is copied, status: alleventsuptolockprocessed: 1533533052229905040 is written to _ xxx_ghc. When this value is filtered by binlogsyncer, all events are applied.

5. Perform the atomic cut-over stage after the copy operation is complete.

5.1) C10: Create Magic Table _ xxx_del. Objective To prevent the RENAME operation and unexpected RENAME operation on the source table and Magic Table _ xxx_del plus write lock 5.2) c11... c19: a new request comes in. The request about the original table is blocked5.3) C20: Run: rename table 't4 'to' _ t4_del ',' _ t4_gho 'to 't4 '; this is blocked, timeout: 3 s. (This step is only after the BINLOG event application is completed) 5.4) check whether there is a blocked rename request through show processlist5.5) C10: delete the Magic Table (only the rename of the block exists in the show processlist file). How can this problem be solved after different stages of the release failure? If 5.1 fails, exit the program, for example, the table is successfully created and the lock fails, exit the program. When the rename request is not locked, the session C10 will die and the lock will be automatically released. At the same time, the rename will fail because of the _ xxx_del, when all requests resume normal rename is blocked, session C10 will die and lock will be automatically released. Similarly, because of the existence of _ xxx_del, rename will fail and all requests will return to normal C20 and die, GH-ost will not capture rename, session C10 will continue to run, release lock, and all requests will return to normal

6. Clear the battlefield

7.1) Disable binlogsyncer connection 7.2) delete source table and _ t4_ghc table

GH-OST principle