I
I
Ivan Koryukov2017-03-18 21:23:42
MySQL
Ivan Koryukov, 2017-03-18 21:23:42

How to setup mysql binary log?

I'm trying to make an incremental backup via binlog.
Added lines to mysql config

server-id = master-01
log-bin = /var/lib/mysql/binary_log
binlog-do-db = base
sync_binlog = 1

For the test, I created a table and inserted several rows into it.
When I look at the log mysqlbinlog -s binary_log.000003 > log.sql
, I get this text:
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @[email protected]@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
ROLLBACK/*!*/;
# [empty]
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
SET TIMESTAMP=1489860072/*!*/;
SET @@session.pseudo_thread_id=999999999/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database base
/*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
use `base`/*!*/;
SET TIMESTAMP=1489860129/*!*/;
create table logs (id int auto_increment primary key, message text)
/*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
SET TIMESTAMP=1489860189/*!*/;
BEGIN
/*!*/;
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
SET TIMESTAMP=1489860194/*!*/;
BEGIN
/*!*/;
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
SET TIMESTAMP=1489860202/*!*/;
BEGIN
/*!*/;
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET [email protected]_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

I see here the creation of a database, the creation of a table and three empty blocks, presumably being an insertion of data, but for some reason there are no queries in them.
Why?

Answer the question

In order to leave comments, you need to log in

1 answer(s)
I
Ivan Koryukov, 2017-03-20
@MadridianFox

As it turned out, mysqlbinlog, without the option, -sdisplays lines like BINLOG 'base64_encoded_text', which is a service command for mysql. In order to see the query, you need to add the argument when calling mysqlbinlog -v, but the query will be displayed as a comment.
And I thought that mysqlbinlog outputs queries in the same way as mysqldump ...

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question