当前位置: 首页 > linux, 数据库 > 正文

mysql slave error:You cannot ‘ALTER’ a log table

mysql执行了mysql_upgrade后从库同步报错,’You cannot ‘ALTER’ a log table if logging is enabled’ on query. 报错显示跟mysql的slow_query log(慢日志)相关:


具体错误信息如下:

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)

修复方案:

#停掉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://sudops.com/mysql-slave-error-cannot-alter-log-table.html | 运维速度

该日志由 u2 于2016年09月14日发表在 linux, 数据库 分类下,
原创文章转载请注明: mysql slave error:You cannot ‘ALTER’ a log table | 运维速度
关键字:

报歉!评论已关闭.