Все технические форумы на одном сайте Удобный поиск информации с популярных форумов в одном месте
Вопрос: восстановление mysql

Я работал в базе данных MySQL в течение нескольких дней, что-то произошло и появилось сообщение об ошибке: Таблица была помечена как поврежденная и должна быть восстановлена. Я не знаю, что теперь можно предпринять для восстановления…не подскажите вы?

Ответ:
Я предупреждал...
Вопрос: Бинарные логи в MySQL Embedded, есть ли у кого опыт решения проблемы?

Использую в приложении MySQL в виде Embedded (5.6, от версии не зависит) и не знаю как быть с бинарными логами. Дело в том, что если включить binlog, то тут же начинает создаваться куча файлов вида binlog.001 binlog.002 binlog.003 и т.д. На обычном сервере таких файлов создаётся за день всего несколько или вообще один, это зависит от настройки max_binlog_size.

В данном случае проблема, как я понимаю, состоит в том, что MySQL Embedded это не постоянно запущенный сервис (в привычном виде), а встроенный в программу движок и поэтому MySQL сервер "как бы запускается и останавливается" при каждом SQL запросе. И поскольку в MySQL почему-то задумано создавать новый бинарный лог при каждом перезапуске сервера (или это не так?), то получается что при использовании MySQL Embedded в программе создаются тысячи вот таких вот мелких файлов, вместо того чтобы хранить все логи в одном.

Возможно у кого-то есть опыт в решении данной проблемы именно при работе со встроенным движком MySQL в своей программе (MySQL Embedded)?

Конфигурация стандартная:
log-bin=mysql-bin
binlog_format=mixed
max_binlog_size=1073741824
Ответ: dartweider, не используются для восстановления.
могут использоваться для восстановления в определенных сценариях умными людьми, но сам mysql их не читает даже никогда.
Вопрос: MySQL 5.7 базы данных

Пару дней назад я получил сообщение: Индекс файла разбился. Я открыл файл во второй раз, но та же ошибка. Я не знаю, методы восстановления базы данных MySQL. Любые решения или советы, пожалуйста в студию ребят ...
Ответ:
alexxis1
Написал как есть
А у вас точно MySQL ?
В MySQL сообщения об ошибках англоязычные. И сообщения "Индекс файла разбился" у него точно не может быть.
Вопрос: Резервное копирование в MySQL

Уважаемые специалисты! Как вы считаете оправдано ли выполнять резервное копирование баз данных в MySQL с помощью бинарного журнала? И вообще интересно ваше мнение, как лучше выполнять резервное копирование, желательно не прибегая к сторонним программам?
Ответ: Возможно зависит от размера БД, сколько нужно будет времени, чтобы восстановить из этих логов бд в 50Гб, по сравнению с восстановлением из стандартного бекапа (замерить не на чем, собсно и спрашиваю=) )?
Вопрос: django останавливает Mysql

Здравствуйте, у меня виртуальный хостинг, на нем два сайта один php другой python. Здесь работает связка nginx + mysql. И в данном случае mysql работает только с php, но работал и с сайтом django python, но потом когда начались проблемы я его перевел на sqlite. А проблемы вот в чем когда я запуская сервер на котором работает djangо и не важно это manage.py runserver это тестовый сервер или uwsgi то при их запуске останавливается mysql, хотя сайт уже не использует мускл. Я понимаю что проблема в коде django, но я так думаю можно узнать причину остановки из логов mysql, поэтому обращаюсь сюда. Поэтому вылаживаю настройки и логи, буду признателен за помощь.
#
# 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            = 3307
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            = 3307
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       = 20 #8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
max_connections        = 100   #
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit       = 10M #1
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
#slow_query_log = 1
#slow_query_log_file = /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      = 36M #/16

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer              = 36M #/16

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#


innodb_buffer_pool_size=4G
innodb_buffer_pool_instances=2
innodb_read_io_threads=12
innodb_write_io_threads=12
innodb_io_capacity=300
innodb_log_file_size=128M
innodb_flush_method            = O_DIRECT
innodb_log_files_in_group      = 2
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table          = 1

!includedir /etc/mysql/conf.d/




