1

I restarted mysqld this morning, to increase the size of the innodb logs and increase the amount of memory allocated to the innodb buffer pool. (MySQL 5.1.53-Community on Ubuntu)

To increase the size of the innodb logs, I modified the my.cnf file, stopped mysqld, renamed the existing innodb log files, and then started mysqld again.

     root@mysql01:# /etc/init.d/mysql.server stop
     Shutting down MySQL
     ............ *
     root@mysql01:# ls -al ib_logfile*
     -rw-rw----  1 mysql mysql  251658240 2013-02-08 08:21 ib_logfile0
     -rw-rw----  1 mysql mysql  251658240 2013-02-08 08:21 ib_logfile1
     root@mysql01:# mv ib_logfile0 ib_logfile0.OLD
     root@mysql01:# mv ib_logfile1 ib_logfile1.OLD
     root@mysql01:# /etc/init.d/mysql.server start
     Starting MySQL
     ................................ *
     root@mysql01:#

The mysql01-err.log shows

130208  8:21:27 [Note] /opt/mysql/bin/mysqld: Normal shutdown

130208  8:21:27 [Note] Event Scheduler: Purging the queue. 0 events
130208  8:21:30  InnoDB: Starting shutdown...
130208  8:21:39  InnoDB: Shutdown completed; log sequence number 661 1658279774
130208  8:21:39 [Note] /opt/mysql/bin/mysqld: Shutdown complete

130208 08:21:39 mysqld_safe mysqld from pid file /opt/mysql_data/mysql01.pid ended
130208 08:22:17 mysqld_safe Starting mysqld daemon with databases from /opt/mysql_data
130208  8:22:17 [Note] Plugin 'FEDERATED' is disabled.
130208  8:22:18  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 1536 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500
130208  8:22:29  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 1536 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
130208  8:22:37  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Last MySQL binlog file position 0 886154568, file name ./mysql-bin.001636
130208  8:22:48  InnoDB: Started; log sequence number 661 1658279948
130208  8:22:48 [Note] Event Scheduler: Loaded 0 events
130208  8:22:48 [Note] /opt/mysql/bin/mysqld: ready for connections.
Version: '5.1.53-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)

I'm concerned with the line, after the new innodb log files are written, that says

130208  8:22:37  InnoDB: Database was not shut down normally!

My questions are:

Is that line expected in the log file, is this the behavior I should expect? Does this indicate a problem, or is this a normal result from creating new log files?

Is there anything in the tail of the mysql01-err.log that indicates that there was a real problem?

Was the InnoDB "crash recovery" successful? Or is there a chance that something is screwed up?

Are there any additional steps that I can take to verify that everything is fine with InnoDB, and that the steps I performed did not cause any corruption?

(I'd rather know about any problems now. It would be much better to do any additional steps to get a full recovery, sooner, rather than later.)

Improve this question

1 Answer 1

Reset to default
1

It is sort of yes and no. Why would I say both?

There are still some transactional data embedded in the old ib_logfiles in conjunction with the ibdata1 file. (See pictorial representation)

What you should have done is this:

mysql -u... -p... -ANe"SET GLOBAL innodb_fast_shutdown = 0"
/etc/init.d/mysql.server stop
mv ib_logfile0 ib_logfile0.OLD
mv ib_logfile0 ib_logfile0.OLD
/etc/init.d/mysql.server start

If you did not know of disabling innodb_fast_shutdown, ther you steps should have been

/etc/init.d/mysql.server stop
/etc/init.d/mysql.server start
/etc/init.d/mysql.server stop
mv ib_logfile0 ib_logfile0.OLD
mv ib_logfile0 ib_logfile0.OLD
/etc/init.d/mysql.server start

Doing either of these coordinate the purging of all uncommitted writes to tables and indexes. Just looking at the error log, I think you dodged a bullet in this instance. In future, please follow this protocol.

If you are not sure either way, you could put back the old log files, set the old size of innodb_log_file_size, restart mysql. Then, start the process over again as I prescribed above.

Improve this answer
2
  • Thank you for the information. The MySQL 5.1 documentation (dev.mysql.com/doc/refman/5.1/en/…) specifies that innodb_fast_shutdown should be changed from a value of 2 to a value of 1 before this procedure (before the shutdown). And that makes sense. In my case, the value was already set to 1, so I followed the procedure. The MySQL 5.1 documentation makes no mention that a value of 0 would be preferred over a value of 1. Thank you for the help.
    – spencer7593
    Feb 8, 2013 at 19:47
  • You are welcome. I prefer zero(0). It is the slowest, but it will completely purge all needed data from the logs.
    – RolandoMySQLDBA
    Feb 8, 2013 at 19:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.