Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question
    
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 –  warl0ck 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 –  warl0ck Sep 25 '12 at 1:54

1 Answer 1

up vote 0 down vote accepted
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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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