当前位置: > 数据库 > MySQL >

Mysql 维护记录(mysql crash)

时间:2015-05-11 03:06来源:linux.it.net.cn 作者:IT

Mysql 维护记录:

事故发生背景:

1.       下班时,几个同事同时在网站发送logbk,但全部发送失败,遂放弃。

2.       下班后,公司停电。服务器自动重启。

3.       第二天上班,启动apache。但网页打不开了。查看服务器,mysql未启动。手动启动mysql,失败,出现提示:空间full

维护记录

mysql数据文件地址:/var/lib/mysql   log文件地址:/var/log/mysql/

1.       新建文件/home/mysql来存放数据文件, /home/mysql-log来存放log文件。将以上两个文件的内容移动到这两个文件了,并建立链接。

2.       增大mysql的分配空间

3.        启动mysqld>mysqld  仍然出现提示:空间full

root@vtnpi:/home# df -h

Filesystem            Size  Used Avail Use% Mounted on

/dev/sda1              72G   69G  204M 100% /

varrun                248M  208K  248M   1% /var/run

varlock               248M  4.0K  248M   1% /var/lock

procbususb            248M   84K  248M   1% /proc/bus/usb

udev                  248M   84K  248M   1% /dev

devshm                248M     0  248M   0% /dev/shm

lrm                   248M   34M  214M  14% /lib/modules/2.6.20-15-386/volatile

 

发现整个磁盘都满了,不是mysql分配空间的问题。于是删除一些大文件。将以上两个文件的内容恢复到原来的文件里。

 

4.       启动mysqld,失败。根据提示,文件无法访问。

分析,因为登录一直都是使用root用户,所以前几步在来回移动文件时,改变了mysql文件的权限。

>chown –R mysql:mysql /var/lib/mysql

>chown –R mysql:mysql /var/log/mysql

 使用以上命令。修改回两个文件的归属。

5.       启动mysqld.失败。

InnoDB: The user has set SRV_FORCE_NO_LOG_REDO on

InnoDB: Skipping log redo

InnoDB: Page directory corruption: supremum not pointed to

121130 16:51:20  InnoDB: Page dump in ascii and hex (16384 bytes)

-------------------------------------------大片的000000000000000-------------------------------------------

121130 16:51:22  InnoDB: Page checksum 1575996416, prior-to-4.0.14-form checksum 1371122432

InnoDB: stored checksum 0, prior-to-4.0.14-form stored checksum 0

InnoDB: Page lsn 0 0, low 4 bytes of lsn at page end 0

InnoDB: Page number (if stored to page already) 0,

InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0

121130 16:51:22InnoDB: Error: trying to access a stray pointer 0x35ad3ff8

InnoDB: buf pool start is at 0xb5ac8000, end at 0xb62c8000

InnoDB: Probable reason is database corruption or memory

InnoDB: corruption. If this happens in an InnoDB database recovery, see

InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html

InnoDB: how to force recovery.

121130 16:51:22InnoDB: Assertion failure in thread 3083409104 in file ./../include/buf0buf.ic line 259

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.0/en/forcing-recovery.html

InnoDB: about forcing recovery.

121130 16:51:22 - mysqld got signal 11;

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.

 

key_buffer_size=0

read_buffer_size=131072

max_used_connections=0

max_connections=100

threads_connected=0

It is possible that mysqld could use up to

key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 217599 K

bytes of memory

Hope that's ok; if not, decrease some variables in the equation.

 

thd=(nil)

Attempting backtrace. You can use the following information to find out

where mysqld died. If you see no messages after this, something went

terribly wrong...

Cannot determine thread, fp=0xbfc231d8, backtrace may not be correct.

Stack range sanity check OK, backtrace follows:

0x81ea513

0x83fe83b

0x8381494

0x838b86b

0x833a50e

0x833bd04

0x8327990

0x832687a

0x82a7ca0

0x829cef2

0x81e9536

0x81ecd95

0xb7ca8ebc

0x8160581

New value of fp=(nil) failed sanity check, terminating stack trace!

Please read http://dev.mysql.com/doc/mysql/en/using-stack-trace.html and follow instructions on how to resolve the stack trace. Resolved

stack trace is much more helpful in diagnosing the problem, so please do

resolve it

The manual page at http://www.mysql.com/doc/en/Crashing.html contains

information that should help you find out what is causing the crash.

 

悲剧了,竟然是mysql的一个bug

但是因为最近一直没有备份数据库,最新的数据库备份都是两周之前的了。所以对于升级mysql这个方案慎重考虑,可能会造成不可挽回的错误。不到万不得已不可采用。

 

6.       根据网上的搜索,是因为innodb的recovery造成的。可修改my.cnf文件避免。

>root@vtnpi:/etc/mysql# vi my.cnf

增加一行:innodb_force_recovery=6

7.       启动mysqld,仍然失败。悲剧中。。。。。。。打算升级mysql

8.       下载最新版本mysql,安装前,再分析下原因,作最后努力。

9.       数据的Recovery出现以上的bug,应该是因为mysql中有了invalidunavailable的数据。所以应该从数据文件/var/lib/mysql中考虑。

(1)       将/var/lib/mysql中的文件全部移动到一个备份文件件里。

