0

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*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# 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.
ROLLBACK/*!*/;
BINLOG '
GAVhUA8BAAAAZwAAAGsAAAABAAQANS41LjI0LTB1YnVudHUwLjEyLjA0LjEtbG9nAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAYBWFQEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

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?

EDIT

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.

EDIT 2

[mysqld]
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
skip-external-locking

log-bin=/var/log/mysql/binlog
binlog-do-db=mydb

bind-address        = 127.0.0.1
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

5
  • The FLUSH LOGS command will flush any remaining data, close the current log file and open a new log file.
    – EEAA
    Sep 25, 2012 at 1:29
  • @ErikA doesn't seem to work, maybe I've missed something
    – daisy
    Sep 25, 2012 at 1:49
  • Are you sure you're looking in the right place for your binlogs?
    – EEAA
    Sep 25, 2012 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, 2012 at 1:51
  • @ErikA right, the name differ, I specified 'mydb', but didn't see anything prefixed with that
    – daisy
    Sep 25, 2012 at 1:54

1 Answer 1

0
binlog-do-db=mydb

@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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .