(1) First knowledge of MySQL Binlog

Mysqlbinlog command---parse Mysqlbinlog logs from the MySQL website with tools

What is the role of the Binlog log?

Used to record MySQL internal additions and deletions to the MySQL database updated content (check is not recorded)

How to turn on Log_bin

[Mysqld]

Log-bin=mysql-bin (name can be casually up)

Verify that it is turned on:

Mysql> Show variables like '%log_bin% ';

+---------------------------------+-------+

| variable_name | Value |

+---------------------------------+-------+

| Log_bin | On

/etc/init.d/mysqld restart

After restarting MySQL (in order to flush the scrolling log log file), execute the following MySQL statement

Create DATABASE Oldboy;use oldboy;create table student (id int (4) Auto_increment primary key, name varchar (+) not NULL); SERT into student (name) VALUES (' Andy '), insert into student (name) VALUES (' Jack '), select * FROM Student;\q

The corresponding Binlog file contents:

[Email protected] mysql]# Mysqlbinlog mysql-bin.000046

/*!40019 SET @ @session. max_insert_delayed_threads=0*/;

/*!50003 SET @[email protected] @COMPLETION_TYPE, completion_type=0*/;

DELIMITER/*!*/;

# at 4

#160411 21:00:35 Server ID 1 end_log_pos 107 start:binlog v 4, Server v 5.5.32-log created 160411 21:00:35 at startup

# Warning:this Binlog is either on use or was not closed properly.

rollback/*!*/;

BINLOG '

858lvw8baaaazwaaagsaaaabaaqans41ljmylwxvzwaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

aaaaaaaaaaaaaaaaaadznwtxezgnaagaegaebaqeegaavaaegggaaaaicagcaa==

‘/*!*/;

# at 107

#160411 21:01:16 Server ID 1 end_log_pos 194 Query thread_id=2 exec_time=0 error_code=0

SET timestamp=1460379676/*!*/;

SET @ @session. pseudo_thread_id=2/*!*/;

SET @ @session. Foreign_key_checks=1, @ @session. sql_auto_is_null=0, @ @session. Unique_checks=1, @ @session. autocommit= 1/*!*/;

SET @ @session. sql_mode=0/*!*/;

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=33/*!*/;

SET @ @session. lc_time_names=0/*!*/;

SET @ @session. collation_database=default/*!*/;

Create DATABASE Oldboy

/*!*/;

# at 194

#160411 21:03:34 Server ID 1 end_log_pos 344 Query thread_id=2 exec_time=0 error_code=0

Use oldboy/*!*/;

SET timestamp=1460379814/*!*/;

CREATE TABLE student (ID int (4) Auto_increment primary key, name varchar (+) NOT NULL)

/*!*/;

# at 344

#160411 21:04:53 Server ID 1 end_log_pos 414 Query thread_id=2 exec_time=0 error_code=0

SET timestamp=1460379893/*!*/;

BEGIN

/*!*/;

# at 414

#160411 21:04:53 Server ID 1 end_log_pos 442 intvar

SET insert_id=1/*!*/;

# at 442

#160411 21:04:53 Server ID 1 end_log_pos 549 Query thread_id=2 exec_time=0 error_code=0

SET timestamp=1460379893/*!*/;

INSERT into student (name) VALUES (' Andy ')

/*!*/;

# at 549

#160411 21:04:53 Server ID 1 end_log_pos 576 Xid = 13

commit/*!*/;

# at 576

#160411 21:04:57 Server ID 1 end_log_pos 646 Query thread_id=2 exec_time=0 error_code=0

SET timestamp=1460379897/*!*/;

BEGIN

/*!*/;

# at 646

#160411 21:04:57 Server ID 1 end_log_pos 674 intvar

SET insert_id=2/*!*/;

# at 674

#160411 21:04:57 Server ID 1 end_log_pos 781 Query thread_id=2 exec_time=0 error_code=0

SET timestamp=1460379897/*!*/;

INSERT into student (name) VALUES (' Jack ')

/*!*/;

# at 781

#160411 21:04:57 Server ID 1 end_log_pos 808 Xid = 14

commit/*!*/;

DELIMITER;

# End of log file

ROLLBACK/* Added by Mysqlbinlog */;

/*!50003 SET [email protected]_completion_type*/;

Errors you may encounter:

[Email protected] mysql]# Mysqlbinlog mysql-bin.000045

Mysqlbinlog:unknown variable ' Default-character-set=utf8 '

The reason for this problem is that I added the following in the Client option group in MY.CNF: Default-character-set=utf8

This is a bug in Mysqlbinlog.

Workaround:

When you use the Mysqlbinlog tool to view the binary log, the MySQL profile my.cnf is reread instead of the configuration file that the server has loaded into memory.

Temporarily comment out can

This article is from the "Tridewah operation and maintenance work Road" blog, please be sure to keep this source http://cuidehua.blog.51cto.com/5449828/1762761

(1) First knowledge of MySQL Binlog