谈谈tmpdir与innodb_tmpdir的区别和用处

【背景】

　　innodb_tmpdir是在innodb online ddl中提到的一个参数；大致的意思是innodb在做online-ddl的时候会向临时目录写入“临时排序文件”

　　而这些文件的大小基本上就要与表的数据+索引的大小差不多。

 

　　这里的临时目录默认就是由“tmpdir”这个参数的值，在我的主机上用了/tmp/这个目录

show global variables like 'tmpdir';                                                                          
+----------------+----------+
| Variable_name  | Value    |
+----------------+----------+
| tmpdir         | /tmp     |
+----------------+----------+

　　通常来说linux上的tmp目录不可能会特别大，常见的也就几个G吧，但是innodb单个表中的“数据”+“索引”几十个G的多了去了；如果是对

　　这样的表做online ddl 那么临时目录是保存不下这么大的数据量的，最终的结果就是online ddl失败。

 

【innodb_tmpdir】

　　如果有设置innodb_tmpdir的值、那么在online ddl时innodb会以innodb_tmpdir的值为准、也就是说事实上形成了一种innodb_tmpdir覆盖

　　tmpdir的效果。

　　回过头来、如果MySQL实例没有设置innodb_tmpdir的值自然就是以tmpdir的值为准啦。

 

【设置innodb_tmpdir的注意事项】

　　1、设置innodb_tmpdir要求用户有file权限

　　2、innodb本身要求innodb_tmpdir的值不能与datadir的值相同

-- 把innodb_tmpdir设置为datadir是行不通的
mysql> set @@global.innodb_tmpdir='/database/mysql/data/3306/';                                                    
ERROR 1231 (42000): Variable 'innodb_tmpdir' can't be set to the value of '/database/mysql/data/3306/'

 

【innodb_tmpdir的最佳实践】

　　1、设计时应该尽可能的想到用数据分片来处理表、不要让表太大(超过tmpdir目录的大小)、虽然这条写在最前面我自己并不怎么care!

　　2、推荐把数据(datadir)与数据库备份文件保存在不同的物理磁盘上，一来可以在备份的时候尽可能的减小对oltp的影响，二来这种情况

　　下把tmpdir配置成备份盘的一个目录是非常好的一个选择。(我们的数据库主机上没有第二块盘呀！“穷”本身就是一个问题)

　　3、实在没有办法就在执行online ddl前给innodb_tmpdir分配一个大点的目录吧，好歹也能保证执行成功呀！

 

【innodb online ddl的官方文档】

　　官方文档 : https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl.html

----