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 copy: src: files/mysqld-custom.cnf dest: /etc/mysql/mysql.conf.d/mysqld-custom.cnf mode: "744"
where files/mysqld-custom.cnf contains the following
[mysqld] # limits /var/lib/mysql/ibtmp1 to 100Mb-1GB innodb_temp_data_file_path = ibtmp1:100M:autoextend:max:1G