Chinaunix首页 | 论坛 | 博客
  • 博客访问: 448897
  • 博文数量: 97
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 1141
  • 用 户 组: 普通用户
  • 注册时间: 2016-09-22 09:50
  • 认证徽章:
个人简介

一个守望数据库的老菜鸟

文章分类

全部博文(97)

文章存档

2019年(18)

2018年(17)

2017年(38)

2016年(24)

我的朋友

分类: Mysql/postgreSQL

2017-05-05 15:17:11

博客文章除注明转载外,均为原创。转载请注明出处。
本文链接地址:http://blog.chinaunix.net/uid-31396856-id-5763818.html


前面已经出过percona-toolkit的介绍,从本文开始后续介绍常用工具的使用。
pt-online-schema-change是percona-toolkit最常用到的开发类工具。功能为在 alter 操作更改表结构的时候不用锁定表,也就是说执行 alter 的时候不会阻塞写和读取操作。注意执行这个工具的时候必须做好备份。其实,建议做表结构变更的时候都应该做备份。

工作原理是创建一个和你要执行 alter 操作的表一样的空表结构,执行表结构修改,然后从原表中 copy 原始数据到表结构修改后的表,当数据 copy 完成以后就会将原表移走,用新表代替原表,默认动作是将原表 drop 掉。在 copy 数据的过程中,任何在原表的更新操作都会更新到新表,因为这个工具在会在原表上创建触发器,触发器会将在原表上更新的内容更新到新表。

注意:如果表中已经定义了触发器这个工具就不能工作了。

1.执行过程

