Shulou(Shulou.com)06/01 Report--

OS:centos 6.5

DB:percona server 5.6.32

Gh-ost:1.0.21

DB-M:192.168.128.128

DB-S: 192.168.128.129

Brief introduction:

Gh-ost is an open source tool for github to execute sql online. Can be used to change the structure of online tables (especially large tables, for the following reasons)

At present, there are several common ways to change the structure of on-line tables:

1. Small table: online ddl (5.6 and later)

Create a temporary table as the changed table structure, update the temporary table data, and record the new data of the original table into the alter log,rename in memory.

two。 Big table: pt-ost tool. Trigger mechanism

3. Big watch: gh-ost. To create a temporary table No trigger, according to binlog mechanism

Test:

Usertb table under test library (1000W)

Gh-ost Test:

Gh-ost\

-- max-load=Threads_running=25\

-- critical-load=Threads_running=64\

-- chunk-size=1000\

-- throttle-control-replicas= "192.168.128.129virtual 3306"\

-- max-lag-millis=1500\

-- initially-drop-old-table\

-- initially-drop-ghost-table\

-- initially-drop-socket-file\

-- ok-to-drop-table\

-- conf= "/ etc/my.cnf"\

-- host= "192.168.128.129"\

-- port=3306\

-user= "root"\

-password= "thy"\

-database= "test"\

-table= "usertb"\

-- verbose\

-alter= "add ps_id int (10) default null"\

-- switch-to-rbr\

-- allow-master-master\

-- cut-over=default\

-- default-retries=120\

-- panic-flag-file=/tmp/ghost.panic.flag\

-- postpone-cut-over-flag-file=/tmp/ghost.postpone.flag\

-- execute

In the course of execution

128:

129:

Finally:

Conclusion: as can be seen from the screenshot, the principle of gh-ost is to make use of the working mode of Mmurs and binlog, which minimizes the intrusion into the main database and the protection of MDL to the source data in the non-trigger mechanism, and can be considered as a tool for large table ddl in the later stage.

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.