Не запускаеться MySQL

[Nafanya]

После очередной грозы был бросок напряжения, сработала защита. ИБП не выдержал таких испытаний (вернее его батарейка). MySQL перестал запускаться. Ошибок на консоль не выводит. Может кто, что посоветует? Батарейку сменю, тут советовать не надо.

[Nafanya]

В логах нашел :

140521 19:11:52 mysqld_safe Starting mysqld daemon with databases from /var/db/mysql
140521 19:11:53 InnoDB: The InnoDB memory heap is disabled
140521 19:11:53 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140521 19:11:53 InnoDB: Compressed tables use zlib 1.2.7
140521 19:11:53 InnoDB: Initializing buffer pool, size = 128.0M
140521 19:11:53 InnoDB: Completed initialization of buffer pool
140521 19:11:53 InnoDB: highest supported file format is Barracuda.
140521 19:11:53  InnoDB: Waiting for the background threads to start
140521 19:11:54 InnoDB: 5.5.33 started; log sequence number 101306677238
140521 19:11:54 [ERROR] /usr/local/libexec/mysqld: unknown option '--`zskip-character-set-client-handshake'
140521 19:11:54 [ERROR] Aborting

140521 19:11:54  InnoDB: Starting shutdown...
140521 19:11:55  InnoDB: Shutdown completed; log sequence number 101306677238
140521 19:11:55 [Note] /usr/local/libexec/mysqld: Shutdown complete

[0xbad0c0d3]

Код:

140521 19:11:54 [ERROR] /usr/local/libexec/mysqld: unknown option '--`zskip-character-set-client-handshake'

Разве не очевидно?

[Nafanya]

Код:

140521 19:11:54 [ERROR] /usr/local/libexec/mysqld: unknown option '--`zskip-character-set-client-handshake'

Разве не очевидно?

Да есть все в конфигурационном файле. Я это проверил в первую очередь.
Теперь в логах:

140521 20:26:36 mysqld_safe Starting mysqld daemon with databases from /var/db/mysql
140521 20:26:36 InnoDB: The InnoDB memory heap is disabled
140521 20:26:36 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140521 20:26:36 InnoDB: Compressed tables use zlib 1.2.7
140521 20:26:36 InnoDB: Initializing buffer pool, size = 128.0M
140521 20:26:36 InnoDB: Completed initialization of buffer pool
140521 20:26:36 InnoDB: highest supported file format is Barracuda.
140521 20:26:36  InnoDB: Waiting for the background threads to start
140521 20:26:37 InnoDB: 5.5.33 started; log sequence number 101306677238
140521 20:26:37 [Note] Recovering after a crash using mysql-bin
140521 20:26:37 [Note] Starting crash recovery...
140521 20:26:37 [Note] Crash recovery finished.
140521 20:26:37 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
140521 20:26:37 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
140521 20:26:37 [Note] Server socket created on IP: '0.0.0.0'.
140521 20:26:37 [Warning] 'user' entry 'root@woody.net.ua' ignored in --skip-name-resolve mode.
140521 20:26:37 [Warning] 'user' entry '@woody.net.ua' ignored in --skip-name-resolve mode.
140521 20:26:37 [Warning] 'proxies_priv' entry '@ root@woody.net.ua' ignored in --skip-name-resolve mode.
140521 20:26:37 [Note] Event Scheduler: Loaded 0 events
140521 20:26:37 [Note] /usr/local/libexec/mysqld: ready for connections.
Version: '5.5.33-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
140521 20:26:37  InnoDB: Assertion failure in thread 34401729536 in file trx0purge.c line 840
InnoDB: Failing assertion: purge_sys->purge_trx_no <= purge_sys->rseg->last_trx_no
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
17:26:37 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

[Nafanya]

может тупо снести и поставить заново? Я думаю, пострадает ли база? подскажите...

[0xbad0c0d3]

Код:

InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html

Ну и еще mysqlcheck

[Nafanya]

Не дождался. Решил все кардинальным образом.

[WideAreaNetwork]

так же после скачка не стартует мускул, выдает ошибку в localhost.err

Attempted to open a previously opened tablespace. Previous tablespace mysql/innodb_table_stats uses space ID: 1 at filepath: ./mysql/innodb_table_stats.ibd. Cannot open tablespace nodeny/admin which uses space ID: 1 at filepath: ./nodeny/admin.ibd

подскажите пжл как можно исправить?

[ser970]

так же после скачка не стартует мускул, выдает ошибку в localhost.err

Attempted to open a previously opened tablespace. Previous tablespace mysql/innodb_table_stats uses space ID: 1 at filepath: ./mysql/innodb_table_stats.ibd. Cannot open tablespace nodeny/admin which uses space ID: 1 at filepath: ./nodeny/admin.ibd

подскажите пжл как можно исправить?

восстанавливать базу...

в my.cnf игратся с параметром   -
[mysqld]
innodb_force_recovery

 далеть дамп - удалть бинаронно , и накатывать дамп...

[WideAreaNetwork]

в my.cnf игратся с параметром   -
[mysqld]
innodb_force_recovery

пробовал выставлять 1 не помогло, остальное как делать не знаю

[ser970]

в my.cnf игратся с параметром   -
[mysqld]
innodb_force_recovery

пробовал выставлять 1 не помогло, остальное как делать не знаю

дай доступы гляну

а вообще
innodb_force_recovery  от   1 до 6

начинать с 1

з.ы. перед этим сделать копию папки mysql полностью на другой винт ...

[WideAreaNetwork]

дай доступы гляну

лс

[WideAreaNetwork]

большое спс ser970 

[WideAreaNetwork]

