MySQL Online DDL

最新推荐文章于 2024-06-13 23:26:25 发布

System_out_println6

最新推荐文章于 2024-06-13 23:26:25 发布

阅读量398

点赞数

文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/System_out_println6/article/details/125170730

版权

使用pt-online-schema-change进行MySQL Online DDL

前言：

MySQL在线ddl工具有很多，比如pt-online-schema-change 、gh-ost 、InnoDB自带的ddl功能等。这些工具的主要目的都是为了在对MySQL进行DDL时不阻塞读写，达到数据库可用的目的。

今天基于MySQL8.0对比一下直接ddl和使用pt-online-schema-change进行ddl的区别。

一、准备数据

实验环境：

参数名	参数值
环境	win10、8C16G
MySQL版本	8.0.25
数据量	1000w

建表语句如下：

CREATE TABLE `t_usr_inf` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `usr_id` int DEFAULT 0 COMMENT '用户编号',
  `usr_nm` varchar(50) DEFAULT '' COMMENT '用户名称',
  `tel` char(11) DEFAULT '' COMMENT '电话号码',
  `addr` varchar(200) DEFAULT '' COMMENT '用户地址',
  `avatar_url` varchar(500) DEFAULT '' COMMENT '头像url',
  `bth_day` varchar(10) DEFAULT '' COMMENT '生日',
  `lvl` varchar(10) DEFAULT 'LV0' COMMENT '用户等级',
  `crt_tm` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `mdf_tm` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `ind_usrId` (`usr_id`)
) COMMENT='用户信息表';

造数存储过程如下：

#创建存储过程
DELIMITER $$

create 	procedure inst_usr_inf(in count int )
begin
  declare v_i int unsigned default 0;
	declare v_up int unsigned default 0;
	
	while count > 0 do
	  start transaction;
	  while v_i < 10000 and count>0 do		  
	    insert into t_usr_inf(usr_id,usr_nm,tel,addr,avatar_url,bth_day) 
		  values(10000000 + v_up,substring(MD5(RAND()),1,10),concat(1,substring(rand(),3,10)),substring(MD5(RAND()),1,10),substring(MD5(RAND()),1,10),'2022-06-07')	;
			set v_i = v_i+1;
			set count = count -1;
			set v_up = v_up + 1;
	  end while;
		commit;	
		set v_i = 0;
	end while ;
end $$

delimiter ;
#使用存储过程插入一千万数据
call inst_usr_inf(10000000 );

插入1000万数据耗时

三、进入测试

从MySQL官网可以看到8.0版本在修改数据类型时是不能进行DML操作的。

在这里插入图片描述

3.1、使用原生语句修改字符类型

在这里插入图片描述

可以看到使用原生语句修改字符类型需要54s之多。并且可以看到新增一条语句被阻塞。

在这里插入图片描述

3.2、使用pt-online-schema-change修改字符类型

Percona Toolkit安装

#下载percona-toolkit的ubuntu安装包
wget https://downloads.percona.com/downloads/percona-toolkit/3.3.1/binary/debian/bionic/x86_64/percona-toolkit_3.3.1-1.bionic_amd64.deb 
# 安装Perl语言依赖，percona-toolkit是用perl语言写的
$ sudo apt-get install libdbi-perl
# 安装 libdbi-perl 时，出错，根据提示执行相关命令
$ sudo apt --fix-broken install
# 安装percona-toolkit的deb包
$ sudo dpkg -i percona-toolkit_3.3.1-1.bionic_amd64.deb

此时如果报错：

dpkg: dependency problems prevent configuration of percona-toolkit:
 percona-toolkit depends on libdbd-mysql-perl | libdbd-mysql-5.1-perl; however:
  Package libdbd-mysql-perl is not installed.
  Package libdbd-mysql-5.1-perl is not installed.
 percona-toolkit depends on libterm-readkey-perl (>= 2.10); however:
  Package libterm-readkey-perl is not installed.
 percona-toolkit depends on libio-socket-ssl-perl; however:
  Package libio-socket-ssl-perl is not installed.

dpkg: error processing package percona-toolkit (--install):
 dependency problems - leaving unconfigured
Processing triggers for man-db (2.7.5-1) ...
Errors were encountered while processing:
 percona-toolkit

是因为缺少依赖，使用sudo apt-get -f install解决，再次安装。

最后使用 pt-table-checksum --help验证是否成功安装。

执行语句

pt-online-schema-change --alter 'modify column lvl char(3) ' h=172.20.10.12,u=root,P=3306,D=program_dev,t=t_usr_inf  --ask-pass  --no-drop-old-table  --execute

执行日志：

No slaves found.  See --recursion-method if host XXX 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 `program_dev`.`t_usr_inf`...
Creating new table...
Created new table program_dev._t_usr_inf_new OK.
Altering new table...
Altered `program_dev`.`_t_usr_inf_new` OK.
2022-06-07T17:14:47 Creating triggers...
2022-06-07T17:14:47 Created triggers OK.
2022-06-07T17:14:47 Copying approximately 9936126 rows...
Copying `program_dev`.`t_usr_inf`:   2% 44:43 remain
2022-06-07T17:14:49 Rows are copying very slowly.  --chunk-size has been automatically reduced to 1.  Check that the server is not being overloaded, or increase --chunk-time.  The last chunk selected 6508 rows and took -54.453 seconds to execute.
Copying `program_dev`.`t_usr_inf`:  68% 00:54 remain
2022-06-07T17:16:20 Copied rows OK.
2022-06-07T17:16:20 Analyzing new table...
2022-06-07T17:16:20 Swapping tables...
2022-06-07T17:16:21 Swapped original and new tables OK.
Not dropping old table because --no-drop-old-table was specified.
2022-06-07T17:16:21 Dropping triggers...
2022-06-07T17:16:21 Dropped triggers OK.
Successfully altered `program_dev`.`t_usr_inf`.