Is it safe to modify innodb_data_file_path value for mysql 5?

Question

I recently experienced data corruption after changing the value of this parameter in my.cnf from :

innodb_data_file_path = ibdata1:10M:autoextend:max:128M

to :

innodb_data_file_path = ibdata1:10M:autoextend:max:256M

I am not completely sure this was the reason of the corruption since previously the DB ran out of space. My question is, is it safe to modify the max size of the DB once the space is full ?

Solution

If you want to be sure, you should to the following:

First, change the innodb_data_file_path in /etc/my.cnf to

[mysqld]
innodb_data_file_path = ibdata1:10M:autoextend

The, run the following

cd
service mysql restart --skip-networking --skip-grant-tables
mysqldump --single-transaction --routines --triggers --all-databases > MySQLData.sql
service mysql stop

Make sure /root/MySQLData.sql exists. Then, proceed with

rm /var/lib/mysql/ibdata1
rm /var/lib/mysql/ib_logfile0
rm /var/lib/mysql/ib_logfile1
service mysql start --skip-networking --skip-grant-tables
mysql < MySQLData.sql
service mysql restart

Give it a Try !!!

Actually, I have a monitoring at my job that uses MySQL as its repository and I changed the max number from 1TB to 16TB. During the collection of data, nothing could be written but no corruption was introduced. You could just change the number.

I would remove the max value altogether as I mentioned

innodb_data_file_path = ibdata1:10M:autoextend

for two reasons:

1. the change is very small
2. ibdata1 will always be written in because the undo tablespace in within it and it must be written. This will cause some occassional growth of ibdata1. You may just run into this again. So, it is best to remove the max option.

Comments

[drcelus]

Well, this is in fact what I did to restore the DB back to a working state. I must have a max size limit for my installation since I am running on very limited resources and some DB are on development and want to avoid any run away process to fill up my server space. So you mean it is not safe to change this value and a complete restore must be done always?