MySQL 表随机崩溃

MySQL 表随机崩溃

这是众多随机损坏的表之一。您知道为什么会这样吗?是什么原因造成的?

如何防止 MySQL 表崩溃以及 MySQL 不崩溃?

Repairing USR_wp537

USR_wp537.rev_commentmeta                     OK
USR_wp537.rev_comments                        OK
USR_wp537.rev_links                           OK
USR_wp537.rev_options                         OK
USR_wp537.rev_postmeta                        OK
USR_wp537.rev_posts
Error    : Incorrect key file for table './USR_wp537/rev_posts'; try to repair it
Error    : Incorrect key file for table 'rev_posts'; try to repair it
error    : Corrupt
USR_wp537.rev_term_relationships              OK
USR_wp537.rev_term_taxonomy                   OK
USR_wp537.rev_terms                           OK
USR_wp537.rev_usermeta                        OK
USR_wp537.rev_users      

最终解决的唯一方法是mysql> REPAIR TABLE <tbl> USE_FRM;

这也是mysql5.5

顶部 - 20:17:11 启动 4 天,8:57,1 个用户,平均负载:0.41、0.37、0.36 任务:总共 204 个,1 个正在运行,203 个正在休眠,0 个已停止,0 个僵尸 Cpu0 :11.0%us,0.3%sy,0.0%ni,88.7%id,0.0%wa,0.0%hi,0.0%si,0.0%st Cpu1 :5.0%us,1.0%sy,0.0%ni,93.4%id,0.0%wa,0.0%hi,0.7%si,0.0%st Cpu2 :10.4%us,0.7%sy,0.0%ni,89.0%id,0.0%wa,0.0%hi, 0.0%si,0.0%st Cpu3:1.3%us,0.3%sy,0.0%ni,98.0%id,0.0%wa,0.0%hi,0.3%si,0.0%st 内存:总计 16288440k,已使用 15292940k,可用 995500k,缓冲区 1398928k 交换:总计 8191992k,已使用 0k,可用 8191992k,缓存 9351404k

我的cnf

[mysqld]
core-file
default-storage-engine=MyISAM
local-infile=0
symbolic-links=0
skip-networking
max_connections = 500
max_user_connections = 40
key_buffer = 500M
myisam_sort_buffer_size = 32M
join_buffer_size = 256M
read_buffer_size = 2M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
table_cache = 1024
thread_cache_size = 16K
wait_timeout = 20
connect_timeout = 10
tmp_table_size = 256M
max_heap_table_size = 256M
max_allowed_packet = 160M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 64M
query_cache_type = 1
low_priority_updates=1
concurrent_insert=ALWAYS
log-error=/var/log/mysql/error.log
myisam_repair_threads=4
[mysqld_safe]
core-file-size = unlimited
open_files_limit = 8192
log-error=/var/log/mysql/error.log

[mysqldump]
quick
max_allowed_packet = 512M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

编辑:看起来 MySQL 由于某种原因重新启动了?

