MySQL5.6 Online DDL Unlocked table (add field online)

MySQL5.6 Online DDL Unlocked table (add field online)

Answer you have a look
MySQL5.6 Online DDL does not lock the table, now I have a 100 million table, need to add a field, if I let you go to increase this field, you should pay attention to what, how to do?

The operation is as follows:
1. Note Disk space (temporary table directory)
2. Current memory Remaining Amount
3. There are currently no large transactions in the execution
4.innodb_online_alter_log_max_size parameters
5. Then add it from the top and add it to the Lord (do not log binlog), and then open it after processing is completed.

If you first operate directly in the Lord, then the master-slave delay will be very large (in the case of large volume). Because the master-slave copy, the SQL thread from the library is a single process, it receives the main library of the Binlog, to one piece of execution, a SQL stuck, the subsequent SQL will be queued, then synchronization delay

Note:

In previous versions, the InnoDB engine was DDL by the following steps:
1 Create an invisible temporary table (tmp_table) in accordance with the table structure and DDL statements of the original table (original_table)----> CREATE table tmp_table like original_table;
2 Write lock on the original table, block all update operations (INSERT, delete, UPDATE, etc., copy the original table data to the Temp list, and update the index).
3 Execute INSERT INTO tmp_table select * from original_table
4 Rename original_table and tmp_table, final drop original_table
5 Release the Write lock.

If you use third-party tools recommended Pt-online-schema-change

Working principle:
If a table has a foreign key, the tool is not executed unless a specific value is specified with--alter-foreign-keys-method.
1 Judging various parameters, whether the operation conditions are met
2 Create an empty table structure that is the same as the original table (tmp_table)
3 Perform table structure modifications, then copy data from the original table to the new table created above
4 Create 3 triggers on the original table (the name format is the PT_OSC_ library name _ Table name _ operation type), when the copy data process, the original table update operation updated to the new table (Note: If a trigger is already defined in the table, the tool will not work)
CREATE TRIGGER ' Pt_osc_dba_t_del ' after DELETE on ' dba '. ' t ' for each ROW DELETE IGNORE from ' dba '. ' _t_new ' WHERE ' dba '. ' _t _new '. ' id ' <=> old ' id '
CREATE TRIGGER ' pt_osc_dba_t_upd ' after UPDATE on ' dba '. ' t ' for each ROW REPLACE into ' dba '. ' _t_new ' (' id ', ' a ', ' B ', ' C1 ' VALUES (new ' id ', new ' a ', new ' B ', new ' C1 ')
CREATE TRIGGER ' Pt_osc_dba_t_ins ' after INSERT on ' dba '. ' t ' for each ROW REPLACE into ' dba '. ' _t_new ' (' id ', ' a ', ' B ', ' C1 ' VALUES (new ' id ', new ' a ', new ' B ', new ' C1 ')
5 when copy is complete, replace the original table with rename table, and delete the original table by default (you can specify that the parameter does not delete the original table operation)
6 Deleting a Trigger
It is important to note that: If it is a small table: Added directly to the Lord
Shenzhen @ Qiu Army mysql Chinese network 9:46:41

Big table to combine business and point of time to consider whether in the Lord or first add from the

Is this tool only available in Percona db?
Shenzhen @ Qiu Army mysql Chinese network 2015/5/29 10:14:02
Not really.
MySQL Percona mariadb all support
As long as the MySQL version
This tool works best when it comes to maintaining a table.
Regularly defragment MySQL InnoDB table fragments you just said online add field, need to have prerequisites
What are the prerequisites?
Shenzhen @ Qiu Army mysql Chinese network 10:29:32
No, nothing.
Add primary key to be aware
Normal field Nothing

MySQL5.6 Online DDL Unlocked table (add field online)