Solved

MySQL doesn't start anymore

Posted on 2006-11-21
7
765 Views
Last Modified: 2008-01-09
Hi Experts,

First of all I'm a regular n00b when it comes to hosting and linux. But I do have my own webserver, linux with Plesk 7.5. (a so called flexserver VPS)

Last week my harddisk was full, so in a attempt to free up from space I emptied the 'ib_logfile0' and 'ib_logfile1' files. That wasn't very smart to do I realise. Later I found a error_log of 4GB.

So my harddrive is good now, but MySQL won't start anymore.

This is my mysqld.log:
-----------------

061120 15:43:53  mysqld started
061120 15:43:53  InnoDB: Out of memory in additional memory pool.
InnoDB: InnoDB will start allocating memory from the OS.
InnoDB: You may get better performance if you configure a bigger
InnoDB: value in the MySQL my.cnf file for
InnoDB: innodb_additional_mem_pool_size.
InnoDB: Error: log file ./ib_logfile0 is of different size
InnoDB: than specified in the .cnf file!
061120 15:43:53  Can't init databases
061120 15:43:53  mysqld ended

061120 15:48:25  mysqld started
061120 15:48:25  InnoDB: Out of memory in additional memory pool.
InnoDB: InnoDB will start allocating memory from the OS.
InnoDB: You may get better performance if you configure a bigger
InnoDB: value in the MySQL my.cnf file for
InnoDB: innodb_additional_mem_pool_size.
InnoDB: Error: log file ./ib_logfile0 is of different size
InnoDB: than specified in the .cnf file!
061120 15:48:25  Can't init databases
061120 15:48:25  mysqld ended

----------------



This is my 'my.cnf':
-------------

[mysqld]
safe-show-database
innodb_data_file_path=ibdata1:10M:autoextend
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
     
