How to use MySQL pt-online-schema-change tools

The OSC is one of the more familiar tools for DBAs and the most heavyweight tool in the toolkit of PT.
After mysql-5.6 support OLDDL, most people may feel that the tool has no meaning, in fact, in some special circumstances, this tool is very useful.
In addition to introducing how the ordinary young people use OSC, this article introduces a literary youth to use the OSC method, which is used to achieve master to slave data differential recovery.

+++++++++++++++++++++++++++ Common youth Dividing line +++++++++++++++++++++++++++++++++++++++
This section is transferred from: http://www.dataguru.cn/article-3460-1.html
The InnoDB engine is currently based on the following steps for DDL:
1 Create an invisible temporary table (tmp_table) According to the table structure and DDL statement of the original table (original_table)
2 Write lock on the original table to block all update operations (INSERT, delete, UPDATE, etc.)
3 Execute INSERT INTO tmp_table SELECT * original_table
4 Rename original_table and tmp_table, last drop original_table
5 Release write lock.
We can see that the original table can only be read and not written when InnoDB executes the DDL. To this end perconal introduced a tool pt-online-schema-change, which is characterized by the modification process will not cause read and write blocking.
Working principle:
If a table has a foreign key, the tool is not executed unless a specific value is specified by using –alter-foreign-keys-method.
1 Create an empty table structure that is the same as the table you want to perform alter operations on.
2 perform table structure modifications, and then from the data in the original table to the modified table structure,
3 Create triggers on the original table the copy data is updated to the new table during the update operation in the original table.
Note: If the trigger is already defined in the table, the tool will not work.
4 copy is completed, replace the original table with rename table, and delete the original table by default.

Usage Introduction:
Pt-online-schema-change [OPTIONS] DSN
Options are available to view the database and the tables you want to help,dns. Here are two parameters to introduce:
–dry-run
This parameter does not create triggers, does not copy data, and does not replace the original table. Just create and change new tables.
–execute
The effect of this parameter, as described in the previous work, is to create a trigger to ensure that the latest changed data will affect the new table. Note: If you do not add this parameter, the tool exits after performing some checks.

Dependent conditions
The 1 operation table must have a primary key otherwise reported as the following error.

[Root@rac1 bin]#./pt-online-schema-change-u root-h 10.250.7.50-p Yang--alter= ' add column vid int '--execute D=houy I,t=ga
Cannot connect to D=houyi,h=127.0.0.1,p=...,u=root
Cannot chunk the original table ' Houyi '. ' ga ': There is no good index and the table is oversized. At./pt-online-schema-change Line 5353.
Test Example:
1 Adding fields

[Root@rac1 bin]# ./pt-online-schema-change-u root-h 10.250.7.50 -P Yang--alter= ' add column vid int '--e Xecute D=houyi,t=ga
Cannot connect to D=houyi,h=127.0.0.1,p=...,u=root
Operation, tries, wait:
  Copy_ Rows, 0.25
  create_triggers, 1
  drop_triggers, 1
  swap_tables, 1
  Update_foreign_keys, 1
Altering ' houyi '. ' ga ' ...
Creating new Table ...
Created new Table Houyi._ga_new OK.
Altering new Table ...
Altered ' Houyi '. ' _ga_new ' OK.
Creating triggers ...
Created triggers OK.
Copying approximately 746279 rows ...
Copied rows OK.
Swapping tables ...
swapped original and new tables OK.
Dropping old table ...
Dropped old table ' Houyi '. ' _ga_old ' OK.
Dropping triggers ...
dropped triggers OK.
successfully altered ' Houyi '. ' GA '.
2 Add index

[Root@rac1 bin]#./pt-online-schema-change-u root-h 10.250.7.50-p Yang--alter= ' add key indx_vid (vid) '--execute D=ho Uyi,t=ga
3 Delete fields

