分享程序员开发的那些事...
更新时间：2022-09-15 16:53:03
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参数
|
1
2
3
4
5
6
|
innodb_undo_directory=/data/undolog
innodb_undo_tablespaces=4innodb_undo_logs=128innodb_max_undo_log_size=1Ginnodb_purge_rseg_truncate_frequencyinnodb_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万行的表进行整表更新
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
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日志
|
1
2
3
4
5
6
7
8
|
2016-09-26T23:51:06.062828Z 0 [Note] InnoDB: Truncating UNDO tablespace with space identifier 12016-09-26T23:51:06.159077Z 0 [Note] InnoDB: Completed truncate of UNDO tablespace with space identifier 12016-09-26T23:51:06.159101Z 0 [Note] InnoDB: Truncating UNDO tablespace with space identifier 22016-09-26T23:51:06.242355Z 0 [Note] InnoDB: Completed truncate of UNDO tablespace with space identifier 22016-09-26T23:51:06.242378Z 0 [Note] InnoDB: Truncating UNDO tablespace with space identifier 32016-09-26T23:51:06.313036Z 0 [Note] InnoDB: Completed truncate of UNDO tablespace with space identifier 32016-09-26T23:51:06.313060Z 0 [Note] InnoDB: Truncating UNDO tablespace with space identifier 42016-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文件越来越大，导致磁盘空间被占、物理备份越来越慢而头疼了。由于笔者的水平有限，编写时间也很仓促，文中难免会出现一些错误或者不准确的地方，不妥之处恳请读者批评指正。
本文转自 dbapower 51CTO博客，原文链接：http://blog.51cto.com/suifu/1856837，如需转载请自行联系原作者