MySQL 大表在线DML神器--pt-online-schema-change

原创 2017年07月28日 14:01:22



一个朋友问我在线对大表进行ddl操作,如何做能尽量避免主从延迟以及不影响在线dml操作呢?我想到一个开源的pt-online-schema-change工具,测试了吧,效果还可以。

pt-online-schema-change原理
1、如果存在外键,根据alter-foreign-keys-method参数的值,检测外键相关的表,做相应设置的处理。
2、创建一个新的表,表结构为修改后的数据表,用于从源数据表向新表中导入数据。
3、创建触发器,用于记录从拷贝数据开始之后,对源数据表继续进行数据修改的操作记录下来,用于数据拷贝结束后,执行这些操作,保证数据不会丢失。
4、拷贝数据,从源数据表中拷贝数据到新表中。
5、修改外键相关的子表,根据修改后的数据,修改外键关联的子表。
6、rename源数据表为old表,把新表rename为源表名,并将old表删除。
7、删除触发器。



1,安装Percona

tar -zxvf DBI-1.625.tar.gz
cd DBI-1.625
perl Makefile.PL
make
make install

# 安装DBD-mysql插件

tar -zxvf DBD-mysql-4.023.tar.gz
cd DBD-mysql-4.023
perl Makefile.PL
make
make install

安装percona-toolkit,pt-online-schema-change 是percona里面的组件之一,通常安装好percona-toolkit之后,基本就能直接用:

##Install percona-toolkit
wget percona.com/get/percona-toolkit.tar.gz
tar -zxvf percona-toolkit-2.2.16.tar.gz
cd percona-toolkit-2.2.16
perl Makefile.PL
make
make install


2,常用操作:

2.1 添加字段

命令:

time pt-online-schema-change –host=192.168.121.91 –port=3307 –user=tim –password=”timisgood” –alter=”add column C_N varchar(64)” –execute D=test,t=UC_USER –set-vars innodb_lock_wait_timeout=50 –no-check-replication-filters

执行过程

[root@hch_test_121_91 ~]# time pt-online-schema-change --host=192.168.121.91 --port=3307 --user=tim --password="timisgood" --alter="add column C_N varchar(64)" --execute D=test,t=UC_USER --set-vars innodb_lock_wait_timeout=50 --no-check-replication-filters
No slaves found.  See --recursion-method if host hch_test_121_91 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `test`.`UC_USER`...
Creating new table...
Created new table test._UC_USER_new OK.
Altering new table...
Altered `test`.`_UC_USER_new` OK.
2017-07-06T11:25:34 Creating triggers...
2017-07-06T11:25:34 Created triggers OK.
2017-07-06T11:25:34 Copying approximately 1457112 rows...
Copying `test`.`UC_USER`:  21% 01:49 remain
Copying `test`.`UC_USER`:  42% 01:20 remain
Copying `test`.`UC_USER`:  62% 00:54 remain
Copying `test`.`UC_USER`:  91% 00:11 remain
2017-07-06T11:27:45 Copied rows OK.
2017-07-06T11:27:45 Analyzing new table...
2017-07-06T11:27:45 Swapping tables...
2017-07-06T11:27:45 Swapped original and new tables OK.
2017-07-06T11:27:45 Dropping old table...
2017-07-06T11:27:46 Dropped old table `test`.`_UC_USER_old` OK.
2017-07-06T11:27:46 Dropping triggers...
2017-07-06T11:27:46 Dropped triggers OK.
Successfully altered `test`.`UC_USER`.

real    2m12.995s
user    0m0.869s
sys 0m0.160s
[root@hch_test_121_91 ~]#


2.2 修改字段

命令:

time pt-online-schema-change –host=192.168.121.91 –port=3307 –user=tim –password=”timisgood” –alter=”modify column C_N varchar(128) default 0” –execute D=test,t=UC_USER –set-vars innodb_lock_wait_timeout=50 –no-check-replication-filters

执行过程:

[root@hch_test_121_91 ~]# time pt-online-schema-change --host=192.168.121.91 --port=3307 --user=tim --password="timisgood" --alter="modify column C_N varchar(128) default 0" --execute D=test,t=UC_USER --set-vars innodb_lock_wait_timeout=50 --no-check-replication-filters
No slaves found.  See --recursion-method if host hch_test_121_91 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `test`.`UC_USER`...
Creating new table...
Created new table test._UC_USER_new OK.
Altering new table...
Altered `test`.`_UC_USER_new` OK.
2017-07-05T19:08:46 Creating triggers...
2017-07-05T19:08:46 Created triggers OK.
2017-07-05T19:08:46 Copying approximately 1457112 rows...
Copying `test`.`UC_USER`:  23% 01:36 remain
Copying `test`.`UC_USER`:  51% 00:56 remain
Copying `test`.`UC_USER`:  71% 00:36 remain
Copying `test`.`UC_USER`:  91% 00:11 remain
2017-07-05T19:11:02 Copied rows OK.
2017-07-05T19:11:02 Analyzing new table...
2017-07-05T19:11:02 Swapping tables...
2017-07-05T19:11:02 Swapped original and new tables OK.
2017-07-05T19:11:02 Dropping old table...
2017-07-05T19:11:03 Dropped old table `test`.`_UC_USER_old` OK.
2017-07-05T19:11:03 Dropping triggers...
2017-07-05T19:11:03 Dropped triggers OK.
Successfully altered `test`.`UC_USER`.

real    2m17.788s
user    0m0.839s
sys 0m0.122s
[root@hch_test_121_91 ~]# 


2.3 改字段名

执行命令:

time pt-online-schema-change –host=192.168.121.91 –port=3307 –user=tim –password=”timisgood” –alter=”change C_N C_N_01 varchar(128) default 11” –execute D=test,t=UC_USER –set-vars innodb_lock_wait_timeout=50 –no-check-replication-filters –no-check-alter

执行过程:

 [root@hch_test_121_91 ~]# time pt-online-schema-change --host=192.168.121.91 --port=3307 --user=tim --password="timisgood" --alter="change C_N C_N_01 varchar(128) default 11" --execute D=test,t=UC_USER --set-vars innodb_lock_wait_timeout=50 --no-check-replication-filters --no-check-alter
No slaves found.  See --recursion-method if host hch_test_121_91 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `test`.`UC_USER`...
Renaming columns:
  C_N to C_N_01
Creating new table...
Created new table test._UC_USER_new OK.
Altering new table...
Altered `test`.`_UC_USER_new` OK.
2017-07-05T19:15:03 Creating triggers...
2017-07-05T19:15:03 Created triggers OK.
2017-07-05T19:15:03 Copying approximately 1457112 rows...
Copying `test`.`UC_USER`:  20% 01:57 remain
Copying `test`.`UC_USER`:  40% 01:27 remain
Copying `test`.`UC_USER`:  60% 00:59 remain
Copying `test`.`UC_USER`:  79% 00:30 remain
Copying `test`.`UC_USER`:  98% 00:02 remain
2017-07-05T19:17:38 Copied rows OK.
2017-07-05T19:17:38 Analyzing new table...
2017-07-05T19:17:38 Swapping tables...
2017-07-05T19:17:38 Swapped original and new tables OK.
2017-07-05T19:17:38 Dropping old table...
2017-07-05T19:17:39 Dropped old table `test`.`_UC_USER_old` OK.
2017-07-05T19:17:39 Dropping triggers...
2017-07-05T19:17:39 Dropped triggers OK.
Successfully altered `test`.`UC_USER`.

real    2m36.045s
user    0m1.000s
sys 0m0.158s
[root@hch_test_121_91 ~]# 


2.4 删除字段

执行命令:

time pt-online-schema-change –host=192.168.121.91 –port=3307 –user=tim –password=”timisgood” –alter=”drop column C_N_01” –execute D=test,t=UC_USER –set-vars innodb_lock_wait_timeout=50 –no-check-replication-filters –no-check-alter

执行过程:

[root@rac1 bin]# ./pt-online-schema-change  -uroot  -pxxx  --alter='drop  column  address ' --execute D=test,t=t_xxx_compensate  
[root@hch_test_121_91 ~]# time pt-online-schema-change --host=192.168.121.91 --port=3307 --user=tim --password="timisgood" --alter="drop column C_N_01" --execute D=test,t=UC_USER --set-vars innodb_lock_wait_timeout=50 --no-check-replication-filters --no-check-alter 
No slaves found.  See --recursion-method if host hch_test_121_91 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `test`.`UC_USER`...
Creating new table...
Created new table test._UC_USER_new OK.
Altering new table...
Altered `test`.`_UC_USER_new` OK.
2017-07-06T10:47:33 Creating triggers...
2017-07-06T10:47:33 Created triggers OK.
2017-07-06T10:47:33 Copying approximately 1403100 rows...
Copying `test`.`UC_USER`:  21% 01:47 remain
Copying `test`.`UC_USER`:  39% 01:31 remain
Copying `test`.`UC_USER`:  61% 00:56 remain
Copying `test`.`UC_USER`:  82% 00:26 remain
2017-07-06T10:50:13 Copied rows OK.
2017-07-06T10:50:13 Analyzing new table...
2017-07-06T10:50:13 Swapping tables...
2017-07-06T10:50:13 Swapped original and new tables OK.
2017-07-06T10:50:13 Dropping old table...
2017-07-06T10:50:14 Dropped old table `test`.`_UC_USER_old` OK.
2017-07-06T10:50:14 Dropping triggers...
2017-07-06T10:50:14 Dropped triggers OK.
Successfully altered `test`.`UC_USER`.

real    2m41.533s
user    0m1.013s
sys 0m0.196s
[root@hch_test_121_91 ~]# 


2.5 添加索引

执行命令:

time pt-online-schema-change –host=192.168.121.91 –port=3307 –user=tim –password=”timisgood” –alter=”add index IDX_MOBILE(MOBILE)” –execute D=test,t=UC_USER –set-vars innodb_lock_wait_timeout=50 –no-check-replication-filters –no-check-alter

执行过程:

[root@hch_test_121_91 ~]# time pt-online-schema-change --host=192.168.121.91 --port=3307 --user=tim --password="timisgood" --alter="add index IDX_MOBILE(MOBILE)" --execute D=test,t=UC_USER --set-vars innodb_lock_wait_timeout=50 --no-check-replication-filters --no-check-alter 
No slaves found.  See --recursion-method if host hch_test_121_91 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `test`.`UC_USER`...
Creating new table...
Created new table test._UC_USER_new OK.
Altering new table...
Altered `test`.`_UC_USER_new` OK.
2017-07-06T10:52:51 Creating triggers...
2017-07-06T10:52:51 Created triggers OK.
2017-07-06T10:52:51 Copying approximately 1457112 rows...
Copying `test`.`UC_USER`:  20% 01:58 remain
Copying `test`.`UC_USER`:  37% 01:39 remain
Copying `test`.`UC_USER`:  54% 01:16 remain
Copying `test`.`UC_USER`:  71% 00:47 remain
Copying `test`.`UC_USER`:  87% 00:21 remain
2017-07-06T10:55:48 Copied rows OK.
2017-07-06T10:55:48 Analyzing new table...
2017-07-06T10:55:48 Swapping tables...
2017-07-06T10:55:48 Swapped original and new tables OK.
2017-07-06T10:55:48 Dropping old table...
2017-07-06T10:55:49 Dropped old table `test`.`_UC_USER_old` OK.
2017-07-06T10:55:49 Dropping triggers...
2017-07-06T10:55:49 Dropped triggers OK.
Successfully altered `test`.`UC_USER`.

real    2m58.237s
user    0m1.092s
sys 0m0.184s
[root@hch_test_121_91 ~]# 


2.6 删除索引

执行命令:

time pt-online-schema-change –host=192.168.121.91 –port=3307 –user=tim –password=”timisgood” –alter=”drop index IDX_MOBILE” –execute D=test,t=UC_USER –set-vars innodb_lock_wait_timeout=50 –no-check-replication-filters –no-check-alter

执行过程:

[root@rac1 bin]# ./pt-online-schema-change -uroot  -pxxx --alter='DROP INDEX indx_test' --execute D=test,t=t_xxx_compensate
[root@hch_test_121_91 ~]# time pt-online-schema-change --host=192.168.121.91 --port=3307 --user=tim --password="timisgood" --alter="drop index IDX_MOBILE" --execute D=test,t=UC_USER --set-vars innodb_lock_wait_timeout=50 --no-check-replication-filters --no-check-alter 
No slaves found.  See --recursion-method if host hch_test_121_91 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `test`.`UC_USER`...
Creating new table...
Created new table test._UC_USER_new OK.
Altering new table...
Altered `test`.`_UC_USER_new` OK.
2017-07-06T10:59:15 Creating triggers...
2017-07-06T10:59:15 Created triggers OK.
2017-07-06T10:59:15 Copying approximately 1457112 rows...
Copying `test`.`UC_USER`:  24% 01:30 remain
Copying `test`.`UC_USER`:  42% 01:20 remain
Copying `test`.`UC_USER`:  60% 00:58 remain
Copying `test`.`UC_USER`:  79% 00:31 remain
2017-07-06T11:01:46 Copied rows OK.
2017-07-06T11:01:46 Analyzing new table...
2017-07-06T11:01:46 Swapping tables...
2017-07-06T11:01:46 Swapped original and new tables OK.
2017-07-06T11:01:46 Dropping old table...
2017-07-06T11:01:47 Dropped old table `test`.`_UC_USER_old` OK.
2017-07-06T11:01:47 Dropping triggers...
2017-07-06T11:01:47 Dropped triggers OK.
Successfully altered `test`.`UC_USER`.