root@crackkc:/var/log/mysql# cat error.log
2015-08-24 01:35:09 13904 [Note] Plugin 'FEDERATED' is disabled.
2015-08-24 01:35:09 13904 [ERROR] Function 'innodb' already exists
2015-08-24 01:35:09 13904 [Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
2015-08-24 01:35:09 13904 [ERROR] Function 'federated' already exists
2015-08-24 01:35:09 13904 [Warning] Couldn't load plugin named 'federated' with soname 'ha_federated.so'.
2015-08-24 01:35:09 13904 [ERROR] Function 'blackhole' already exists
2015-08-24 01:35:09 13904 [Warning] Couldn't load plugin named 'blackhole' with soname 'ha_blackhole.so'.
2015-08-24 01:35:09 13904 [ERROR] Function 'archive' already exists
2015-08-24 01:35:09 13904 [Warning] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.
2015-08-24 01:35:09 13904 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-08-24 01:35:09 13904 [Note] InnoDB: The InnoDB memory heap is disabled
2015-08-24 01:35:09 13904 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-08-24 01:35:09 13904 [Note] InnoDB: Memory barrier is not used
2015-08-24 01:35:09 13904 [Note] InnoDB: Compressed tables use zlib 1.2.7
2015-08-24 01:35:09 13904 [Note] InnoDB: Using Linux native AIO
2015-08-24 01:35:09 13904 [Note] InnoDB: Not using CPU crc32 instructions
2015-08-24 01:35:09 13904 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2015-08-24 01:35:09 13904 [Note] InnoDB: Completed initialization of buffer pool
2015-08-24 01:35:09 13904 [Note] InnoDB: Highest supported file format is Barracuda.
2015-08-24 01:35:09 13904 [Note] InnoDB: 128 rollback segment(s) are active.
2015-08-24 01:35:09 13904 [Note] InnoDB: Waiting for purge to start
2015-08-24 01:35:09 13904 [Note] InnoDB: 5.6.25 started; log sequence number 54580288
2015-08-24 01:35:09 13904 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
2015-08-24 01:35:09 13904 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
2015-08-24 01:35:09 13904 [Note] Server socket created on IP: '127.0.0.1'.
2015-08-24 01:35:09 13904 [Note] Event Scheduler: Loaded 0 events
2015-08-24 01:35:09 13904 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.25-1~dotdeb+7.1-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Debian)
2015-08-24 01:35:15 14034 [Note] Plugin 'FEDERATED' is disabled.
2015-08-24 01:35:15 14034 [ERROR] Function 'innodb' already exists
2015-08-24 01:35:15 14034 [Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
2015-08-24 01:35:15 14034 [ERROR] Function 'federated' already exists
2015-08-24 01:35:15 14034 [Warning] Couldn't load plugin named 'federated' with soname 'ha_federated.so'.
2015-08-24 01:35:15 14034 [ERROR] Function 'blackhole' already exists
2015-08-24 01:35:15 14034 [Warning] Couldn't load plugin named 'blackhole' with soname 'ha_blackhole.so'.
2015-08-24 01:35:15 14034 [ERROR] Function 'archive' already exists
2015-08-24 01:35:15 14034 [Warning] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.
2015-08-24 01:35:15 14034 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-08-24 01:35:15 14034 [Note] InnoDB: The InnoDB memory heap is disabled
2015-08-24 01:35:15 14034 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-08-24 01:35:15 14034 [Note] InnoDB: Memory barrier is not used
2015-08-24 01:35:15 14034 [Note] InnoDB: Compressed tables use zlib 1.2.7
2015-08-24 01:35:15 14034 [Note] InnoDB: Using Linux native AIO
2015-08-24 01:35:15 14034 [Note] InnoDB: Not using CPU crc32 instructions
2015-08-24 01:35:15 14034 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2015-08-24 01:35:15 14034 [Note] InnoDB: Completed initialization of buffer pool
2015-08-24 01:35:16 14067 [Note] Plugin 'FEDERATED' is disabled.
2015-08-24 01:35:16 14067 [ERROR] Function 'innodb' already exists
2015-08-24 01:35:16 14067 [Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
2015-08-24 01:35:16 14067 [ERROR] Function 'federated' already exists
2015-08-24 01:35:16 14067 [Warning] Couldn't load plugin named 'federated' with soname 'ha_federated.so'.
2015-08-24 01:35:16 14067 [ERROR] Function 'blackhole' already exists
2015-08-24 01:35:16 14067 [Warning] Couldn't load plugin named 'blackhole' with soname 'ha_blackhole.so'.
2015-08-24 01:35:16 14067 [ERROR] Function 'archive' already exists
2015-08-24 01:35:16 14067 [Warning] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.
2015-08-24 01:35:16 14067 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-08-24 01:35:16 14067 [Note] InnoDB: The InnoDB memory heap is disabled
2015-08-24 01:35:16 14067 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-08-24 01:35:16 14067 [Note] InnoDB: Memory barrier is not used
2015-08-24 01:35:16 14067 [Note] InnoDB: Compressed tables use zlib 1.2.7
2015-08-24 01:35:16 14067 [Note] InnoDB: Using Linux native AIO
2015-08-24 01:35:16 14067 [Note] InnoDB: Not using CPU crc32 instructions
2015-08-24 01:35:16 14067 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2015-08-24 01:35:16 14067 [Note] InnoDB: Completed initialization of buffer pool
2015-08-24 02:20:53 14239 [Note] Plugin 'FEDERATED' is disabled.
2015-08-24 02:20:53 14239 [ERROR] Function 'innodb' already exists
2015-08-24 02:20:53 14239 [Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
2015-08-24 02:20:53 14239 [ERROR] Function 'federated' already exists
2015-08-24 02:20:53 14239 [Warning] Couldn't load plugin named 'federated' with soname 'ha_federated.so'.
2015-08-24 02:20:53 14239 [ERROR] Function 'blackhole' already exists
2015-08-24 02:20:53 14239 [Warning] Couldn't load plugin named 'blackhole' with soname 'ha_blackhole.so'.
2015-08-24 02:20:53 14239 [ERROR] Function 'archive' already exists
2015-08-24 02:20:53 14239 [Warning] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.
2015-08-24 02:20:53 14239 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-08-24 02:20:53 14239 [Note] InnoDB: The InnoDB memory heap is disabled
2015-08-24 02:20:53 14239 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-08-24 02:20:53 14239 [Note] InnoDB: Memory barrier is not used
2015-08-24 02:20:53 14239 [Note] InnoDB: Compressed tables use zlib 1.2.7
2015-08-24 02:20:53 14239 [Note] InnoDB: Using Linux native AIO
2015-08-24 02:20:53 14239 [Note] InnoDB: Not using CPU crc32 instructions
2015-08-24 02:20:53 14239 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2015-08-24 02:20:54 14271 [Note] Plugin 'FEDERATED' is disabled.
2015-08-24 02:20:54 14271 [ERROR] Function 'innodb' already exists
2015-08-24 02:20:54 14271 [Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
2015-08-24 02:20:54 14271 [ERROR] Function 'federated' already exists
2015-08-24 02:20:54 14271 [Warning] Couldn't load plugin named 'federated' with soname 'ha_federated.so'.
2015-08-24 02:20:54 14271 [ERROR] Function 'blackhole' already exists
2015-08-24 02:20:54 14271 [Warning] Couldn't load plugin named 'blackhole' with soname 'ha_blackhole.so'.
2015-08-24 02:20:54 14271 [ERROR] Function 'archive' already exists
2015-08-24 02:20:54 14271 [Warning] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.
2015-08-24 02:20:54 14271 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-08-24 02:20:54 14271 [Note] InnoDB: The InnoDB memory heap is disabled
2015-08-24 02:20:54 14271 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-08-24 02:20:54 14271 [Note] InnoDB: Memory barrier is not used
2015-08-24 02:20:54 14271 [Note] InnoDB: Compressed tables use zlib 1.2.7
2015-08-24 02:20:54 14271 [Note] InnoDB: Using Linux native AIO
2015-08-24 02:20:54 14271 [Note] InnoDB: Not using CPU crc32 instructions
2015-08-24 02:20:54 14271 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2015-08-24 02:20:55 14303 [Note] Plugin 'FEDERATED' is disabled.
2015-08-24 02:20:55 14303 [ERROR] Function 'innodb' already exists
2015-08-24 02:20:55 14303 [Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
2015-08-24 02:20:55 14303 [ERROR] Function 'federated' already exists
2015-08-24 02:20:55 14303 [Warning] Couldn't load plugin named 'federated' with soname 'ha_federated.so'.
2015-08-24 02:20:55 14303 [ERROR] Function 'blackhole' already exists
2015-08-24 02:20:55 14303 [Warning] Couldn't load plugin named 'blackhole' with soname 'ha_blackhole.so'.
2015-08-24 02:20:55 14303 [ERROR] Function 'archive' already exists
2015-08-24 02:20:55 14303 [Warning] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.
2015-08-24 02:20:55 14303 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-08-24 02:20:55 14303 [Note] InnoDB: The InnoDB memory heap is disabled
2015-08-24 02:20:55 14303 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-08-24 02:20:55 14303 [Note] InnoDB: Memory barrier is not used
2015-08-24 02:20:55 14303 [Note] InnoDB: Compressed tables use zlib 1.2.7
2015-08-24 02:20:55 14303 [Note] InnoDB: Using Linux native AIO
2015-08-24 02:20:55 14303 [Note] InnoDB: Not using CPU crc32 instructions
2015-08-24 02:20:55 14303 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2015-08-24 02:21:04 14340 [Note] Plugin 'FEDERATED' is disabled.
2015-08-24 02:21:04 14340 [ERROR] Function 'innodb' already exists
2015-08-24 02:21:04 14340 [Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
2015-08-24 02:21:04 14340 [ERROR] Function 'federated' already exists
2015-08-24 02:21:04 14340 [Warning] Couldn't load plugin named 'federated' with soname 'ha_federated.so'.
2015-08-24 02:21:04 14340 [ERROR] Function 'blackhole' already exists
2015-08-24 02:21:04 14340 [Warning] Couldn't load plugin named 'blackhole' with soname 'ha_blackhole.so'.
2015-08-24 02:21:04 14340 [ERROR] Function 'archive' already exists
2015-08-24 02:21:04 14340 [Warning] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.
2015-08-24 02:21:04 14340 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-08-24 02:21:04 14340 [Note] InnoDB: The InnoDB memory heap is disabled
2015-08-24 02:21:04 14340 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-08-24 02:21:04 14340 [Note] InnoDB: Memory barrier is not used
2015-08-24 02:21:04 14340 [Note] InnoDB: Compressed tables use zlib 1.2.7
2015-08-24 02:21:04 14340 [Note] InnoDB: Using Linux native AIO
2015-08-24 02:21:04 14340 [Note] InnoDB: Not using CPU crc32 instructions
2015-08-24 02:21:04 14340 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2015-08-24 02:21:04 14340 [Note] InnoDB: Completed initialization of buffer pool
2015-08-24 02:21:04 14340 [Note] InnoDB: Highest supported file format is Barracuda.
2015-08-24 02:21:04 14340 [Note] InnoDB: The log sequence numbers 54580288 and 54580288 in ibdata files do not match the log sequence number 54580298 in the ib_logfiles!
2015-08-24 02:21:04 14340 [Note] InnoDB: Database was not shutdown normally!
2015-08-24 02:21:04 14340 [Note] InnoDB: Starting crash recovery.
2015-08-24 02:21:04 14340 [Note] InnoDB: Reading tablespace information from the .ibd files...
2015-08-24 02:21:04 14340 [Note] InnoDB: Restoring possible half-written data pages 
2015-08-24 02:21:04 14340 [Note] InnoDB: from the doublewrite buffer...
2015-08-24 02:21:04 14340 [Note] InnoDB: 128 rollback segment(s) are active.
2015-08-24 02:21:04 14340 [Note] InnoDB: Waiting for purge to start
2015-08-24 02:21:04 14340 [Note] InnoDB: 5.6.25 started; log sequence number 54580298
2015-08-24 02:21:04 14340 [Note] Recovering after a crash using /var/log/mysql/mysql-bin
2015-08-24 02:21:04 14340 [Note] Starting crash recovery...
2015-08-24 02:21:04 14340 [Note] Crash recovery finished.
2015-08-24 02:21:04 14340 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3307
2015-08-24 02:21:04 14340 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
2015-08-24 02:21:04 14340 [Note] Server socket created on IP: '127.0.0.1'.
2015-08-24 02:21:04 14340 [Note] Event Scheduler: Loaded 0 events
2015-08-24 02:21:04 14340 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.25-1~dotdeb+7.1-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3307  (Debian)
2015-08-24 02:22:20 14468 [Note] Plugin 'FEDERATED' is disabled.
2015-08-24 02:22:20 14468 [ERROR] Function 'innodb' already exists
2015-08-24 02:22:20 14468 [Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
2015-08-24 02:22:20 14468 [ERROR] Function 'federated' already exists
2015-08-24 02:22:20 14468 [Warning] Couldn't load plugin named 'federated' with soname 'ha_federated.so'.
2015-08-24 02:22:20 14468 [ERROR] Function 'blackhole' already exists
2015-08-24 02:22:20 14468 [Warning] Couldn't load plugin named 'blackhole' with soname 'ha_blackhole.so'.
2015-08-24 02:22:20 14468 [ERROR] Function 'archive' already exists
2015-08-24 02:22:20 14468 [Warning] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.
2015-08-24 02:22:20 14468 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-08-24 02:22:20 14468 [Note] InnoDB: The InnoDB memory heap is disabled
2015-08-24 02:22:20 14468 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-08-24 02:22:20 14468 [Note] InnoDB: Memory barrier is not used
2015-08-24 02:22:20 14468 [Note] InnoDB: Compressed tables use zlib 1.2.7
2015-08-24 02:22:20 14468 [Note] InnoDB: Using Linux native AIO
2015-08-24 02:22:20 14468 [Note] InnoDB: Not using CPU crc32 instructions
2015-08-24 02:22:20 14468 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2015-08-24 02:22:20 14468 [Note] InnoDB: Completed initialization of buffer pool
2015-08-24 02:22:21 14505 [Note] Plugin 'FEDERATED' is disabled.
2015-08-24 02:22:21 14505 [ERROR] Function 'innodb' already exists
2015-08-24 02:22:21 14505 [Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
2015-08-24 02:22:21 14505 [ERROR] Function 'federated' already exists
2015-08-24 02:22:21 14505 [Warning] Couldn't load plugin named 'federated' with soname 'ha_federated.so'.
2015-08-24 02:22:21 14505 [ERROR] Function 'blackhole' already exists
2015-08-24 02:22:21 14505 [Warning] Couldn't load plugin named 'blackhole' with soname 'ha_blackhole.so'.
2015-08-24 02:22:21 14505 [ERROR] Function 'archive' already exists
2015-08-24 02:22:21 14505 [Warning] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.
2015-08-24 02:22:21 14505 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-08-24 02:22:21 14505 [Note] InnoDB: The InnoDB memory heap is disabled
2015-08-24 02:22:21 14505 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-08-24 02:22:21 14505 [Note] InnoDB: Memory barrier is not used
2015-08-24 02:22:21 14505 [Note] InnoDB: Compressed tables use zlib 1.2.7
2015-08-24 02:22:21 14505 [Note] InnoDB: Using Linux native AIO
2015-08-24 02:22:21 14505 [Note] InnoDB: Not using CPU crc32 instructions
2015-08-24 02:22:21 14505 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2015-08-24 02:24:56 14546 [Note] Plugin 'FEDERATED' is disabled.
2015-08-24 02:24:56 14546 [ERROR] Function 'innodb' already exists
2015-08-24 02:24:56 14546 [Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
2015-08-24 02:24:56 14546 [ERROR] Function 'federated' already exists
2015-08-24 02:24:56 14546 [Warning] Couldn't load plugin named 'federated' with soname 'ha_federated.so'.
2015-08-24 02:24:56 14546 [ERROR] Function 'blackhole' already exists
2015-08-24 02:24:56 14546 [Warning] Couldn't load plugin named 'blackhole' with soname 'ha_blackhole.so'.
2015-08-24 02:24:56 14546 [ERROR] Function 'archive' already exists
2015-08-24 02:24:56 14546 [Warning] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.
2015-08-24 02:24:56 14546 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-08-24 02:24:56 14546 [Note] InnoDB: The InnoDB memory heap is disabled
2015-08-24 02:24:56 14546 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-08-24 02:24:56 14546 [Note] InnoDB: Memory barrier is not used
2015-08-24 02:24:56 14546 [Note] InnoDB: Compressed tables use zlib 1.2.7
2015-08-24 02:24:56 14546 [Note] InnoDB: Using Linux native AIO
2015-08-24 02:24:56 14546 [Note] InnoDB: Not using CPU crc32 instructions
2015-08-24 02:24:56 14546 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2015-08-24 02:24:56 14546 [Note] InnoDB: Completed initialization of buffer pool
2015-08-24 02:24:56 14546 [Note] InnoDB: Highest supported file format is Barracuda.
2015-08-24 02:24:56 14546 [Note] InnoDB: The log sequence numbers 54580288 and 54580288 in ibdata files do not match the log sequence number 54580308 in the ib_logfiles!
2015-08-24 02:24:56 14546 [Note] InnoDB: Database was not shutdown normally!
2015-08-24 02:24:56 14546 [Note] InnoDB: Starting crash recovery.
2015-08-24 02:24:56 14546 [Note] InnoDB: Reading tablespace information from the .ibd files...
2015-08-24 02:24:56 14546 [Note] InnoDB: Restoring possible half-written data pages 
2015-08-24 02:24:56 14546 [Note] InnoDB: from the doublewrite buffer...
2015-08-24 02:24:56 14546 [Note] InnoDB: 128 rollback segment(s) are active.
2015-08-24 02:24:56 14546 [Note] InnoDB: Waiting for purge to start
2015-08-24 02:24:56 14546 [Note] InnoDB: 5.6.25 started; log sequence number 54580308
2015-08-24 02:24:56 14546 [Note] Recovering after a crash using /var/log/mysql/mysql-bin
2015-08-24 02:24:56 14546 [Note] Starting crash recovery...
2015-08-24 02:24:56 14546 [Note] Crash recovery finished.
2015-08-24 02:24:56 14546 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3307
2015-08-24 02:24:56 14546 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
2015-08-24 02:24:56 14546 [Note] Server socket created on IP: '127.0.0.1'.
2015-08-24 02:24:56 14546 [Note] Event Scheduler: Loaded 0 events
2015-08-24 02:24:56 14546 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.25-1~dotdeb+7.1-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3307  (Debian)
2015-08-24 02:25:02 14677 [Note] Plugin 'FEDERATED' is disabled.
2015-08-24 02:25:02 14677 [ERROR] Function 'innodb' already exists
2015-08-24 02:25:02 14677 [Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
2015-08-24 02:25:02 14677 [ERROR] Function 'federated' already exists
2015-08-24 02:25:02 14677 [Warning] Couldn't load plugin named 'federated' with soname 'ha_federated.so'.
2015-08-24 02:25:02 14677 [ERROR] Function 'blackhole' already exists
2015-08-24 02:25:02 14677 [Warning] Couldn't load plugin named 'blackhole' with soname 'ha_blackhole.so'.
2015-08-24 02:25:02 14677 [ERROR] Function 'archive' already exists
2015-08-24 02:25:02 14677 [Warning] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.
2015-08-24 02:25:02 14677 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-08-24 02:25:02 14677 [Note] InnoDB: The InnoDB memory heap is disabled
2015-08-24 02:25:02 14677 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-08-24 02:25:02 14677 [Note] InnoDB: Memory barrier is not used
2015-08-24 02:25:02 14677 [Note] InnoDB: Compressed tables use zlib 1.2.7
2015-08-24 02:25:02 14677 [Note] InnoDB: Using Linux native AIO
2015-08-24 02:25:02 14677 [Note] InnoDB: Not using CPU crc32 instructions
2015-08-24 02:25:02 14677 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2015-08-24 02:25:02 14677 [Note] InnoDB: Completed initialization of buffer pool
2015-08-24 02:25:02 14710 [Note] Plugin 'FEDERATED' is disabled.
2015-08-24 02:25:02 14710 [ERROR] Function 'innodb' already exists
2015-08-24 02:25:02 14710 [Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
2015-08-24 02:25:02 14710 [ERROR] Function 'federated' already exists
2015-08-24 02:25:02 14710 [Warning] Couldn't load plugin named 'federated' with soname 'ha_federated.so'.
2015-08-24 02:25:02 14710 [ERROR] Function 'blackhole' already exists
2015-08-24 02:25:02 14710 [Warning] Couldn't load plugin named 'blackhole' with soname 'ha_blackhole.so'.
2015-08-24 02:25:02 14710 [ERROR] Function 'archive' already exists
2015-08-24 02:25:02 14710 [Warning] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.
2015-08-24 02:25:02 14710 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-08-24 02:25:02 14710 [Note] InnoDB: The InnoDB memory heap is disabled
2015-08-24 02:25:02 14710 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-08-24 02:25:02 14710 [Note] InnoDB: Memory barrier is not used
2015-08-24 02:25:02 14710 [Note] InnoDB: Compressed tables use zlib 1.2.7
2015-08-24 02:25:02 14710 [Note] InnoDB: Using Linux native AIO
2015-08-24 02:25:02 14710 [Note] InnoDB: Not using CPU crc32 instructions
2015-08-24 02:25:02 14710 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2015-08-24 02:25:02 14710 [Note] InnoDB: Completed initialization of buffer pool
2015-08-24 02:25:19 14767 [Note] Plugin 'FEDERATED' is disabled.
2015-08-24 02:25:19 14767 [ERROR] Function 'innodb' already exists
2015-08-24 02:25:19 14767 [Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
2015-08-24 02:25:19 14767 [ERROR] Function 'federated' already exists
2015-08-24 02:25:19 14767 [Warning] Couldn't load plugin named 'federated' with soname 'ha_federated.so'.
2015-08-24 02:25:19 14767 [ERROR] Function 'blackhole' already exists
2015-08-24 02:25:19 14767 [Warning] Couldn't load plugin named 'blackhole' with soname 'ha_blackhole.so'.
2015-08-24 02:25:19 14767 [ERROR] Function 'archive' already exists
2015-08-24 02:25:19 14767 [Warning] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.
2015-08-24 02:25:19 14767 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-08-24 02:25:19 14767 [Note] InnoDB: The InnoDB memory heap is disabled
2015-08-24 02:25:19 14767 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-08-24 02:25:19 14767 [Note] InnoDB: Memory barrier is not used
2015-08-24 02:25:19 14767 [Note] InnoDB: Compressed tables use zlib 1.2.7
2015-08-24 02:25:19 14767 [Note] InnoDB: Using Linux native AIO
2015-08-24 02:25:19 14767 [Note] InnoDB: Not using CPU crc32 instructions
2015-08-24 02:25:19 14767 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2015-08-24 02:25:19 14767 [Note] InnoDB: Completed initialization of buffer pool
2015-08-24 02:25:19 14767 [Note] InnoDB: Highest supported file format is Barracuda.
2015-08-24 02:25:19 14767 [Note] InnoDB: The log sequence numbers 54580288 and 54580288 in ibdata files do not match the log sequence number 54580318 in the ib_logfiles!
2015-08-24 02:25:19 14767 [Note] InnoDB: Database was not shutdown normally!
2015-08-24 02:25:19 14767 [Note] InnoDB: Starting crash recovery.
2015-08-24 02:25:19 14767 [Note] InnoDB: Reading tablespace information from the .ibd files...
2015-08-24 02:25:19 14767 [Note] InnoDB: Restoring possible half-written data pages 
2015-08-24 02:25:19 14767 [Note] InnoDB: from the doublewrite buffer...
2015-08-24 02:25:20 14767 [Note] InnoDB: 128 rollback segment(s) are active.
2015-08-24 02:25:20 14767 [Note] InnoDB: Waiting for purge to start
2015-08-24 02:25:20 14767 [Note] InnoDB: 5.6.25 started; log sequence number 54580318
2015-08-24 02:25:20 14767 [Note] Recovering after a crash using /var/log/mysql/mysql-bin
2015-08-24 02:25:20 14767 [Note] Starting crash recovery...
2015-08-24 02:25:20 14767 [Note] Crash recovery finished.
2015-08-24 02:25:20 14767 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3307
2015-08-24 02:25:20 14767 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
2015-08-24 02:25:20 14767 [Note] Server socket created on IP: '127.0.0.1'.
2015-08-24 02:25:20 14767 [Note] Event Scheduler: Loaded 0 events
2015-08-24 02:25:20 14767 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.25-1~dotdeb+7.1-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3307  (Debian)
root@crackkc:/var/log/mysql# 


root@crackkc:/var/log/mysql# cat mysql.log
/usr/sbin/mysqld, Version: 5.6.25-1~dotdeb+7.1-log ((Debian)). started with:
Tcp port: 3307  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
150824  3:33:27     1 Connect   debian-sys-maint@localhost on 
                    1 Quit
                    2 Connect   debian-sys-maint@localhost on mysql
                    2 Query     select @@version_comment limit 1
                    2 Query     SET SQL_LOG_BIN=0
                    2 Query     show variables like 'datadir'
                    2 Quit
                    3 Connect   debian-sys-maint@localhost on 
                    3 Query     select @@version_comment limit 1
                    3 Query     SELECT count(*) FROM mysql.user WHERE user='root' and password=''
                    3 Quit
                    4 Connect   debian-sys-maint@localhost on 
                    4 Query     select @@version_comment limit 1
                    4 Query     select concat('select count(*) into @discard from `',
                    TABLE_SCHEMA, '`.`', TABLE_NAME, '`') 
      from information_schema.TABLES where ENGINE='MyISAM'
                    4 Quit
                    5 Connect   debian-sys-maint@localhost on 
                    5 Query     select @@version_comment limit 1
                    5 Query     select count(*) into @discard from `information_schema`.`COLUMNS`
                    5 Quit
                    6 Connect   debian-sys-maint@localhost on 
                    6 Query     select @@version_comment limit 1
                    6 Query     select count(*) into @discard from `information_schema`.`EVENTS`
                    6 Quit
                    7 Connect   debian-sys-maint@localhost on 
                    7 Query     select @@version_comment limit 1
                    7 Query     select count(*) into @discard from `information_schema`.`OPTIMIZER_TRACE`
                    7 Quit
                    8 Connect   debian-sys-maint@localhost on 
                    8 Query     select @@version_comment limit 1
                    8 Query     select count(*) into @discard from `information_schema`.`PARAMETERS`
                    8 Quit
                    9 Connect   debian-sys-maint@localhost on 
                    9 Query     select @@version_comment limit 1
                    9 Query     select count(*) into @discard from `information_schema`.`PARTITIONS`
                    9 Quit
                   10 Connect   debian-sys-maint@localhost on 
                   10 Query     select @@version_comment limit 1
                   10 Query     select count(*) into @discard from `information_schema`.`PLUGINS`
                   10 Quit
                   11 Connect   debian-sys-maint@localhost on 
                   11 Query     select @@version_comment limit 1
                   11 Query     select count(*) into @discard from `information_schema`.`PROCESSLIST`
                   11 Quit
                   12 Connect   debian-sys-maint@localhost on 
                   12 Query     select @@version_comment limit 1
                   12 Query     select count(*) into @discard from `information_schema`.`ROUTINES`
                   12 Quit
                   13 Connect   debian-sys-maint@localhost on 
                   13 Query     select @@version_comment limit 1
                   13 Query     select count(*) into @discard from `information_schema`.`TRIGGERS`
                   13 Quit
                   14 Connect   debian-sys-maint@localhost on 
                   14 Query     select @@version_comment limit 1
                   14 Query     select count(*) into @discard from `information_schema`.`VIEWS`
                   14 Quit
                   15 Connect   debian-sys-maint@localhost on 
                   15 Query     select @@version_comment limit 1
                   15 Query     select count(*) into @discard from `mysql`.`columns_priv`
                   15 Quit
                   16 Connect   debian-sys-maint@localhost on 
                   16 Query     select @@version_comment limit 1
                   16 Query     select count(*) into @discard from `mysql`.`db`
                   16 Quit
                   17 Connect   debian-sys-maint@localhost on 
                   17 Query     select @@version_comment limit 1
                   17 Query     select count(*) into @discard from `mysql`.`event`
                   17 Quit
                   18 Connect   debian-sys-maint@localhost on 
                   18 Query     select @@version_comment limit 1
                   18 Query     select count(*) into @discard from `mysql`.`func`
                   18 Quit
                   19 Connect   debian-sys-maint@localhost on 
                   19 Query     select @@version_comment limit 1
                   19 Query     select count(*) into @discard from `mysql`.`help_category`
                   19 Quit
                   20 Connect   debian-sys-maint@localhost on 
                   20 Query     select @@version_comment limit 1
                   20 Query     select count(*) into @discard from `mysql`.`help_keyword`
                   20 Quit
                   21 Connect   debian-sys-maint@localhost on 
                   21 Query     select @@version_comment limit 1
                   21 Query     select count(*) into @discard from `mysql`.`help_relation`
                   21 Quit
                   22 Connect   debian-sys-maint@localhost on 
                   22 Query     select @@version_comment limit 1
                   22 Query     select count(*) into @discard from `mysql`.`help_topic`
                   22 Quit
                   23 Connect   debian-sys-maint@localhost on 
                   23 Query     select @@version_comment limit 1
                   23 Query     select count(*) into @discard from `mysql`.`ndb_binlog_index`
                   23 Quit
                   24 Connect   debian-sys-maint@localhost on 
                   24 Query     select @@version_comment limit 1
                   24 Query     select count(*) into @discard from `mysql`.`plugin`
                   24 Quit
                   25 Connect   debian-sys-maint@localhost on 
                   25 Query     select @@version_comment limit 1
                   25 Query     select count(*) into @discard from `mysql`.`proc`
                   25 Quit
                   26 Connect   debian-sys-maint@localhost on 
                   26 Query     select @@version_comment limit 1
                   26 Query     select count(*) into @discard from `mysql`.`procs_priv`
                   26 Quit
                   27 Connect   debian-sys-maint@localhost on 
                   27 Query     select @@version_comment limit 1
                   27 Query     select count(*) into @discard from `mysql`.`proxies_priv`
                   27 Quit
                   28 Connect   debian-sys-maint@localhost on 
                   28 Query     select @@version_comment limit 1
                   28 Query     select count(*) into @discard from `mysql`.`servers`
                   28 Quit
                   29 Connect   debian-sys-maint@localhost on 
                   29 Query     select @@version_comment limit 1
                   29 Query     select count(*) into @discard from `mysql`.`tables_priv`
                   29 Quit
                   30 Connect   debian-sys-maint@localhost on 
                   30 Query     select @@version_comment limit 1
                   30 Query     select count(*) into @discard from `mysql`.`time_zone`
                   30 Quit
                   31 Connect   debian-sys-maint@localhost on 
                   31 Query     select @@version_comment limit 1
                   31 Query     select count(*) into @discard from `mysql`.`time_zone_leap_second`
                   31 Quit
                   32 Connect   debian-sys-maint@localhost on 
                   32 Query     select @@version_comment limit 1
                   32 Query     select count(*) into @discard from `mysql`.`time_zone_name`
                   32 Quit
                   33 Connect   debian-sys-maint@localhost on 
                   33 Query     select @@version_comment limit 1
                   33 Query     select count(*) into @discard from `mysql`.`time_zone_transition`
                   33 Quit
                   34 Connect   debian-sys-maint@localhost on 
                   34 Query     select @@version_comment limit 1
                   34 Query     select count(*) into @discard from `mysql`.`time_zone_transition_type`
                   34 Quit
                   35 Connect   debian-sys-maint@localhost on 
                   35 Query     select @@version_comment limit 1
                   35 Query     select count(*) into @discard from `mysql`.`user`
                   35 Quit
                   36 Connect   debian-sys-maint@localhost on 
                   36 Query     select @@version_comment limit 1
                   36 Query     select count(*) into @discard from `niws`.`catalog`
                   36 Quit
                   37 Connect   debian-sys-maint@localhost on 
                   37 Query     select @@version_comment limit 1
                   37 Query     select count(*) into @discard from `niws`.`comments`
                   37 Quit
                   38 Connect   debian-sys-maint@localhost on 
                   38 Query     select @@version_comment limit 1
                   38 Query     select count(*) into @discard from `niws`.`content`
                   38 Quit
                   39 Connect   debian-sys-maint@localhost on 
                   39 Query     select @@version_comment limit 1
                   39 Query     select count(*) into @discard from `niws`.`forum`
                   39 Quit
                   40 Connect   debian-sys-maint@localhost on 
                   40 Query     select @@version_comment limit 1
                   40 Query     select count(*) into @discard from `niws`.`mail`
                   40 Quit
                   41 Connect   debian-sys-maint@localhost on 
                   41 Query     select @@version_comment limit 1
                   41 Query     select count(*) into @discard from `niws`.`menu`
                   41 Quit
                   42 Connect   debian-sys-maint@localhost on 
                   42 Query     select @@version_comment limit 1
                   42 Query     select count(*) into @discard from `niws`.`news`
                   42 Quit
                   43 Connect   debian-sys-maint@localhost on 
                   43 Query     select @@version_comment limit 1
                   43 Query     select count(*) into @discard from `niws`.`pages`
                   43 Quit
                   44 Connect   debian-sys-maint@localhost on 
                   44 Query     select @@version_comment limit 1
                   44 Query     select count(*) into @discard from `niws`.`user_admin`
                   44 Quit
                   45 Connect   debian-sys-maint@localhost on 
                   45 Query     select @@version_comment limit 1
                   45 Query     select count(*) into @discard from `phpmyadmin`.`pma_bookmark`
                   45 Quit
                   46 Connect   debian-sys-maint@localhost on 
                   46 Query     select @@version_comment limit 1
                   46 Query     select count(*) into @discard from `phpmyadmin`.`pma_column_info`
                   46 Quit
                   47 Connect   debian-sys-maint@localhost on 
                   47 Query     select @@version_comment limit 1
                   47 Query     select count(*) into @discard from `phpmyadmin`.`pma_designer_coords`
                   47 Quit
                   48 Connect   debian-sys-maint@localhost on 
                   48 Query     select @@version_comment limit 1
                   48 Query     select count(*) into @discard from `phpmyadmin`.`pma_history`
                   48 Quit
                   49 Connect   debian-sys-maint@localhost on 
                   49 Query     select @@version_comment limit 1
                   49 Query     select count(*) into @discard from `phpmyadmin`.`pma_pdf_pages`
                   49 Quit
                   50 Connect   debian-sys-maint@localhost on 
                   50 Query     select @@version_comment limit 1
                   50 Query     select count(*) into @discard from `phpmyadmin`.`pma_relation`
                   50 Quit
                   51 Connect   debian-sys-maint@localhost on 
                   51 Query     select @@version_comment limit 1
                   51 Query     select count(*) into @discard from `phpmyadmin`.`pma_table_coords`
                   51 Quit
                   52 Connect   debian-sys-maint@localhost on 
                   52 Query     select @@version_comment limit 1
                   52 Query     select count(*) into @discard from `phpmyadmin`.`pma_table_info`
                   52 Quit
                   53 Connect   debian-sys-maint@localhost on 
                   53 Query     select @@version_comment limit 1
                   53 Query     select count(*) into @discard from `phpmyadmin`.`pma_tracking`
                   53 Quit
                   54 Connect   debian-sys-maint@localhost on 
                   54 Query     select @@version_comment limit 1
                   54 Query     select count(*) into @discard from `phpmyadmin`.`pma_userconfig`
                   54 Quit
150824  3:33:29    55 Connect   root@localhost on 
                   55 Init DB   niws
                   55 Query     SET NAMES utf8
                   55 Query     SELECT *
FROM (`menu`)
WHERE `id` =  '88'
                   55 Query     SELECT *
FROM (`menu`)
WHERE `id_mom` =  '22'
ORDER BY `order` ASC
                   55 Query     SELECT *
FROM (`menu`)
WHERE `id` =  '88'
                   55 Query     SELECT *
FROM (`menu`)
ORDER BY `order` ASC
                   55 Query     SELECT *
FROM (`catalog`)
WHERE `cat` =  '88'
ORDER BY `id` DESC
                   55 Query     SELECT *
FROM (`menu`)
WHERE `url` =  '1'
ORDER BY `order` ASC
                   56 Connect   root@localhost on 
                   56 Init DB   niws
                   56 Query     SET NAMES utf8
                   56 Query     SELECT *
FROM (`pages`)
WHERE `name` =  'main'
                   56 Query     SELECT *
FROM (`menu`)
ORDER BY `order` ASC
                   56 Query     SELECT *
FROM (`menu`)
WHERE `id_mom` =  '22'
ORDER BY `order` ASC
                   56 Query     SELECT *
FROM (`content`)
WHERE `cat` =  'main'
                   56 Query     SELECT *
FROM (`catalog`)
WHERE `tip_text` =  'history'
ORDER BY `id` DESC
LIMIT 2
                   56 Query     SELECT *
FROM (`catalog`)
WHERE `tip_text` =  'open'
ORDER BY `id` DESC
LIMIT 2
                   56 Query     SELECT *
FROM (`catalog`)
WHERE `tip_text` =  'ludi'
ORDER BY `id` DESC
LIMIT 2
/usr/sbin/mysqld, Version: 5.6.25-1~dotdeb+7.1-log ((Debian)). started with:
Tcp port: 3307  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
150824  3:33:59     1 Connect   debian-sys-maint@localhost on 
                    1 Quit
                    2 Connect   debian-sys-maint@localhost on mysql
                    2 Query     select @@version_comment limit 1
                    2 Query     SET SQL_LOG_BIN=0
                    2 Query     show variables like 'datadir'
                    2 Quit
                    3 Connect   debian-sys-maint@localhost on 
                    3 Query     select @@version_comment limit 1
                    3 Query     SELECT count(*) FROM mysql.user WHERE user='root' and password=''
                    3 Quit
                    4 Connect   debian-sys-maint@localhost on 
                    4 Query     select @@version_comment limit 1
                    4 Query     select concat('select count(*) into @discard from `',
                    TABLE_SCHEMA, '`.`', TABLE_NAME, '`') 
      from information_schema.TABLES where ENGINE='MyISAM'
                    4 Quit
                    5 Connect   debian-sys-maint@localhost on 
                    5 Query     select @@version_comment limit 1
                    5 Query     select count(*) into @discard from `information_schema`.`COLUMNS`
                    5 Quit
                    6 Connect   debian-sys-maint@localhost on 
                    6 Query     select @@version_comment limit 1
                    6 Query     select count(*) into @discard from `information_schema`.`EVENTS`
                    6 Quit
                    7 Connect   debian-sys-maint@localhost on 
                    7 Query     select @@version_comment limit 1
                    7 Query     select count(*) into @discard from `information_schema`.`OPTIMIZER_TRACE`
                    7 Quit
                    8 Connect   debian-sys-maint@localhost on 
                    8 Query     select @@version_comment limit 1
                    8 Query     select count(*) into @discard from `information_schema`.`PARAMETERS`
                    8 Quit
                    9 Connect   debian-sys-maint@localhost on 
                    9 Query     select @@version_comment limit 1
                    9 Query     select count(*) into @discard from `information_schema`.`PARTITIONS`
                    9 Quit
                   10 Connect   debian-sys-maint@localhost on 
                   10 Query     select @@version_comment limit 1
                   10 Query     select count(*) into @discard from `information_schema`.`PLUGINS`
                   10 Quit
                   11 Connect   debian-sys-maint@localhost on 
                   11 Query     select @@version_comment limit 1
                   11 Query     select count(*) into @discard from `information_schema`.`PROCESSLIST`
                   11 Quit
                   12 Connect   debian-sys-maint@localhost on 
                   12 Query     select @@version_comment limit 1
                   12 Query     select count(*) into @discard from `information_schema`.`ROUTINES`
                   12 Quit
                   13 Connect   debian-sys-maint@localhost on 
                   13 Query     select @@version_comment limit 1
                   13 Query     select count(*) into @discard from `information_schema`.`TRIGGERS`
                   13 Quit
                   14 Connect   debian-sys-maint@localhost on 
                   14 Query     select @@version_comment limit 1
                   14 Query     select count(*) into @discard from `information_schema`.`VIEWS`
                   14 Quit
                   15 Connect   debian-sys-maint@localhost on 
                   15 Query     select @@version_comment limit 1
                   15 Query     select count(*) into @discard from `mysql`.`columns_priv`
                   15 Quit
                   16 Connect   debian-sys-maint@localhost on 
                   16 Query     select @@version_comment limit 1
                   16 Query     select count(*) into @discard from `mysql`.`db`
                   16 Quit
                   17 Connect   debian-sys-maint@localhost on 
                   17 Query     select @@version_comment limit 1
                   17 Query     select count(*) into @discard from `mysql`.`event`
                   17 Quit
                   18 Connect   debian-sys-maint@localhost on 
                   18 Query     select @@version_comment limit 1
                   18 Query     select count(*) into @discard from `mysql`.`func`
                   18 Quit
                   19 Connect   debian-sys-maint@localhost on 
                   19 Query     select @@version_comment limit 1
                   19 Query     select count(*) into @discard from `mysql`.`help_category`
                   19 Quit
                   20 Connect   debian-sys-maint@localhost on 
                   20 Query     select @@version_comment limit 1
                   20 Query     select count(*) into @discard from `mysql`.`help_keyword`
                   20 Quit
                   21 Connect   debian-sys-maint@localhost on 
                   21 Query     select @@version_comment limit 1
                   21 Query     select count(*) into @discard from `mysql`.`help_relation`
                   21 Quit
                   22 Connect   debian-sys-maint@localhost on 
                   22 Query     select @@version_comment limit 1
                   22 Query     select count(*) into @discard from `mysql`.`help_topic`
                   22 Quit
                   23 Connect   debian-sys-maint@localhost on 
                   23 Query     select @@version_comment limit 1
                   23 Query     select count(*) into @discard from `mysql`.`ndb_binlog_index`
                   23 Quit
150824  3:34:00    24 Connect   debian-sys-maint@localhost on 
                   24 Query     select @@version_comment limit 1
                   24 Query     select count(*) into @discard from `mysql`.`plugin`
                   24 Quit
                   25 Connect   debian-sys-maint@localhost on 
                   25 Query     select @@version_comment limit 1
                   25 Query     select count(*) into @discard from `mysql`.`proc`
                   25 Quit
                   26 Connect   debian-sys-maint@localhost on 
                   26 Query     select @@version_comment limit 1
                   26 Query     select count(*) into @discard from `mysql`.`procs_priv`
                   26 Quit
                   27 Connect   debian-sys-maint@localhost on 
                   27 Query     select @@version_comment limit 1
                   27 Query     select count(*) into @discard from `mysql`.`proxies_priv`
                   27 Quit
                   28 Connect   debian-sys-maint@localhost on 
                   28 Query     select @@version_comment limit 1
                   28 Query     select count(*) into @discard from `mysql`.`servers`
                   28 Quit
                   29 Connect   debian-sys-maint@localhost on 
                   29 Query     select @@version_comment limit 1
                   29 Query     select count(*) into @discard from `mysql`.`tables_priv`
                   29 Quit
                   30 Connect   debian-sys-maint@localhost on 
                   30 Query     select @@version_comment limit 1
                   30 Query     select count(*) into @discard from `mysql`.`time_zone`
                   30 Quit
                   31 Connect   debian-sys-maint@localhost on 
                   31 Query     select @@version_comment limit 1
                   31 Query     select count(*) into @discard from `mysql`.`time_zone_leap_second`
                   31 Quit
                   32 Connect   debian-sys-maint@localhost on 
                   32 Query     select @@version_comment limit 1
                   32 Query     select count(*) into @discard from `mysql`.`time_zone_name`
                   32 Quit
                   33 Connect   debian-sys-maint@localhost on 
                   33 Query     select @@version_comment limit 1
                   33 Query     select count(*) into @discard from `mysql`.`time_zone_transition`
                   33 Quit
                   34 Connect   debian-sys-maint@localhost on 
                   34 Query     select @@version_comment limit 1
                   34 Query     select count(*) into @discard from `mysql`.`time_zone_transition_type`
                   34 Quit
                   35 Connect   debian-sys-maint@localhost on 
                   35 Query     select @@version_comment limit 1
                   35 Query     select count(*) into @discard from `mysql`.`user`
                   35 Quit
                   36 Connect   debian-sys-maint@localhost on 
                   36 Query     select @@version_comment limit 1
                   36 Query     select count(*) into @discard from `niws`.`catalog`
                   36 Quit
                   37 Connect   debian-sys-maint@localhost on 
                   37 Query     select @@version_comment limit 1
                   37 Query     select count(*) into @discard from `niws`.`comments`
                   37 Quit
                   38 Connect   debian-sys-maint@localhost on 
                   38 Query     select @@version_comment limit 1
                   38 Query     select count(*) into @discard from `niws`.`content`
                   38 Quit
                   39 Connect   debian-sys-maint@localhost on 
                   39 Query     select @@version_comment limit 1
                   39 Query     select count(*) into @discard from `niws`.`forum`
                   39 Quit
                   40 Connect   debian-sys-maint@localhost on 
                   40 Query     select @@version_comment limit 1
                   40 Query     select count(*) into @discard from `niws`.`mail`
                   40 Quit
                   41 Connect   debian-sys-maint@localhost on 
                   41 Query     select @@version_comment limit 1
                   41 Query     select count(*) into @discard from `niws`.`menu`
                   41 Quit
                   42 Connect   debian-sys-maint@localhost on 
                   42 Query     select @@version_comment limit 1
                   42 Query     select count(*) into @discard from `niws`.`news`
                   42 Quit
                   43 Connect   debian-sys-maint@localhost on 
                   43 Query     select @@version_comment limit 1
                   43 Query     select count(*) into @discard from `niws`.`pages`
                   43 Quit
                   44 Connect   debian-sys-maint@localhost on 
                   44 Query     select @@version_comment limit 1
                   44 Query     select count(*) into @discard from `niws`.`user_admin`
                   44 Quit
                   45 Connect   debian-sys-maint@localhost on 
                   45 Query     select @@version_comment limit 1
                   45 Query     select count(*) into @discard from `phpmyadmin`.`pma_bookmark`
                   45 Quit
                   46 Connect   debian-sys-maint@localhost on 
                   46 Query     select @@version_comment limit 1
                   46 Query     select count(*) into @discard from `phpmyadmin`.`pma_column_info`
                   46 Quit
                   47 Connect   debian-sys-maint@localhost on 
                   47 Query     select @@version_comment limit 1
                   47 Query     select count(*) into @discard from `phpmyadmin`.`pma_designer_coords`
                   47 Quit
                   48 Connect   debian-sys-maint@localhost on 
                   48 Query     select @@version_comment limit 1
                   48 Query     select count(*) into @discard from `phpmyadmin`.`pma_history`
                   48 Quit
                   49 Connect   debian-sys-maint@localhost on 
                   49 Query     select @@version_comment limit 1
                   49 Query     select count(*) into @discard from `phpmyadmin`.`pma_pdf_pages`
                   49 Quit
                   50 Connect   debian-sys-maint@localhost on 
                   50 Query     select @@version_comment limit 1
                   50 Query     select count(*) into @discard from `phpmyadmin`.`pma_relation`
                   50 Quit
                   51 Connect   debian-sys-maint@localhost on 
                   51 Query     select @@version_comment limit 1
                   51 Query     select count(*) into @discard from `phpmyadmin`.`pma_table_coords`
                   51 Quit
                   52 Connect   debian-sys-maint@localhost on 
                   52 Query     select @@version_comment limit 1
                   52 Query     select count(*) into @discard from `phpmyadmin`.`pma_table_info`
                   52 Quit
                   53 Connect   debian-sys-maint@localhost on 
                   53 Query     select @@version_comment limit 1
                   53 Query     select count(*) into @discard from `phpmyadmin`.`pma_tracking`
                   53 Quit
                   54 Connect   debian-sys-maint@localhost on 
                   54 Query     select @@version_comment limit 1
                   54 Query     select count(*) into @discard from `phpmyadmin`.`pma_userconfig`
                   54 Quit
150824  3:34:02    55 Connect   root@localhost on 
                   55 Init DB   niws
                   55 Query     SET NAMES utf8
                   55 Query     SELECT *
FROM (`menu`)
WHERE `id` =  '85'
                   55 Query     SELECT *
FROM (`menu`)
WHERE `id_mom` =  '22'
ORDER BY `order` ASC
                   55 Query     SELECT *
FROM (`menu`)
WHERE `id` =  '85'
                   55 Query     SELECT *
FROM (`menu`)
ORDER BY `order` ASC
                   55 Query     SELECT *
FROM (`catalog`)
WHERE `cat` =  '85'
ORDER BY `id` DESC
                   55 Query     SELECT *
FROM (`menu`)
WHERE `url` =  '1'
ORDER BY `order` ASC
                   56 Connect   root@localhost on 
                   56 Init DB   niws
                   56 Query     SET NAMES utf8
                   56 Query     SELECT *
FROM (`pages`)
WHERE `name` =  'main'
                   56 Query     SELECT *
FROM (`menu`)
ORDER BY `order` ASC
                   56 Query     SELECT *
FROM (`menu`)
WHERE `id_mom` =  '22'
ORDER BY `order` ASC
                   56 Query     SELECT *
FROM (`content`)
WHERE `cat` =  'main'
                   56 Query     SELECT *
FROM (`catalog`)
WHERE `tip_text` =  'history'
ORDER BY `id` DESC
LIMIT 2
                   56 Query     SELECT *
FROM (`catalog`)
WHERE `tip_text` =  'open'
ORDER BY `id` DESC
LIMIT 2
                   56 Query     SELECT *
FROM (`catalog`)
WHERE `tip_text` =  'ludi'
ORDER BY `id` DESC
LIMIT 2
root@crackkc:/var/log/mysql# 
Ответ: !includedir /etc/mysql/conf.d/

вот тут
Вопрос: Оптимизация MySQL по результатам mysqltuner.pl

Добрый день всем, честно говоря я не профи в настройке mysql но после переноса части сайтов мускул стал грузить процессор под 300-500 процентов что и заставило меня полезть в более глубокую настройку.

Временные файлы вынес уже в ОЗУ, таблицы чекал раза четыре и разными способами но количество фрагментированных таблиц остается в пределах 300-500 таблиц, как ни крути. Не знаю почему, но вопрос в другом. Следую плавно советам mysqltuner и подкручиваю значения, паралелльно читая что за что отвечает и где какие должны быть оптимальные значения но уже начинаю волноватся за некоторые параметры которые кажутся "большими" скромно говоря. Можете проверить и подсказать что где не так или на что стоит обратить внимание, буду очень признателен.

вот содержимое файла 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
default-character-set = utf8

# 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		= /dev/shm
language	= /usr/share/mysql/english
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.
#
# * Fine Tuning
#
key_buffer		= 3048M
max_allowed_packet	= 16M
thread_stack		= 192K
thread_cache_size       = 32
join_buffer_size	= 256K
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
max_connections        = 300
table_cache            = 8096
open_files_limit	= 16192
#interactive_timeout	= 28800
#wait_timeout		= 28800
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit	= 18M
query_cache_size        = 896M
table_open_cache	= 8000
#
# * 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 logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# 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
default-character-set = utf8
#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


tmp_table_size = 512M
max_heap_table_size = 256M
table_cache = 4048
innodb_buffer_pool_size = 2048M



[mysqldump]
quick
quote-names
max_allowed_packet	= 16M
default-character-set = utf8

[mysql]
#no-auto-rehash	# faster start of mysql but no tab completition

default-character-set = utf8
[isamchk]
key_buffer		= 2048M

#
# * 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/


вот что выдает myslqtuner

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.63-0+squeeze1-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 11G (Tables: 3504)
[--] Data in InnoDB tables: 1G (Tables: 292)
[--] Data in MEMORY tables: 0B (Tables: 4)
[!!] Total fragmented tables: 461

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 29m 5s (523K q [300.139 qps], 18K conn, TX: 2B, RX: 63M)
[--] Reads / Writes: 94% / 6%
[--] Total buffers: 6.1G global + 2.8M per thread (300 max threads)
[OK] Maximum possible memory usage: 6.9G (22% of installed RAM)
[OK] Slow queries: 1% (10K/523K)
[OK] Highest usage of available connections: 21% (65/300)
[OK] Key buffer size / total MyISAM indexes: 3.0G/1.5G
[OK] Key buffer hit rate: 100.0% (966M cached / 219K reads)
[OK] Query cache efficiency: 64.7% (284K cached / 439K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (260 temp sorts / 54K sorts)
[!!] Joins performed without indexes: 1227
[!!] Temporary tables created on disk: 42% (12K on disk / 28K total)
[OK] Thread cache hit rate: 99% (65 created / 18K connections)
[OK] Table cache hit rate: 26% (4K open / 15K opened)
[OK] Open file limit used: 44% (7K/16K)
[OK] Table locks acquired immediately: 99% (191K immediate / 191K locks)
[OK] InnoDB data size / buffer pool: 1.5G/2.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Adjust your join queries to always utilize indexes
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    join_buffer_size (> 256.0K, or always use indexes with joins)


вот что творится в топе

top - 00:48:28 up 237 days,  7:29,  1 user,  load average: 5.15, 6.64, 7.16
Tasks: 307 total,   5 running, 302 sleeping,   0 stopped,   0 zombie
Cpu(s): 75.1%us, 14.2%sy,  0.0%ni,  9.8%id,  0.7%wa,  0.0%hi,  0.1%si,  0.0%st
Mem:  32703444k total, 31550156k used,  1153288k free,  2300076k buffers
Swap: 33553332k total,     8296k used, 33545036k free, 21243024k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
15750 mysql     20   0 6620m 2.6g 7896 S  345  8.4  70:06.29 mysqld
23246 www-data  20   0  344m 127m 7276 R   73  0.4   0:35.28 apache2
24004 www-data  20   0  290m  77m 5928 S   44  0.2   0:06.39 apache2
23957 www-data  20   0  292m  77m 5976 R   41  0.2   0:19.32 apache2
24025 www-data  20   0  272m  60m 4292 S   30  0.2   0:00.90 apache2
23913 www-data  20   0  295m  83m 6716 R   29  0.3   0:37.49 apache2
23912 www-data  20   0  277m  65m 6768 S   25  0.2   0:47.11 apache2
24003 www-data  20   0  273m  60m 6232 S   24  0.2   0:16.04 apache2
23292 www-data  20   0  290m  78m 7556 S   21  0.2   1:06.56 apache2
23935 www-data  20   0  284m  69m 6780 S   19  0.2   0:32.12 apache2
23922 www-data  20   0  291m  78m 6120 R   16  0.2   1:02.37 apache2
23920 www-data  20   0  278m  65m 6872 S   15  0.2   0:21.00 apache2
23958 www-data  20   0  279m  67m 6920 S   11  0.2   0:18.90 apache2
24023 www-data  20   0  268m  57m 4784 S   10  0.2   0:01.65 apache2
23286 www-data  20   0  281m  67m 6816 S    9  0.2   0:07.07 apache2
23291 www-data  20   0  288m  74m 7092 S    1  0.2   0:33.66 apache2
 9591 root      20   0     0    0    0 S    0  0.0   1:51.62 kworker/4:1
18429 root      20   0     0    0    0 S    0  0.0   0:37.95 kworker/1:1
23311 www-data  20   0  277m  66m 6568 S    0  0.2   0:03.41 apache2
24024 root      20   0 19256 1456  948 R    0  0.0   0:00.02 top
26329 gitlab    20   0  943m 133m 2168 S    0  0.4 167:56.34 ruby1.9.1
28214 www-data  20   0 56732  28m 1788 S    0  0.1   1:08.11 nginx
    1 root      20   0  8404  716  592 S    0  0.0   2:09.28 init
    2 root      20   0     0    0    0 S    0  0.0   0:00.22 kthreadd
    3 root      20   0     0    0    0 S    0  0.0   9:10.40 ksoftirqd/0
    6 root      RT   0     0    0    0 S    0  0.0   0:26.19 migration/0
    7 root      RT   0     0    0    0 S    0  0.0   0:47.69 watchdog/0
    8 root      RT   0     0    0    0 S    0  0.0   0:32.35 migration/1
   10 root      20   0     0    0    0 S    0  0.0   5:38.45 ksoftirqd/1
   12 root      RT   0     0    0    0 S    0  0.0   0:31.18 watchdog/1
   13 root      RT   0     0    0    0 S    0  0.0   0:23.97 migration/2
   15 root      20   0     0    0    0 S    0  0.0   5:21.56 ksoftirqd/2
   16 root      RT   0     0    0    0 S    0  0.0   0:23.93 watchdog/2
   17 root      RT   0     0    0    0 S    0  0.0   0:18.57 migration/3
   19 root      20   0     0    0    0 S    0  0.0   5:15.37 ksoftirqd/3


в целом в данный момент показатели вроде в норме но иногда без видимой причины резко растет la и вырастает до 20, иногда до 50 и mysqltuner выдает совет увеличить

query_cache_limit = 18M
query_cache_size = 896M

которые на мой взгляд уже нереально большие обосновывая это большим количеством

Query cache prunes per day

Баз на сервере очень много, 32гб памяти, поэтому все так сложно. Подскажите, буду очень признателен за любые советы и мнения.
Ответ: ну вот, при кеше в 128мб уже через 15 минут полезли prunes

-------- Performance Metrics -------------------------------------------------
[--] Up for: 14m 53s (328K q [368.365 qps], 12K conn, TX: 1B, RX: 43M)
[--] Reads / Writes: 93% / 7%
[--] Total buffers: 4.4G global + 6.6M per thread (300 max threads)
[OK] Maximum possible memory usage: 6.3G (20% of installed RAM)
[OK] Slow queries: 2% (8K/328K)
[OK] Highest usage of available connections: 16% (49/300)
[OK] Key buffer size / total MyISAM indexes: 2.0G/1.5G
[OK] Key buffer hit rate: 100.0% (545M cached / 178K reads)
[OK] Query cache efficiency: 63.3% (176K cached / 278K selects)
[!!] Query cache prunes per day: 1996875
[OK] Sorts requiring temporary tables: 0% (141 temp sorts / 35K sorts)
[!!] Joins performed without indexes: 1069
[!!] Temporary tables created on disk: 41% (7K on disk / 18K total)
[OK] Thread cache hit rate: 99% (49 created / 12K connections)
[OK] Table cache hit rate: 25% (4K open / 15K opened)
[OK] Open file limit used: 44% (7K/16K)
[OK] Table locks acquired immediately: 99% (128K immediate / 128K locks)
[OK] InnoDB data size / buffer pool: 1.5G/2.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Adjust your join queries to always utilize indexes
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    query_cache_size (> 128M)
    join_buffer_size (> 4.0M, or always use indexes with joins)


Сейчас поставлю 256 но уверен что и этого будет мало и будет просить дальше, до примерно отметки в 1гб (1гб долго не гонял, возможно и там будет лезть)
Вопрос: Ошибка при импорте БД в MySQL - Note: table storage engine for *** doesn't have this option

Есть MySQL-server (1), настроенный давно, характеристики настройки не сохранились (инсталляционная версия 5.6.13). На нем есть БД, к которой по локальной сети обращается другой сервер (10).

Задача: Создать MySQL-server (2) – точную копию с (1), т.е. настроить из дистрибутива MySQL (1), выгрузить БД с (1), залить на (2). Критерий успешности – работающий (10) в паре с (2).

Выгрузку с (1) БД сделала HeidiSQL – Ошибок не выдал, загружаю на (2) тоже ей же;
При импорте этой БД в настроенный (2), HeidiSQL выдает ошибку.

Кликните здесь для просмотра всего текста
Ошибка при импорте БД в MySQL - Note: table storage engine for *** doesn't have this option


Сделала загрузку БД на (2) через Workbench – (10) тоже не работает.

Поиск по Google/Яндекс дает общую информацию, не приближающую к решению.

Исключила брандмауэр, проброс портов – настройки одинаковые на обоих компах.

MySQL на (2) принял и порт, и пользователя, и пароль (в ранних версиях настройки программы проблемы вылезали по этим пунктам).

Вопрос: Какие настройки при экспорте/импорте БД могут исправить эту ошибку.
Ответ: Изучение темы дало следующие результаты. Лог ошибок Name-comp.err выдал вот что:

Кликните здесь для просмотра всего текста
2015-03-28 09:38:21 3052 [Note] Plugin 'FEDERATED' is disabled.
2015-03-28 09:38:21 3052 [Warning] option 'innodb-autoextend-increment': unsigned value 67108864 adjusted to 1000
2015-03-28 09:38:22 45c InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2015-03-28 09:38:22 3052 [Note] InnoDB: The InnoDB memory heap is disabled
2015-03-28 09:38:22 3052 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2015-03-28 09:38:22 3052 [Note] InnoDB: Compressed tables use zlib 1.2.3
2015-03-28 09:38:22 3052 [Note] InnoDB: Not using CPU crc32 instructions
2015-03-28 09:38:23 3052 [Note] InnoDB: Initializing buffer pool, size = 8.0M
2015-03-28 09:38:23 3052 [Note] InnoDB: Completed initialization of buffer pool
2015-03-28 09:38:23 3052 [Note] InnoDB: Highest supported file format is Barracuda.
2015-03-28 09:38:26 3052 [Note] InnoDB: 128 rollback segment(s) are active.
2015-03-28 09:38:26 3052 [Note] InnoDB: Waiting for purge to start
2015-03-28 09:38:27 3052 [Note] InnoDB: 5.6.13 started; log sequence number 1625977
2015-03-28 09:38:27 3052 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: a8a42d2e-d50c-11e4-b573-000c29a2e388.
2015-03-28 09:38:27 3052 [Note] Server hostname (bind-address): '*'; port: 33060
2015-03-28 09:38:27 3052 [Note] IPv6 is available.
2015-03-28 09:38:27 3052 [Note] - '::' resolves to '::';
2015-03-28 09:38:27 3052 [Note] Server socket created on IP: '::'.
2015-03-28 09:38:32 3052 [Note] Event Scheduler: Loaded 0 events
2015-03-28 09:38:32 3052 [Note] C:/MySQL/MySQL Server 5.6/bin\mysqld: ready for connections.
Version: '5.6.13' socket: '' port: 33060 MySQL Community Server (GPL)


Стало понятно, что не хватает каких-то библиотек... И загружаемая БД просто не распознается MySQL'ем. С учетом, что я установила MySQL в комплектации Default (по-умолчанию), а еще был вариант Full, имеет смысл переставить MySQL в полной комплектации.

Нашла статью УСТАНАВЛИВАЕМ БИБЛИОТЕКУ ДЛЯ РАБОТЫ С БАЗОЙ ДАННЫХ MYSQL (публиковать тут ссылки запрещено...)
Кликните здесь для просмотра всего текста

Как установить библиотеку для работы с базой данных MySQL
1. На оффсайте скачиваем коннектор(com.mysql.jdbc.Driver)Connector/J 5.1.18 или
скачать коннектор com.mysql.jdbc.Driver
2. смотрим в нашей IDE какую JDK мы используем, для примера /java-6-openjdk/
в директорию для linux:
/usr/lib/jvm/java-6-openjdk/jre/lib/ копируем mysql-connector-java-5.1.9-bin.jar (он в прикрепленном файле)
в директорию для Windows:
...Program Files/.../путь к вашей jdk/jre/lib/
3. далее идем в настройки нашей IDE, и в разделе JRE добавляем наш .jar
для Eclipse: Window- Prefarences- Java- Installed JREs- (клик по jdk)- Edit...- Add External JARs...

В Java коде:
Загружаем в память драйвер-
Code

try {
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
} catch (Exception e) {
e.printStackTrace();
}


Подключаемся к базе -
Code

String pathConnection = "jdbc:mysql://localhost/dbname";
String nameConnection = "user";
String passConnection = "pass";
try {
conn = DriverManager.getConnection(pathConnection,
nameConnection, passConnection);
} catch (Exception e) {
e.printStackTrace();
}


Я не поняла, куда это нужно прописывать... Java у меня нет, а PHP-движок на (10). Его никто не трогал и не менял. Сейчас у меня установлено несколько версий Connector'ов. Возможно, в FULL-версии установлены еще какие-то библиотеки.

О результатах переустановки MySQL отпишусь позже.
Вопрос: MySQL embedded

здраствуйте, возникла проблема.
дело в том что в проекте используется встроенный Mysql, и подключение выполняется через libmysqld.
само подключение файлов настроено как нужно (наверное), при запуске клиентской программы код которой инициализирует библиотечный файл libmysqld, программа создает нужные файлы в указанном каталоге (ibdata1, ib_logfile0 и т.д). но как только код доходит до строчки "mysql = mysql_ini(NULL)", дальше он не двигается и консольное приложение просто закрывается. В чем может быть проблема?

Пример:

#include <conio.h>
#include <iostream>
#include <stdio.h>
#include <stdlib.h>
#include <stdarg.h>
#include <winsock.h>
#include "mysql.h"

using namespace std;

MYSQL* mysql;
MYSQL_RES *results;
MYSQL_ROW record;

static char *server_options[] = {"NULL", "--defaults-file=my.ini", NULL};
static char *server_groups[] = { "client", NULL };
int num_elements = (sizeof(server_options) / sizeof(char *)) - 1;

int main()
{
	// инициализация библиотеки MySQL
	mysql_library_init(num_elements, server_options, server_groups);
	cout << "Выше код отработал";

	mysql = mysql_init(NULL);
	cout << "Это сообщение не будет отображено";

	mysql_options(mysql, MYSQL_READ_DEFAULT_GROUP, "client");
	mysql_options(mysql, MYSQL_OPT_USE_EMBEDDED_CONNECTION, NULL);
	
	mysql_real_connect(mysql, NULL, NULL, NULL, NULL, 0, NULL, 0)
	mysql_query(mysql, "CREATE TABLE food (id int, name varchar(10))");

	mysql_close(mysql);
	mysql_library_end();

	_getch();
	return 0;
}
Ответ:
Ucali
отлично, отображение лог-файла включено. после запуска в лог-файл записались различные ошибки.
причина была в параметрах которые не были добавлены в .ini (innodb_data_home_dir=C:\\test\\data и innodb_data_file_path=ibdata1) и в содержимом каталога data, оно должно быть таким как mysql\data\*.
теперь код продолжает работу.
функция mysql_library_init() по прежднему возвращает отличное значение от 0.
использовал функцию mysql_errno() - возвращает код ошибки 2000 (неизвестная ошибка mysql).
по прежднему что-то не инициализировано в my.ini или в передаваемых аргументах функции mysql_library_init().

нормальное добавление параметра
innodb_data_file_path = ibdata1:10M:autoextend
приводите полный точный my.ini во избежание доп вопросов
лучше взять любой my*.ini из любого скачанного MySQL-server-noinstall
и скопировать параметры оттуда, этот файл не "любит" ошибок (т.е. сервер MySQL )
Вопрос: MySQL Workbench (трансфер данных): обратные апострофы включаются в название БД

WinXP, MySQL Workbench 6.1.7 (последняя версия, поддерживающая WinXP).
Выполняю трансфер данных из удалённой базы (Debian + mysql) в локальную. На удалённом сервере создал пользователя user@% и разрешил удалённый коннект. Из MySQL Workbench Reverse Engineer из удалённой базы выполняется нормально - схема БД нормально читается, строится EER-диаграмма и пр. Т.е. с коннектом и с правами всё в порядке.

Далее пытаюсь выполнить трансфер данных из удалённой базы в локальную (Schema Transfer Wizzard). В процессе транфера получаю ошибку (в тексте ошибки имя БД заменил на database):
автор
ERROR: Reverse Engineering: QueryError("Error executing 'SHOW TABLES FROM ```database```'
Unknown database '`database`'.
SQL Error: 1049"): error calling Python module function DbMySQLRE.reverseEngineer
Failed
Предварительно на всякий случай проверил следующее:
1. На удалённом сервере mysql зашёл под тем же пользователем, под которым выполняю трансфер данных, и нормально выполнил запрос SHOW TABLES FROM `database` - получил список таблиц
2. Создал 2 локальных соединения и попробовал выполнить трансфер данных из локальной БД в локальную БД - та же самая ошибка
3. Попробовал выполнить трансфер из тестовой таблицы test (которую создаёт mysql workbench при установке) - та же самая ошибка

А теперь возвращаемся к сообщению об ошибке. Видно, что обратные апострофы включены в название БД - поэтому и генерируется ошибка, что БД не существует.
Сделал следующее (тестирую трансфер данных из локальной БД в локальную БД)
:

1. В настройках локального mysql вместо режима TRADITIONAL (для названий объектов БД используются обратные апострофы) указал режим ANSI_QUOTES (для названий объектов БД используются двойные кавычки и апострофы):
sql-mode="ANSI_QUOTES,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
Перезапустил локальный mysql. На всякий случай проверил корректность считывания этой настройки в Server - Options File и непосредственно в Server - Status And System Variables - System Variables. Выполняю трансфер данных - та же ошибка.

2. В настройках mysql workbench на вкладке "Model: MySQL" в поле "SQL_MODE to be used in generated scrips" вместо "TRADITIONAL,ALLOW_INVALID_DATES" указываю "ANSI_QUOTES,ALLOW_INVALID_DATES". На всякий случай перезагружаю mysql workbench. Выполняю трансфер данных - та же ошибка.

3. В настройках mysql workbench на вкладке "General" в поле "Interactive GRT Shell language" вместо "python" указываю "lua". На всякий случай перезагружаю mysql workbench. Выполняю трансфер данных - та же ошибка.

Во всех случаях (даже в режиме ANSI_SQL) имена баз данных в Schema Transfer Wizzard отображаются в обратных апострофах.

Почему mysql workbench (Schema Transfer Wizzard) включает обратные апострофы в название БД ?
Или это глючит питоновский модуль ?
Ответ:
автор
А в воркбенче ты его типа не засвечиваешь... какая в пень разница? опять же кто тебе мешает интерактивно на старте в самом батнике запросить пароль? ну будет он в памяти во время исполнения - так ещё до среды конкретного процесса достучаться нужно... а в тексте его не будет.
Так mysql workbench его тоже в открытом виде нигде не хранит. И вводить ничего не нужно.
А если в батнике пароль запрашивать, тогда чем он лучше варианта с mysql workbench ? Необходим такой вариант: компьютер загружается, открывается phpStorm и... начинаешь работать с проектом. При это локальная база к этому моменту уже будет синхронизирована с продакшеном.

В mysql workbench как-то много телодвижений нужно делать. Выбирать соединения, тыкать по кнопкам, снимать галку "Migrate MyISAM tanles to InnoDB" (достаёт)...
Вопрос: Почему несбросить пароль в mysql

Здравствуйте ,простите не так хорошо знаком с особенностями работы mysql в linuxe ,очень хотел бы научиться. Могли бы вы подсказать в чём дело , почему несбрасывается пароль к mysql , делаю всё точно вроде
может надо обратить на что-то ещё внимаение.
Спасибо.

Bash
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
saladdd@saladdd-EasyNote-LM85 ~ $ sudo /etc/init.d/mysql stop
[ ok ] Stopping mysql (via systemctl): mysql.service.
saladdd@saladdd-EasyNote-LM85 ~ $ sudo mysqld_safe --skip-grant-tables &
[1] 25268
saladdd@saladdd-EasyNote-LM85 ~ $ 2017-04-22T21:59:12.889994Z mysqld_safe Logging to syslog.
2017-04-22T21:59:12.894515Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2017-04-22T21:59:12.898707Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2017-04-22T21:59:12.902958Z mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.
 
[1]+  Выход из 1       sudo mysqld_safe --skip-grant-tables
 
saladdd@saladdd-EasyNote-LM85 ~ $ sudo /etc/init.d/mysql start
 
 sudo /etc/init.d/mysql start
[ ok ] Starting mysql (via systemctl): mysql.service.
saladdd@saladdd-EasyNote-LM85 ~ $ sudo mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
saladdd@saladdd-EasyNote-LM85 ~ $ sudo mysql -u root -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
Ответ: magirus, Fedor Vlasenko,
Bash
1
2
3
4
5
6
7
8
2017-04-22T18:18:51.497297Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-04-22T18:18:55.593863Z 0 [Warning] InnoDB: New log files created, LSN=45790
2017-04-22T18:18:56.238622Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2017-04-22T18:18:56.471964Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 2600eb75-2788-11e7-85e3-4$
2017-04-22T18:18:56.504482Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2017-04-22T18:18:56.505319Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2017-04-22T18:19:21.843564Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2017-04-22T18:19:21.843641Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
Добавлено через 3 часа 3 минуты
Что значит вот эта строка
Bash
1
2017-04-22T18:18:56.504482Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
Добавлено через 6 часов 42 минуты
Честно говоря не очень понимаю логи