[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

-----------------

And when I try to start the 'psa' service by typing : service psa startall. I get this error message:
-----

ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
websrvmng: db_connect: failed to connect to database: Error: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
websrvmng: db_connect: failed to connect to database: Error: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
websrvmng: Unable to connect to the mysql database
websrvmng: Unable to connect to the mysql database

-----------


Can someone help my please?

Many thanks in advance!

0
Comment
Question by:RSBuTCHeR
  • 2
  • 2
7 Comments
 
LVL 17

Expert Comment

by:akshah123
Comment Utility
Well, in order to solve this first move the current ./ib_logfile0  and ./ib_logfile1 files to a safe location.  Then delete these files.  Restart the server.  If the files are not there, mysql will create the new ones.  I think currently, you have files that are of different size than specified in your my.cnf.  

Also, you might want to define the innodb variables in my.cnf to provide more room for additional memory pool as according to your mysqld.log, it is running out of it.

Add something like following under [mysqld]:


# You can write your other MySQL server options here
# ...
# Data files must be able to hold your data and indexes.
# Make sure that you have enough free disk space.
innodb_data_file_path = ibdata1:10M:autoextend
#
# Set buffer pool size to 50-80% of your computer's memory
innodb_buffer_pool_size=70M
innodb_additional_mem_pool_size=10M
#
# Set the log file size to about 25% of the buffer pool size
innodb_log_file_size=20M
innodb_log_buffer_size=8M
#
innodb_flush_log_at_trx_commit=1

Ofcourse, you should change the numbers to optimize the performance in your scenario.  Taken from: http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html
0
 
LVL 1

Author Comment

by:RSBuTCHeR
Comment Utility
Thanks for your quick response!

I tried what you said, but without succes. I moved the files, rebooted the server, changed the my.cnf.

Still getting this error, when I type 'service psa start':

-----

ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/my
ysql.sock' (2)E
ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/my
ysql.sock' (2)d
db_connect: failed to connect to database: Error: Can't connect to local MySQL se
erver through socket '/var/lib/mysql/mysql.sock' (2)d
db_connect: failed to connect to database: Error: Can't connect to local MySQL se
erver through socket '/var/lib/mysql/mysql.sock' (2)K
Key register failed/
/usr/local/psa/admin/bin/httpsdctl: line 227: 15476 Terminated              $HTTP
PD/

---------------

0
 
LVL 17

Expert Comment

by:akshah123
Comment Utility
What are you getting in your mysqld.log file?  
0
 
LVL 1

Author Comment

by:RSBuTCHeR
Comment Utility
This is my mysqld.log:
---------------------------------------------------

061120 15:43:53  mysqld started
061120 15:43:53  InnoDB: Out of memory in additional memory pool.
InnoDB: InnoDB will start allocating memory from the OS.
InnoDB: You may get better performance if you configure a bigger
InnoDB: value in the MySQL my.cnf file for
InnoDB: innodb_additional_mem_pool_size.
InnoDB: Error: log file ./ib_logfile0 is of different size
InnoDB: than specified in the .cnf file!
061120 15:43:53  Can't init databases
061120 15:43:53  mysqld ended

061120 15:48:25  mysqld started
061120 15:48:25  InnoDB: Out of memory in additional memory pool.
InnoDB: InnoDB will start allocating memory from the OS.
InnoDB: You may get better performance if you configure a bigger
InnoDB: value in the MySQL my.cnf file for
InnoDB: innodb_additional_mem_pool_size.
InnoDB: Error: log file ./ib_logfile0 is of different size
InnoDB: than specified in the .cnf file!
061120 15:48:25  Can't init databases
061120 15:48:25  mysqld ended

061120 19:39:34  mysqld started
/usr/libexec/mysqld: unrecognized option `--innodb_log_file_size=100M'
/usr/libexec/mysqld  Ver 3.23.58 for redhat-linux-gnu on i386
Copyright (C) 2000 MySQL AB, by Monty and others
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Starts the MySQL server

Usage: /usr/libexec/mysqld [OPTIONS]

  --ansi            Use ANSI SQL syntax instead of MySQL syntax
  -b, --basedir=path      Path to installation directory. All paths are
                  usually resolved relative to this
  --big-tables            Allow big result sets by saving all temporary sets
                  on file (Solves most 'table full' errors)
  --bind-address=IP      Ip address to bind to
  --bootstrap            Used by mysql installation scripts
  --character-sets-dir=...
                        Directory where character sets are
  --chroot=path            Chroot mysqld daemon during startup
  --core-file            Write core on errors
  -h, --datadir=path      Path to the database root
  --default-character-set=charset
                  Set the default character set
  --default-table-type=type
                  Set the default table type for tables
  --delay-key-write-for-all-tables
                  Don't flush key buffers between writes for any MyISAM
                  table
  --enable-locking      Enable system locking
  -T, --exit-info      Used for debugging;  Use at your own risk!
  --flush            Flush tables to disk between SQL commands
  -?, --help            Display this help and exit
  --init-file=file      Read SQL commands from this file at startup
  -L, --language=...      Client error messages in given language. May be
                  given as a full path
  --local-infile=[1|0]  Enable/disable LOAD DATA LOCAL INFILE
  -l, --log[=file]      Log connections and queries to file
  --log-bin[=file]      Log queries in new binary format (for replication)
  --log-bin-index=file  File that holds the names for last binary log files
  --log-update[=file]      Log updates to file.# where # is a unique number
                  if not given.
  --log-isam[=file]      Log all MyISAM changes to file
  --log-long-format      Log some extra information to update log
  --low-priority-updates INSERT/DELETE/UPDATE has lower priority than selects
  --log-slow-queries=[file]
                  Log slow queries to this log file.  Defaults logging
                        to hostname-slow.log
  --pid-file=path      Pid file used by safe_mysqld
  --myisam-recover[=option[,option...]] where options is one of DEAULT,
                  BACKUP or FORCE.
  --memlock            Lock mysqld in memory
  -n, --new            Use very new possible 'unsafe' functions
  -o, --old-protocol      Use the old (3.20) protocol
  -P, --port=...      Port number to use for connection

  -O, --set-variable var=option
                  Give a variable an value. --help lists variables
  -Sg, --skip-grant-tables
                  Start without grant tables. This gives all users
                  FULL ACCESS to all tables!
  --safe-mode            Skip some optimize stages (for testing)
  --safe-show-database  Don't show databases for which the user has no
                        privileges
  --safe-user-create      Don't new users cretaion without privileges to the
                    mysql.user table
  --skip-concurrent-insert
                    Don't use concurrent insert with MyISAM
  --skip-delay-key-write
                  Ignore the delay_key_write option for all tables
  --skip-host-cache      Don't cache host names
  --skip-locking      Don't use system locking. To use isamchk one has
                  to shut down the server.
  --skip-name-resolve      Don't resolve hostnames.
                  All hostnames are IP's or 'localhost'
  --skip-networking      Don't allow connection with TCP/IP.
  --skip-new            Don't use new, possible wrong routines.

  --skip-stack-trace    Don't print a stack trace on failure
  --skip-show-database  Don't allow 'SHOW DATABASE' commands
  --skip-thread-priority
                  Don't give threads different priorities.
  --socket=...            Socket file to use for connection
  -t, --tmpdir=path      Path for temporary files
  --sql-mode=option[,option[,option...]] where option can be one of:
                        REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES,
                        IGNORE_SPACE, SERIALIZE, ONLY_FULL_GROUP_BY.
  --transaction-isolation
                    Default transaction isolation level
  --temp-pool           Use a pool of temporary files
  -u, --user=user_name      Run mysqld daemon as user
  -V, --version            output version information and exit
  -W, --warnings        Log some not critical warnings to the log file

  --bdb-home=  directory  Berkeley home direcory
  --bdb-lock-detect=#        Berkeley lock detect
                          (DEFAULT, OLDEST, RANDOM or YOUNGEST, # sec)
  --bdb-logdir=directory  Berkeley DB log file directory
  --bdb-no-sync              Don't synchronously flush logs
  --bdb-no-recover        Don't try to recover Berkeley DB tables on start
  --bdb-shared-data        Start Berkeley DB in multi-process mode
  --bdb-tmpdir=directory  Berkeley DB tempfile name
  --skip-bdb              Don't use berkeley db (will save memory)

  --innodb_data_home_dir=dir   The common part for Innodb table spaces
  --innodb_data_file_path=dir  Path to individual files and their sizes
  --innodb_flush_method=#  With which method to flush data
  --innodb_flush_log_at_trx_commit[=#]
                    Value 0: write and flush once per second
                          Value 1: write and flush at each commit
                          Value 2: write at commit, flush once per second
  --innodb_log_arch_dir=dir    Where full logs should be archived
  --innodb_log_archive[=#]     Set to 1 if you want to have logs archived
  --innodb_log_group_home_dir=dir  Path to innodb log files.
  --skip-innodb                   Don't use Innodb (will save memory)


Default options are read from the following files in the given order:
/etc/my.cnf /var/lib/mysql/my.cnf ~/.my.cnf
The following groups are read: mysqld server
The following options may be given as the first argument:
--print-defaults      Print the program argument list and exit
--no-defaults            Don't read default options from any options file
--defaults-file=#      Only read default options from the given file #
--defaults-extra-file=# Read this file after the global files are read

To see what values a running MySQL server is using, type
'mysqladmin variables' instead of 'mysqld --help'.
The default values (after parsing the command line arguments) are:

basedir:     /usr/
datadir:     /var/lib/mysql/
tmpdir:      /tmp/
language:    /usr/share/mysql/english/
pid file:    /var/lib/mysql/mijnstukje.pid
TCP port:    3306
Unix socket: /var/lib/mysql/mysql.sock

system locking is not in use

Possible variables for option --set-variable (-O) are:
back_log              current value: 50
bdb_cache_size        current value: 8388600
bdb_log_buffer_size   current value: 0
bdb_max_lock          current value: 10000
bdb_lock_max          current value: 10000
binlog_cache_size     current value: 32768
connect_timeout       current value: 5
delayed_insert_timeout  current value: 300
delayed_insert_limit  current value: 100
delayed_queue_size    current value: 1000
flush_time            current value: 0
innodb_mirrored_log_groups  current value: 1
innodb_log_files_in_group  current value: 2
innodb_log_file_size  current value: 5242880
innodb_log_buffer_size  current value: 1048576
innodb_buffer_pool_size  current value: 8388608
innodb_additional_mem_pool_size  current value: 1048576
innodb_file_io_threads  current value: 4
innodb_lock_wait_timeout  current value: 50
innodb_thread_concurrency  current value: 8
innodb_force_recovery  current value: 0
interactive_timeout   current value: 28800
join_buffer_size      current value: 131072
key_buffer_size       current value: 8388600
long_query_time       current value: 10
lower_case_table_names  current value: 0
max_allowed_packet    current value: 1048576
max_binlog_cache_size  current value: 4294967295
max_binlog_size       current value: 1073741824
max_connections       current value: 100
max_connect_errors    current value: 10
max_delayed_threads   current value: 20
max_heap_table_size   current value: 16777216
max_join_size         current value: 4294967295
max_sort_length       current value: 1024
max_tmp_tables        current value: 32
max_user_connections  current value: 0
max_write_lock_count  current value: 4294967295
myisam_max_extra_sort_file_size  current value: 256
myisam_max_sort_file_size  current value: 2047
myisam_sort_buffer_size  current value: 8388608
net_buffer_length     current value: 16384
net_retry_count       current value: 10
net_read_timeout      current value: 30
net_write_timeout     current value: 60
open_files_limit      current value: 0
query_buffer_size     current value: 0
record_buffer         current value: 131072
record_rnd_buffer     current value: 0
slave_net_timeout     current value: 3600
slow_launch_time      current value: 2
sort_buffer           current value: 2097144
table_cache           current value: 64
thread_concurrency    current value: 10
thread_cache_size     current value: 0
tmp_table_size        current value: 33554432
thread_stack          current value: 65536
wait_timeout          current value: 28800
061120 19:39:34  mysqld ended

061121 19:33:09  mysqld started
/usr/libexec/mysqld: unrecognized option `--innodb_log_file_size=100M'
/usr/libexec/mysqld  Ver 3.23.58 for redhat-linux-gnu on i386
Copyright (C) 2000 MySQL AB, by Monty and others
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Starts the MySQL server

Usage: /usr/libexec/mysqld [OPTIONS]

  --ansi            Use ANSI SQL syntax instead of MySQL syntax
  -b, --basedir=path      Path to installation directory. All paths are
                  usually resolved relative to this
  --big-tables            Allow big result sets by saving all temporary sets
                  on file (Solves most 'table full' errors)
  --bind-address=IP      Ip address to bind to
  --bootstrap            Used by mysql installation scripts
  --character-sets-dir=...
                        Directory where character sets are
  --chroot=path            Chroot mysqld daemon during startup
  --core-file            Write core on errors
  -h, --datadir=path      Path to the database root
  --default-character-set=charset
                  Set the default character set
  --default-table-type=type
                  Set the default table type for tables
  --delay-key-write-for-all-tables
                  Don't flush key buffers between writes for any MyISAM
                  table
  --enable-locking      Enable system locking
  -T, --exit-info      Used for debugging;  Use at your own risk!
  --flush            Flush tables to disk between SQL commands
  -?, --help            Display this help and exit
  --init-file=file      Read SQL commands from this file at startup
  -L, --language=...      Client error messages in given language. May be
                  given as a full path
  --local-infile=[1|0]  Enable/disable LOAD DATA LOCAL INFILE
  -l, --log[=file]      Log connections and queries to file
  --log-bin[=file]      Log queries in new binary format (for replication)
  --log-bin-index=file  File that holds the names for last binary log files
  --log-update[=file]      Log updates to file.# where # is a unique number
                  if not given.
  --log-isam[=file]      Log all MyISAM changes to file
  --log-long-format      Log some extra information to update log
  --low-priority-updates INSERT/DELETE/UPDATE has lower priority than selects
  --log-slow-queries=[file]
                  Log slow queries to this log file.  Defaults logging
                        to hostname-slow.log
  --pid-file=path      Pid file used by safe_mysqld
  --myisam-recover[=option[,option...]] where options is one of DEAULT,
                  BACKUP or FORCE.
  --memlock            Lock mysqld in memory
  -n, --new            Use very new possible 'unsafe' functions
  -o, --old-protocol      Use the old (3.20) protocol
  -P, --port=...      Port number to use for connection

  -O, --set-variable var=option
                  Give a variable an value. --help lists variables
  -Sg, --skip-grant-tables
                  Start without grant tables. This gives all users
                  FULL ACCESS to all tables!
  --safe-mode            Skip some optimize stages (for testing)
  --safe-show-database  Don't show databases for which the user has no
                        privileges
  --safe-user-create      Don't new users cretaion without privileges to the
                    mysql.user table
  --skip-concurrent-insert
                    Don't use concurrent insert with MyISAM
  --skip-delay-key-write
                  Ignore the delay_key_write option for all tables
  --skip-host-cache      Don't cache host names
  --skip-locking      Don't use system locking. To use isamchk one has
                  to shut down the server.
  --skip-name-resolve      Don't resolve hostnames.
                  All hostnames are IP's or 'localhost'
  --skip-networking      Don't allow connection with TCP/IP.
  --skip-new            Don't use new, possible wrong routines.

  --skip-stack-trace    Don't print a stack trace on failure
  --skip-show-database  Don't allow 'SHOW DATABASE' commands
  --skip-thread-priority
                  Don't give threads different priorities.
  --socket=...            Socket file to use for connection
  -t, --tmpdir=path      Path for temporary files
  --sql-mode=option[,option[,option...]] where option can be one of:
                        REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES,
                        IGNORE_SPACE, SERIALIZE, ONLY_FULL_GROUP_BY.
  --transaction-isolation
                    Default transaction isolation level
  --temp-pool           Use a pool of temporary files
  -u, --user=user_name      Run mysqld daemon as user
  -V, --version            output version information and exit
  -W, --warnings        Log some not critical warnings to the log file

  --bdb-home=  directory  Berkeley home direcory
  --bdb-lock-detect=#        Berkeley lock detect
                          (DEFAULT, OLDEST, RANDOM or YOUNGEST, # sec)
  --bdb-logdir=directory  Berkeley DB log file directory
  --bdb-no-sync              Don't synchronously flush logs
  --bdb-no-recover        Don't try to recover Berkeley DB tables on start
  --bdb-shared-data        Start Berkeley DB in multi-process mode
  --bdb-tmpdir=directory  Berkeley DB tempfile name
  --skip-bdb              Don't use berkeley db (will save memory)

  --innodb_data_home_dir=dir   The common part for Innodb table spaces
  --innodb_data_file_path=dir  Path to individual files and their sizes
  --innodb_flush_method=#  With which method to flush data
  --innodb_flush_log_at_trx_commit[=#]
                    Value 0: write and flush once per second
                          Value 1: write and flush at each commit
                          Value 2: write at commit, flush once per second
  --innodb_log_arch_dir=dir    Where full logs should be archived
  --innodb_log_archive[=#]     Set to 1 if you want to have logs archived
  --innodb_log_group_home_dir=dir  Path to innodb log files.
  --skip-innodb                   Don't use Innodb (will save memory)


Default options are read from the following files in the given order:
/etc/my.cnf /var/lib/mysql/my.cnf ~/.my.cnf
The following groups are read: mysqld server
The following options may be given as the first argument:
--print-defaults      Print the program argument list and exit
--no-defaults            Don't read default options from any options file
--defaults-file=#      Only read default options from the given file #
--defaults-extra-file=# Read this file after the global files are read

To see what values a running MySQL server is using, type
'mysqladmin variables' instead of 'mysqld --help'.
The default values (after parsing the command line arguments) are:

basedir:     /usr/
datadir:     /var/lib/mysql/
tmpdir:      /tmp/
language:    /usr/share/mysql/english/
pid file:    /var/lib/mysql/mijnstukje.pid
TCP port:    3306
Unix socket: /var/lib/mysql/mysql.sock

system locking is not in use

Possible variables for option --set-variable (-O) are:
back_log              current value: 50
bdb_cache_size        current value: 8388600
bdb_log_buffer_size   current value: 0
bdb_max_lock          current value: 10000
bdb_lock_max          current value: 10000
binlog_cache_size     current value: 32768
connect_timeout       current value: 5
delayed_insert_timeout  current value: 300
delayed_insert_limit  current value: 100
delayed_queue_size    current value: 1000
flush_time            current value: 0
innodb_mirrored_log_groups  current value: 1
innodb_log_files_in_group  current value: 2
innodb_log_file_size  current value: 5242880
innodb_log_buffer_size  current value: 1048576
innodb_buffer_pool_size  current value: 8388608
innodb_additional_mem_pool_size  current value: 1048576
innodb_file_io_threads  current value: 4
innodb_lock_wait_timeout  current value: 50
innodb_thread_concurrency  current value: 8
innodb_force_recovery  current value: 0
interactive_timeout   current value: 28800
join_buffer_size      current value: 131072
key_buffer_size       current value: 8388600
long_query_time       current value: 10
lower_case_table_names  current value: 0
max_allowed_packet    current value: 1048576
max_binlog_cache_size  current value: 4294967295
max_binlog_size       current value: 1073741824
max_connections       current value: 100
max_connect_errors    current value: 10
max_delayed_threads   current value: 20
max_heap_table_size   current value: 16777216
max_join_size         current value: 4294967295
max_sort_length       current value: 1024
max_tmp_tables        current value: 32
max_user_connections  current value: 0
max_write_lock_count  current value: 4294967295
myisam_max_extra_sort_file_size  current value: 256
myisam_max_sort_file_size  current value: 2047
myisam_sort_buffer_size  current value: 8388608
net_buffer_length     current value: 16384
net_retry_count       current value: 10
net_read_timeout      current value: 30
net_write_timeout     current value: 60
open_files_limit      current value: 0
query_buffer_size     current value: 0
record_buffer         current value: 131072
record_rnd_buffer     current value: 0
slave_net_timeout     current value: 3600
slow_launch_time      current value: 2
sort_buffer           current value: 2097144
table_cache           current value: 64
thread_concurrency    current value: 10
thread_cache_size     current value: 0
tmp_table_size        current value: 33554432
thread_stack          current value: 65536
wait_timeout          current value: 28800
061121 19:33:10  mysqld ended

0
 
LVL 30

Accepted Solution

by:
todd_farmer earned 500 total points
Comment Utility
With version 3.23, you need to use the "set-variable = var_name=value" syntax (defined at http://dev.mysql.com/doc/refman/4.1/en/option-files.html).  The syntax you are using is valid for recent versions of MySQL (> 4.0.2 - 5.0 is GA and 5.1 is beta currently).
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now