real    2m32.376s
user    0m0.944s
sys 0m0.169s
[root@hch_test_121_91 ~]# 


3,总结:

在没有执行sql语句时,mysql在线修改表结构的时间与pt-online-schma-change的基本上相等,无大的差别;pt-online-schma-change方式会占用较多内存,负载也会略高
在mysql预热以后,在线修改表结构的时间,直接修改会比pt-online-schema-change方式略快
在线修改表结构的同时执行mysql语句,mysql直接修改的方式会先修改完表结构再执行sql语句,pt-online-schema-change方式会优先执行sql语句,再复制数据表,复制完毕后再把执行sql语句的结果更新到新表,因此,在时间上,直接修改表结构会比pt-online-schema-change方式,在实际用时上,直接修改会略快

参考文章:https://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html

4,问题一:

[root@hch_test_121_91 percona-toolkit-3.0.3]# time pt-online-schema-change -uroot -p –alter=’add column C_N varchar(64)’ –execute D=test,t=UC_USER
Cannot connect to MySQL: install_driver(mysql) failed: Attempt to reload DBD/mysql.pm aborted.
Compilation failed in require at (eval 13) line 3.

at /usr/local/bin/pt-online-schema-change line 2296

real 0m0.336s
user 0m0.314s
sys 0m0.021s
[root@hch_test_121_91 percona-toolkit-3.0.3]#

解决办法是移走/usr/local/lib64/perl5的目录:
[root@hch_test_121_91 /]# find / -name perl5
/data/backup/perl5
/usr/local/lib64/perl5
/usr/lib64/perl5
/usr/share/swig/1.3.40/perl5
/usr/share/perl5
[root@hch_test_121_91 /]# mv /usr/local/lib64/perl5 /usr/local/lib64/perl5_bak
[root@hch_test_121_91 /]#


5,问题二

[root@hch_test_121_91 /]# time pt-online-schema-change --user=root --alter="add column C_N varchar(64)" --execute D=test,t=UC_USER
Error setting innodb_lock_wait_timeout: DBD::mysql::db do failed: Variable 'innodb_lock_wait_timeout' is a read only variable [for Statement "SET SESSION innodb_lock_wait_timeout=1"].  The current value for innodb_lock_wait_timeout is 50.  If the variable is read only (not dynamic), specify --set-vars innodb_lock_wait_timeout=50 to avoid this warning, else manually set the variable and restart MySQL.

Error setting innodb_lock_wait_timeout: DBD::mysql::db do failed: Variable 'innodb_lock_wait_timeout' is a read only variable [for Statement "SET SESSION innodb_lock_wait_timeout=1"].  The current value for innodb_lock_wait_timeout is 50.  If the variable is read only (not dynamic), specify --set-vars innodb_lock_wait_timeout=50 to avoid this warning, else manually set the variable and restart MySQL.

No slaves found.  See --recursion-method if host hch_test_121_91 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
The original table `test`.`UC_USER` does not exist.

real    0m0.362s
user    0m0.321s
sys 0m0.033s
[root@hch_test_121_91 /]# 


解决办法:添加--set-vars innodb_lock_wait_timeout=50 参数


6,问题三

问题:
[root@hch_test_121_91 /]# time pt-online-schema-change –host=192.168.121.91 –port=3307 –user=root –alter=”add column C_N varchar(64)” –execute D=test,t=UC_USER –set-vars innodb_lock_wait_timeout=50 –no-check-replication-filters
Cannot connect to MySQL: DBI connect(‘test;host=192.168.121.91;port=3307;mysql_read_default_group=client’,’root’,…) failed: Access denied for user ‘root’@’192.168.121.91’ (using password: NO) at /usr/local/bin/pt-online-schema-change line 2296

