5 min To learn about MySQL5.7 's undo log online Shrinkage new feature

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