一.直接執行 alter table XXX ……

1.按照原始表（original_table）的表結構和DDL語句，新建一張不可見的臨時表。

2.在原表上加write lock，此時對原表的所有U D I DDL 都是阻塞的。

3.執行insert into tmp_table select * from oldtable;

4.rename oldtable 和 tmp_table，再drop oldtable。

5.釋放 write lock。

二.pt-online-schema-change

1.如果存在外來鍵，根據alter-foreign-keys-method引數的值，檢測外來鍵相關的表，做相應設定的處理。

2.建立一個和你要執行 alter 操作的表一樣的新的空表結構(是alter之前的結構)。

3.在新表執行alter table 語句，

4.在原表中建立觸發器（3個）三個觸發器分別對應insert,update,delete操作

5.從原表複製資料到臨時表，複製過程中透過原表上的觸發器在原表進行的寫操作都會更新到新建的臨時表。

6.Rename 原表到old表中，在把臨時表Rename為原表，預設最後將原表刪除，將原表上所建立的觸發器刪除。

限制：

1.該工具所適用的表必須是單一列的主鍵或者單一唯一鍵。

2.有外來鍵參照的表要修改則不成功，需要指定引數--alter-foreign-keys-method=auto|rebuild_constraints|drop_swap|none

3.要修改的表上不能有trigger，否則修改失敗。

報錯如下：

1.DBD::mysql::db selectall_arrayref failed: Lost connection to MySQL server during query [for Statement "SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE constraint_schema='test' AND referenced_table_name='tool2'"] at /usr/bin/pt-online-schema-change line 9749.

2.You did not specify --alter-foreign-keys-method, but there are foreign keys that reference the table. Please read the tool's documentation carefully.

3.[root@mysql3-1 ~]# /usr/bin/pt-online-schema-change --alter='add column title varchar(30)' --execute D=test,t=tool3_for --set-vars innodb_lock_wait_timeout=50

The table `test`.`tool3_for` has triggers. This tool needs to create its own triggers, so the table cannot already have triggers.

pt-online-schema-change 執行結果如下：

[root@mysql3-1 ~]# /usr/bin/pt-online-schema-change --alter='add column title varchar(30)' --execute D=test,t=tool3 --set-vars innodb_lock_wait_timeout=50

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 `test`.`tool3`...

Creating new table...

Created new table test._tool3_new OK.

Altering new table...

Altered `test`.`_tool3_new` OK.

2014-02-19T22:59:26 Creating triggers...

2014-02-19T22:59:26 Created triggers OK.

2014-02-19T22:59:26 Copying approximately 2 rows...

2014-02-19T22:59:26 Copied rows OK.

2014-02-19T22:59:26 Swapping tables...

2014-02-19T22:59:26 Swapped original and new tables OK.

2014-02-19T22:59:26 Dropping old table...

2014-02-19T22:59:26 Dropped old table `test`.`_tool3_old` OK.

2014-02-19T22:59:26 Dropping triggers...

2014-02-19T22:59:26 Dropped triggers OK.

Successfully altered `test`.`tool3`.

-------------------------------------------------------------------------

--set-vars innodb_lock_wait_timeout=50 # 因為此工具設定了一些引數的預設值，此值預設是1.

Creating triggers...：三個觸發器：insert 、update、delete 。

三.解析XtraBackup備份MySQL的原理和過程:

XtraBackup在啟動的時候會記錄一個LSN(logsequence number)，然後就把所有的Innodb資料檔案(共享表空間和ibd)複製出來，這樣複製出來的資料檔案是不一致的，但是XtraBackup會在後臺執行一個程式把所有對redo log file的修改記錄下來，只要有了這個資料，就能進行崩潰恢復。只所以要額外記錄下來，是因為MySQL自身的redo log file是可重用的。

如果用innobackupex 指令碼，剛才的步驟完成以後，innobackupex就會去備份MyISAM表和.frm檔案，這時要保證資料的一致性就會先鎖表了，透過FLUSH TABLES WITHREAD LOCK命令鎖表然後把檔案複製出來，再釋放掉這個鎖。

在恢復資料的時候，要經過prepare(recovery)和restore兩個步驟。在prepare結束以後，Innodb的表恢復到了複製Innodb檔案結束的時間點，這個時間點也就是鎖表複製MyISAM表的起點，所以最終資料是一致的。一般我們在恢復的時候執行兩次prepare，是因為第二次prepare會幫助我們生成redo log檔案,從而加快MySQL資料庫啟動的速度。

10701 03:29:39 innobackupex: Starting to lock all tables...

>> log scanned up to (2371742526)

110701 03:29:51 innobackupex: All tables locked and flushed to disk

110701 03:29:51 innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI,

innobackupex: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files in

上面是否需要最後備份myisam表時執行FLUSH TABLES WITHREAD LOCK 加鎖還有待原始碼考證，這是最坑爹的地方！！若果是這樣的話，全庫加全域性讀鎖，MyISM表多了對MySQL的寫入等就影響大了。

*XtraBackup 執行過程：

wKioL1ME19LTCqShAANLHOFKoOw743.jpg

wKiom1ME1_fxnT24AAL7EB1x9KA528.jpg

wKioL1ME19KQzy1EAAKtprPDUp4257.jpg

工作原理XtraBackuppt-online-schema-chamysql

pt-online-schema-change和XtraBackup的

相關文章