Reputation: 407
How to Sync mySQL Logs
I recently experienced a DDoS attack. It overwhelmed and crashed my server. Upon restart all of the innodb tables were corrupted in every database on the server.
I have since rebuilt the databases and all of the tables. I had to recreate innodb_table_stats, innodb_index_stats.
Everything now seems to be running fine, the website is up but I have one persistent error that keeps coming up. The general log is filling with these quite rapidly.
2014-07-13 15:28:37 7fd70b374700 InnoDB: Error: page 193 log sequence number 526819726
InnoDB: is in the future! Current system log sequence number 156433332.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: for more information.
I tried Changing the Number and Size of mySQL logs. It all went fine with no errors. But I am still seeing these errors mount up in the new log.
What else can I try to sync up these log sequence numbers? I am pretty new to this backend database work. Being forced to learn since my hosts tech support sucks.
I am currently on: CENTOS 6.5 x86_64 virtuozzo – vps- WHM 11.44.0 (build 22) mySQL: 5.6.17
Upvotes: 0
Views: 702
Answers (1)
Reputation: 2258
"page ... log sequence number" is when the page was last modified. The LSN in the header is larger than one in the redo log.
The easiest way to fix it is to rebuild the table with noop ALTER TABLE.
ALTER TABLE mytable ENGINE InnoDB;
It will rebuild PRIMARY index as well as its secondary indexes. After that the error should go away.
The ALTER is going to block the table, so if it's large and if the site is in production the best option is to rebuild it with pt-online-schema-change. It will do the same, but won't block the table but for brief moment.
pt-online-schema-change --alter "ENGINE=InnoDB" D=sakila,t=actor
Upvotes: 1