急急急！导入大表过程中本本电池耗完自动关机，重启机器mysqld无法启动。。

木万万

急急急！导入大表过程中本本电池耗完自动关机，重启机器mysqld无法启动。。

1. sudo /etc/init.d/mysqld start

复制代码

1. Starting MySQL.....Manager of pid-file quit without updatin[FAILED]

复制代码

1. sudo /etc/init.d/mysqld status

复制代码

1. MySQL is not running, but lock exists                      [FAILED]

复制代码

日志信息：

1. 120321 01:18:41 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
   
2. 120321  1:18:41 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
   
3. 120321  1:18:41 [Warning] options --log-slow-admin-statements, --log-queries-not-using-indexes and --log-slow-slave-statements have no effect if --log_slow_queries is not set
   
4. 120321  1:18:41 [Note] Plugin 'FEDERATED' is disabled.
   
5. 120321  1:18:41  InnoDB: Initializing buffer pool, size = 2.0G
   
6. 120321  1:18:41  InnoDB: Completed initialization of buffer pool
   
7. InnoDB: Log scan progressed past the checkpoint lsn 16 3634558071
   
8. 120321  1:18:42  InnoDB: Database was not shut down normally!
   
9. InnoDB: Starting crash recovery.
   
10. InnoDB: Reading tablespace information from the .ibd files...
    
11. InnoDB: Restoring possible half-written data pages from the doublewrite
    
12. InnoDB: buffer...
    
13. InnoDB: Doing recovery: scanned up to log sequence number 16 3639800832
    
14. InnoDB: Doing recovery: scanned up to log sequence number 16 3640983625
    
15. InnoDB: 1 transaction(s) which must be rolled back or cleaned up
    
16. InnoDB: in total 50083976 row operations to undo
    
17. InnoDB: Trx id counter is 0 4352
    
18. 120321  1:18:43  InnoDB: Starting an apply batch of log records to the database...
    
19. InnoDB: Progress in percents: 0 1 2 InnoDB: Database page corruption on disk or a failed
    
20. InnoDB: file read of page 16037.
    
21. InnoDB: You may have to recover from a backup.
    
22. 120321  1:18:45  InnoDB: Page dump in ascii and hex (16384 bytes):
    
23. len 16384; hex ffa1794000003ea5000536d00004c81700000010d8c3eacb45bf000000000000000000000025007b3e1883a2159b1d0515680002002801eb0000000000000f610000000000000000007f0000000000000000000...

复制代码

怎么办？？以前发生这种情况，mysql会自动同步的，现在要怎么做才能让服务启动进行同步呀？？

love100

把innodb_force_recovery=6放入到my.cnf中，然后重启mysqld
正常启动后再把innodb_force_recovery=6删除掉
然后再启动，最后重新导入数据

木万万

love100 发表于 2012-03-20 18:34
把innodb_force_recovery=6放入到my.cnf中，然后重启mysqld
正常启动后再把innodb_force_recovery=6删除掉 ...

大拿，你好，我照 你说的方法去做了。但我不知道为什么，配置文件当中添加innodb_force_recovery=6后，服务可正常启动，当我把这句话删掉之后再重启服务就会提示

1. ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

复制代码

当我再次加上那一行之后就又可以起作用了。。
我能不能在加上innodb_force_recovery=6这句话的情况下，启动mysql，然后导入，导入后再删除呢？？

love100

木万万 发表于 2012-03-20 20:39
大拿，你好，我照 你说的方法去做了。但我不知道为什么，配置文件当中添加innodb_force_recovery=6后，服 ...

去掉innodb_force_recovery=6后，启动不起来的log发出来看看

配置文件当中添加innodb_force_recovery=6后，可以把有问题的table进行drop掉，但不能进行insert,delete,update操作

木万万

love100 发表于 2012-03-20 20:46
去掉innodb_force_recovery=6后，启动不起来的log发出来看看

配置文件当中添加innodb_force_recovery= ...

刚才着急，忘了看日志。日志竟然一直是

1. InnoDB: Your database may be corrupt or you may have copied the InnoDB
   
2. InnoDB: tablespace but not the InnoDB log files. See
   
3. InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html
   
4. InnoDB: for more information.
   
