Mysql ibtmp1 taking too much space: solutions

I recently had one of my AWS instances running out of disk space. Mysql server (version 14.14, running a few hundred Mb single databae) created a temporary file of over 11Gb at the path /var/lib/mysql/ibtmp1 and saturated the 16GB disk.

I solved that with this setting

innodb_temp_data_file_path = ibtmp1:100M:autoextend:max:1G

And also the following command, that sets fast shutdown, stops mysql, deletes that temp file, and start mysql again

mysql -u root -e "SET GLOBAL innodb_fast_shutdown = 0;"; 
service mysql stop; 
rm /var/lib/mysql/ibtmp1; 
service mysql start

If you use ansible, you can just have this task

- name: mysql custom config
    src: files/mysqld-custom.cnf
    dest: /etc/mysql/mysql.conf.d/mysqld-custom.cnf
    mode: "744"

where files/mysqld-custom.cnf contains the following


# limits /var/lib/mysql/ibtmp1 to 100Mb-1GB
innodb_temp_data_file_path = ibtmp1:100M:autoextend:max:1G