MySQL InnoDB manages and backs up binary logs

(I) Importance of binary logs

If there is data backup at a certain point in time and all binary logs from then on
You can replay the binary logs generated since the last full backup and roll back all the changes.


(Ii) Best practices for binary log Configuration

It is not enough for InnoDB to enable binary logs only. Other measures are required to ensure security:
The recommended configuration is as follows:

● Sync_binlog = 1

Indicates that binary logs are written in synchronous mode to the disk, and the write operation bypasses the OS buffer.
The default value is 0.

● Innodb_support_xa = 1

Ensure the synchronization of binary logs and InnoDB data files


(Iii) factors that affect the binary log backup policy

For example:

(Iv) binary log format

The granularity of binary logs is that events, each event has a fixed event header, including: when, what, who, etc.
Because its format is binary, we can use mysqlbinlog to view its content.
The following is an example:

[mysql@even data]$ mysqlbinlog -vv mysql-bin.000023/*!40019 SET @@session.max_insert_delayed_threads=0*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;# at 4#130515 12:35:29 server id 2  end_log_pos 107   Start: binlog v 4, server v 5.5.16-log created 130515 12:35:29 at startup# Warning: this binlog is either in use or was not closed properly.ROLLBACK/*!*/;BINLOG 'kRCTUQ8CAAAAZwAAAGsAAAABAAQANS41LjE2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACREJNREzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA=='/*!*/;# at 107#130515 12:37:47 server id 2  end_log_pos 255   Query   thread_id=3     exec_time=0     error_code=0SET TIMESTAMP=1368592667/*!*/;SET @@session.pseudo_thread_id=3/*!*/;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=2, @@session.auto_increment_offset=2/*!*/;/*!\C utf8 *//*!*/;SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;SET @@session.lc_time_names=0/*!*/;SET @@session.collation_database=DEFAULT/*!*/;alter database db_rocky character set = utf8mb4 COLLATE = utf8mb4_unicode_ci/*!*/;DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

Two events are listed here to view the second event:

# at 107#130515 12:37:47 server id 2  end_log_pos 255   Query   thread_id=3     exec_time=0     error_code=0

The first line indicates the start position of the event: at 107
The second line contains the following items:
● Event Date and Time: 130515 12:37:47
● Server ID, which is necessary to prevent infinite loops between replication and other problems
● Start position of the next event: end_log_pos 255
Note: This value is usually incorrect.
Because the master database will copy the event to a buffer, but in this case
MySQL does not know the location of the next log event

● Event type. query is used here.
● The thread ID for executing the change event, which is very important for Auditing
● Time difference between statement Timestamp and binary log writing: exec_time
This value varies greatly from the standby database that is outdated in replication.

● Error code generated by the event


(V) how to clear old binary logs

Do not use RM to delete logs; otherwise, the mysql-bin.index state file will be inconsistent with that on the disk
The following cron command should be used:

0 0 * * * /usr/bin/myql -e "PURGE MASTER LOGS BEFORE CURRENT_DATE - INTERVAL N DAY"