5. 120321  5:10:34  InnoDB: Error: page 52 log sequence number 14 1323463206
   
6. InnoDB: is in the future! Current system log sequence number 0 8204.
   
7. InnoDB: Your database may be corrupt or you may have copied the InnoDB
   
8. InnoDB: tablespace but not the InnoDB log files. See
   
9. InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html
   
10. InnoDB: for more information.
    
11. 120321  5:10:34  InnoDB: Error: page 53 log sequence number 14 1323723003
    
12. InnoDB: is in the future! Current system log sequence number 0 8204.
    

复制代码

这段错误日志在重复。。

木万万

love100 发表于 2012-03-20 20:46
去掉innodb_force_recovery=6后，启动不起来的log发出来看看

配置文件当中添加innodb_force_recovery= ...

但如果配置文件加上那句话的话日志就正常了

1. 120321 05:14:41 mysqld_safe mysqld from pid file /var/lib/mysql/db.local.pid ended
   
2. 120321 05:14:41 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
   
3. 120321  5:14:41 [Warning] options --log-slow-admin-statements, --log-queries-not-using-indexes and --log-slow-slave-statements have no effect if --log_slow_queries is not set
   
4. 120321  5:14:41 [Note] Plugin 'FEDERATED' is disabled.
   
5. 120321  5:14:41  InnoDB: Initializing buffer pool, size = 2.0G
   
6. 120321  5:14:42  InnoDB: Completed initialization of buffer pool
   
7. InnoDB: The user has set SRV_FORCE_NO_LOG_REDO on
   
8. InnoDB: Skipping log redo
   
9. 120321  5:14:42  InnoDB: Started; log sequence number 0 0
   
10. InnoDB: !!! innodb_force_recovery is set to 6 !!!
    
11. 120321  5:14:42 [Note] Event Scheduler: Loaded 0 events
    
12. 120321  5:14:42 [Note] /usr/sbin/mysqld: ready for connections.
    
13. Version: '5.1.61'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
    

复制代码

love100

木万万 发表于 2012-03-20 21:12
刚才着急，忘了看日志。日志竟然一直是这段错误日志在重复。。

知道了
1.配置文件当中添加innodb_force_recovery=6后，把有用的数据dump出来
2.可以把有问题的table进行drop掉，停止mysql,删除ibdata*,ib_logfile*
3.去掉 innodb_force_recovery=6启动mysql，导入你需要的数据。

木万万

love100 发表于 2012-03-20 21:16
知道了
1.配置文件当中添加innodb_force_recovery=6后，把有用的数据dump出来
2.可以把有问题的table ...

好的。我刚试了一下，再加innodb_force_recovery=6情况下，可以show tables;

1. mysql> show tables;
   
2. ERROR 2006 (HY000): MySQL server has gone away
   
3. No connection. Trying to reconnect...
   
4. Connection id:    1
   
5. Current database: vprod
   
6. 
   
7. +---------------------+
   
8. | Tables_in_vprod     |
   
9. +---------------------+
   
10. | actions             |
    
11. | building_types      |
    
12. | categories          |
    
13. | cities              |
    
14. | city_ips            |
    
15. | citylists           |
    
16. | comments            |
    
17. | communities         |
    
18. | community_types     |
    
19. | companies           |
    
20. | cores               |
    
21. | developers          |
    
22. | districts           |
    
23. | favlists            |
    
24. | flags               |
    
25. | interiors           |
    
26. | managements         |
    
27. | oauth_users         |
    
28. | pages               |
    
29. | photos              |
    
30. | posts               |
    
31. | properties          |
    
32. | provinces           |
    

复制代码

但是drop table的话，

1. mysql> drop table stats;
   
2. ERROR 2006 (HY000): MySQL server has gone away
   
3. No connection. Trying to reconnect...
   
4. Connection id:    1
   
5. Current database: vprod
   
6. 
   
7. ERROR 2013 (HY000): Lost connection to MySQL server during query
   

复制代码

老是断开连接，没办法删除。。

liu-shaobo

ibdata1 文件删除看看，删除前记得备份

hunter_search

从日志上看，楼主开启了doublewrite ,数据也不会丢失,数据库应该可以启动起来的，只不过要回滚50083976 row, 这个时间超级长,