how to change innodb_log_file_size'
Moderator: crythias
-
- Znuny newbie
- Posts: 16
- Joined: 08 Jul 2015, 08:57
- Znuny Version: 4.0.9
- Real Name: dharmendra
- Company: neso
how to change innodb_log_file_size'
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
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
Re: how to change innodb_log_file_size'
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.
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.
-
- Znuny newbie
- Posts: 16
- Joined: 08 Jul 2015, 08:57
- Znuny Version: 4.0.9
- Real Name: dharmendra
- Company: neso
Re: how to change innodb_log_file_size'
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
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
-
- Administrator
- Posts: 3965
- Joined: 18 Dec 2007, 12:23
- Znuny Version: Znuny and Znuny LTS
- Real Name: Roy Kaldung
- Company: Znuny
- Contact:
Re: how to change innodb_log_file_size'
What does the following query returns?
Code: Select all
SHOW VARIABLES LIKE 'innodb_log_file_size';
Znuny and Znuny LTS running on CentOS / RHEL / Debian / SLES / MySQL / PostgreSQL / Oracle / OpenLDAP / Active Directory / SSO
Use a test system - always.
Do you need professional services? Check out https://www.znuny.com/
Do you want to contribute or want to know where it goes ?
Use a test system - always.
Do you need professional services? Check out https://www.znuny.com/
Do you want to contribute or want to know where it goes ?
-
- Znuny newbie
- Posts: 16
- Joined: 08 Jul 2015, 08:57
- Znuny Version: 4.0.9
- Real Name: dharmendra
- Company: neso
Re: how to change innodb_log_file_size'
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
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
-
- Administrator
- Posts: 3965
- Joined: 18 Dec 2007, 12:23
- Znuny Version: Znuny and Znuny LTS
- Real Name: Roy Kaldung
- Company: Znuny
- Contact:
Re: how to change innodb_log_file_size'
Look's like you haven't set the innodb_log_filesize in the mtsql server configuration file.
- Shutdown the mysql server
- adjust the log file setting in the mysql sever config file
- move the logfiles away
- restart the server and check the setting with the query
Znuny and Znuny LTS running on CentOS / RHEL / Debian / SLES / MySQL / PostgreSQL / Oracle / OpenLDAP / Active Directory / SSO
Use a test system - always.
Do you need professional services? Check out https://www.znuny.com/
Do you want to contribute or want to know where it goes ?
Use a test system - always.
Do you need professional services? Check out https://www.znuny.com/
Do you want to contribute or want to know where it goes ?
-
- Znuny newbie
- Posts: 16
- Joined: 08 Jul 2015, 08:57
- Znuny Version: 4.0.9
- Real Name: dharmendra
- Company: neso
Re: how to change innodb_log_file_size'
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>
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>
Re: how to change innodb_log_file_size'
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.
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.
-
- Moderator
- Posts: 10169
- Joined: 04 May 2010, 18:38
- Znuny Version: 5.0.x
- Location: SouthWest Florida, USA
- Contact:
Re: how to change innodb_log_file_size'
http://www.mysql.com/support/supportedp ... abase.htmlbharat wrote:I am using mysql 5.1.7 version
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
OTRS 6.0.x (private/testing/public) on Linux with MySQL database.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
-
- Znuny newbie
- Posts: 16
- Joined: 08 Jul 2015, 08:57
- Znuny Version: 4.0.9
- Real Name: dharmendra
- Company: neso
Re: how to change innodb_log_file_size'
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
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
-
- Znuny newbie
- Posts: 16
- Joined: 08 Jul 2015, 08:57
- Znuny Version: 4.0.9
- Real Name: dharmendra
- Company: neso
Re: how to change innodb_log_file_size'
Hi,
Thanks a lot for information.
I have updated mysql to 5.5 now.
regards
Thanks a lot for information.
I have updated mysql to 5.5 now.
regards
-
- Znuny newbie
- Posts: 16
- Joined: 08 Jul 2015, 08:57
- Znuny Version: 4.0.9
- Real Name: dharmendra
- Company: neso
Re: how to change innodb_log_file_size'
Hi,
Now getting below error.
even though I made settings and restarted mysqld and https multiple time.
regards
Now getting below error.
even though I made settings and restarted mysqld and https multiple time.
regards
You do not have the required permissions to view the files attached to this post.
-
- Moderator
- Posts: 10169
- Joined: 04 May 2010, 18:38
- Znuny Version: 5.0.x
- Location: SouthWest Florida, USA
- Contact:
Re: how to change innodb_log_file_size'
[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.
OTRS 6.0.x (private/testing/public) on Linux with MySQL database.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
-
- Znuny newbie
- Posts: 16
- Joined: 08 Jul 2015, 08:57
- Znuny Version: 4.0.9
- Real Name: dharmendra
- Company: neso
Re: how to change innodb_log_file_size'
thanks crythias.
-
- Znuny newbie
- Posts: 49
- Joined: 18 Nov 2013, 17:37
- Znuny Version: 4.0.5
- Real Name: Timmy Lutz
- Company: ERNI AG
Re: how to change innodb_log_file_size'
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
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
OTRS 4.0.7
OS: CentOS 6.6
OS: CentOS 6.6
-
- Znuny newbie
- Posts: 14
- Joined: 08 Dec 2016, 13:21
- Znuny Version: 5.x.x
Re: how to change innodb_log_file_size'
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...
-
- Administrator
- Posts: 3965
- Joined: 18 Dec 2007, 12:23
- Znuny Version: Znuny and Znuny LTS
- Real Name: Roy Kaldung
- Company: Znuny
- Contact:
Re: how to change innodb_log_file_size'
Give us your complete my.cnf
Znuny and Znuny LTS running on CentOS / RHEL / Debian / SLES / MySQL / PostgreSQL / Oracle / OpenLDAP / Active Directory / SSO
Use a test system - always.
Do you need professional services? Check out https://www.znuny.com/
Do you want to contribute or want to know where it goes ?
Use a test system - always.
Do you need professional services? Check out https://www.znuny.com/
Do you want to contribute or want to know where it goes ?
-
- Znuny newbie
- Posts: 14
- Joined: 08 Dec 2016, 13:21
- Znuny Version: 5.x.x
Re: how to change innodb_log_file_size'
Fixed it already, don't really know how tho xD
-
- Znuny newbie
- Posts: 14
- Joined: 08 Dec 2016, 13:21
- Znuny Version: 5.x.x
Re: how to change innodb_log_file_size'
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:
Although, I did change the my.cnf file:
Please help?!
EDIT: Got it to work now, took some time to process I think...
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
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/
EDIT: Got it to work now, took some time to process I think...