Исправляем ошибку неожиданной остановки mysql на vesta

Конфигурационный файл nginx

Находится:

/home/username/conf/web/nginx.conf

Уменьшаем нагрузку, вынося обработку статистики отдельно. Вырезаем её из:

location / { }

И ставим на следующей строке отдельно:

location ~* ^.+\.(jpg|jpeg|gif|png|ico|svg|css|zip|tgz|gz|rar|bz2|exe|pdf|doc|xls|ppt|txt|odt|ods|odp|odf|tar|bmp|rtf|js|mp3|avi|mpeg|flv|html|htm)$ { root /home/UserName/web/ploshadka.net/public_html; access_log /var/log/httpd/domains/ploshadka.net.log combined; access_log /var/log/httpd/domains/ploshadka.net.bytes bytes; expires max; try_files $uri @fallback; }

А затем в этой location / уменьшаем лимиты (10 подключений в секунду):

location / { limit_req zone=dyn burst=10; proxy_pass http://1.11.72.33:8080; }

limit_req (burst=10) дублирует функционал iptables (limit 600/min), но на всякий случай оставляем его там.

На этом нагрузка на сервер должна значительно уменьшится. А неожиданные остановки и выключения служб базы данных больше не беспокоить.

InnoDB Log Files

A common configuration change which can prevent MySQL from starting involves the InnoDB log file size. The InnoDB log file is the ‘redo’ or recovery log for the InnoDB storage engine. It contains transactions which have been committed to a MySQL table, but which have not yet been written to disk. Should MySQL crash and lose the contents of its buffer pool, the log file can be used to recover any data changes which were in the buffer pool. These recovered data changes can then be written to disk.

The file contains a few options which affect the InnoDB log files. In this instance, the key one is . This option sets the size of the InnoDB log file.

Failed Registration of InnoDB as a Storage Engine

Frequently individuals decide to make changes to the option. Generally, they attempt to increase the value, and restart the MySQL server. Unless the proper procedure is followed, MySQL will fail to start (or start without support for InnoDB). The individual may be confused about why MySQL suddenly decided not to start. A quick check of the MySQL error log will usually reveal the cause.

In the case of changing the , one will often find an error similar to the following:

110509 12:04:27  InnoDB: Initializing buffer pool, size = 384.0M
110509 12:04:27  InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file .ib_logfile0 is of different size  5242880 bytes
InnoDB: than specified in the .cnf file  157286400 bytes!
110509 12:04:27 ERROR Plugin 'InnoDB' init function returned error.
110509 12:04:27 ERROR Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
110509 12:04:27 ERROR Unknownunsupported table type: innodb
110509 12:04:27 ERROR Aborting
 
110509 12:04:27 Note usrsbinmysqld: Shutdown complete

While the cause of MySQL’s failure to start is obvious from the log file, this is often overlooked.

There are two solutions to this particular problem:

Restore the my.cnf file to its original state, with an innodb_log_file_size equal to the actual size of the existing InnoDB log files.

Rename or move both the ./ib_logfile0 and ./ib_logfile1 files, and then start the MySQL server.

The and files are located in the InnoDB data directory (usually ). Both files must be moved or renamed for the above procedure to work. When starting MySQL, new InnoDB log files of the appropriate size will be created.

The original InnoDB log files only need to be kept as long as they may be needed for data recovery. If the MySQL server successfully starts after the above procedure, and all data is intact, the original InnoDB log files can be discarded.

Конфигурационный файл межсетевого экрана (брандмауэр)

Изменяем конфигурацию брандмауэра в Linux, который называется IPTables.

Файл лежит здесь:

etc — sysconfig — iptables

Закомментируем всё что там есть. А затем добавим следующее:

*filter :INPUT ACCEPT :FORWARD ACCEPT :OUTPUT ACCEPT -A INPUT -i lo -j ACCEPT -A INPUT -m state —state RELATED,ESTABLISHED -j ACCEPT -A INPUT -p tcp -m tcp —dport 21 -m state —state NEW -m recent —rcheck —seconds 6000 —name BLOCK —rsource -j DROP -A INPUT -p tcp -m tcp —dport 21 -m state —state NEW -m hashlimit —hashlimit-above 2/min —hashlimit-burst 2 —hashlimit-mode srcip —hashlimit-name BLOCK -m recent —set —name BLOCK —rsource -j DROP -A INPUT -p tcp -m tcp —dport 22 -m state —state NEW -m recent —rcheck —seconds 6000 —name BLOCK —rsource -j DROP -A INPUT -p tcp -m tcp —dport 22 -m state —state NEW -m hashlimit —hashlimit-above 2/min —hashlimit-burst 2 —hashlimit-mode srcip —hashlimit-name BLOCK -m recent —set —name BLOCK —rsource -j DROP -A INPUT -p tcp -m tcp —dport 22 —tcp-flags FIN,SYN,RST,ACK SYN -j ACCEPT -A INPUT -p tcp -m tcp —dport 80 -m state —state NEW -m limit —limit 600/min —limit-burst 100 -j ACCEPT -A INPUT -p tcp -m tcp —dport 443 -m state —state NEW -m limit —limit 600/min —limit-burst 100 -j ACCEPT -A INPUT -p tcp -m tcp —tcp-flags FIN,SYN,RST,ACK SYN -m limit —limit 1/sec -j ACCEPT -A INPUT -p tcp -m tcp —tcp-flags FIN,SYN,RST,ACK SYN -j DROP -A INPUT -p tcp -m tcp —tcp-flags FIN,SYN,RST,ACK RST -m limit —limit 1/sec -j ACCEPT -A INPUT -p tcp -m tcp —tcp-flags FIN,SYN,RST,ACK RST -j DROP -A INPUT -p icmp -m icmp —icmp-type 8 -m limit —limit 1/sec -j ACCEPT -A INPUT -p icmp -m icmp —icmp-type 8 -j DROP -A INPUT -j REJECT —reject-with icmp-host-prohibited COMMIT

Почему это делаем?

Мы ограничиваем лимит в 600 соединений в минуту с одного IP адреса, а затем блокируем его на 100 секунд (limit-burst 100). Это позволит избежать перенагрузки сервера от одного пользователя.

MySQL Logging

MySQL has many types of logs… a general query log, a connection log, an error log, a binary log, a slow query log, etc. And then there are the Linux system logs.

In the case of a failed MySQL start due to a configuration error or MySQL problem, details are usually output to the error log. The default location of the error log is the data directory (usually ). The log is normally named based on the hostname of the server. For example, .

If this file doesn’t seem to exist in your MySQL data directory, MySQL has probably been configured to log elsewhere. Check your file, which is normally at . The file can contain a option in the section which specifies a different location of the log. If no such option exists and you can’t find the log, try specifying a location for the log yourself, such as