real    0m0.358s
user    0m0.312s
sys 0m0.039s

解决办法,用户密码不能为null值

[root@hch_test_121_91 /]# time pt-online-schema-change --host=192.168.121.91 --port=3307 --user=tim --password="timisgood" --alter="add column C_N varchar(64)" --execute D=test,t=UC_USER --set-vars innodb_lock_wait_timeout=50 --no-check-replication-filters
No slaves found.  See --recursion-method if host hch_test_121_91 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `test`.`UC_USER`...
Creating new table...
Created new table test._UC_USER_new OK.
Altering new table...
Altered `test`.`_UC_USER_new` OK.
2017-07-05T15:18:46 Creating triggers...
2017-07-05T15:18:46 Created triggers OK.
2017-07-05T15:18:46 Copying approximately 1381835 rows...
Copying `test`.`UC_USER`:  20% 01:56 remain
Copying `test`.`UC_USER`:  41% 01:23 remain
Copying `test`.`UC_USER`:  61% 00:56 remain
Copying `test`.`UC_USER`:  82% 00:24 remain
2017-07-05T15:21:22 Copied rows OK.
2017-07-05T15:21:22 Analyzing new table...
2017-07-05T15:21:22 Swapping tables...
2017-07-05T15:21:22 Swapped original and new tables OK.
2017-07-05T15:21:22 Dropping old table...
2017-07-05T15:21:23 Dropped old table `test`.`_UC_USER_old` OK.
2017-07-05T15:21:23 Dropping triggers...
2017-07-05T15:21:23 Dropped triggers OK.
Successfully altered `test`.`UC_USER`.

real    2m42.191s
user    0m0.978s
sys 0m0.156s
[root@hch_test_121_91 /]# 


7,验证不影响在线DML操作

自己单独执行alter操作

