Mysql 维护记录(mysql crash)
来源:互联网 发布:办公软件排名 编辑:程序博客网 时间:2024/05/19 09:38
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中有了invalid或unavailable的数据。所以应该从数据文件/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.MYD,user.MYI,user.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中了
很奇怪,为什么一次断电事故,需要这么多的改动来恢复。。。。。。。。。。。。。。。。。。。
- Mysql 维护记录(mysql crash)
- mysql维护记录
- linux mysql安装维护记录
- centos下mysql维护记录
- MySQL主从复制(维护)
- MySQL Crash Course
- MySQL crash 日志
- MySQL crash-safe replication
- Latch导致MySQL Crash
- MySQL常用维护管理工具(五款)
- MySQL笔记(索引和表维护)
- MYSQL服务维护笔记
- MYSQL服务维护笔记
- MySQL服务维护笔记
- MYSQL服务维护
- MySQL服务维护笔记
- MYSQL日志文件维护
- Mysql的维护语句
- How to reset the root passwd if we forget the root password in red hat
- ZOJ2770-Burn the Linked Camp(差分约束系统)
- oracle数据泵导入/导出语法
- XP系统开启AHCI硬盘工作模式(解决DELL蓝屏问题0X0000007B)
- HBTC2012 Hadoop与大数据技术大会,感受
- Mysql 维护记录(mysql crash)
- AT&T 汇编语言学习笔记二
- printf输出格式
- Java乔晓松-文件的上传和下载(Web) 2
- QueryPerformanceFrequency,RDTSC,CPU频率
- HBase 0.1.0 数据存储基本结构详解
- zju 2212优先队列
- 取整数绝对值的宏定义
- sendmail 维护记录