MySQL InnoDB manages and backs up binary logs

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 since then can replay the binary logs since the last full backup and" roll back "all changes (ii) binary log configuration best practices for InnoDB if only enabling binary logs is not enough, other measures are required to ensure security: the recommended configuration is as follows: ● sync_binlog = 1 indicates that binary logs are written to the disk synchronously. In this case, the write operation bypasses the OS buffer. The default value is 0 ● innodb_support_xa = 1 to ensure the binary log and InnoDB data files. synchronization (iii) factors that affect the binary log backup policy include: (iv) binary log format: the granularity of binary logs is that events, each event has a fixed event header, including: when, What, and Who are unreadable because of their binary format. We can use mysqlbinlog to view their content. The following is an example: [plain] [mysql @ even d Ata] $ 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 'your aaaaaaaaaaaaaaaacrejnrezgnaagaegaebaqeegaavaaegggaaaaicagcaa = '/*! */; # At 107 #130515 12:37:47 server id 2 end_log_pos 255 Query thread_id = 3 exec_time = 0 error_code = 0 set timestamp = 1368592667 /*! */; SET @ session. pseudo do_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 file ROLLBACK/* added by mysqlbinlog */;/*! 50003 SET COMPLETION_TYPE = @ OLD_COMPLETION_TYPE */; two events are listed here to view the second event: [plain] # 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: the second line of at 107 contains the following items: ● date and time of the event: 130515 12:37:47 ● server ID, which is necessary to prevent infinite loops between copies and other problems ● start position of the next event: end_log_pos 255 note that this value is usually incorrect because the master database copies the event to a buffer zone. However, MySQL does not know the location of the next log event in this case. ● event type, here it is the thread ID of the Query ● execution of the change event, which is very important for auditing ● The statement Timestamp and the time difference between writing binary logs: exec_time this value has a large deviation on the standby database that is lagging behind replication ● error code generated by the event (v) methods for clearing old binary logs do not use rm to delete logs; otherwise, it will cause mysql-bin.index status files the following cron command should be used for inconsistency with the disk: [plain] 0 0 ****/usr/bin/myql-e "purge master logs before CURRENT_DATE-interval n day"