mysqldump: Got error: 1168 differently defined non-MyISAM LOCK TABLES

mysqldump: Got error: 1168: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist when using LOCK TABLES

备份其他的数据库可以成功。但是备份下面这个数据库starive就失败了。

备份数据库starive:
sudo mysqldump -uroot -p密码 -l starive > /home/***/mysql/backup/starive.sql

出现了如下错误:
mysqldump: Got error: 1168: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist when using LOCK TABLES

查看错误日志 /usr/local/mysql/data/***-VirtualBox.err, 最新的内容如下：
141015 11:15:34 [Note] Plugin 'FEDERATED' is disabled.
141015 11:15:34 InnoDB: The InnoDB memory heap is disabled
141015 11:15:34 InnoDB: Mutexes and rw_locks use InnoDB's own implementation
141015 11:15:34 InnoDB: Compressed tables use zlib 1.2.3
141015 11:15:34 InnoDB: Using Linux native AIO
141015 11:15:34 InnoDB: Initializing buffer pool, size = 128.0M
141015 11:15:35 InnoDB: Completed initialization of buffer pool
141015 11:15:35 InnoDB: highest supported file format is Barracuda.
141015 11:15:36  InnoDB: Waiting for the background threads to start
141015 11:15:37 InnoDB: 5.5.39 started; log sequence number 44666057
141015 11:15:37 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
141015 11:15:37 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
141015 11:15:37 [Note] Server socket created on IP: '0.0.0.0'.
141015 11:15:37 [Note] Event Scheduler: Loaded 1 event
141015 11:15:37 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.5.39-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Se
rver (GPL)

解决方法及其过程：

把原来的starive2.sql 删除掉，然后再来备份：

sudo mysqldump -uroot -p*** starive2 > /home/*****/mysql/backup/starive2.sql
成功了。

 

 

重新启动Ubuntu：

参考书籍《深入浅出MySQL 数据库开发、优化与管理维护（第2版）》 P 438: 加入 --single-transaction       ----》加这个参数后立马找到原因了

*****@*****-VirtualBox:/usr/local/mysql/bin$ sudo mysqldump -uroot -pgao --single-transaction starive > /home/*****/mysql/backup/starive.sql

[sudo] password for *****: 

mysqldump: Couldn't execute 'show create table `payment_all`': Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist (1168)

 

去数据库starive查询表：payment_all, 果然没有这个表。

那就把这个表删除掉：

1. drop table payment_all;

*****@*****-VirtualBox:/usr/local/mysql/bin$ sudo mysqldump -uroot -p*** --single-transaction starive > /home/*****/mysql/backup/starive.sql*****@*****-VirtualBox:/usr/local/mysql/bin$ 

成功了！！！

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26435490/viewspace-1299449/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26435490/viewspace-1299449/