Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MySQL Online DDL tools of pt-online-schema-change     - How to use jgit to manage Git submodule (Linux)

- The user how to install Notepadqq 0.41.0 under ubuntu and debian (Linux)

- Developing a Web server yourself (Server)

- Linux system Iptables Firewall User Manual (Linux)

- The ActiveMQ JMS installation and application examples for Linux (Linux)

- Systemd on RHEL7 (Linux)

- Download Google Android source code under Ubuntu 4.4 (Linux)

- GO five stages of language learning (Programming)

- Ubuntu 15.04 installation Powercommands 2.0 (Linux)

- Linux system with a firewall to prevent the DOS attack (Linux)

- Linux Detailed instructions alias settings (Linux)

- Ubuntu and derived versions of the user how to install G Mic 1.5.8.5 (Linux)

- CentOS5 installation Nodejs (Linux)

- To configure and use multiple Tomcat under Linux environment (Server)

- Enable Intel Rapid Start in GNU / Linux (Linux)

- Security: Unix operating system intrusion tracking Strikes Back (Linux)

- Unetbootin make use U disk loading Linux system (Linux)

- Oracle 11g partition maintenance (Nice) - Truncating And Partitions (Database)

- Hadoop2.6.3 build clusters and the development of MapReduce WIN7 by Eclipse on Linux demo (Server)

- Let CentOS6 yum upgrade to support more source rpm package (Linux)

 
         
  MySQL Online DDL tools of pt-online-schema-change
     
  Add Date : 2018-11-21      
         
       
         
  MySQL DDL:
DDL is a way to make all MySQL dDBA criticized a function, because in MySQL when the table dDDL, will lock the table, when the table is relatively small such as less than 1W row, the smaller the impact on the front end, when thousands of levels of experience table, it will affect the front-end application writes to the table!

InnoDB engine through the following steps to carry out the DDL:
1, according to the original table (original_table) DDL statements and table structure, create a temporary table invisible (tmp_table)
2, plus write lock on the original table, blocking all update operations (insert, delete, update, etc.)
3, the implementation insert into tmp_table select * from original_table
4, rename original_table and tmp_table, the last drop original_table
5. Release write lock.
You can see when InnoDB DDL execution, the original table can only be read but not written. To this end perconal launched a tool pt-online-schema-change, which is characterized by the modification process will not cause obstruction to read and write.

working principle:
If the table has a foreign key, unless --alter-foreign-keys-method to specify a particular value, otherwise the tool will not be executed.
1 Create a table and you want to perform the same empty table structure alter operation.
2 Run the table structure changes, and then modify the table structure of the data from the original table to copy to the rear table,
3 create a trigger on the original table to copy data in the process, an update operation updates the original table to the new table.
  Note: If the table is already defined trigger the tool will not work.
After 4 copy is complete, rename table instead of the original table new table, delete the original default table.


Usage introduction:
pt-online-schema-change [OPTIONS] DSN
You can view the self-help options, DNS databases and tables you want to operate. There are two parameters need to explain:
--dry-run
 This argument does not establish a trigger, not a copy of the data, it will not replace the original table. Just create a new table and change.
--execute
Like this effect parameters of the previous works presented, we will create a trigger, to ensure that the latest changes will affect the data to the new table. NOTE: If you do not add this parameter, the tool will quit after performing some checks.


Dependencies:
Operating table must have a primary key or the following error message:
[Root @ linuxidc ~] # pt-online-schema-change -u root -plinuxidc -h127.0.0.1 --alter = 'add column vname varchar (20)' --execute D = linuxidc, t = linuxidc
No slaves found. See --recursion-method if host linuxidc.com has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `linuxidc`.`linuxidc` ...
Creating new table ...
Created new table linuxidc._linuxidc_new OK.
Altering new table ...
Altered `linuxidc`.`_linuxidc_new` OK.
2016-01-08T17: 51: 43 Dropping new table ...
2016-01-08T17: 51: 43 Dropped new table OK.
`Linuxidc`.`linuxidc` was not altered.
The new table `linuxidc`.`_linuxidc_new` does not have a PRIMARY KEY or a unique index which is required for the DELETE trigger.


