Я получаю это сообщение об ошибке в версии MySQL 5.5.15.
Вот ошибки сервера MySQL:
110927 21:56:51 InnoDB: Assertion failure in thread 1126107456 in file ibuf0ibuf.c line
4185
InnoDB: Failing assertion: page_get_n_recs(page) > 1
InnoDB: We intentionally generate a memory trap.
InnoDB: about forcing recovery.
110927 21:56:51 - mysqld got signal 6 ;
key_buffer_size=536870912
read_buffer_size=4194304
max_used_connections=446
max_threads=2500
thread_count=341
connection_count=341
110927 21:56:54 mysqld_safe Number of processes running now: 0
110927 21:56:54 mysqld_safe mysqld restarted
110927 21:56:54 InnoDB: The InnoDB memory heap is disabled
110927 21:56:54 InnoDB: Mutexes and rw_locks use GCC atomic builtins
110927 21:56:54 InnoDB: Compressed tables use zlib 1.2.3
110927 21:56:54 InnoDB: Initializing buffer pool, size = 48.0G
110927 21:56:56 InnoDB: Completed initialization of buffer pool
110927 21:56:56 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 2476542143668
110927 21:56:56 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 2476547386368
InnoDB: Doing recovery: scanned up to log sequence number 2476552629248
InnoDB: Doing recovery: scanned up to log sequence number 2476557872128
InnoDB: Doing recovery: scanned up to log sequence number 2476563115008
InnoDB: 45 transaction(s) which must be rolled back or cleaned up
InnoDB: in total 45 row operations to undo
InnoDB: Trx id counter is E6B79400
110927 21:58:04 InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47
110927 21:59:08 InnoDB: Assertion failure in thread 1104251200 in file ibuf0ibuf.c line
4185
InnoDB: Failing assertion: page_get_n_recs(page) > 1
После этой ошибки MySQL не смог полностью восстановить, и сервер MySQL был выключен.
Я попытался выполнить принудительное восстановление InnoDB. Я не мог восстановить поврежденные данные с помощью InnoDB force recovery уровня 0–3, но уровня 4.
Оказалось, что я просто умею sqldump data.
Пока один из slavedb заменяется на masterdb, мои службы не работают.
Боюсь, что такая же или похожая ошибка может повториться снова на новой masterdb.
Параметры my.cnf для отказавшего сервера MySQL:
max_allowed_packet = 1024M
net_buffer_length = 32768
binlog_cache_size = 16M
bulk_insert_buffer_size = 64M
datadir = /data/mysql/data
table_cache = 2048
sort_buffer_size = 4M
read_buffer_size = 4M
join_buffer_size = 2M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 4M
thread_cache_size = 8
query_cache_size = 1024M
query_cache_limit = 2M
thread_stack = 192K
thread_concurrency = 48
max_connections = 2500
max_connect_errors = 1000
max_heap_table_size = 64M
back_log = 200
innodb_file_per_table = 1
innodb = on
innodb_additional_mem_pool_size = 20M
innodb_buffer_pool_size = 48G
innodb_data_file_path = inno:1G:autoextend
innodb_data_home_dir = /data/mysql/data
innodb_thread_concurrency = 48
innodb_commit_concurrency = 48
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 1024M
innodb_log_files_in_group = 3
innodb_log_group_home_dir = /data/mysql/data
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_open_files = 102400
В момент времени:
=== status ===
Threads: 354 Questions: 187502669 Slow queries: 209 Opens: 3150 Flush tables: 1 Open
tables: 617 Queries per second avg: 4089.301
top - 21:56:01 up 4 days, 20:59, 0 users, load average: 5.05, 5.41, 5.64
Tasks: 1 total, 0 running, 1 sleeping, 0 stopped, 0 zombie
Cpu(s): 1.5%us, 0.3%sy, 0.0%ni, 95.8%id, 2.2%wa, 0.0%hi, 0.2%si, 0.0%st
Mem: 98929268k total, 97815088k used, 1114180k free, 245976k buffers
Swap: 0k total, 0k used, 0k free, 40979780k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
26019 mysql 15 0 58.1g 52g 7012 S 321.8 55.5 1128:48 mysqld
*after mysql's crashed
=== status ===
top - 21:58:01 up 4 days, 21:01, 0 users, load average: 2.47, 4.59, 5.33
Tasks: 1 total, 0 running, 1 sleeping, 0 stopped, 0 zombie
Cpu(s): 1.5%us, 0.3%sy, 0.0%ni, 95.8%id, 2.2%wa, 0.0%hi, 0.2%si, 0.0%st
Mem: 98929268k total, 55021972k used, 43907296k free, 246568k buffers
Swap: 0k total, 0k used, 0k free, 40997228k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
13077 mysql 25 0 53.8g 11g 3904 D 86.0 12.3 1:06.61 mysqld