how to change innodb_log_file_size'

[bharat]

Hi,
I am using centos and OTRS 4.0.9 and running first time installer.pl and it is stucked on innodb log file size.
please let me know how to change as I have already made changes in my.cnf and restarted httpd and myswld service but still it is prompting same message.

I have made all possible changes suggested in links.
like
SET GLOBAL innodb_fast_shutdown = 1;
and var/mysql file moving to bkp and then restarting myswl services still problem is same.

regards

[EXG133]

Versions older than 5.7 of MySQL won't change the logfiles automatically. You need to shut down the service, change the option and then move the current logfiles away. Then start the service and MySQL will throw an error it can't find the logfiles and create new ones.

To make sure I didn't forget anything heck here (for whatever version of MySQL you are using): http://dev.mysql.com/doc/refman/5.6/en/ ... ation.html

In the future include what version of MySQL you are using when asking database questions, much easier to answer then.

[bharat]

Hi,
Sorry for delay in response.
please find below details.
I am using mysql 5.1.7 version and all the steps required already taken still I am getting same error message of size change is required.

please suggest.

Regards

[root]

What does the following query returns?

Code: Select all

SHOW VARIABLES LIKE 'innodb_log_file_size';

[bharat]

Hi,
I got below result from command.

mysql> SHOW VARIABLES LIKE 'innodb_log_file_size';
+----------------------+---------+
| Variable_name | Value |
+----------------------+---------+
| innodb_log_file_size | 5242880 |
+----------------------+---------+
1 row in set (0.00 sec)

mysql>


Regards

[root]

Look's like you haven't set the innodb_log_filesize in the mtsql server configuration file.

1. Shutdown the mysql server
2. adjust the log file setting in the mysql sever config file
3. move the logfiles away
4. restart the server and check the setting with the query

[bharat]

Hi,
I have already done this activity twice still same message.
whereas in mysql size is showing
query.mysql> SELECT variable_value FROM information_schema.global_variables WHERE variable_name = 'innodb_buffer_pool_size';
+----------------+
| variable_value |
+----------------+
| 8388608 |
+----------------+
1 row in set (0.00 sec)

mysql>

[EXG133]

innodb_buffer_pool_size is not the same as innodb_log_file_size.

innodb_buffer_pool_size = RAM used as cache for innodb table and index
innodb_log_file_size = size of the log files for innodb tables

Both values are very important for your DB but do completely different things.

[crythias]

I am using mysql 5.1.7 version

http://www.mysql.com/support/supportedp ... abase.html
MySQL 5.1 is not supported on CentOS 6+

OTRS is not supported on CentOS 5-
https://otrs.github.io/doc/manual/admin ... -on-centos

[bharat]

Thanks for information.
would you please help to upgrade mysql from 5.1 to 5.5 on this box as it is not yet in production .
as whenever i am running yum it is only upgrading upto final version of mysql 5.1 only.
regards

[bharat]

Hi,
Thanks a lot for information.
I have updated mysql to 5.5 now.

regards

[bharat]

Hi,
Now getting below error.
even though I made settings and restarted mysqld and https multiple time.

regards

[crythias]

[mysqdld_safe] may not be the context under which you are running mysql. see if [mysql] or [mysqld] is a section under my.cnf and make your change there.

[bharat]

thanks crythias.

[timmy80]

Für alle die es sich einfach machen wollen und nicht erst einige Commands zusammen suchen wollen. Hier Step by Step

Stopping mysql
# service mysqld stop

Rename all logfiles to xyz_OLD
# find /var/lib/mysql -type f -name "ib_logfile?" -exec mv {} {}_OLD \;

Check the logfiles if this is renamed
# ls -la /var/lib/mysql/ib_logfile*
-rw-rw---- 1 mysql mysql 5242880 Apr 3 01:24 /var/lib/mysql/ib_logfile0_OLD
-rw-rw---- 1 mysql mysql 5242880 Jan 31 2010 /var/lib/mysql/ib_logfile1_OLD

Grep your my.cnf if there a parameter already set
# grep innodb_log_file_size /etc/my.cnf
You will see, nothing will displayed.

Open my.cnf in "etc" with notepad++ or similar
And add following parameter

innodb_log_file_size = 265M
Looks like:
[mysqld]
query_cache_size = 48M
max_allowed_packet = 48M
innodb_log_file_size = 256M

Run the command again
# grep innodb_log_file_size /etc/my.cnf

And you will see the grep will now show you a value.

# grep innodb_log_file_size /etc/my.cnf
innodb_log_file_size = 256M

Start mysql
#service mysqld start

Done

[MusicMaikel]

Hmmm, I have the same problem, but it still gives the error when I add it to the config file... Where do I need to put the line, because I already tried it under [mysqld], but with no result...

[root]

Give us your complete my.cnf

[MusicMaikel]

Fixed it already, don't really know how tho xD

[MusicMaikel]

Started with a fresh new installation and this time I can't get it to work...
Added the lines that are given above, but when I add them and repeat the grep command, it gives me the following output:

Code: Select all

grep: /etc/my.cnf: No such file or directory

Although, I did change the my.cnf file:

Code: Select all

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0

[mysqld]
query_cache_size = 48M
max_allowed_packet = 48M
innodb_log_file_size = 256M
#
# * Basic Settings
#
user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /var/lib/mysql
tmpdir		= /tmp
lc-messages-dir	= /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address		= 127.0.0.1
#
# * Fine Tuning
#
key_buffer		= 16M
max_allowed_packet	= 48M
thread_stack		= 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit	= 1M
query_cache_size        = 48M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#slow_query_log_file = /var/log/mysql/mysql-slow.log
#slow_query_log      = 1
#long_query_time = 2
#log_queries_not_using_indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id		= 1
#log_bin			= /var/log/mysql/mysql-bin.log
expire_logs_days	= 10
max_binlog_size         = 100M
#binlog_do_db		= include_database_name
#binlog_ignore_db	= include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem



[mysqldump]
quick
quote-names
max_allowed_packet	= 21M

[mysql]
#no-auto-rehash	# faster start of mysql but no tab completition

[isamchk]
key_buffer		= 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

Please help?!

EDIT: Got it to work now, took some time to process I think...