[Root@rac1 bin]#./pt-online-schema-change-u root-h 10.250.7.50-p Yang--alter= ' drop column vid '--execute D=houyi,t =ga
++++++++++++++++++++++++++++ Literary Youth Dividing line +++++++++++++++++++++++++++
The so-called literary usage, is through the OSC to achieve slave and master data differences when the recovery. It is said that this is pt-table-sync to do. But when the table data is very different, using the OSC can be more efficient and simpler and more reliable.
How does the OSC implement master to slave data differential recovery?
Because the OSC principle is to create a new table and use triggers. Then insert the data from the original table into the select from to import the new table. If this time, we change binlog to row format. Then insert Into the record must be the data from the source table. Triggers are also data from the source table recorded in the log when they are in row format. That is, the OSC can be logically, non-blocking, synchronizing data from the source table to all slave.

Pt-online-schema-change–set-vars ' Binlog_format=row ' –alter ' Engine=innodb ' D=test,t=tt1,h=localhost–execute
If you are already working in ROW format, then –set-vars ' Binlog_format=row ' will not be set.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Detailed principle anatomy, transferred from http://hi.baidu.com/ytjwt/item/7ea9c637c29079f6e6bb7a0d
Pt-online-schema-change the implementation core of the online change table structure has the following several processes:
(Note: In the process of modification involves three tables: the original table, Tmp_table, as the original form of the temporary table, old_table in the final rename the original table results table)
1, CREATE table ' $db '. ' $tmp _tbl ' like ' $db '. ' $tbl ' "New tmp_table, table structure with original table
2. Change the table structure to the required table structure on the tmp_table
3. Set up three triggers on the original table, as follows:
(1) Createtrigger Mk_osc_del after DELETE in $table "for each ROW"
"DELETE IGNORE from $new _table" "where$new_table. $chunk _column = old. $chunk _column";
(2) Createtrigger mk_osc_ins after INSERT on $table "for each ROW"
"Replaceinto $new _table ($columns)" "VALUES ($new _values)";
(3) Createtrigger mk_osc_upd after UPDATE on $table "for each ROW"
"REPLACE into $new _table ($columns)" "VALUES ($new _values)";

We can see that these three triggers correspond to the INSERT, UPDATE, delete three actions respectively:
(1) Mk_osc_del,delete operation, we note that Deleteignore, when the new data, we do operation, that is, when the subsequent import process, if the deleted data has not been imported into the new table, then we can not perform operations on the new table, Because in the future import process, the row data in the original table has been deleted, there is no data, then he will not be imported into the new table;
(2) Mk_osc_ins,insert operation, all insert into the replaceinto, in order to ensure the consistency of the data, when a new data is inserted into the original table, if the trigger has not synchronized the original table data to the new table, this data has been imported into the new table, Then we can use Replaceinto to cover, so the data is consistent.
(3) MK_OSC_UPD Update operation, all update is also converted to Replaceinto, because when the row with the new data is not synchronized to the new table, the new table does not exist this record, then we can only insert the data, if you have synchronized to the new table, Then you can also insert the overlay, all the data and the original table is also consistent;
We can also see that the essence of this is also these several replaceinto operations, precisely because these several replaceinto to ensure the consistency of the data
4, copy the original table data to the temporary table, in the script to use the following statement
INSERT IGNORE into $to _table ($columns) "" Select $columns from $from _table "WHERE ($chunks->[$chunkno))" We can see that he is through some Query (basic primary key, unique key value) in batches to import data into the new table, before importing, we can through the parameter –chunk-size to each import row number control, has reduced to the original table lock time, and in the import, we can through the-sleep parameter control, After each chunk import and the next chunk import begins to sleep for a while, the longer it is, the smaller the impact on disk IO
5, Rename the original table to the old table, in the temporary table Rename as the original table,
"RENAME TABLE ' $db '. ' $tmp _tbl ' to ' $db '. ' $tbl '; In the rename process, in fact we will still cause write read blocking, therefore, strictly speaking, our OSC does not have a little impact on the online environment, but because the rename operation is only a process of modifying the name, it will only modify the information of some tables, the basic is instantaneous end, so the impact on the line is not too big
6, clean up the process of no longer used data, such as old table.