130131 20:30:59 [ERROR] Got an error from thread_id=817995, /builddir/build/BUILD/mysql-5.5.28/mysql-5.5.28/storage/myisam/mi_write.c:223
130131 20:30:59 [ERROR] MySQL thread id 817998, OS thread handle 0x7f8589e2a700, query id 24842525 localhost ahmad_wp Waiting for table level lock
SELECT option_value FROM wp_options WHERE option_name = 'widget_momizat-posts-images' LIMIT 1
130131 20:30:59 [ERROR] MySQL thread id 817995, OS thread handle 0x7f858692f700, query id 24842524 localhost ahmad_wp update
INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`) VALUES ('rewrite_rules', 'a:91:{s:27:\"typename/([0-9]{4})/(.+)/?$\";s:30:\"index.php?typename=$matches[2]\";s:47:\"category/(.+?)/feed/(feed|rdf|rss|rss2|atom)/?$\";s:52:\"index.php?category_name=$matches[1]&feed=$matches[2]\";s:42:\"category/(.+?)/(feed|rdf|rss|rss2|atom)/?$\";s:52:\"index.php?category_name=$matches[1]&feed=$matches[2]\";s:35:\"category/(.+?)/page/?([0-9]{1,})/?$\";s:53:\"index.php?category_name=$matches[1]&paged=$matches[2]\";s:17:\"category/(.+?)/?$\";s:35:\"index.php?category_name=$matches[1]\";s:44:\"tag/([^/]+)/feed/(feed|rdf|rss|rss2|atom)/?$\";s:42:\"index.php?tag=$matches[1]&feed=$matches[2]\";s:39:\"tag/([^/]+)/(feed|rdf|rss|rss2|atom)/?$\";s:42:\"index.php?tag=$matches[1]&feed=$matches[2]\";s:32:\"tag/([^/]+)/page/?([0-9]{1,})/?$\";s:43:\"index.php?tag=$matches[1]&paged=$matches[2]\";s:14:\"tag/([^/]+)/?$\";s:25:
    130131 20:33:42 mysqld_safe Number of processes running now: 0
    130131 20:33:42 mysqld_safe mysqld restarted
    130131 20:33:42 [Note] libgovernor.so found
    130131 20:33:42 [Note] All governors functions found too
    130131 20:33:42 [ERROR] Governor not connected
    130131 20:33:42 [Note] All governors lve functions found too
    130131 20:33:42 [Note] Plugin 'FEDERATED' is disabled.
    130131 20:33:42 InnoDB: The InnoDB memory heap is disabled
    130131 20:33:42 InnoDB: Mutexes and rw_locks use GCC atomic builtins
    130131 20:33:42 InnoDB: Compressed tables use zlib 1.2.3
    130131 20:33:42 InnoDB: Using Linux native AIO
    130131 20:33:42 InnoDB: Initializing buffer pool, size = 128.0M
    130131 20:33:42 InnoDB: Completed initialization of buffer pool
    130131 20:33:42 InnoDB: highest supported file format is Barracuda.
    InnoDB: The log sequence number in ibdata files does not match
    InnoDB: the log sequence number in the ib_logfiles!
    130131 20:33:42  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...
    130131 20:33:42  InnoDB: Waiting for the background threads to start
    130131 20:33:43 InnoDB: 1.1.8 started; log sequence number 5726524860
    130131 20:33:43 [Note] Event Scheduler: Loaded 1 event
    130131 20:33:43 [Note] /usr/sbin/mysqld: ready for connections.
    Version: '5.5.28-cll-lve'  socket: '/var/lib/mysql/mysql.sock'  port: 0  MySQL Community Server (GPL)

编辑:硬盘是干净的

编辑:使用 mcelog 检查 RAM

编辑:以下内容用于检查消息中的问题

  902  fgrep -i seg /var/log/messages
  903  fgrep -i mce /var/log/messages
  904  tail /var/log/mysql/error.log
  906  smartctl --all /dev/sda
  907  smartctl -t short /dev/sda
  908  tail /var/log/mysql/error.log
  910  smartctl --all /dev/sda
  911  fgrep -i i/o /var/log/messages
  912  fgrep -i i/o /var/log/messages*
  913  fgrep -i sda /var/log/messages*
  914  fgrep -i sense /var/log/messages*
  915  egrep 'sda.*Error' sense /var/log/messages*
  916  egrep -h 'sda.*Error' sense /var/log/messages*
  917  egrep -h 'sda.*Error' /var/log/messages*

df /tmp
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/sda5              4031680     97304   3729576   3% /tmp

答案1

与普遍看法相反,MySQL 表不会无缘无故崩溃。崩溃通常归因于高负载或太多行。这太糟糕了。

如果表崩溃,则服务器存在潜在问题,例如内存或硬盘驱动器损坏。也可能是 MySQL 服务器中的错误,但这种情况不太可能发生。如果您是网络托管服务商的客户,则除了让托管服务商修复潜在问题外,您无能为力。

答案2

消息日志(通常是 /var/log/messages)中是否有任何有趣的内容。磁盘或内存问题可能在这里可见...

当我们谈论磁盘时,我不得不更换很多磁盘......(即使企业系列也不是防弹的)

答案3

我们已经看到过这种情况,因为由于连接写得不好,临时表的磁盘空间不足。临时表是暂时的,所以很难及时发现。

相关内容