mysql> alter table test.UC_USER add column C_N_2 varchar(64);
Query OK, 0 rows affected (2 min 8.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 

再次执行操作,看是否能在线ddl
(1)添加字段操作

[root@hch_test_121_91 /]# time pt-online-schema-change --host=192.168.121.91 --port=3307 --user=tim --password="timisgood" --alter="add column C_N_3 varchar(64) not null default 'sky'" --execute D=test,t=UC_USER --set-vars innodb_lock_wait_timeout=50 --no-check-replication-filters
No slaves found.  See --recursion-method if host hch_test_121_91 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `test`.`UC_USER`...
Creating new table...
Created new table test._UC_USER_new OK.
Altering new table...
Altered `test`.`_UC_USER_new` OK.
2017-07-05T15:30:32 Creating triggers...
2017-07-05T15:30:32 Created triggers OK.
2017-07-05T15:30:32 Copying approximately 1425328 rows...
Copying `test`.`UC_USER`:  20% 01:59 remain
Copying `test`.`UC_USER`:  39% 01:31 remain
Copying `test`.`UC_USER`:  61% 00:57 remain
Copying `test`.`UC_USER`:  87% 00:16 remain
2017-07-05T15:32:49 Copied rows OK.
2017-07-05T15:32:49 Analyzing new table...
2017-07-05T15:32:49 Swapping tables...
2017-07-05T15:32:49 Swapped original and new tables OK.
2017-07-05T15:32:49 Dropping old table...
2017-07-05T15:32:49 Dropped old table `test`.`_UC_USER_old` OK.
2017-07-05T15:32:49 Dropping triggers...
2017-07-05T15:32:49 Dropped triggers OK.
Successfully altered `test`.`UC_USER`.

real    2m18.175s
user    0m0.758s
sys 0m0.136s
[root@hch_test_121_91 /]# 

(2)同时进行update操作

mysql> update test.UC_USER set UPDATE_DATE=now(),NAME=concat(NAME,1) where ID=300;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select ID,UPDATE_DATE,NAME from test.UC_USER  where ID=300;
+-----+---------------------+------------+
| ID  | UPDATE_DATE | NAME   |
+-----+---------------------+------------+
| 300 | 2017-07-05 15:33:05 | 刘崇武1|
+-----+---------------------+------------+
1 row in set (0.00 sec)

mysql> 
mysql> select ID,UPDATE_DATE,NAME,C_N_3 from test.UC_USER  where ID=300;
+-----+---------------------+------------+-------+
| ID  | UPDATE_DATE | NAME   | C_N_3 |
+-----+---------------------+------------+-------+
| 300 | 2017-07-05 15:33:05 | 刘崇武1| sky   |
+-----+---------------------+------------+-------+
1 row in set (0.00 sec)

mysql> 


总结:
(1)不适合跨网络操作,适合本地操作
(2)上千万记录表或者上百万记录含大字段的表,使用pt比直接alter慢很多。
(3)pt在业务高峰期不适合使用,在高并发情景下也不太适合使用。


8,补充pt组件涉及到的三个触发器:

        DELIMITER $$

        USE `test`$$

        DROP TRIGGER /*!50032 IF EXISTS */ `pt_osc_test_UC_USER_del`$$

        CREATE
            /*!50017 DEFINER = 'tim'@'192.168.%' */
            TRIGGER `pt_osc_test_UC_USER_del` AFTER DELETE ON `UC_USER` 
            FOR EACH ROW DELETE IGNORE FROM `test`.`_UC_USER_new` WHERE `test`.`_UC_USER_new`.`id` <=> OLD.`id`;
        $$

        DELIMITER ;


        -----------------------------------------------------------------------------------------------------------
        DELIMITER $$

        USE `test`$$

        DROP TRIGGER /*!50032 IF EXISTS */ `pt_osc_test_UC_USER_upd`$$

        CREATE
            /*!50017 DEFINER = 'tim'@'192.168.%' */
            TRIGGER `pt_osc_test_UC_USER_upd` AFTER UPDATE ON `UC_USER` 
            FOR EACH ROW BEGIN DELETE IGNORE FROM `test`.`_UC_USER_new` WHERE !(OLD.`id` <=> NEW.`id`) AND `test`.`_UC_USER_new`.`id` <=> OLD.`id`;REPLACE INTO `test`.`_UC_USER_new` (`id`, `user_name`, `user_pwd`, `birthday`, `name`, `user_icon`, `sex`, `nickname`, `stat`, `user_mall`, `last_login_date`, `last_login_ip`, `src_open_user_id`, `email`, `mobile`, `is_del`, `is_email_confirmed`, `is_phone_confirmed`, `creater`, `create_date`, `update_date`, `pwd_intensity`, `mobile_tgc`, `mac`, `source`, `activate`, `activate_type`, `is_life`, `reserve_create_date`, `c_n_2`, `c_n_3`) VALUES (NEW.`id`, NEW.`user_name`, NEW.`user_pwd`, NEW.`birthday`, NEW.`name`, NEW.`user_icon`, NEW.`sex`, NEW.`nickname`, NEW.`stat`, NEW.`user_mall`, NEW.`last_login_date`, NEW.`last_login_ip`, NEW.`src_open_user_id`, NEW.`email`, NEW.`mobile`, NEW.`is_del`, NEW.`is_email_confirmed`, NEW.`is_phone_confirmed`, NEW.`creater`, NEW.`create_date`, NEW.`update_date`, NEW.`pwd_intensity`, NEW.`mobile_tgc`, NEW.`mac`, NEW.`source`, NEW.`activate`, NEW.`activate_type`, NEW.`is_life`, NEW.`reserve_create_date`, NEW.`c_n_2`, NEW.`c_n_3`);END;
        $$

        DELIMITER ;


        -----------------------------------------------------------------------------------------------------------
        DELIMITER $$

        USE `test`$$

        DROP TRIGGER /*!50032 IF EXISTS */ `pt_osc_test_UC_USER_ins`$$

        CREATE
            /*!50017 DEFINER = 'tim'@'192.168.%' */
            TRIGGER `pt_osc_test_UC_USER_ins` AFTER INSERT ON `UC_USER` 
            FOR EACH ROW REPLACE INTO `test`.`_UC_USER_new` (`id`, `user_name`, `user_pwd`, `birthday`, `name`, `user_icon`, `sex`, `nickname`, `stat`, `user_mall`, `last_login_date`, `last_login_ip`, `src_open_user_id`, `email`, `mobile`, `is_del`, `is_email_confirmed`, `is_phone_confirmed`, `creater`, `create_date`, `update_date`, `pwd_intensity`, `mobile_tgc`, `mac`, `source`, `activate`, `activate_type`, `is_life`, `reserve_create_date`, `c_n_2`, `c_n_3`) VALUES (NEW.`id`, NEW.`user_name`, NEW.`user_pwd`, NEW.`birthday`, NEW.`name`, NEW.`user_icon`, NEW.`sex`, NEW.`nickname`, NEW.`stat`, NEW.`user_mall`, NEW.`last_login_date`, NEW.`last_login_ip`, NEW.`src_open_user_id`, NEW.`email`, NEW.`mobile`, NEW.`is_del`, NEW.`is_email_confirmed`, NEW.`is_phone_confirmed`, NEW.`creater`, NEW.`create_date`, NEW.`update_date`, NEW.`pwd_intensity`, NEW.`mobile_tgc`, NEW.`mac`, NEW.`source`, NEW.`activate`, NEW.`activate_type`, NEW.`is_life`, NEW.`reserve_create_date`, NEW.`c_n_2`, NEW.`c_n_3`);
        $$

        DELIMITER ;

mysql数据库更改root密码以及授权用户DML权限

一 修改root密码的三种办法 方法1: 用SET PASSWORD命令 首先登录MySQL。 格式:mysql> set password for 用户名@localhost = passwo...
  • Eason_oracle
  • Eason_oracle
  • 2017年06月07日 16:12
  • 594

mysql在线ddl操作

mysql5.6开始支持在线ddl,在线ddl能够提供下面的好处; 1提高生产环境的可用性2在ddl执行期间,获得性能和并发性的平衡,可以指定LOCK从句与algorithm从句,lock=exclu...
  • aoerqileng
  • aoerqileng
  • 2016年05月07日 12:52
  • 3815

MySQL常用DDL、DML、DCL语言整理(附样例)

在介绍这些SQL语言之前,先罗列一下mysql的常用数据类型和数据类型修饰,供查询参考 后面的带数字表示此类型的字段长度 原文地址:http://www.linuxidc.com/Linu...
  • Gy__My
  • Gy__My
  • 2016年10月28日 16:41
  • 738

Mysql之pt-online-schema-change在线更新大表加索引

由于目前生产环境中大表比较多,这里选择了一个600M的小表做一次在线测试。 环境:Ubuntu 12.04 mysql版本 :5.6.29-log 表引擎:Innodb 一、pt-on...
  • wlzjsj
  • wlzjsj
  • 2016年05月30日 15:06
  • 3397

mysql 常用dml 语句

最近在准备给公司的研发培训一点mysql 知识,其实我也懂的不是太多,只能自己先学点,然后在去给他们讲,下面是自己整理的一些东西 3.1 建表 存储引擎说白了就是如何存储数据、如何为存储的数据建立...
  • wll_1017
  • wll_1017
  • 2015年04月09日 15:02
  • 1238

5分钟了解MySQL5.7的Online DDL雷区

原创作品,允许转载,转载时请务必以超链接形式标明文章 原始出处 、作者信息和本声明。否则将追究法律责任。http://suifu.blog.51cto.com/9167728/1855872 ...
  • carry9148
  • carry9148
  • 2016年09月23日 17:15
  • 639

详解MySQL大表优化方案

当MySQL单表记录数过大时,增删改查性能都会急剧下降,可以参考以下步骤来优化:单表优化、字段、索引、查询SQL、引擎等。...
  • yin767833376
  • yin767833376
  • 2016年08月04日 18:00
  • 2865

Mysql5.6支持在线修改表结构

根据工业业务,需要对一个核心业务表,增加字段。
  • wulantian
  • wulantian
  • 2014年07月07日 14:05
  • 3262

Oracle如何监控表的DML次数

转载自:http://www.ningoo.net/html/2010/how_to_monitor_table_dml_statistics.html 在数据库技术大会上,做了《构...
  • zhaoyangyao888
  • zhaoyangyao888
  • 2015年12月25日 15:58
  • 1161

Mysql 如何 删除大表

【问题隐患】     由于业务需求不断变化,可能在DB中存在超大表占用空间或影响性能;对这些表的处理操作,容易造成mysql性能急剧下降,IO性能占用严重等。先前有在生产库drop table造成服...
  • hjm4702192
  • hjm4702192
  • 2013年01月14日 13:59
  • 880
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:MySQL 大表在线DML神器--pt-online-schema-change
举报原因:
原因补充:

(最多只允许输入30个字)