0

mysql server is not starting after modifying configurations

key_buffer      = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
query_cache_size    = 16M

after change ->

key_buffer_size = 384M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M



innodb_buffer_pool_size = 375M

after change ->

innodb_buffer_pool_size = 384M
innodb_additional_mem_pool_size = 20M

# Set .._log_file_size to 25 % of buffer pool size

innodb_log_file_size = 100M
innodb_log_buffer_size = 8M

#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

configuration -> my.cnf

# 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]
#
# * 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_size = 384M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
#thread_concurrency = 8

query_cache_limit   = 10M
query_cache_size        = 16M

#
# * 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
#log_slow_queries   = /var/log/mysql/mysql-slow.log
#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!


#innodb_data_home_dir = /var/lib/mysql
#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high

innodb_buffer_pool_size = 384M
innodb_additional_mem_pool_size = 20M

# Set .._log_file_size to 25 % of buffer pool size

innodb_log_file_size = 100M
innodb_log_buffer_size = 8M

#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50


#    #



#
# * 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  = 16M

[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/

140725 10:33:07 [Warning] Using unique option prefix myisam-recover instead of myisam-      recover-options is deprecated and will be removed in a future release. Please use the full name instead.
140725 10:33:07 [Note] Plugin 'FEDERATED' is disabled.
140725 10:33:07 InnoDB: The InnoDB memory heap is disabled
140725 10:33:07 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140725 10:33:07 InnoDB: Compressed tables use zlib 1.2.8
140725 10:33:07 InnoDB: Using Linux native AIO
140725 10:33:07 InnoDB: Initializing buffer pool, size = 375.0M
140725 10:33:07 InnoDB: Completed initialization of buffer pool
140725 10:33:07 InnoDB: highest supported file format is Barracuda.
140725 10:33:07  InnoDB: Waiting for the background threads to start
140725 10:33:08 InnoDB: 5.5.38 started; log sequence number 1224364093
140725 10:33:08 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
140725 10:33:08 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
140725 10:33:08 [Note] Server socket created on IP: '127.0.0.1'.
140725 10:33:08 [Note] Event Scheduler: Loaded 0 events
140725 10:33:08 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.38-0ubuntu0.14.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
140725 10:33:57 [Note] /usr/sbin/mysqld: Normal shutdown

140725 10:33:57 [Note] Event Scheduler: Purging the queue. 0 events
140725 10:33:57  InnoDB: Starting shutdown...
140725 10:33:57  InnoDB: Shutdown completed; log sequence number 1224364093
140725 10:33:57 [Note] /usr/sbin/mysqld: Shutdown complete

140725 10:40:16 [Note] Plugin 'FEDERATED' is disabled.
140725 10:40:16 InnoDB: The InnoDB memory heap is disabled
140725 10:40:16 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140725 10:40:16 InnoDB: Compressed tables use zlib 1.2.8
140725 10:40:16 InnoDB: Using Linux native AIO
140725 10:40:16 InnoDB: Initializing buffer pool, size = 384.0M
140725 10:40:16 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 104857600 bytes!
140725 10:40:16 [ERROR] Plugin 'InnoDB' init function returned error.
140725 10:40:16 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
140725 10:40:16 [ERROR] Unknown/unsupported storage engine: InnoDB
140725 10:40:16 [ERROR] Aborting

140725 10:40:16 [Note] /usr/sbin/mysqld: Shutdown complete

140725 10:40:17 [Note] Plugin 'FEDERATED' is disabled.
140725 10:40:17 InnoDB: The InnoDB memory heap is disabled
140725 10:40:17 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140725 10:40:17 InnoDB: Compressed tables use zlib 1.2.8
140725 10:40:17 InnoDB: Using Linux native AIO
140725 10:40:17 InnoDB: Initializing buffer pool, size = 384.0M
140725 10:40:17 InnoDB: Completed initialization of buffer pool

InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes InnoDB: than specified in the .cnf file 0 104857600 bytes! 140725 10:40:17 [ERROR] Plugin 'InnoDB' init function returned error. 140725 10:40:17 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 140725 10:40:17 [ERROR] Unknown/unsupported storage engine: InnoDB 140725 10:40:17 [ERROR] Aborting

140725 10:40:17 [Note] /usr/sbin/mysqld: Shutdown complete

140725 10:40:18 [Note] Plugin 'FEDERATED' is disabled.
140725 10:40:18 InnoDB: The InnoDB memory heap is disabled
140725 10:40:18 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140725 10:40:18 InnoDB: Compressed tables use zlib 1.2.8
140725 10:40:18 InnoDB: Using Linux native AIO
140725 10:40:18 InnoDB: Initializing buffer pool, size = 384.0M
140725 10:40:18 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 104857600 bytes!
140725 10:40:18 [ERROR] Plugin 'InnoDB' init function returned error.
140725 10:40:18 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
140725 10:40:18 [ERROR] Unknown/unsupported storage engine: InnoDB
140725 10:40:18 [ERROR] Aborting

140725 10:40:18 [Note] /usr/sbin/mysqld: Shutdown complete

140725 10:48:07 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
140725 10:48:07 [Note] Plugin 'FEDERATED' is disabled.
140725 10:48:07 InnoDB: The InnoDB memory heap is disabled
140725 10:48:07 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140725 10:48:07 InnoDB: Compressed tables use zlib 1.2.8
140725 10:48:07 InnoDB: Using Linux native AIO
140725 10:48:07 InnoDB: Initializing buffer pool, size = 375.0M
140725 10:48:07 InnoDB: Completed initialization of buffer pool
140725 10:48:07 InnoDB: highest supported file format is Barracuda.
140725 10:48:07  InnoDB: Waiting for the background threads to start
140725 10:48:08 InnoDB: 5.5.38 started; log sequence number 1224364093
140725 10:48:08 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
140725 10:48:08 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
140725 10:48:08 [Note] Server socket created on IP: '127.0.0.1'.
140725 10:48:08 [Note] Event Scheduler: Loaded 0 events
140725 10:48:08 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.38-0ubuntu0.14.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)