root@vtnpi:/var/lib/mysql# ls

SIPpDB                      dotproject         gallery2     mydms               scrumsmile           vacplan_development

bugzilla                    egroupware         ib_logfile0  mysql               seas                 vacplan_production

codebluereview_development  empirecms         ib_logfile1  mysql_upgrade.info  sodality             vacplan_test

commence                    eteam_development ibdata1      mysql_upgrade_info  testplan             wordpress

debian-5.0.flag             eteam_production   itlpool      opendocman          tmsrept_development  xaxiaotuan

demo_development            eteam_test         limesurvey   ptrack              tracker

(2)       启动mysqld,以上的bug提示没有了。出现新提示:

[ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist

(3)       搜索一下,网上说是因为mysql_install_db中没有使用参数datadir的问题,但我的已经使用了。

(4)       打开本来已经为空的文件夹/var/lib/mysql,发现多了三个文件:ib_logfile0,ib_logfile1,ibdata1

并且通过逐步删除原来/var/lib/mysql文件的方法,发现删除其他文件,都会出现以上的bug提示,只有在删除以上三个文件时,才不会有hit bug

(5)      损坏的文件可以锁定了:ib_logfile0,ib_logfile1,ibdata1

(6)       将之前所有备份的/var/lib/mysql里的文件恢复到这个文件里,并使用新生成的ib_logfile0,ib_logfile1,ibdata1替换原来的三个文件。

(7)       mysqld启动成功。mysql进入成功,不用多说,赶紧备份数据库。

10.   启动mysql  >etc/init.d/mysql start。失败。

root@vtnpi:~# /etc/init.d/mysql start

 * Starting MySQL database server mysqld                                                                                        [ OK ]

/usr/bin/mysqladmin: connect to server at 'localhost' failed

error: 'Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)'

 * Root password is blank.  To change it use:

 * /etc/init.d/mysql reset-password

(菜鸟我很器官,为什么启动mysql用的是user 'debian-sys-maint',这个用户名是在那设立的?mysql.user里压根就木有个debian-sys-maint,我怎么让它使用root?)

11.   搜索到网页:http://www.lsanotes.cn/mysql-error。说的很详细,可以解决这个问题,但我在update user的时候,出现失败提示:

table "user" read only

12.   使用root登录的mysql竟然没有权限,莫名其妙了。

13.   进入user的数据源文件

root@vtnpi:/var/lib/mysql/mysql# ls

columns_priv.MYD  help_category.MYD  help_topic.MYD  procs_priv.MYD   time_zone_leap_second.MYD  time_zone_transition_type.MYD

columns_priv.MYI  help_category.MYI  help_topic.MYI  procs_priv.MYI   time_zone_leap_second.MYI  time_zone_transition_type.MYI

columns_priv.frm  help_category.frm  help_topic.frm  procs_priv.frm   time_zone_leap_second.frm  time_zone_transition_type.frm

db.MYD            help_keyword.MYD   host.MYD        tables_priv.MYD  time_zone_name.MYD         user.MYD

db.MYI            help_keyword.MYI   host.MYI        tables_priv.MYI  time_zone_name.MYI         user.MYI

db.frm            help_keyword.frm   host.frm        tables_priv.frm  time_zone_name.frm         user.frm

func.MYD          help_relation.MYD  proc.MYD        time_zone.MYD    time_zone_transition.MYD

func.MYI          help_relation.MYI  proc.MYI        time_zone.MYI    time_zone_transition.MYI

func.frm          help_relation.frm  proc.frm        time_zone.frm    time_zone_transition.frm

将里面的三个user相关的文件(user.MYDuser.MYIuser.frm),权限全改为777

-rwxrwxrwx  1 mysql mysql    252 Dec  2 10:55 user.MYD

-rwxrwxrwx  1 mysql mysql   2048 Dec  2 10:55 user.MYI

-rwxrwxrwx  1 mysql mysql  10330 Dec  2 10:55 user.frm

 

14.   执行以下步骤:(如果没有用户debian-sys-maint,则添加)

root@vtnpi:/var/lib/mysql/mysql#mysqladmin -u root -p flush-tables

Enter password: 

root@vtnpi:/var/lib/mysql/mysql# cd ..

root@vtnpi:/var/lib/mysql#mysqladmin -u root -p flush-tables

Enter password: 

root@vtnpi:/var/lib/mysql# mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 37

Server version: 5.0.38-Ubuntu_0ubuntu1.3-log Ubuntu 7.04 distribution

 

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

 

mysql> update mysql.user set Password = password("rKc6wvmLwaJoZ0by") where User ="debian-sys-maint";

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

 

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

 

mysql> exit

Bye

15 启动mysql

root@vtnpi:/var/lib/mysql# /etc/init.d/mysql restart

 * Stopping MySQL database server mysqld                                                                                        [fail]

 * Starting MySQL database server mysqld                                                                                        [ OK ]

 * Root password is blank.  To change it use:

 * /etc/init.d/mysql reset-password

 

附:数据库备份文件暂放在/home/data中了

很奇怪,为什么一次断电事故,需要这么多的改动来恢复。。。。。。。。。。。。。。。。。。。



(责任编辑:IT)
------分隔线----------------------------
栏目列表
推荐内容