MySQLのIbdata1异常及恢复

MySQLのIbdata1异常及恢复

环境:CentOS5.4/MySQL-5.1.48
1.将优化后的my.cnf替换旧的my.cnf,导致MySQLd无法启动
err文件提示如下:
InnoDB: Error: auto-extending data file /data/mysqldata/innodb/data/ibdata1 is of a different size
InnoDB: 65536 pages (rounded down to MB) than specified in the .cnf file:
InnoDB: initial 327680 pages, max 0 (relevant if non-zero) pages!
131205  9:55:36 InnoDB: Could not open or create data files.
131205  9:55:36 InnoDB: If you tried to add new data files, and it failed here,
131205  9:55:36 InnoDB: you should now edit innodb_data_file_path in my.cnf back
131205  9:55:36 InnoDB: to what it was, and remove the new ibdata files InnoDB created
131205  9:55:36 InnoDB: in this failed attempt. InnoDB only wrote those files full of
131205  9:55:36 InnoDB: zeros, but did not yet use them in any way. But be careful: do not
131205  9:55:36 InnoDB: remove old data files which contain your precious data!
131205  9:55:36 [ERROR] Plugin 'InnoDB' init function returned error.
131205  9:55:36 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
131205  9:55:36 [ERROR] Unknown/unsupported storage engine: InnoDB
131205  9:55:36 [ERROR] Aborting
从Error看,表空间ibdata1跑在旧的my.cnf下大小65536/64M(64个page=1M,1个page=16k)比现在my.cnf文件中配置的
ibdata1值要小,导致数据文件无法打开，同时InnoDB存储引擎加载失败;
解决方式:
1.删除现在ibdata1数据文件,引起另外麻烦,mysqld启动了，但是所有InnoDB表报废，select时提示该表不存在---慎用；
2.注释该设置的参数(InnoDB_data_file_path),MySQLd启用默认设置ibdata1值大小;
3.ibdata1大小扩展方式,网上搜索一把,查询是ibdata1如何瘦身,涉及参数:InnoDB_data_file_path;

2.ibdata1如何扩展?
如：
#Ibdata1存放路径,若不设置,默认存放在datadir下
InnoDB_data_home_dir = /opt/mysql/data  
innodb_data_file_path = ibdata1:10M:autoextend
若直接将此参数值修改比10M大的值，启动mysqld,提示上述错误，即使MySQLd可以启动，InnoDB引擎初始化失败,涉及InnoDB
表无法使用;
假设ibdata1所在磁盘空间剩余大小1G,过段时间该数据文件(ibdata1)长到988MB,那么就必须增加一个新的数据文件，来保证有
富裕的表空间,增加如下:
1.innodb_data_file_path = ibdata1:988M:autoextend #这种方式导致InnoDB引擎无法正常加载
2.innodb_data_file_path = ibdata1:10M;ibdata2:998M:autoextend
MySQLd启动时，创建新的数据文件:ibdata2 
err记录如下:
131203 18:19:36  InnoDB: Data file /opt/mysql/data2/ibdata2 did not exist: new to be created
131203 18:19:36  InnoDB: Setting file /opt/mysql/data2/ibdata2 size to 988 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900
131203 18:19:41  InnoDB: Started; log sequence number 0 48574


3.ibdata1如何瘦身?
方法:
1.dump出所有DB
2.正常关闭MySQLd
3.rm ibdata1 ib_ib_logfile* mysql-bin.index
4.配置新表空间InnoDB_data_home_dir/InnoDB_data_file_path
4.启动MySQLd
5.导入dump文件，完成ibdata1瘦身;

-------

4.若误删除MySQLInnoDB相关的数据文件ibdata1,日志文件ib_logfile*如何恢复?
观察其跑的系统环境,发现一切正常,数据的读取与操作完全正常,原因:mysqld在
运行状态中，会保持这些文件为打开状态；
即使被删除了，它们仍旧存在于文件系统中，mysqld仍然可以对其进行读写
*****************************
务必这会不要关闭或重启mysqld
*****************************
查询pid-file运行目录:
ps -ef | grep mysql
mysql     5839  5696  0 18:21 pts/0    00:00:00 /opt/mysql/libexec/mysqld 
--defaults-file=/opt/mysql/data2/my3307.cnf 
--basedir=/opt/mysql --datadir=/opt/mysql/data2 
--user=mysql 
--log-error=/opt/mysql/data2/node3307.err 
--pid-file=/opt/mysql/data2/node3307.pid 
--socket=/opt/mysql/data2/mysql3307.sock 
--port=3307
获取mysqld的pid(进程ID)
cat /opt/mysql/data2/node3307.pid 
5839
[root@node proc]# ls -la /proc/5839/fd/ | grep -e ibdata -e ib_
lrwx------ 1 root  root  64 Dec  3 19:08 10 -> /opt/mysql/data2/ib_logfile1(deleted)
lrwx------ 1 root  root  64 Dec  3 19:08 4 -> /opt/mysql/data2/ibdata1(deleted)
lrwx------ 1 root  root  64 Dec  3 19:08 9 -> /opt/mysql/data2/ib_logfile0(deleted)
只要mysqld不结束,就可以通过proc文件系统找到这几个被删除的文件(已经被标示为deleted状态)
那么是否只要把这几个文件复制回/opt/mysql/data2就可以了吗？--No
因为，在innodb的buffer pool中，有许多dirty page(内存中数据已经被修改，与磁盘中数据不一致，但是没有
写回到文件中).
如果直接复制回去，可能数据丢失，或ibdata1文件损坏。

