I created a Database, a table and inserted some data, and found this binlog.0000001 in my log folder, but when I do mysqlbinlog binlog.0000001, it only shows stuff below, seems incomplete: (There's only two files in the log dir: binlog.000001 binlog.index)

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
# at 4
#120924 21:12:56 server id 1  end_log_pos 107   Start: binlog v 4, server v 5.5.24-0ubuntu0.12.04.1-log created 120924 21:12:56 at startup
# Warning: this binlog is either in use or was not closed properly.
# End of log file
ROLLBACK /* added by mysqlbinlog */;

If this warning was the cause: Warning: this binlog is either in use or was not closed properly..

How do I force close the log?


After flush logs command, I see "0 rows" affected, and a few new files, binlog.000001 binlog.000002 binlog.000003 binlog.000004 binlog.index, the contents are nearly the same as binlog.000001.

Now I dropped the database, and try restore it with mysqlbinlog binlog.0* | mysql -u root -p, but the database wasn't recovered.


user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql


bind-address        =
key_buffer      = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
query_cache_limit   = 1M
query_cache_size        = 16M
expire_logs_days    = 10
max_binlog_size         = 100M

P.S /var/log/mysql{.err,.log} are both empty

  • The FLUSH LOGS command will flush any remaining data, close the current log file and open a new log file. – EEAA Sep 25 '12 at 1:29
  • @ErikA doesn't seem to work, maybe I've missed something – daisy Sep 25 '12 at 1:49
  • Are you sure you're looking in the right place for your binlogs? – EEAA Sep 25 '12 at 1:49
  • Do you have any binlog-do-db or binlog-ignore-db statements in your my.cnf? Actually, could you just post the [mysqld] portion of that file? – EEAA Sep 25 '12 at 1:51
  • @ErikA right, the name differ, I specified 'mydb', but didn't see anything prefixed with that – daisy Sep 25 '12 at 1:54

@ErikA right, the name differ, I specified 'mydb', but didn't see anything prefixed with that

Which binlog format are you using: statement-based or row-based?

  • Statement-based logging: only statements which has default database is mydb are written to binary log.
  • Row-based logging: only changes to table belonging to mydb are logged, the default database has no effect.

Take a look at this for more details.

P.S /var/log/mysql{.err,.log} are both empty

Specify a log-error option under the [mysqld] section, then restart MySQL and try again.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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