Shulou(Shulou.com)06/01 Report--

The binary log consists of files that contain "events" that describe changes to the contents of the database. The server writes these files in binary format.

1. View a list of binlog file names

Show binary logs

two。 View the contents of the binlog file

Show binlog events in 'mysql-bin.000001' limit 10

3. View binlog's current location point

Show master status

4. View binlog format

Log format: statement,row

Row: advantages: the log is relatively clear, and each line of data modification details will be recorded. Disadvantages: a large number of logs will be generated.

Statement: advantages: only record sql statements, solve the shortcomings of row format, reduce the number of logs, save Icano and storage resources, and improve performance. Disadvantages: some sql statements can not be copied to slava, resulting in the main inconsistent, there are some bug, statement execution errors are not easy to recover.

Mixed: between row and statement formats, dealing with their advantages and disadvantages very well.

SELECT @ @ binlog_format

5. Refresh the log

Flush logs

6. Clear the log

Reset master

7. Use the mysqlbinlog tool to view

Mysqlbinlog-v-base64-output=decode-rows / usr/app/mysqllog/mysql_bin.000001

Based on start / end time

Mysqlbinlog-- start-datetime='2018-12-13 00 hadoop 00'-- stop-datetime='2018-12-14 15 stop-datetime='2018 01purl 01'-d hadoop / usr/app/mysqllog/mysql-bin.000001

Output to a compressed file based on the posvalue, note: test is the library name, / var/lib/mysql/mysql-bin.000001 is the binary file path

Mysqlbinlog-start-position=2098-stop-position=2205 d test / usr/app/mysqllog/mysql-bin.000001 | gzip > test.sql.gz

Extract multiple binlog log files from a specified location

Mysqlbinlog-- start-position= "120"-- stop-position= "332" / usr/app/mysqllog/mysql-bin.000001 / usr/app/mysqllog/mysql-bin.000002 | more

Extract the specified database binlog and convert the character set to UTF8

Mysqlbinlog-- database=test-- set-charset=utf8 / usr/app/mysqllog/mysql-bin.000001 / usr/app/mysqllog/mysql-bin.000002 > test.sql

Recover data

Mysqlbinlog-- start-position= "120s"-- stop-position= "332" / usr/app/mysqllog/mysql-bin.000001 | mysql- uroot-p

Backup log: must be done by two mysqlbinlog

Mysqlbinlog-read-from-remote-server-host=host_name-raw

Binlog.000001 binlog.000131 binlog.000132

-- read-from-remote-server can be replaced by-R: connect to the server and request its binary log,-- host: host ip

Mysqlbinlog-read-from-remote-server-host=host_name-raw

-- to-last-log binlog.000001

-- raw: write the original (binary) output, binlog.000001: the first file,-- to-last-log: the last file is read and exited

Read logs in real time

Mysqlbinlog-read-from-remote-server-host=host_name-raw

-- stop-never binlog.000001

-- stop-never: maintains a connection to the server and continues to read new events after reaching the end of the last log file, there is no need to specify-- to-last-log reads the last log file because this option is implicit.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.