в my.cnf игратся с параметром   -
[mysqld]
innodb_force_recovery

пробовал выставлять 1 не помогло, остальное как делать не знаю

запускать
/usr/local/etc/rc.d/mysql-server forcestart


перед любым движением сначала дамп всего
http_://forum.nodeny.com.ua/index.php?topic=2203.msg34873#msg34873

З.Ы. ихмо всегда проще восстановить...

есть отсутствующие таблицы

Код:

mysql.innodb_index_stats
Error    : Table 'mysql.innodb_index_stats' doesn't exist
status   : Operation failed

mysql.innodb_table_stats
Error    : Table 'mysql.innodb_table_stats' doesn't exist
status   : Operation failed

mysql.slave_master_info
Error    : Table 'mysql.slave_master_info' doesn't exist
status   : Operation failed

mysql.slave_relay_log_info
Error    : Table 'mysql.slave_relay_log_info' doesn't exist
status   : Operation failed

mysql.slave_worker_info
Error    : Table 'mysql.slave_worker_info' doesn't exist
status   : Operation failed

правильно ли будет восстановить их следующим методом?

удалить эти таблицы через DROP TABLE table_name;

удалить в папке /var/db/mysql/mysql файлы *.frm и *.ibd вышеуказанных таблиц;

заново создать эти таблицы

Код:

CREATE TABLE `innodb_index_stats` (
`database_name` varchar(64) COLLATE utf8_bin NOT NULL,
`table_name` varchar(64) COLLATE utf8_bin NOT NULL,
`index_name` varchar(64) COLLATE utf8_bin NOT NULL,
`last_update` timestamp NOT NULL DEFAULT 
CURRENT_TIMESTAMP ON UPDATE 
CURRENT_TIMESTAMP,
`stat_name` varchar(64) COLLATE utf8_bin NOT NULL,
`stat_value` bigint(20) unsigned NOT NULL,
`sample_size` bigint(20) unsigned DEFAULT NULL,
`stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
COLLATE=utf8_bin STATS_PERSISTENT=0;

Код:

CREATE TABLE `innodb_table_stats` (
`database_name` varchar(64) COLLATE utf8_bin NOT NULL,
`table_name` varchar(64) COLLATE utf8_bin NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`n_rows` bigint(20) unsigned NOT NULL,
`clustered_index_size` bigint(20) unsigned NOT NULL,
`sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`database_name`,`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;

Код:

CREATE TABLE `slave_master_info` (
`Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file.',
`Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log currently being read from the master.',
`Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last read event.',
`Host` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'The host name of the master.',
`User_name` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The user name used to connect to the master.',
`User_password` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The password used to connect to the master.',
`Port` int(10) unsigned NOT NULL COMMENT 'The network port used to connect to the master.',
`Connect_retry` int(10) unsigned NOT NULL COMMENT 'The period (in seconds) that the slave will wait before trying to reconnect to the master.',
`Enabled_ssl` tinyint(1) NOT NULL COMMENT 'Indicates whether the server supports SSL connections.',
`Ssl_ca` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Authority (CA) certificate.',
`Ssl_capath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path to the Certificate Authority (CA) certificates.',
`Ssl_cert` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL certificate file.',
`Ssl_cipher` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the cipher in use for the SSL connection.',
`Ssl_key` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL key file.',
`Ssl_verify_server_cert` tinyint(1) NOT NULL COMMENT 'Whether to verify the server certificate.',
`Heartbeat` float NOT NULL,
`Bind` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'Displays which interface is employed when connecting to the MySQL server',
`Ignored_server_ids` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The number of server IDs to be ignored, followed by the actual server IDs',
`Uuid` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The master server uuid.',
`Retry_count` bigint(20) unsigned NOT NULL COMMENT 'Number of reconnect attempts, to the master, before giving up.',
`Ssl_crl` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Revocation List (CRL)',
`Ssl_crlpath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path used for Certificate Revocation List (CRL) files',
`Enabled_auto_position` tinyint(1) NOT NULL COMMENT 'Indicates whether GTIDs will be used to retrieve events from the master.',
PRIMARY KEY (`Host`,`Port`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Master Information';

Код:

CREATE TABLE `slave_relay_log_info` (
`Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file or rows in the table. Used to version table definitions.',
`Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the current relay log file.',
`Relay_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The relay log position of the last executed event.',
`Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log file from which the events in the relay log file were read.',
`Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last executed event.',
`Sql_delay` int(11) NOT NULL COMMENT 'The number of seconds that the slave must lag behind the master.',
`Number_of_workers` int(10) unsigned NOT NULL,
`Id` int(10) unsigned NOT NULL COMMENT 'Internal Id that uniquely identifies this record.',
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Relay Log Information';

Код:

CREATE TABLE `slave_worker_info` (
`Id` int(10) unsigned NOT NULL,
`Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`Relay_log_pos` bigint(20) unsigned NOT NULL,
`Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`Master_log_pos` bigint(20) unsigned NOT NULL,
`Checkpoint_relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`Checkpoint_relay_log_pos` bigint(20) unsigned NOT NULL,
`Checkpoint_master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`Checkpoint_master_log_pos` bigint(20) unsigned NOT NULL,
`Checkpoint_seqno` int(10) unsigned NOT NULL,
`Checkpoint_group_size` int(10) unsigned NOT NULL,
`Checkpoint_group_bitmap` blob NOT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Worker Information';

перезапустить MySQL
# /usr/local/etc/rc.d/mysql-server restart

пишут что известный баг мускуля 5.6 - https_://bugs.mysql.com/bug.php?id=67179
решение - https_://stackoverflow.com/questions/37856155/mysql-upgrade-failed-innodb-tables-doesnt-exist

пс. для истории