I have been running a website using Percona mysql for about 18 months now. Today suddenly the web site stopped working and I found that it was because the mysql database had stopped responding.
I logged into the server and ran “service mysql restart” and it showed that the database shutdown ok but then when it tried to restart it failed. I tried several more times to restart the database but always get a failed status. I rebooted the server and tried again, but that did not help.
root@ambling2:/var/lib/mysql# service mysql start
- Starting MySQL (Percona Server) database server mysqld [fail]
I checked and there is plenty of disk space available:
root@ambling2:/var/lib/mysql# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sdc1 704G 12G 657G 2% /var/lib/mysql
The last thing logged in the /var/lib/mysql/mysql-error.log file is the following:
140119 23:12:52 [Note] /usr/sbin/mysqld: ready for connections.
Version: ‘5.5.35-33.0-log’ socket: ‘/var/run/mysqld/mysqld.sock’ port: 3306 Percona Server (GPL), Release 33.0
140310 13:56:50 [Note] /usr/sbin/mysqld: Normal shutdown
140310 13:56:50 [Note] Event Scheduler: Purging the queue. 0 events
140310 13:56:52 [Warning] /usr/sbin/mysqld: Forcing close of thread 14664450 user: ‘root’
140310 13:56:52 [Warning] /usr/sbin/mysqld: Forcing close of thread 14662690 user: ‘root’
140310 13:56:52 InnoDB: Starting shutdown…
140310 13:56:52 InnoDB: Waiting for 1 pages to be flushed
140310 13:57:10 InnoDB: Shutdown completed; log sequence number 214690824939
140310 13:57:10 [Note] /usr/sbin/mysqld: Shutdown complete
140310 13:57:10 mysqld_safe mysqld from pid file /var/lib/mysql/mysql.pid ended
No new lines are added to the mysql-error.log file each time that I type ‘service mysql start’. The last lines are the lines logged when mysql shut down.
Where can I look next to try and determine why mysql is not working. Mysql has been installed and running fine for 18 months now and the server had been up over a month when the database suddenly stopped responding today.
Here is my /etc/mysql/my.cnf configuration:
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
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 = 16M
max_allowed_packet = 16M
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 = 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!
* 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/
I found a different error log in /var/log/mysql/error.log which does have information about the failed starts. This log shows the following:
140310 19:04:55 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
140310 19:31:04 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
140310 19:31:04 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
140310 19:31:04 [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.
140310 19:31:04 [Note] Plugin ‘FEDERATED’ is disabled.
140310 19:31:04 InnoDB: The InnoDB memory heap is disabled
140310 19:31:04 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140310 19:31:04 InnoDB: Compressed tables use zlib 1.2.3.4
140310 19:31:04 InnoDB: Using Linux native AIO
140310 19:31:04 InnoDB: Initializing buffer pool, size = 128.0M
140310 19:31:04 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 536870912 bytes
InnoDB: than specified in the .cnf file 0 5242880 bytes!
140310 19:31:04 [ERROR] Plugin ‘InnoDB’ init function returned error.
140310 19:31:04 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
140310 19:31:04 [ERROR] Unknown/unsupported storage engine: InnoDB
140310 19:31:04 [ERROR] Aborting
140310 19:31:04 [Note] /usr/sbin/mysqld: Shutdown complete
140310 19:31:04 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
Your ib_logfiles are 512MB but your my.cnf says they should be 5MB. So you either need to update the innodb_log_file_size value in your my.cnf to 512M or delete your ib_logfiles and let it recreate them as 5M (likely want to go with the first option and update your my.cnf).