MySQL Binlog maintenance detailed analysis (open, Binlog related parameters function, Mysqlbinlog interpretation, binlog delete)

Binary log role: the main implementation of three important functions: for replication, for recovery, for auditing.
Binary Log related parameters:
Log_bin
Setting this parameter means enabling the Binlog feature and specifying the path name
Log_bin_index
Set this parameter to specify the path and name of the binary index file
binlog_do_db
This parameter indicates that only the binary log of the specified database is logged
binlog_ignore_db
This parameter indicates that the binary log of the specified database is not logged
Max_binlog_cache_size
This parameter represents the maximum size of memory used by Binlog
Binlog_cache_size
This parameter represents the memory size used by Binlog, which can be helped by state variables Binlog_cache_use and Binlog_cache_disk_use.
Binlog_cache_use: Number of transactions using binary log cache
Binlog_cache_disk_use: The number of transactions using the binary log cache but exceeding the Binlog_cache_size value and using temporary files to hold statements in the transaction
Max_binlog_size
Binlog maximum, the maximum and default value is 1GB, this setting does not strictly control the size of the binlog, especially when the Binlog is closer to the maximum and encounters a relatively large transaction, in order to ensure the integrity of the transaction, it is impossible to do the switch log action, All SQL for the transaction can only be logged into the current log until the end of the transaction
Sync_binlog
This parameter directly affects MySQL's performance and integrity
Sync_binlog=0:
When a transaction commits, MySQL simply writes the data in Binlog_cache to the Binlog file, but does not perform a disk synchronization instruction such as Fsync to notify the filesystem to flush the cache to disk, and let filesystem decide when to synchronize, which is the best performance.
Sync_binlog=n, after committing n transactions, MySQL performs a disk synchronization instruction such as Fsync, and the gay file system flushes the Binlog file cache to disk.
The default setting in MySQL is sync_binlog=0, i.e. without any mandatory disk refresh instructions, performance is the best, but the risk is greatest. Once the system is stretched crash, all binlog information in the file system cache is lost

