MySQL5.7-VS-MySQL5.6的undo log在线收缩

wKiom1ftzhWzUkt3AAA4eVx2Dz8050.jpg


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             |
+--------------------------+---------------+
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日志

1
2
3
4
5
6
7
8
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


观察物理文件

[root@HE3 undolog]# ls -lshrt

total 168M

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

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

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

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

可以看到超过100M设定值的undo日志已经回收,默认为10M,undo log空间得以释放


 

Part3:意义

该功能降低了磁盘的使用率,并且可以提高诸如Xtrabackup这类物理备份软件的备份速度。

 

——总结——

MySQL5.7的undo log在线回收功能,相比MySQL5.6更加灵活和实用,DBA们再也不必因为ibdata1文件越来越大,导致磁盘空间被占、物理备份越来越慢而头疼了。由于笔者的水平有限,编写时间也很仓促,文中难免会出现一些错误或者不准确的地方,不妥之处恳请读者批评指正。

版权所有:《太阳花工作室》 => 《MySQL5.7-VS-MySQL5.6的undo log在线收缩
本文地址:http://bg.artuion.com/win_lin_mac/350.html
除非注明,文章均为 《太阳花工作室》 原创,欢迎转载!转载请注明本文地址,谢谢。