MySQL 报错收集整理

一、MySQL5.6开始提供新特性GTID模式

Warning: Using a password on the command line interface can be insecure.  
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.  

解决方法：

• dump的时候加上参数--set-gtid-purged=OFF

二、报错

• MySQL 往一张大表添加字段时报如下错误：

ERROR 1799 (HY000) at line 1: Creating index 'PRIMARY' required more than 'innodb_online_alter_log_max_size' bytes of modification log. Please try again.  

解决方法：

• 数据库为MySQL 5.5 版本innodb_online_alter_log_max_size值为默认大小128M。该参数为动态参数且全局的，可通过如下命令加大

mysql> set global innodb_online_alter_log_max_size=402653184;  
Query OK, 0 rows affected (0.03 sec)  

三、MySQL日志

InnoDB: ERROR: the age of the last checkpoint is 9434024,  
InnoDB: which exceeds the log group capacity 9433498.  
InnoDB: If you are using big BLOB or TEXT rows, you must set the  
InnoDB: combined size of log files at least 10 times bigger than the  
InnoDB: largest such row.  

解决办法

• Innodb引擎下日志大小设置过小导致的，某个事物产生大量日志，但innodblogfile_size设置过小，可以加大解决。

四、mysqldump出现如下错误

mysqldump: Got error: 1044: Access denied for user 'dbback'@'IP' to database 'game_db' when doing LOCK TABLES  

解决办法

• 加上–skip-lock-tables即可

五、Waiting for table level lock 参考文档

• 查看哪些线程正在运行

SHOW PROCESSLIST;  

• 暂时解决方案

SELECT  
 a.*,CONCAT("kill " ,a.id,";") 
FROM  
 information_schema.`PROCESSLIST` a
WHERE  
 a.STATE = 'Waiting for table level lock'

• kill死进程

kill ${进程ID}  

• 过渡解决方案

Yes, you can change your storage engine from MyISAM to InnoDB - MyISAM only knows table level locking (when it writes to a record, it blocks the whole table), while InnoDB knows row level locking (it locks just the row you are writing to)  

• 终极解决方案

做数据库读写分离，针对读和写 设置不同的数据库引擎