mysql slave error：You cannot ‘ALTER’ a log table
Sep142016
mysql执行了mysql_upgrade后从库同步报错，’You cannot ‘ALTER’ a log table if logging is enabled’ on query. 报错显示跟mysql的slow_query log(慢日志)相关：
具体错误信息如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
mysql> SHOW SLAVE STATUS\G Last_SQL_Errno: 1580 Last_SQL_Error: Error 'You cannot 'ALTER' a log table if logging is enabled' on query. Default database: 'mysql'. Query: 'ALTER TABLE slow_log MODIFY start_time TIMESTAMP NOT NULL, MODIFY user_host MEDIUMTEXT NOT NULL, MODIFY query_time TIME NOT NULL, MODIFY lock_time TIME NOT NULL, MODIFY rows_sent INTEGER NOT NULL, MODIFY rows_examined INTEGER NOT NULL, MODIFY db VARCHAR(512) NOT NULL, MODIFY last_insert_id INTEGER NOT NULL, MODIFY insert_id INTEGER NOT NULL, MODIFY server_id INTEGER UNSIGNED NOT NULL, MODIFY sql_text MEDIUMTEXT NOT NULL' Replicate_Ignore_Server_Ids: Master_Server_Id: 6512 1 row in set (0.00 sec) |
修复方案：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
#停掉slave STOP SLAVE; #Disable the relevant logs (don't copy+paste this but check your own configuration first!): SET GLOBAL slow_query_log = 'OFF'; #启动slave START SLAVE; #检查slave 状态 SHOW SLAVE STATUS\G #set slow_query_log on and enable the relevant logs again: SET GLOBAL slow_query_log = 'ON'; |
现在slave已经可以同步了。
本文固定链接: https://www.sudops.com/mysql-slave-error-cannot-alter-log-table.html | 运维·速度
mysql slave error：You cannot ‘ALTER’ a log table：等您坐沙发呢！
发表评论
欢迎关注 运维·速度 微信公众号
