MySQL causing hard disk to running out of space

Recently we have had a couple of server run out of hard disk space causing inaccessibility and crashing. On further investigation the hard drive was full so many operations were complaining. The hard drive was being consumed by a very large file within the /var/lib/mysql directory called ibtmp1. So MySQL is causing the server to run out of disk space.

It turns out that this is the temporary working file for MySQL. When you turn the MySQL server off and on again the file is deleted and the server runs again without issue until the file becomes too large again.

After some quick research we found that adding or editing a few settings in the MySQL configuration allows you to limit the size of this file and prevent this issue from reoccurring.

Open the mysqld.cnf file. The location of our mysqld.cnf was in /etc/mysql/mysql.conf.d/mysqld.conf. According to the file you can also copy this configuration to /etc/mysql/my.cnf. Our my.cnf just had an include to the mysqld.cnf file.

The file should have some sections like:

[mysqld_safe]
[mysqld]

There should be a section for InnoDB settings. If not, it is fine to add these configuration settings at the bottom of the file.

Here is the configuration variable:

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

This value can be tweaked based on your server setup and needs but these are the settings we used for our server to set the file to start at 12 MB and limit it to 1 GB.

While setting this we also set a few other variables for optimisation:

innodb_buffer_pool_size=2048M
innodb_log_file_size=25M
innodb_log_buffer_size=80M

If you have had any other experiences on optimising MySQL, please let us know.

Mysql export/import

The following commands export mysql database to file then import from the file:

mysqldump -u root -p[PASSWORD] [DB_NAME] > [FILE_NAME].sql
mysql -u root -p[PASSWORD] [DB_NAME] < [FILE_NAME].sql

Make sure the database is already created before importing.

Case insensitive mysql query

The following addition to a query corrects an encoding quirk if your mysql database isn’t using case insensitive encoding.

SELECT * FROM myTable WHERE name COLLATE UTF_GENERAL_CI LIKE '%input%'

The ‘CI’ stands for Case Insensitive. Putting both the comparing field and the input into lowercase using LOWER() can fix similar issues but not with this encoding issue.

changing mysql database encoding

Dump the mysql database

sed 's/DEFAULT CHARACTER SET latin1/DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci/g' Dump.sql > Dump-update1.sql

sed 's/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8/g' Dump-update1.sql > Dump-update2.sql

import Dump-update2.sql