Database mysql/mariadb Knowledge points-logging (2) binary log

Binary log

A SQL statement that records committed transactions causing data changes or potentially causing data changes, generating copies of the data through events in the replay log file, independent of the storage engine type.

Binary logs are turned on by default, binary logs and data are stored separately

the ability to turn on binary files：

In my.cnf [mysqld], add

log_bin[=/path/somefile]The default binary logs are in the database directory,

mariadb-bin.000001is a binary log data file,

mariadb-bin.indexIndex file for binary log

Three ways to record binary logs:

• Statement-based recording, each statement that changes the data is recorded as a statement, saving space, the system defaults to this mode, but is not recommended, there will be hidden risks
• With row-based logging, each row changes to a statement that makes it change, and the log volume is large, but the security of the data is very high
• Mixed mode: Mixed, let the system decide in which way

MariaDB [(None)]> SHOW VARIABLES like ' Binlog_format ';  +---------------+-----------+| variable_name | Value     |+---------------+-----------+| Binlog_format | STATEMENT | --record mode, system default based on statement mode +---------------+-----------+

Modifying binary logging methods

Correlation variables for binary logs

View the list of binary log files in MARIADB self-managed use

MariaDB [(None)]> SHOW master| BINARY LOGS; +--------------------+-----------+| Log_name           | File_size |+--------------------+-----------+| mariadb-bin.000002 |       290 | | mariadb-bin.000003 |       264 | | mariadb-bin.000004 |    529038 | | mariadb-bin.000005 |       245 |+--------------------+-----------+

To view binary log files in use

MariaDB [(None)]> SHOW MASTER STATUS; +--------------------+----------+--------------+------------------+| File               | Position | binlog_do_db | binlog_ignore_db |+--------------------+----------+--------------+------------------+| mariadb-bin.000005 |      245 |              |                  | +--------------------+----------+--------------+------------------+

To view the specified content in a binary file

MariaDB [(None)]> SHOW BINLOG EVENTS in ' mariadb-bin.000004 ' from 1 LIMIT 2,3\g

Whether to log binary logs, default on

MariaDB [(None)]> SHOW VARIABLES like ' sql_log_bin ';

Specifies the file location; The default off, which means that binary logging is not enabled, both of these are turned on to

MariaDB [(None)]> SHOW VARIABLES like ' Log_bin '

　　

MariaDB [(None)]> SHOW VARIABLES like ' max_binlog_size '; +-----------------+------------+| Variable_name   | Value      |+-----------------+------------+| max_binlog_size | 1073741824 |-the maximum volume of a single binary log file, the maximum value is automatically scrolled, and the default is 1g+----- ------------+------------+mariadb [(none)]> SHOW VARIABLES like ' sync_binlog ';    +---------------+-------+| variable_name | Value |+---------------+-------+| Sync_binlog   | 0     |--Set whether to start the binary Log Instant Sync disk feature, default 0, the OS is responsible for synchronizing logs to disk +---------------+-------+mariadb [(None)]> SHOW VARIABLES like ' expire_logs_days '; +------------------+-------+| Variable_name    | Value |+------------------+-------+| Expire_logs_days | 0     |--The number of days the binary log can be automatically deleted. The default is 0, which is not automatically deleted +------------------+-------+

Mysqlbinlog command

Client command tools for binary logs

• --start-position=# Specify the starting position
• --stop-position=# Specify end Position
• --start-datetime= (yyyy-mm-dd hh:mm:ss) specify start time
• --stop-datetime= (yyyy-mm-dd hh:mm:ss) specify end time
• --base64-output=decode-row
• -V |-VV |-vvv |-VVVV Show more information

[Email protected] mysql]# mysqlbinlog--start-position=528864--stop-position=529019 mariadb-bin.000004-- base64-output=decode-row-v# at 528864#180611 20:59:46 server ID 1  end_log_pos 528992        Query   thread_id=29    exec_time=0     error_code=0use ' School '/*!*/; SET Timestamp=1528721986/*!*/;insert Students (Stuid,name,age,gender) VALUES (+, ' Tom ', A, ' M ')  #改变数据的SQL语句/*!*/ The date and time the event occurred: 180611 20:59:46 the server ID of the event that occurs: the end location of the server 1 event: The type of the End_log_pos 528992 event: The Id:thread of the thread on which the server executes this event when the query event occurs The time stamp of the _id=29 statement and the difference between writing it to the binary file: exec_time=0 error code: Error_code=0 Event Content: Gtid:global Transaction ID, mysql5.6 with MARIADB10 version exclusive properties: GTID

Use mysqlbinlog mariadb-bin.000001 > file.sql commands to redirect to a SQL file, and then use mysql -uroot -p < file.sql commands to import data directly, enabling backup restore functionality

Management of binary logs

Delete to 02, note:02 do not delete

MariaDB [(None)]> PURGE BINARY LOGS to ' mariadb.000002 ';

Delete logs prior to 2018 -01-23

MariaDB [(None)]> PURGE BINARY LOGS before ' 2018-01-23 '; MariaDB [(None)]> PURGE BINARY LOGS before ' 2017-03-22 09:25:30 ';

Delete all binary logs, index file re-count

MariaDB [(None)]> RESET MASTER;

Manually triggering log scrolling

MariaDB [(None)]> FLUSH LOGS;

　　

Database mysql/mariadb Knowledge points-logging (2) binary log