Field vname not added successfully!
mysql> show create table linuxidc G
*************************** 1. row ******************** *******
      Table: linuxidc
Create Table: CREATE TABLE `linuxidc` (
  `Actor_id` smallint (8) unsigned NOT NULL DEFAULT '0',
  `First_name` varchar (45) NOT NULL,
  `Last_name` varchar (45) NOT NULL,
  `Last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE = InnoDB DEFAULT CHARSET = utf8


Add a primary key:
mysql> alter table linuxidc modify actor_id smallint (8) unsigned primary key;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0


[Root @ linuxidc ~] # pt-online-schema-change -u root -plinuxidc -h127.0.0.1 --alter = 'add column vname varchar (20)' --execute D = linuxidc, t = linuxidc
No slaves found. See --recursion-method if host linuxidc.com has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `linuxidc`.`linuxidc` ...
Creating new table ...
Created new table linuxidc._linuxidc_new OK.
Altering new table ...
Altered `linuxidc`.`_linuxidc_new` OK.
2016-01-08T17: 57: 09 Creating triggers ...
2016-01-08T17: 57: 09 Created triggers OK.
2016-01-08T17: 57: 09 Copying approximately 200 rows ...
2016-01-08T17: 57: 09 Copied rows OK.
2016-01-08T17: 57: 09 Swapping tables ...
2016-01-08T17: 57: 09 Swapped original and new tables OK.
2016-01-08T17: 57: 09 Dropping old table ...
2016-01-08T17: 57: 09 Dropped old table `linuxidc`.`_linuxidc_old` OK.
2016-01-08T17: 57: 09 Dropping triggers ...
2016-01-08T17: 57: 09 Dropped triggers OK.
Successfully altered `linuxidc`.`linuxidc`.

 


mysql> show create table linuxidc G
*************************** 1. row ******************** *******
      Table: linuxidc
Create Table: CREATE TABLE `linuxidc` (
  `Actor_id` smallint (8) unsigned NOT NULL,
  `First_name` varchar (45) NOT NULL,
  `Last_name` varchar (45) NOT NULL,
  `Last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `Vname` varchar (20) DEFAULT NULL,
  PRIMARY KEY ( `actor_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8

Add multiple fields:
[Root @ linuxidc ~] # pt-online-schema-change -u root -plinuxidc -h127.0.0.1 --alter = 'add column aname varchar (20), add column bname varchar (30)' --execute D = linuxidc, t = linuxidc
No slaves found. See --recursion-method if host linuxidc.com has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `linuxidc`.`linuxidc` ...
Creating new table ...
Created new table linuxidc._linuxidc_new OK.
Altering new table ...
Altered `linuxidc`.`_linuxidc_new` OK.
2016-01-08T18: 04: 25 Creating triggers ...
2016-01-08T18: 04: 25 Created triggers OK.
2016-01-08T18: 04: 25 Copying approximately 200 rows ...
2016-01-08T18: 04: 25 Copied rows OK.
2016-01-08T18: 04: 25 Swapping tables ...
2016-01-08T18: 04: 26 Swapped original and new tables OK.
2016-01-08T18: 04: 26 Dropping old table ...
2016-01-08T18: 04: 26 Dropped old table `linuxidc`.`_linuxidc_old` OK.
2016-01-08T18: 04: 26 Dropping triggers ...
2016-01-08T18: 04: 26 Dropped triggers OK.
Successfully altered `linuxidc`.`linuxidc`.


mysql> show create table linuxidc G
*************************** 1. row ******************** *******
      Table: linuxidc
Create Table: CREATE TABLE `linuxidc` (
  `Actor_id` smallint (8) unsigned NOT NULL,
  `First_name` varchar (45) NOT NULL,
  `Last_name` varchar (45) NOT NULL,
  `Last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `Vname` varchar (20) DEFAULT NULL,
  `Aname` varchar (20) DEFAULT NULL,
  `Bname` varchar (30) DEFAULT NULL,
  PRIMARY KEY ( `actor_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8

 


Delete field:
[Root @ linuxidc ~] # pt-online-schema-change -u root -plinuxidc -h127.0.0.1 --alter = 'drop column aname, drop column bname' --execute D = linuxidc, t = linuxidc
No slaves found. See --recursion-method if host linuxidc.com has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `linuxidc`.`linuxidc` ...
Creating new table ...
Created new table linuxidc._linuxidc_new OK.
Altering new table ...
Altered `linuxidc`.`_linuxidc_new` OK.
2016-01-08T18: 05: 45 Creating triggers ...
2016-01-08T18: 05: 45 Created triggers OK.
2016-01-08T18: 05: 45 Copying approximately 200 rows ...
2016-01-08T18: 05: 45 Copied rows OK.
2016-01-08T18: 05: 45 Swapping tables ...
2016-01-08T18: 05: 45 Swapped original and new tables OK.
2016-01-08T18: 05: 45 Dropping old table ...
2016-01-08T18: 05: 45 Dropped old table `linuxidc`.`_linuxidc_old` OK.
2016-01-08T18: 05: 45 Dropping triggers ...
2016-01-08T18: 05: 46 Dropped triggers OK.
Successfully altered `linuxidc`.`linuxidc`.

 


Add Index:
[Root @ linuxidc ~] # pt-online-schema-change -u root -plinuxidc -h127.0.0.1 --alter = 'add key index_first (first_name)' --execute D = linuxidc, t = linuxidc No slaves found . See --recursion-method if host linuxidc.com has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `linuxidc`.`linuxidc` ...
Creating new table ...
Created new table linuxidc._linuxidc_new OK.
Altering new table ...
Altered `linuxidc`.`_linuxidc_new` OK.
2016-01-08T18: 06: 38 Creating triggers ...
2016-01-08T18: 06: 38 Created triggers OK.
2016-01-08T18: 06: 38 Copying approximately 200 rows ...
2016-01-08T18: 06: 38 Copied rows OK.
2016-01-08T18: 06: 38 Swapping tables ...
2016-01-08T18: 06: 38 Swapped original and new tables OK.
2016-01-08T18: 06: 38 Dropping old table ...
2016-01-08T18: 06: 38 Dropped old table `linuxidc`.`_linuxidc_old` OK.
2016-01-08T18: 06: 38 Dropping triggers ...
2016-01-08T18: 06: 38 Dropped triggers OK.
Successfully altered `linuxidc`.`linuxidc`.
     
         
       
         
  More:      
 
- Git uses Details (Linux)
- Diagnose and resolve the SSH connection slow (Linux)
- Vim copy and paste register (Linux)
- Java thread pool: ExecutorService, Executors (Programming)
- Free compiler install MySQL-5.6.14 (Database)
- C ++ sequence containers basics summary (Programming)
- Bug tracking library after FastJson omitted the decimal point 0 (Programming)
- How MAT Android application memory leak analysis (Programming)
- Mac OS X systems create Ubuntu USB boot disk for the Mac (Linux)
- Examples of Python any parameters (Programming)
- C # how to generate a folder or file automatically rename (Programming)
- MySQL stored procedures execute dynamic sql statement (Database)
- Java development environment to build under Ubuntu (Linux)
- Source install Python3.4 on CentOS (Linux)
- Ubuntu Series Installation Docker (Linux)
- Use exp exported EXP-00091 error (Database)
- Linux security configuration (Linux)
- CentOS installation pycurl (Linux)
- Linux system security knowledge (Linux)
- Teach you how to protect the Linux operating system security tips (Linux)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.