xtrabackup恢复数据后，MySQL无法启动问题处理
3,402 total views, 2 views today
应审计需要，需将MySQL数据恢复到14天前。
公司MySQL采用xtrabackup方式备份。使用xtrabackup恢复MySQL后，无法启动MySQL，报错如下
|
1 |
Starting MySQL....The server quit without updating PID file[失败]a/mysql/data/SL010A-IVAMD1.pid). |
查看MySQL错误日志
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
2017-03-15 09:08:01 23485 [Note] InnoDB: Memory barrier is not used 2017-03-15 09:08:01 23485 [Note] InnoDB: Compressed tables use zlib 1.2.3 2017-03-15 09:08:01 23485 [Note] InnoDB: Using Linux native AIO 2017-03-15 09:08:01 23485 [Note] InnoDB: Using CPU crc32 instructions 2017-03-15 09:08:01 23485 [Note] InnoDB: Initializing buffer pool, size = 6.0G 2017-03-15 09:08:01 23485 [Note] InnoDB: Completed initialization of buffer pool 2017-03-15 09:08:01 23485 [ERROR] InnoDB: ./ibdata1 can't be opened in read-write mode 2017-03-15 09:08:01 23485 [ERROR] InnoDB: The system tablespace must be writable! 2017-03-15 09:08:01 23485 [ERROR] Plugin 'InnoDB' init function returned error. 2017-03-15 09:08:01 23485 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. ^G/data/mysql/bin/mysqld: File '/mysql/log/slow.log' not found (Errcode: 2 - No such file or directory) 2017-03-15 09:08:01 23485 [ERROR] Could not open /mysql/log/slow.log for logging (error 2). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown t he MySQL server and restart it. 2017-03-15 09:08:01 23485 [ERROR] Unknown/unsupported storage engine: InnoDB 2017-03-15 09:08:01 23485 [ERROR] Aborting |
解决办法：
由于执行xtrabackup使用root用户，恢复处的文件属组、属主都是root，将其修改为mysql即可，chown mysql.mysql *
再次启动，错误日志中依然有错误，显示数据损坏。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
InnoDB: Last MySQL binlog file position 0 458112111, file name mysql-bin.000958 2017-03-15 09:37:06 26872 [Note] InnoDB: 128 rollback segment(s) are active. 2017-03-15 09:37:06 26872 [Note] InnoDB: Waiting for purge to start 2017-03-15 09:37:06 7f6b697a4720 InnoDB: Error: tablespace size stored in header is 131712 pages, but 2017-03-15 09:37:06 7f6b697a4720 InnoDB: the sum of data file sizes is only 131072 pages 2017-03-15 09:37:06 7f6b697a4720 InnoDB: Cannot start InnoDB. The tail of the system tablespace is 2017-03-15 09:37:06 7f6b697a4720 InnoDB: missing. Have you edited innodb_data_file_path in my.cnf in an 2017-03-15 09:37:06 7f6b697a4720 InnoDB: inappropriate way, removing ibdata files from there? 2017-03-15 09:37:06 7f6b697a4720 InnoDB: You can set innodb_force_recovery=1 in my.cnf to force 2017-03-15 09:37:06 7f6b697a4720 InnoDB: a startup if you are trying to recover a badly corrupt database. 2017-03-15 09:37:06 26872 [ERROR] Plugin 'InnoDB' init function returned error. 2017-03-15 09:37:06 26872 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 2017-03-15 09:37:06 26872 [ERROR] Unknown/unsupported storage engine: InnoDB 2017-03-15 09:37:06 26872 [ERROR] Aborting |
解决办法：
|
1 2 |
[mysqld] innodb_force_recovery=1 |
再次启动，成功！
遇到MySQL无法启动问题，切记一定要查看MySQL错误日志。你遇到的问题可能与我不同，但是通过错误日志能够快速定位问题并解决。