Part1: writing at the top
In the MysQL5.6 release, the Undo log rollback log can be separated into a separate tablespace, with the drawback that the space size cannot be reclaimed, until Mysql5.7,but MariadDB10.1 is not supported.
This article describes and demonstrates how MysQL5.7 shrinks the undo log online.
The Undo log log is saved in the shared tablespace ibdata1 file, and as the database runs, the ibdata1 file becomes larger, and in previous versions of MySQL database, if we want to reclaim the space occupied by ibdata1 files, it can be very complex and difficult, You must first export the Mysqldump-a whole library, then delete the data directory, then re-initialize the database, and finally import the full library backup, to achieve ibdata1 recycling.
MySQL Full library backup method can be consulted:
http://suifu.blog.51cto.com/9167728/1758022
The installation method of MySQL5.7 can be used for reference:
http://suifu.blog.51cto.com/9167728/1855415
① first we need to adjust the my.cnf parameters
Innodb_undo_directory=/data/undologinnodb_undo_tablespaces=4innodb_undo_logs=128innodb_max_undo_log_size= 1ginnodb_purge_rseg_truncate_frequencyinnodb_undo_log_truncate=1
Warning: Warning
Where the innodb_undo_directory parameter needs to be written to MY.CNF when the database is initialized, otherwise the following error is reported:
Expected to open 4 undo tablespaces but is 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 Online Recycling Undolog
① First update the entire table with a table of 1 million rows
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: 0mysql > update helei set c2=222; query ok, 1000000 rows affected (43.25 sec) rows matched: 1000000 changed: 1000000 warnings: 0mysql> update helei set c4= ' Heleiheleiheleiheleihel '; query ok, 1000000 rows affected (10.28 sec) rows matched: 1000000 changed: 1000000 warnings: 0
② Note the log size of the Undolog
[Email protected] undolog]# Ls-lshrt
Total 412M
128m-rw-r-----1 mysql mysql 128M Sep 16:56 undo004
76m-rw-r-----1 mysql mysql 76M Sep 16:56 undo003
136m-rw-r-----1 mysql mysql 136M Sep 16:56 undo001
72m-rw-r-----1 mysql mysql 72M Sep 16:56 undo002
③
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
④ Observing physical files
[Email protected] undolog]# Ls-lshrt
Total 168M
76m-rw-r-----1 mysql mysql 76M Sep 16:56 undo003
10m-rw-r-----1 mysql mysql 10M Sep 16:56 undo001
10m-rw-r-----1 mysql mysql 10M Sep 16:56 undo004
72m-rw-r-----1 mysql mysql 72M Sep 16:56 undo002
You can see that the undo log over the 100M SetPoint has been reclaimed, and the default is 10m,undo log space is released
Part3: meaning
This feature reduces disk usage and improves backup speeds for physical backup software such as Xtrabackup.
-- Summary --
MySQL5.7 's undo Log Online recovery feature is more flexible and practical than MySQL5.6, and DBAs will no longer have to be bothered by the fact that the ibdata1 files are getting bigger, causing disk space to be occupied, and physical backups getting slower. due to the author's limited level, writing time is also very hasty, the text will inevitably appear some errors or inaccurate places, inappropriate to ask readers to criticize correct.
This article is from the "Age volt" blog, please make sure to keep this source http://suifu.blog.51cto.com/9167728/1856837
5 min To learn about MySQL5.7 's undo log online Shrinkage new feature