MySQL Binlog Log

One: three types of MySQL logs:

Statement, row, mix format. It is recommended to use row format.

How to set your own log format?

1.set globle binlog_format=‘MIXED‘

2、 vi /etc/my.cnf

1 Statement:
Pros: No need to record changes in each row, reduce the Binlog log volume, save IO, improve performance
Cons: Because the records only execute statements, in order for these statements to run correctly on the slave, it is also necessary to record some information about each statement at the time of execution, to ensure that all statements can be slave and executed at the master side of the same result. In addition to MySQL replication, like some specific function functions, slave can be consistent with master on a number of related issues.

PS: Compared to row can save much performance and log volume, this depends on the SQL case of the application, the normal record is modified or inserted in the row format is less than the amount of log generated by statement, but given the condition of the update operation, and the whole table is deleted, ALTER TABLE operations, the row format generates a large number of logs, so the amount of log generated will increase, as well as the IO performance issues, when considering whether to use the row format log should be followed according to the actual application.

2 row format:
Advantage: Binlog can not record the context-sensitive information of executed SQL statements, only need to record what the record was modified to. So the Rowlevel log content will be very clear to record the details of each row of data modification. There are no stored procedures, or function, and trigger calls and triggers that cannot be copied correctly in certain situations.
Cons: All executed statements are recorded in the log when they are recorded, which can result in a large amount of log content.

Row (recommended), Statement, Mix (Statement Main, switch to row under special conditions)
Sync_binlog = 1--binlog-do-db 、--binlog-ignore-db (master side)
--replicate-do-db 、--replicate-ignore-db (slave end)

Reset Master (unless you give him up, never use it) Purge binary logs XXXXXX (backup first, then Purge)

3:mixed format:
In mixed mode, the general statement modification using statment format to save Binlog, such as some functions, statement can not complete the operation of the master-slave copy, the row format to save Binlog, MySQL differentiates the log form of the treated record according to each specific SQL statement executed, that is, choosing between statement and row.

Two: How to view your own log format:

MySQL Binlog Log