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'
Bytes
AAAAAAAAAAAAAAAAAACREJNREzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA =
'/*! */;
# 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 */;

1 2 Next Page