auto-extending data file /ibdata1 is of a different size 17152 pages (rounded down to MB)

Myth丶恋晨 2022-05-13 20:39 52阅读 0赞

**问题描述：**由于某一个mysql库经常性导致库崩溃，现在需要把该库迁移到另外一个库中，通过xtrabackup备份恢复，当恢复后无法正常启动mysql，查看日志提示如下内容：

2018-09-05 10:01:29 20972 \[Warning\] You need to use --log-bin to make --binlog-format work.  
2018-09-05 10:01:29 20972 \[Note\] Plugin 'FEDERATED' is disabled.  
2018-09-05 10:01:29 20972 \[Note\] InnoDB: Using atomics to ref count buffer pool pages  
2018-09-05 10:01:29 20972 \[Note\] InnoDB: The InnoDB memory heap is disabled  
2018-09-05 10:01:29 20972 \[Note\] InnoDB: Mutexes and rw\_locks use GCC atomic builtins  
2018-09-05 10:01:29 20972 \[Note\] InnoDB: Memory barrier is not used  
2018-09-05 10:01:29 20972 \[Note\] InnoDB: Compressed tables use zlib 1.2.3  
2018-09-05 10:01:29 20972 \[Note\] InnoDB: Using Linux native AIO  
2018-09-05 10:01:29 20972 \[Note\] InnoDB: Using CPU crc32 instructions  
2018-09-05 10:01:29 20972 \[Note\] InnoDB: Initializing buffer pool, size = 1.0G  
2018-09-05 10:01:29 20972 \[Note\] InnoDB: Completed initialization of buffer pool  
2018-09-05 10:01:29 20972 **\[ERROR\] InnoDB: auto-extending data file /tData/3307/data/ibdata1 is of a different size 17152 pages (rounded down to MB) than specified in the .cnf file: initial 65536 pages, max 0 (relevant if non-zero) pages!**  
2018-09-05 10:01:29 20972 \[ERROR\] InnoDB: Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb\_data\_file\_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful: do not remove old data files which contain your precious data!  
2018-09-05 10:01:29 20972 \[ERROR\] Plugin 'InnoDB' init function returned error.  
2018-09-05 10:01:29 20972 \[ERROR\] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.  
2018-09-05 10:01:29 20972 **\[ERROR\] Unknown/unsupported storage engine: innodb**  
2018-09-05 10:01:29 20972 **\[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