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, 果然没有这个表。
那就把这个表删除掉：
- 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博客 ” ，链接：https://blog.itpub.net/26435490/viewspace-1299449/ ，如需转载，请注明出处，否则将追究法律责任。