备份mysql数据时，也不同直接备份这几个文件，也是同样的道理.

接下来需要做的事：

这会我们要做的事，就是保证所有buffer pool中的数据修改都保存在磁盘文件上面.
为此，首先要停止更多的写入、更新、删除操作,而后等待innodb flush pages to disk.
停止写入，可以把跑于该DB系统关闭对外服务.或lock tables;
mysql>flush tables with read lock;
这时等待它flush结束，怎样知道结束没有？观看checkpoint age变化.
mysql>show engine innodb status \G;
--- 
LOG 
--- 
Log SEQUENCE NUMBER 363096003 
Log flushed up TO 363096003 
LAST checkpoint at 363096003
--checkpoint age:Log SEQUENCE NUMBER减去LAST checkpoint at的值
若为0，那么所有page都flush到磁盘文件中了。
不过为了加速这个flush过程,可以设置:
mysql>set global innodb_max_dirty_pages_pct=0;
此外，必须保证一些后台的线程完成了它们的工作,
如，insert buffer thread.ibuf的大小应=1
------------------------------------- 
INSERT BUFFER AND ADAPTIVE HASH INDEX 
------------------------------------- 
Ibuf: SIZE 1, free list len 398, seg SIZE 400,

还有purge thread，应purge了全部transactions:
------------ 
TRANSACTIONS 
------------ 
Trx id counter 0 16644 
Purge done FOR trx's n:o < 0 16644 undo n:o < 0 0
还要确保innodb IO不再写操作:
FILE I/O 
-------- 
I/O thread 0 state: waiting FOR i/o request (INSERT buffer thread) 
I/O thread 1 state: waiting FOR i/o request (log thread) 
I/O thread 2 state: waiting FOR i/o request (READ thread)
 I/O thread 3 state: waiting FOR i/o request (WRITE thread) 
Pending normal aio reads: 0, aio writes: 0, 
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
332 OS file reads, 47 OS file writes, 32 OS fsyncs
0.00 reads/s, 0 avg bytes/READ, 0.00 writes/s, 0.00 fsyncs/s
上面都完成后，把文件复制回去：
[root@node proc]# cp /proc/5839/fd/10 /opt/mysql/data2/ib_logfile1
[root@node proc]# cp /proc/5839/fd/4 /opt/mysql/data2/ibdata1
[root@node proc]# cp /proc/5839/fd/9 /opt/mysql/data2/ib_logfile0
注意修改文件权限:
chown -R mysql:mysql ibdata1 ib_logfile*  重启mysql

总结:
1,解决方案不明确时，不要进行操作，如重启Mysqld，重启服务器
2,有必要监控mysql的ibdata，ib_logfile*等文件存在
-----------------

5.InnoDB涉及共享表空间和独立表空间优/缺点，之后再了解?

附上:第一次开启MySQLd，*.err文件记录初始化信息:
如下一段信息是MySQL5.1.48在scripts/mysql_install_db后,执行bin/mysqld_safe生成内容:
131203 16:39:55 mysqld_safe Starting mysqld daemon with databases from /opt/mysql/data2
131203 16:39:55 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please us
e '--skip-external-locking' instead.
InnoDB: The first specified data file /opt/mysql/data2/ibdata1 did not exist:
InnoDB: a new database to be created!
131203 16:39:55  InnoDB: Setting file /opt/mysql/data2/ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
131203 16:39:56  InnoDB: Log file /opt/mysql/data2/ib_logfile0 did not exist: new to be created
InnoDB: Setting log file /opt/mysql/data2/ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
131203 16:39:56  InnoDB: Log file /opt/mysql/data2/ib_logfile1 did not exist: new to be created
InnoDB: Setting log file /opt/mysql/data2/ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
131203 16:39:56  InnoDB: Started; log sequence number 0 0
131203 16:39:56 [Note] Event Scheduler: Loaded 0 events
...ommited...
......
131203 16:39:56 [Note] /opt/mysql/libexec/mysqld: ready for connections.
Version: '5.1.48-log'  socket: '/opt/mysql/data2/mysql3307.sock'  port: 3307  Source distribution