1. Turn on the binary log
Mysql>show variables like '%log_bin% ';
+---------------------------------+-------+
| variable_name | Value |
+---------------------------------+-------+
| Log_bin | OFF | --This parameter is used to set whether binary logging is enabled
| log_bin_trust_function_creators | OFF |
| Sql_log_bin | On |
+---------------------------------+-------+
[[Email protected] ~]# service MySQL stop
Shutting down MySQL .... [OK]
[Email protected] ~]# Cp/etc/my.cnf/etc/my.cnf.bak
[Email protected] ~]# VI/ETC/MY.CNF
Description: Add Log_bin=/var/lib/mysql/binarylog/binlog in the/etc/my.cnf file
[Email protected] ~]# mkdir-p/var/lib/mysql/binarylog
[Email protected] ~]# chown-r Mysql:mysql/var/lib/mysql/binarylog
[[Email protected] mysql]# service MySQL start
Starting MySQL. [OK]
Mysql> Show variables like '%log_bin% ';
+---------------------------------+---------------------------------------+
| variable_name | Value |
+---------------------------------+---------------------------------------+
| Log_bin | On |
| Log_bin_basename | /var/lib/mysql/binarylog/binlog |
| Log_bin_index | /var/lib/mysql/binarylog/binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| Sql_log_bin | On |
+---------------------------------+---------------------------------------+
6 rows in Set (0.00 sec)
[Email protected] mysql]# ll/var/lib/mysql/binarylog/
Total 8
-RW-RW----1 MySQL mysql 16:57 binlog.000001
-RW-RW----1 MySQL mysql 16:57 binlog.index
2. Switch binary log
mysql> flush logs;
Query OK, 0 rows affected (0.05 sec)
[Email protected] mysql]# ll/var/lib/mysql/binarylog/
Total 12
-RW-RW----1 MySQL mysql 164 may 17:09 binlog.000001
-RW-RW----1 MySQL mysql 17:09 binlog.000002
-RW-RW----1 MySQL mysql 17:09 binlog.index
3. View Binary Log Count
Mysql> show binary logs;
+---------------+-----------+
| Log_name | File_size |
+---------------+-----------+
|       binlog.000001 | 164 |
|       binlog.000002 | 164 |
4. View the binary log being used
Mysql> Show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |
+---------------+----------+--------------+------------------+-------------------+
|      binlog.000010 |              120 |                  |                   | |
+---------------+----------+--------------+------------------+-------------------+
5. Viewing binary log Events
5.1
Mysql> show Binlog events in ' binlog.000010 ';
+---------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | server_id | End_log_pos | Info |
+---------------+-----+-------------+-----------+-------------+---------------------------------------+
|   binlog.000010 | 4 |         Format_desc |         1 | 120 | Server ver:5.6.25-log, Binlog ver:4 |
| binlog.000010 | 120 |         Query |         1 | 219 | Use ' test '; CREATE TABLE andy (id int) |
+---------------+-----+-------------+-----------+-------------+---------------------------------------+
2 rows in Set (0.00 sec)
5.2
Mysql> show Binlog events in ' binlog.000010 ' from + limit 2;
+---------------+-----+------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | server_id | End_log_pos | Info |
+---------------+-----+------------+-----------+-------------+---------------------------------------+
| binlog.000010 | 120 |         Query |         1 | 219 | Use ' test '; CREATE TABLE andy (id int) |
| binlog.000010 | 219 |         Query |         1 | 298 | BEGIN |
+---------------+-----+------------+-----------+-------------+---------------------------------------+
2 rows in Set (0.00 sec)
6. View binary logs with the Mysqlbinlog tool
6.1
[Email protected] ~]# mysqlbinlog/var/lib/mysql/binarylog/binlog.000016
# at 199
#170530 19:42:52 Server ID 1 end_log_pos 306 CRC32 0x64d982e4 Query thread_id= exec_time=0error_code=0
Use ' Test '/*!*/;
SET timestamp=1496144572/*!*/;
INSERT into name values (' Tao ye ')
/*!*/;
# at 306
#170530 19:42:52 Server ID 1 end_log_pos 337 CRC32 0xf75d46d1 Xid = 36
commit/*!*/;
DELIMITER;
# End of log file
ROLLBACK/* Added by Mysqlbinlog */;
/*!50003 SET [email protected]_completion_type*/;
/*!50530 SET @ @SESSION. pseudo_slave_mode=0*/;
Content resolution:
Location > location in file, "at 199" indicates the starting point of "event", starting with the 199th byte; "End_log_pos 306" is the end of the No. 306 byte, the next event will start at the end of an event and cycle.
Timestamp > Time stamp of event occurred: "170530 19:42:52"
Event Execution Time > Event Execution time Spent: "Exec_time=0"
Error code > Error code: "Error_code=0"
Identity > Server identity ID for server: "Server ID 1"
6.2 Use the Mysqlbinlog tool to view the specified timestamp binlog
[Email protected] ~]# mysqlbinlog--start-datetime= "2017-05-30 19:42:52"/var/lib/mysql/binarylog/binlog.000016
6.3 Using the Mysqlbinlog tool to view the binlog of the specified position
[Email protected] ~]# Mysqlbinlog--start-position=199--stop-position=306/var/lib/mysql/binarylog/binlog.000016
7. Delete binary Log
7.1 Auto-Delete, add Expire_logs_days in MY.CNF
expire_logs_days = x # x is the specified number of days
7.2 Manual removal (automatic removal of OS file at OS level)
mysql> Reset Master; Delete Master's Binlog
mysql> Reset Slave; To delete a slave trunk log
Mysql> purge master logs before ' 2017-05-30 18:27:00 '; Deletes the Binlog log file from the previous log index for the specified date
Mysql> purge master logs to ' binlog.000011 '; Before deleting binlog.000011, does not contain binlog.000011

MySQL Binlog maintenance detailed analysis (open, Binlog related parameters function, Mysqlbinlog interpretation, binlog delete)