"Go" MySQL Tens data table structure modification

When you need to perform DDL operations on a table, such as indexing, adding or deleting columns, the data volume is not affected by the direct online modification table structure.
The table structure cannot be modified directly online when the table reaches millions and tens of thousands of data

Here are the specific procedures:
1. Backup Data
SELECT * from Ih_order into outfile '/bak/order.txt ';
Mysql> SELECT * from Ih_order to outfile ' d:/bak/order.txt ';
Query OK, 10001000 rows affected (1 min 30.18 sec)

2. Copying the physical structure of the original table
CREATE table Ih_order_bak like Ih_order;

3, the implementation of table structure modification, here is to increase the index
ALTER TABLE ' Ih_order_bak ' Add index (' Consulate ');

4. Import the original table data into a new table
mysql> INSERT INTO Ih_order_bak select * from Ih_order;
Query OK, 10001000 rows affected (6 min 10.30 sec)
records:10001000 duplicates:0 warnings:0

5. Delete the original table
drop table Ih_order;

6, new table rename the original table
Rename table Ih_order_bak to Ih_order;


Attention:
1, this method will affect the operation of the business, we recommend the use of online DDL tools: Pt-online-schema-change, and at low traffic
2, most of the ALTER TABLE operation will involve lock-->copy to new table-->rename-->unlock process, the lock table time is very long, and ALTER TABLE's process can not be kill, Once executed, it cannot be rolled back.
In mysql5.5 and previous versions, performing an alter operation on a large table (over millions of records) in a running production environment is a difficult task. Because the table and lock table will be rebuilt, affecting the user's use.
Starting with mysql5.6, the Online DDL feature was introduced. Many kinds of ALTER TABLE operations have been enhanced to avoid copying tables and lock tables, allowing the Select,insert,update,delete statement to run while running the alter operation. So in the latest version, we can suppress file copies and locking by using the algorithm and lock options.
But even in mysql5.6, there are still some alter operations (adding/removing columns, adding/Removing primary keys, changing data types, etc.) need to be rebuilt

"Turn from" above: http://blog.csdn.net/nuli888/article/details/52443165

Here's how I used it:

1. Rename the cousin and complete it instantly. For example, table changed to Table_bak

2. Create a new cousin. such as table

3. If necessary, gradually write the cousin data to the new table. Example: INSERT INTO table select * from Table_bak;

Cond

"Go" MySQL Tens data table structure modification