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

This article mainly explains "MYSQL modifies the table structure gh-ost". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "MYSQL modify table structure gh-ost" it!

In fact, PT tools have been used in MYSQL for "half a century". The main reason for its fame is because of pt-osc. If you don't know, you have really used MYSQL. In fact, there are two other FB-OST, GH-OST.

In fact, although PT-OSC has been used for so many years, it still has some problems.

PT-OSC

1 some operations will cause high load of write operations

2 it is possible to fail in the process of changing the name between the original table and the new table (although this is rare, it may exist)

3 many requirements, primary key (unique), the table has a foreign key when you need to add parameters, and there may still be problems, which may lead to master-slave delay, the table is not recommended by other trigger, PTOSC is to use update, insert, delete triggers to solve the problem of data synchronization between the original table and the new table.

4 for large tables, the main business list, still do not dare to do business time, but also to non-business or trough period, to do.

Although there are so many problems, it took many years. FB-OST has not studied it, but the principle is similar. (FB-OST)

GH-OST is actually one of the three, with different principles and a bit of brainstorming. The developer is a DBA with 15 years of experience.

The following is the developer's self-introduction at the GITHUB conference

After considering the shortcomings of the above two tools, I used binary log, and although I was also inspired by FB-OST, the advantage of my design here over the above-mentioned tools is that the data source of my new table is not from tigger but from binlong. You can think about it, if my colleague changes 15 tables, how much trigger will be generated, how much connections will be consumed, the workload of the system will be very heavy, MYSQL does not like this, and using binlog no matter how many tables he modifies, it is a serialization thing for MYSQL, MYSQL likes serialization things, so it will not generate more load on the system.

Summarize the following advantages of using BINOG

1 binlary logs can be read from anywhere, and GH-OST is equivalent to a slave node

2 gh-ost controls the entire data flow to avoid sudden and uncontrollable incremental writes

3 gh-ost has been decoupled from master node workload

4 gh-ost is designed according to the principle of writing in order, which avoids locks completely.

To server, it is a single connection.

(5) the incremental calculation method of data is simple.

The above three diagrams are a good illustration of why gh-ost is better than other tools. You can read data from the library, write to master, I can read it on master, then write on master, read on slave, and change on slave.

And gh-ost can also do real tests instead of dry-run.

Another advantage is that when GH-OST executes, it can stop the executing task according to the status of master, and wait until the load of master becomes normal, then continue to deal with the delayed work according to BINLOG. So GH-OST is a secure and trusted tool.

(end)

-

Of course, this tool also has Chinese characteristics.

-

In order to use the tool itself, the MYSQL must support binlog and must be opened.

Log-bin=mysql-bin

Binlog-format=ROW

Log-slave-updates=ON

Here is a small experiment.

Gh-ost-allow-on-master-assume-rbr-exact-rowcount

-critical-load Threads_running=400-critical-load-hibernate-seconds 60

-database employees-max-load Threads_running=100

-nice-ratio 0.1-chunk-size 5000-ask-pass-table employeess

-user ghost-host 192.168.198.81

-alter 'add COLUMN add_column varchar (2000)'

-verbose-execute 2 > & 1 | tee gh-ost.log

There is no problem adding a large field.

In the program, the following section selects the U D I operations that need to be synchronized from binlog

Create a hidden devil table

By reading part of the source code, the insert of the password uses insert DUPLICATE KEY to insert data.

In fact, from the design point of view, the author's idea is very interesting, because during the period from copying the data of the original table to the end, the modification in this table cannot be applied, but the BINLOG can record 100% of the changes in the data of this table. As long as you start copying the table from the time point to the end of the copy, extract the data in the binlog, and then operate on the new table. After the completion of the replacement of the two tables rename, to achieve the same effect as the original trigger. Taking advantage of the sequence, stability and accuracy of BINLOG, the performance problem of trigger is solved.

What is more interesting is that gh-ost can modify some configurations during the operation of the program.

For example, in the previous example, you can use the following example to type some parameters into the running command

Echo 'dml-batch-size=100' | nc-U / tmp/gh-ost.employees.employeess.sock

Finally, let's talk about several important parameters.

-allow-master-master runs in the main library

-allow-nullable-unique-key if it is the only index in the table, NULL is not allowed. Here, if the situation exists, give this value and the program can continue to run.

-assume-master-host string, when your current situation is the master's situation,

-assume-rbr set this flag to avoid restarting replication, and you can continue to use gh-ost without super privileges

-number of rows to be processed by chunk-size int at a time

-concurrent-rowcount calculates the lines that require copy

-critical-load sets the maximum threshold

-critical-load-hibernate-seconds how many seconds will the system stop operation when the load value is reached

-critical-load-interval-millis sets how long it takes to retry when the critical value is reached

-cut-over selects the type of switch between the old and new tables

-discard-foreign-keys ignores foreign keys. When operating, it should be noted that if the table has foreign keys, the new table will not create foreign keys.

-how many DML does dml-batch-size handle in a single task? default is 10.

-exact-rowcount gets the exact number of rows of the table

-if initially-drop-ghost-table is run many times and the last existing ghost table has been retained, select this parameter, and the tables that have not been cleaned before will be cleaned up during the operation. Use with caution.

-initially-drop-old-table is the same as the above table. Delete the old table left by the last operation.

-initially-drop-socket-file if you need to change a parameter similar to the one above, then you need to specify this parameter

-the time that nice-ratio float needs to wait between each operation

-the path where throttle-additional-flag-file saves the settings of throttle

-throttle-control-replicas detects those slave libraries that need to be detected with delay

Finally, end with a command, according to the command to comment on the role of some special lines

Gh-ost\

-allow-on-master\

-max-load=Threads_running=25\ stops when the threads_runing threshold is exceeded

-chunk-size=1000\ 1000 line batch processing

-throttle-control-replicas= "192.168.56.144"\ check this slave library

-max-lag-millis=1500\ threshold of delay time

-user= "ghost"\

-password= "ghost"\

-host=192.168.56.145\

-database= "mysqlslap"\

-table= "T1"\

-verbose\

-alter= "add column whatever6 varchar (50)"\

-cut-over=default\

-default-retries=120\

-switch-to-rbr\

-panic-flag-file=/tmp/ghost.panic.flag\ when the file is created, the work stops immediately

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

Switching is not allowed when this file exists, and table switching can only begin when the file disappears.

-execute

At this point, I believe you have a deeper understanding of "MYSQL modify table structure gh-ost". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.