Complete analysis of MySQL's InnoDB expansion and ibdata1 file slimming solution_MySQL

Innodb expansion of mysql
In order to add a data file to the table space, you must first close the MySQL database, edit the my.cnf file, and confirm whether the actual situation of the innodb ibdata file is consistent with the configuration of my.cnf. There are two situations here:
1.Configuration of my.cnf

innodb_data_file_path=ibdata1:10G;ibdata2:10G:autoextend 

If the current database is using ibdata1 or ibdata2, but ibdata2 does not exceed 10G, then directly change the my.cnf configuration to:

innodb_data_file_path=ibdata1:10G;ibdata2:10G;ibdata3:10G:autoextend 

2. If the last ibdata is automatically expanded, it is possible that the space occupied by the last ibdata is larger than the configuration space of my.cnf. For example:

 mysql@test:/data1/mysqldata/innodb/data> ls -lh 

-rw-rw---- 1 mysql mysql 10737418240 2010-01-26 16:34 ibdata1 
-rw-rw---- 1 mysql mysql 16106127360 2010-01-26 16:34 ibdata2 

At this time, it is necessary to accurately calculate the size of ibdata2 15360M, modify:

innodb_data_file_path=ibdata1:10G;ibdata2:15360M;ibdata3:10G:autoextend 

Restart mysql.
Note:
1. Pay attention to whether the disk space is sufficient before expansion.
2. After restarting, pay attention to whether new ibdata is generated.
More instructions:
If the last file is described with the keyword autoextend, then when editing my.cnf, you must check the size of the last file and make it closer to a multiple of 1024 * 1024 bytes (= 1 MB) (for example Now the /ibdata/ibdata1 of autoextend is 18.5M, but it is 10M in the old my.ini. You need to modify it to innodb_data_file_path = /ibdata/ibdata1:19M; and it must be 19M. If you specify 20M, an error will be reported). And specify its size explicitly in innodb_data_file_path. Then you can add another data file. Remember that only the last file in innodb_data_file_path can be specified as auto-extending.
An example: Suppose there is only one auto-extending data file ibdata1 at first, and this file is close to 988 MB. Here is a possible example after adding another auto-extending data file.

innodb_data_home_dir = 
innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend 

ibdata1 slimming

0. What is stored in ibdata1

When you enable innodb_file_per_table, tables are stored in their own tablespaces, but the shared tablespace still stores other InnoDB internal data:
(1) Data dictionary, which is the metadata of the InnoDB table
(2) Change buffer
(3) Double write buffer
(4) Undo log

Some of these can be configured on Percona Server to avoid growing too large. For example, you can set the maximum change buffer via innodb_ibuf_max_size, or set innodb_doublewrite_file to store the doublewrite buffer in a separate file.

In MySQL version 5.6 you can also create external undo tablespaces, so they can be placed in their own files instead of being stored in ibdata1.

1. What causes ibdata1 to grow rapidly?

When there is a problem with MySQL, usually the first command we need to execute is:

SHOW ENGINE INNODB STATUS/G

This will show us some valuable information. We start with the TRANSACTION section and we find this:

---TRANSACTION 36E, ACTIVE 1256288 sec
MySQL thread id 42, OS thread handle 0x7f8baaccc700, query id 7900290 localhost root
show engine innodb status
Trx read view will not see trx with id >= 36F, sees < 36F

This is the most common reason, a fairly old transaction created 14 days ago. This state is active, which means that InnoDB has created a snapshot of the data, so old pages need to be maintained in the undo log to ensure a consistent view of the database until the transaction begins. If your database has a lot of writes, that means a lot of undo pages are being stored.

If you can't find any long-running transactions, you can also monitor other variables in INNODB STATUS. "History list length" shows some pending cleanup operations. Problems often occur in this case because the cleanup thread (or the main thread in older versions) cannot process undos as fast as these records come in.

2. How do I check what is stored in ibdata1?

Unfortunately, MySQL does not provide information about what is stored in the ibdata1 shared tablespace, but two tools will be helpful. The first is a modified version of innochecksum made by Mark Callahan and published in this vulnerability report.

It's pretty easy to use:

# ./innochecksum /var/lib/mysql/ibdata1
0 bad checksum
13 FIL_PAGE_INDEX
19272 FIL_PAGE_UNDO_LOG
230 FIL_PAGE_INODE
1 FIL_PAGE_IBUF_FREE_LIST
892 FIL_PAGE_TYPE_ALLOCATED
2 FIL_PAGE_IBUF_BITMAP
195 FIL_PAGE_TYPE_SYS
1 FIL_PAGE_TYPE_TRX_SYS
1 FIL_PAGE_TYPE_FSP_HDR
1 FIL_PAGE_TYPE_XDES
0 FIL_PAGE_TYPE_BLOB
0 FIL_PAGE_TYPE_ZBLOB
0 other
3 max index_id

There are 19272 undo log pages out of a total of 20608. This takes up 93% of the table space.

The second way to check the contents of a tablespace is the InnoDB Ruby tool by Jeremy Cole. It is a more advanced tool for inspecting the internal structure of InnoDB. For example we can use the space-summary parameter to get a list of each page and its data type. We can count the number of undo log pages using standard Unix tools:

# innodb_space -f /var/lib/mysql/ibdata1 space-summary | grep UNDO_LOG | wc -l
19272

Although innochedcksum is faster and easier to use in this particular case, I recommend you use Jeremy's tool to learn more about the data distribution and internal structure of InnoDB.

Okay, now we know the problem.

3. ibdata1 瘦身方案
其中的一些在 Percona 服务器上可以被配置来避免增长过大的。例如你可以通过 innodb_ibuf_max_size 设置最大变更缓冲区，或设置 innodb_doublewrite_file 来将双写缓冲区存储到一个分离的文件。

MySQL 5.6 版中你也可以创建外部的撤销表空间，所以它们可以放到自己的文件来替代存储到 ibdata1。

通常不能移除 InnoDB 的数据文件。为了减小数据文件的大小，你必须使用 mysqldump 来转储(dump)所有的数据表，再重新建立一个新的数据库，并将数据导入新的数据库中。具体步骤如下：
（1）备份数据库
mysqldump -uroot -p123456 --default-character-set=utf8 --opt --extended-insert=true --triggers -R --hex-blob --single-transaction --no-autocommit test > db_name.sql <br /> （2）停止数据库

service mysqld stop 

（3）删除相关文件

ibdata1 
ib_logfile* 
mysql-bin.index 

（4）手动删除除Mysql之外所有数据库文件夹，然后启动数据库

service mysqld start 

（5）还原数据

 /usr/local/mysql/bin/mysql -uroot -phigkoo < /data/bkup/mysqldump.sql 

主要是使用Mysqldump时的一些参数，建议在使用前看一个说明再操作。另外备份前可以先查看一下当前数据库里哪些表占用空间大，把一些不必要的给truncate table掉。这样省些空间和时间