How to update mysql my.cnf without getting error?

Question

I updated the MySQL my.conf file and and restarted my server without a problem.

As soon as I tried to access a table I received the error

mysql #1033 - Incorrect information in file:

I reverted the my.cnf file back to the original and the server is working properly.

I am looking to update some of the innodb settings:

innodb_buffer_pool_size
innodb_additional_mem_pool_size
innodb_log_file_size
innodb_log_buffer_size

I think I am seeing that I have to update the my.cnf file before creating any databases on the server, but that doesn't seem right.

Is there a way to update the my.cnf file after a database has been created on the server?

CentOS 5.6

MySQL 5.0.77

Original:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

New:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# This is my configuration!
innodb_buffer_pool_size=4G
innodb_additional_mem_pool_size=20M

innodb_log_file_size=1G
innodb_log_buffer_size=8M

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

score 1 · Accepted Answer · edited Apr 13 '17 at 12:42

You mentioned these four options

innodb_buffer_pool_size
innodb_additional_mem_pool_size
innodb_log_file_size
innodb_log_buffer_size

The one that worries me is innodb_log_file_size

Upon initial startup, innodb_log_file_size is 5MB. You need to make new InnoDB log files. There are two InnoDB log files that got created 5MB each:

/var/lib/mysql/ib_logfile0
/var/lib/mysql/ib_logfile1

All you have to do is the following:

service mysql stop
rm -f ib_logfile*
service mysql start

The two log files will be regenerated as 1GB files. I wrote about this last year.

Give it a Try !!!

BTW DO NOT TOUCH /var/lib/mysql/ibata1 !!!

How to update mysql my.cnf without getting error?

1 Answers1