closed as unclear what you're asking by Mat, Paul White, gbn, Philᵀᴹ, Michael - sqlbot Sep 5 '14 at 16:40

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • What did you change? What errors are you getting, including in the server log? – Mat Jul 25 '14 at 10:53
  • i have added changes @Mat – Surjit Sidhu Jul 25 '14 at 11:00
  • Did you at least do a quick google for that error message? – Mat Jul 25 '14 at 11:05
  • @Mat I'm new to Mysql don't know what line causing it so, i can do search on google. – Surjit Sidhu Jul 25 '14 at 11:11
1

You changed the innodb_log_file_size and, as you are not using 5.6 (where that is handled automatically), you have to delete manually ib_logfile0 and ib_logfile1 when MySQL is stopped for InnoDB to recreate them with the new size on start.

Make sure you do a clean shutdown before doing all that, not a crash / innodb_fast_shutdown = 2.

  • it solved the issue thnx. – Surjit Sidhu Jul 25 '14 at 13:26
  • any idea why count query too slow. SELECT COUNT(*) AS count FROM perfectlocal.businesses AS Business LEFT JOIN perfectlocal.postal AS Postal ON (Business.postal_id = Postal.id) LEFT JOIN perfectlocal.businesses_categories AS BusinessesCategory ON (Business.id = BusinessesCategory.business_id) LEFT JOIN perfectlocal.categories AS Category ON (BusinessesCategory.category_id = Category.id) WHERE Category.category_slug = 'restaurants' AND Business.address_locality = 'montreal' – Surjit Sidhu Jul 25 '14 at 13:26
  • I recommend you creating a different question and linking it here, the formatting is unreadable. Cheers. – jynus Jul 25 '14 at 14:47
  • how to do that ? – Surjit Sidhu Jul 25 '14 at 15:57

Not the answer you're looking for? Browse other questions tagged or ask your own question.