[root@master01 data]# pt-online-schema-change --alter="add column city varchar(20) not null default '' after name" --user=root  D=test,t=tc_user 
--charset=utf8 --execute
Cannot connect to A=utf8,D=test,h=192.168.10.84,u=root
No slaves found.  See --recursion-method if host master01 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`.`tc_user`...
Creating new table...
Created new table test._tc_user_new OK.
Altering new table...
Altered `test`.`_tc_user_new` OK.
2017-05-05T00:27:42 Creating triggers...
2017-05-05T00:27:42 Created triggers OK.
2017-05-05T00:27:42 Copying approximately 4 rows...
Cannot connect to A=utf8,D=test,h=192.168.10.84,u=root
2017-05-05T00:27:42 Copied rows OK.
2017-05-05T00:27:42 Analyzing new table...
2017-05-05T00:27:42 Swapping tables...
2017-05-05T00:27:42 Swapped original and new tables OK.
2017-05-05T00:27:42 Dropping old table...
2017-05-05T00:27:42 Dropped old table `test`.`_tc_user_old` OK.
2017-05-05T00:27:42 Dropping triggers...
2017-05-05T00:27:42 Dropped triggers OK.
Successfully altered `test`.`tc_user`.

分为如下过程:
1.创建新表,
2.通过触发器跟踪复制更新数据,
3.copy数据,
4.更新外键关联子表,
5.rename表,。
6.删除原表和触发器

2.pt-osc原理浅析
step1.执行变更前的检查
2017-05-04T16:49:12.041502Z        26 Connect   root@localhost on test using Socket
2017-05-04T16:49:12.041675Z        26 Query     set autocommit=1
2017-05-04T16:49:12.041849Z        26 Query     /*!40101 SET NAMES "utf8"*/
2017-05-04T16:49:12.042012Z        26 Query     SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout'
2017-05-04T16:49:12.044172Z        26 Query     SET SESSION innodb_lock_wait_timeout=1
2017-05-04T16:49:12.044493Z        26 Query     SHOW VARIABLES LIKE 'lock\_wait_timeout'
2017-05-04T16:49:12.046557Z        26 Query     SET SESSION lock_wait_timeout=60
2017-05-04T16:49:12.046714Z        26 Query     SHOW VARIABLES LIKE 'wait\_timeout'
2017-05-04T16:49:12.048398Z        26 Query     SET SESSION wait_timeout=10000
以上是超时相关检查
2017-05-04T16:49:12.048536Z        26 Query     SELECT @@SQL_MODE
2017-05-04T16:49:12.048633Z        26 Query     SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/
2017-05-04T16:49:12.048730Z        26 Query     SELECT @@server_id /*!50038 , @@hostname*/
2017-05-04T16:49:12.049311Z        27 Connect   root@localhost on test using Socket
2017-05-04T16:49:12.049645Z        27 Query     set autocommit=1
2017-05-04T16:49:12.049782Z        27 Query     /*!40101 SET NAMES "utf8"*/
2017-05-04T16:49:12.049908Z        27 Query     SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout'
2017-05-04T16:49:12.051635Z        27 Query     SET SESSION innodb_lock_wait_timeout=1
2017-05-04T16:49:12.051864Z        27 Query     SHOW VARIABLES LIKE 'lock\_wait_timeout'
2017-05-04T16:49:12.054104Z        27 Query     SET SESSION lock_wait_timeout=60
2017-05-04T16:49:12.054259Z        27 Query     SHOW VARIABLES LIKE 'wait\_timeout'
2017-05-04T16:49:12.056477Z        27 Query     SET SESSION wait_timeout=10000
2017-05-04T16:49:12.056632Z        27 Query     SELECT @@SQL_MODE
2017-05-04T16:49:12.056731Z        27 Query     SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/
2017-05-04T16:49:12.056887Z        27 Query     SELECT @@server_id /*!50038 , @@hostname*/
2017-05-04T16:49:12.057081Z        26 Query     SHOW VARIABLES LIKE 'wsrep_on'
2017-05-04T16:49:12.059325Z        26 Query     SHOW VARIABLES LIKE 'version%'
2017-05-04T16:49:12.061212Z        26 Query     SHOW ENGINES
2017-05-04T16:49:12.061719Z        26 Query     SHOW VARIABLES LIKE 'innodb_version'
2017-05-04T16:49:12.063859Z        26 Query     SHOW VARIABLES LIKE 'innodb_stats_persistent'
2017-05-04T16:49:12.065907Z        26 Query     SELECT @@SERVER_ID
2017-05-04T16:49:12.066060Z        26 Query     SHOW GRANTS FOR CURRENT_USER()
2017-05-04T16:49:12.066198Z        26 Query     SHOW FULL PROCESSLIST
2017-05-04T16:49:12.070568Z        26 Query     SHOW GLOBAL STATUS LIKE 'Threads_running'
2017-05-04T16:49:12.071989Z        26 Query     SHOW GLOBAL STATUS LIKE 'Threads_running'
2017-05-04T16:49:12.073650Z        26 Query     SELECT CONCAT(@@hostname, @@port)
2017-05-04T16:49:12.074099Z        26 Query     SHOW TABLES FROM `test` LIKE 'tc\_user'
2017-05-04T16:49:12.074534Z        26 Query     SHOW TRIGGERS FROM `test` LIKE 'tc\_user'
2017-05-04T16:49:12.074926Z        26 Query     /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
----环境变量和状态检查,权限检查
2017-05-04T16:49:12.075022Z        26 Query     USE `test`
2017-05-04T16:49:12.075119Z        26 Query     SHOW CREATE TABLE `test`.`tc_user`
2017-05-04T16:49:12.075259Z        26 Query     /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2017-05-04T16:49:12.075691Z        26 Query     EXPLAIN SELECT * FROM `test`.`tc_user` WHERE 1=1
2017-05-04T16:49:12.076480Z        26 Query     SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE referenced_table_schema='test' AND referenced_table_name='tc_user'
2017-05-04T16:49:12.081589Z        26 Query     SHOW VARIABLES LIKE 'wsrep_on'
2017-05-04T16:49:12.083498Z        26 Query     /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
2017-05-04T16:49:12.083617Z        26 Query     USE `test`
2017-05-04T16:49:12.083723Z        26 Query     SHOW CREATE TABLE `test`.`tc_user`
2017-05-04T16:49:12.083878Z        26 Query     /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
---表检查,关联表检查
step2.创建新表,其命名规则是_+原表名+_new
2017-05-04T16:49:12.084022Z        26 Query     CREATE TABLE `test`.`_tc_user_new` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL DEFAULT '0',
  `mail` varchar(120) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

step3.根据语句更新新表结构
2017-05-04T16:49:12.118804Z        26 Query     ALTER TABLE `test`.`_tc_user_new` add column city varchar(20) not null default '' after name
2017-05-04T16:49:12.224088Z        26 Query     /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */

step4.在原表上创建到新表的触发器,实现数据的在线同步
2017-05-04T16:49:12.224255Z        26 Query     USE `test`
2017-05-04T16:49:12.224612Z        26 Query     SHOW CREATE TABLE `test`.`_tc_user_new`
2017-05-04T16:49:12.224920Z        26 Query     /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2017-05-04T16:49:12.226233Z        26 Query     CREATE TRIGGER `pt_osc_test_tc_user_del` AFTER DELETE ON `test`.`tc_user` FOR EACH ROW DELETE IGNORE FROM `test`.`_tc_user_new` WHERE `test`.`_tc_user_new`.`id` <=> OLD.`id`
---创建触发器1,delete触发器
2017-05-04T16:49:12.251356Z        26 Query     CREATE TRIGGER `pt_osc_test_tc_user_upd` AFTER UPDATE ON `test`.`tc_user` FOR EACH ROW BEGIN DELETE IGNORE FROM `test`.`_tc_user_new` WHERE !(OLD.`id` <=> NEW.`id`) AND `test`.`_tc_user_new`.`id` <=> OLD.`id`;REPLACE INTO `test`.`_tc_user_new` (`id`, `name`, `mail`) VALUES (NEW.`id`, NEW.`name`, NEW.`mail`);END
---创建触发器2,update触发器
2017-05-04T16:49:12.275066Z        26 Query     CREATE TRIGGER `pt_osc_test_tc_user_ins` AFTER INSERT ON `test`.`tc_user` FOR EACH ROW REPLACE INTO `test`.`_tc_user_new` (`id`, `name`, `mail`) VALUES (NEW.`id`, NEW.`name`, NEW.`mail`)
---创建触发器3,insert触发器

step5,通过explain来判断执行chunk拷贝的成本
2017-05-04T16:49:12.300640Z        26 Query     EXPLAIN SELECT * FROM `test`.`tc_user` WHERE 1=1
2017-05-04T16:49:12.301992Z        26 Query     EXPLAIN SELECT `id`, `name`, `mail` FROM `test`.`tc_user` LOCK IN SHARE MODE /*explain pt-online-schema-change 4707 copy table*/

step6,数据copy到新表
2017-05-04T16:49:12.302580Z        26 Query     INSERT LOW_PRIORITY IGNORE INTO `test`.`_tc_user_new` (`id`, `name`, `mail`) SELECT `id`, `name`, `mail` FROM `test`.`tc_user` LOCK IN SHARE MODE /*pt-online-schema-change 4707 copy table*/
---在线copy数据
2017-05-04T16:49:12.311598Z        26 Query     SHOW WARNINGS
2017-05-04T16:49:12.312039Z        26 Query     SELECT @@SERVER_ID
2017-05-04T16:49:12.312190Z        26 Query     SHOW GRANTS FOR CURRENT_USER()
2017-05-04T16:49:12.312592Z        26 Query     SHOW FULL PROCESSLIST
2017-05-04T16:49:12.316742Z        26 Query     SHOW GLOBAL STATUS LIKE 'Threads_running'
---检查负载状态

step7,完成数据同步后对表进行检查
2017-05-04T16:49:12.318729Z        26 Query     ANALYZE TABLE `test`.`_tc_user_new` /* pt-online-schema-change */
step8,交换表
2017-05-04T16:49:12.328526Z        26 Query     RENAME TABLE `test`.`tc_user` TO `test`.`_tc_user_old`, `test`.`_tc_user_new` TO `test`.`tc_user`
step9,删除旧表
2017-05-04T16:49:12.379665Z        26 Query     DROP TABLE IF EXISTS `test`.`_tc_user_old`
step10,删除触发器
2017-05-04T16:49:12.404253Z        26 Query     DROP TRIGGER IF EXISTS `test`.`pt_osc_test_tc_user_del`
2017-05-04T16:49:12.414229Z        26 Query     DROP TRIGGER IF EXISTS `test`.`pt_osc_test_tc_user_upd`
2017-05-04T16:49:12.419883Z        26 Query     DROP TRIGGER IF EXISTS `test`.`pt_osc_test_tc_user_ins`
11.检查表状态
2017-05-04T16:49:12.428199Z        26 Query     SHOW TABLES FROM `test` LIKE '\_tc\_user\_new'
2017-05-04T16:49:12.428796Z        27 Quit
2017-05-04T16:49:12.430346Z        26 Quit
--The end
阅读(1390) | 评论(0) | 转发(1) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册