5分钟了解MySQL5.7的undo log在线收缩新特性

最新推荐文章于 2024-07-10 23:07:29 发布

岁伏的博客

最新推荐文章于 2024-07-10 23:07:29 发布

阅读量1.2k

点赞数 1

分类专栏： MySQL 文章标签： mysql undo log

本文链接：https://blog.csdn.net/carry9148/article/details/52787131

版权

MySQL 专栏收录该内容

49 篇文章 0 订阅

订阅专栏

原创作品，允许转载，转载时请务必以超链接形式标明文章原始出处、作者信息和本声明。否则将追究法律责任。 http://suifu.blog.51cto.com/9167728/1856837

Part1:写在最前

在MysQL5.6版本中，可以把undo log 回滚日志分离到一个单独的表空间里；其缺点是不能回收空间大小，until MysQL5.7，but MariadDB10.1暂不支持。

本文介绍并演示MysQL5.7是如何在线收缩undo log的。

undo log日志是保存在共享表空间ibdata1文件中的，随着数据库的运行时间的不断增长，ibdata1文件会越来越大，在以往的MySQL数据库版本中，如果我们想要回收ibdata1文件所占空间，会非常的复杂和困难，必须先将mysqldump -A全库导出，然后删掉data目录，再重新对数据库进行初始化，最后导入全库备份，方可实现ibdata1的回收。

MySQL全库备份方式可参考：

http://suifu.blog.51cto.com/9167728/1758022

MySQL5.7的安装方式可参考：

http://suifu.blog.51cto.com/9167728/1855415

①首先我们需要调整my.cnf参数

 
         innodb_undo_directory= 
         /data/undolog 
        
         innodb_undo_tablespaces=4 
        
         innodb_undo_logs=128 
        
         innodb_max_undo_log_size=1G 
        
         innodb_purge_rseg_truncate_frequency 
        
         innodb_undo_log_truncate=1

Warning:警告

其中innodb_undo_directory参数要在数据库初始化时就需要写入my.cnf，否则会报如下错误：

Expected to open 4 undo tablespaces but was able to find only 0 undo tablespaces. Set the innodb_undo_tablespaces parameter to the correct value and retry. Suggested value is 0

Part2:MySQL5.7在线回收undolog

①首先对一张100万行的表进行整表更新

 
         mysql> show variables  
         like  
         'innodb_undo%' 
         ; 
        
         + 
         --------------------------+---------------+ 
        
         | Variable_name            | Value         | 
        
         + 
         --------------------------+---------------+ 
        
         | innodb_undo_directory    | /data/undolog | 
        
         | innodb_undo_log_truncate |  
         ON             
         | 
        
         | innodb_undo_logs         | 128           | 
        
         | innodb_undo_tablespaces  | 4             | 
        
         + 
         --------------------------+---------------+ 
        
         4  
         rows  
         in  
         set  
         (0.00 sec) 
        
         mysql>  
         update  
         helei  
         set  
         c1=1; 
        
         Query OK, 1000000  
         rows  
         affected (45.48 sec) 
        
         Rows  
         matched: 1000000  Changed: 1000000  Warnings: 0 
        
         mysql>  
         update  
         helei  
         set  
         c2=222; 
        
         Query OK, 1000000  
         rows  
         affected (43.25 sec) 
        
         Rows  
         matched: 1000000  Changed: 1000000  Warnings: 0 
        
         mysql>  
         update  
         helei  
         set  
         c4= 
         'heleiheleiheleiheleihel' 
         ; 
        
         Query OK, 1000000  
         rows  
         affected (10.28 sec) 
        
         Rows  
         matched: 1000000  Changed: 1000000  Warnings: 0

②注意undolog的日志大小

[root@HE3 undolog]# ls -lshrt

total 412M

128M -rw-r----- 1 mysql mysql 128M Sep 26 16:56 undo004

76M -rw-r----- 1 mysql mysql 76M Sep 26 16:56 undo003

136M -rw-r----- 1 mysql mysql 136M Sep 26 16:56 undo001

72M -rw-r----- 1 mysql mysql 72M Sep 26 16:56 undo002

③error.log日志

 
         2016-09-26T23:51:06.062828Z 0 [Note] InnoDB: Truncating UNDO tablespace with space identifier 1 
        
         2016-09-26T23:51:06.159077Z 0 [Note] InnoDB: Completed truncate of UNDO tablespace with space identifier 1 
        
         2016-09-26T23:51:06.159101Z 0 [Note] InnoDB: Truncating UNDO tablespace with space identifier 2 
        
         2016-09-26T23:51:06.242355Z 0 [Note] InnoDB: Completed truncate of UNDO tablespace with space identifier 2 
        
         2016-09-26T23:51:06.242378Z 0 [Note] InnoDB: Truncating UNDO tablespace with space identifier 3 
        
         2016-09-26T23:51:06.313036Z 0 [Note] InnoDB: Completed truncate of UNDO tablespace with space identifier 3 
        
         2016-09-26T23:51:06.313060Z 0 [Note] InnoDB: Truncating UNDO tablespace with space identifier 4 
        
         2016-09-26T23:51:06.403003Z 0 [Note] InnoDB: Completed truncate of UNDO tablespace with space identifier 4