pt-online-schema-change和XtraBackup的工作原理-原创手记-慕课网

一.直接执行 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