MySQL binary log

The MySQL binary log, known as Binlog, records the operation of database modification data and can be used as a data recovery.

There are three ways to record Binlog

1, statement

The original Sql,io amount for the record modification data is small. When you have a function in SQL that relies on a running environment such as user (), it may cause data recovery to be incorrect.

2. Row

Record each row of data modification action, IO is large, the accuracy is highest, the production environment is recommended to use this mode.

3, Mixed

These two modes are used in a mixed way.

Binlog parameter settings

1, Log_bin

Read-only parameters, can only be set in My.ini (MY.CNF), the value of Log_bin is the name of the binary file, the presence of the Log_bin configuration means that the binary file is turned on, and does not exist to indicate shutdown.

2, Sql_log_bin

The session-bound variable, on indicates that the current session is logged Binlog,off indicates that the current session is not logged Binlog.

3, Binlog_format

Three ways to record Binlog.

4, Max_binlog_size

Binlog log file size, in bytes, where the log exceeds this size creates a new Binlog log, and the file name suffix automatically adds 1.

5, Sync_binlog

1, which means that the in-memory binary logs are synchronized to the binary log file on disk immediately after each transaction commit.

0, expressed by MySQL itself decide when to write the file.

N, which indicates that the file is written after n transactions are committed.

Obviously, the 0 has the highest performance, the least security, 1 the worst performance and the highest security.

Binary log Query

1. View the binary log file list

binary logs;

2. View the binary log files that are currently in use

Show master status;

3. View the events in the binary log file (view Binlog content)

Show Binlog eventsshow binlog eventsinch 'mybinlog.000001'Show Binlog Eventsinch 'mybinlog.000001' from 245Show Binlog Eventsinch 'mybinlog.000001'Limit3Show Binlog Eventsinch 'mybinlog.000001'Limit2,5Show Binlog Eventsinch 'mybinlog.000001' from 245LimitTenShow Binlog Eventsinch 'mybinlog.000001' from 245Limit4, -

4. Mysqlbinlog command

(1) The file system uses the Mysqlbinlog command to view the corresponding binary log: Mysqlbinlog mybinlog.000001

(2) Viewing binary logs from a specified location: Mysqlbinlog--start-position 215 mybinlog.000001

(3) Specify where to start, end at that location, and view the binary log files between: Mysqlbinlog--start-position 215--stop-position 324 mybinlog.000001

(4) The specified time to start viewing, for example, view June 4, 2017 10 o'clock 40 after the log: Mysqlbinlog--start-datetime "2017-6-4 11:40:00" mybinlog.000001;

(5) Specify end time: Mysqlbinlog--start-datetime "2017-6-4 11:40:00"--stop-datetime "2017-6-4 12:55:00" mybinlog.000001;

MySQL binary log