MySQL Binlog log < go >

Binlog basic understanding of MySQL binary log is the most important log of MySQL, it records all the DDL and DML (except the data query statement) statements, logged as an event, and also contains the time spent by the statement, MySQL binary log is transaction-safe. In general, the binary log will probably have a 1% performance loss (see MySQL's Official Chinese manual, 5.1.24).     The binary has two most important usage scenarios: one: MySQL replication on the master side binlog,mster the binary log to slaves to achieve master-slave data consistency.        Second: It is natural that data is restored by using the Mysqlbinlog tool to recover the data. Binary logs include two types of files: The binary log index file (the file name suffix. Index) is used to record all binaries, and the binary log file (the filename suffix is. 00000*) records all DDL and DML (except data query statement) statement events for the database.  First, open the Binlog log: VI edit open the MySQL profile # vi/usr/local/mysql/etc/my.cnf in [MYSQLD] block Settings/Add Log-bin=mysql-bin confirm is open state (value Mysql-bin is the base or prefix name of the log); Restart the mysqld service to make the configuration effective # Pkill Mysqld #/usr/local/mysql/bin/mysqld_safe--user=mysql & Second, can also    Log in to the MySQL server and check if the binary log is turned on by using the MySQL variable configuration table: Variable[?v?ri?b?l] Variable Login Server #/usr/local/mysql/bin/mysql-uroot-p123456     Mysql> Show variables like ' log_% '; +----------------------------------------+---------------------------------------+    | variable_name |    Value | +----------------------------------------+---------------------------------------+    | Log_bin | On | ------> on indicates that Binlog log is turned on | Log_bin_basename |    /usr/local/mysql/data/mysql-bin | | Log_bin_index |    /usr/local/mysql/data/mysql-bin.index | | log_bin_trust_function_creators |    OFF | | log_bin_use_v1_row_events |    OFF | | Log_error |    /usr/local/mysql/data/martin.err | | Log_output |    FILE | | log_queries_not_using_indexes |    OFF | | Log_slave_updates |    OFF | | log_slow_admin_statements |    OFF | | Log_slow_slave_statements             |    OFF | | log_throttle_queries_not_using_indexes |    0 | | log_warnings |    1 | +----------------------------------------+---------------------------------------+ three, commonly used binlog log Operations Command 1.    View all Binlog log lists mysql> show master logs;    2. View the master status, which is the last (most recent) one Binlog log's number name, and its last action event Pos end point (Position) value mysql> show Master status;      3. Refresh log log, starting from now to produce a new numbered Binlog log file mysql> flush logs; Note: Each time the MYSQLD service restarts, the command is automatically executed, the Binlog log is refreshed, and the Binlog log is refreshed with the-F option mysqldump backup data; 4. Reset (empty) all Binlog logs mysql> reset master; Four, to view a binlog log content, commonly used in two ways: 1. Use the Mysqlbinlog to view the command method: Note: Binlog is a binary file, ordinary file viewer cat more VI can not be opened, you must use the own Mysqlbinlog command to check If the Binlog log is in the same directory as the database file (my environment configuration installation is selected in/usr/local/mysql/data), if an error occurs when you use the Mysqlbinlog command in the following version of MySQL5.5, add "--no-defaul TS "Option #/usr/local/mysql/bin/mysqlbinlog/usr/local/mysql/data/mysql-bin.000013 Intercept A fragment analysis below: ..... ...... ................. ....... ..... ....................... # at 552 #131128 17:50:46 Server ID 1 end_log_pos 665 Query thread_id=11 exec_time=0 error_code=0---         Execution time: 17:50:46;pos point: 665 SET timestamp=1385632246/*!*/;         Update zyyshop.stu set name= ' John Doe ' where id=4----> executed SQL/*!*/; # at 665 #131128 17:50:46 server ID 1 end_log_pos 692 Xid = 1454----> Execution time: 17:50:46;pos point: 692 ...         ......................................................................... Note: Server ID 1 The service number of the database host; End_log_pos 665 POS Point thread_id=11 thread number 2. The above method reads the full text of the Binlog log. More, not easy to distinguish between viewing the Pos point information, here is a more convenient query command: mysql> show Binlog events [in ' Log_name '] [from POS] [LIMIT [offset,] row_count]             ; Option resolution: In ' Log_name ' specifies the Binlog file name to query (not specified is the first Binlog file) from POS specifies which POS starting point to start with (not specifying the WholeThe first POS point of the file is counted) the LIMIT [offset,] offset (not specified is 0) Row_count The total number of queries (not specifying all rows) to intercept some of the query results: 20. Row *************************** log_name:mysql-bin.000021----------------------------------------------& Gt              Binlog log file name of the query pos:11197----------------------------------------------------------> Pos starting point: Event_type:query----------------------------------------------------------> Event Type: Query Server_i D:1--------------------------------------------------------------> Identity is the end_log_pos:11308 that is performed by which server------  ----------------------------------------------------> Pos End point: 11308 (i.e.: Pos starting point of the downlink) Info:use ' zyyshop '; INSERT into ' team2 ' VALUES (0,345, ' ASDF8ER5 ')---> Execute the SQL statement *************************** 21. Row *************************** log_name:mysql-bin.000021 pos:11308----------------------------------------------------------> POS starting point: 11308 (ie: upstream pos end point) event_type:query Server_id:1 end_log_pos:11417 info:use ' zyyshop '; /*!40000 ALTER TABLE ' team2 ' ENABLE KEYS */*************************** 22. Row *************************** log_name:mysql-bin.000021 pos:11417 even T_type:query server_id:1 end_log_pos:11510 info:use ' zyyshop '; DROP TABLE IF EXISTS ' type ' this statement can return the specified Binlog log file into a valid event line and use limit to specify the starting offset of the POS point, query the number of bars; A. Query the first (earliest) Binlo           G log: mysql> show Binlog events\g;      B. Specify the query mysql-bin.000021 this file: Mysql> show Binlog events in ' mysql-bin.000021 ' \g;      C. Specify query mysql-bin.000021 This file, starting from point of Pos: 8224 start: mysql> show Binlog events in ' mysql-bin.000021 ' from 8224\g; D. Specify query mysql-bin.000021 This file, starting from Pos: 8224, check 10 mysql> show BInlog events in ' mysql-bin.000021 ' from 8224 limit 10\g;  E. Specify query mysql-bin.000021 this file, from Pos: 8224 start, offset 2 lines, query 10 mysql> show Binlog events in ' mysql-bin.000021 ' from 8224  Limit 2,10\g; Binlog log Experiment (Zyyshop is a database) 1. Assuming it's 4:00, my scheduled task starts performing a full database backup: Back up the Zyyshop database to/root/bak.zyyshop.sql File: #/usr/local/mysql/bin/mysqldump-uroot-p123456-lf--log-error=/root/mydump.err-b zyyshop >/root/BAK.zyy        Shop.sql ... After a few minutes, the backup is complete, I don't have to worry about data loss, because I have a backup, Gaga ~ ~ ~ because I use the-f option, when the backup work just started, the system will refresh the log log, generate a new Binlog log to record the database after the backup "delete and change" operation, check: MYSQ      L> Show master status; +------------------+----------+--------------+------------------+      | File | Position | binlog_do_db |      binlog_ignore_db | +------------------+----------+--------------+------------------+      |      mysql-bin.000023 |              120 |                  |      | +------------------+----------+--------------+------------------+ in other words, mysql-bin.000023 is used to record 4:00 afterAll "additions and deletions" to the database operation. 2. Early 9:00 to work, the business needs of the database for a variety of "add and change" Operation ~~~~~~~ @ For example: Create a student table and insert, modify the data and so on: Creating table IF not EXISTS ' TT ' (' I d ' Int (ten) unsigned NOT NULL auto_increment, ' name ' varchar (+) NOT null, ' Sex ' enum (' m ', ' W ') is not null D Efault ' m ', ' Age ' tinyint (3) unsigned not NULL, ' ClassID ' char (6) DEFAULT NULL, PRIMARY KEY (' I      d ')) Engine=innodb DEFAULT Charset=utf8; Import experimental data mysql> INSERT INTO zyyshop.tt (' name ', ' sex ', ' age ', ' ClassID ') VALUES (' Yiyi ', ' w ', ', ' cls1 '), (' Xiaoer ', ' m ', 22      , ' CLS3 '), (' Zhangsan ', ' w ', +, ' cls5 '), (' Lisi ', ' m ', ', ' Cls4 '), (' Wangwu ', ' w ', +, ' cls6 ');      View Data mysql> select * from Zyyshop.tt; +----+----------+-----+-----+---------+      | ID | name | sex | Age |      ClassID |  +----+----------+-----+-----+---------+      | 1 | Yiyi |  W | 20 |      CLS1 |  | 2 | Xiaoer |  m | 22 |      CLS3 |  | 3 | Zhangsan |  W | 21 |      CLS5 |  | 4 | Lisi |  m | 20 | CLS4 |  | 5 | Wangwu |  W | 26 |      CLS6 |      +----+----------+-----+-----+---------+ Noon and then perform the modified data operation mysql> Update zyyshop.tt set name= ' John Doe ' where id=4;      mysql> Update zyyshop.tt set name= ' small two ' where id=2;      Modified Result: mysql> select * from Zyyshop.tt; +----+----------+-----+-----+---------+      | ID | name | sex | Age |      ClassID |  +----+----------+-----+-----+---------+      | 1 | Yiyi |  W | 20 |      CLS1 |  | 2 | Small two |  m | 22 |      CLS3 |  | 3 | Zhangsan |  W | 21 |      CLS5 |  | 4 | John Doe |  m | 20 |      CLS4 |  | 5 | Wangwu |  W | 26 |      CLS6 |     +----+----------+-----+-----+---------+ Assuming this is 18:00, inexplicable execution of a sad SQL statement, the entire database is gone: mysql> drop zyyshop; 3. The cup is now, don't panic!  Take a closer look at the last Binlog log, and record the key point of POS where the operation of the POS point is causing the database to break (usually in the last few steps); Back up the last Binlog log file: # Ll/usr/local/mysql/data | grep Mysql-bin # Cp-v/usr/local/mysql/data/mysql-bin.000023/root/now performs aRefresh Log index operation, restart the new Binlog log record file, the theory says mysql-bin.000023 this file will no longer be written (for us to analyze the reason and find Pos point), all database operations will be written to the next log file; mysql>      Flush logs;          Mysql> Show master status; 4. Read Binlog log, analyze problem mode one: Read Binlog log with Mysqlbinlog: #/usr/local/mysql/bin/mysqlbinlog/usr/local/mysql/data/mysql                -bin.000023 mode two: Log in to the server and view (recommended): Mysql> show Binlog events in ' mysql-bin.000023 '; The following fragment is at the end: +------------------+------+------------+-----------+-------------+------------------------------------- -----------------------+        | Log_name | Pos | Event_type | server_id | End_log_pos |        Info | +------------------+------+------------+-----------+-------------+--------------------------------------------- ---------------+        |  mysql-bin.000023 | 922 |         Xid |         1 | 953 |        COMMIT/* xid=3820 */| |  mysql-bin.000023 | 953 |    Query  |        1 | 1038 |        BEGIN | | mysql-bin.000023 | 1038 |         Query |        1 | 1164 | Use ' zyyshop ';        Update zyyshop.tt set name= ' John Doe ' where id=4| | mysql-bin.000023 | 1164 |         Xid |        1 | 1195 |        COMMIT/* xid=3822 */| | mysql-bin.000023 | 1195 |         Query |        1 | 1280 |        BEGIN | | mysql-bin.000023 | 1280 |         Query |        1 | 1406 | Use ' zyyshop ';        Update zyyshop.tt set name= ' small two ' where id=2| | mysql-bin.000023 | 1406 |         Xid |        1 | 1437 |        COMMIT/* xid=3823 */| | mysql-bin.000023 | 1437 |         Query |        1 | 1538 |        Drop Database Zyyshop | +------------------+------+------------+-----------+-------------+------------------------------------------------------------+ through analysis, the point of the database destruction of the POS interval is between 1437--1538, as long as the recovery to 1437 ago can be.      5. Now the early morning backup of the data recovery: #/usr/local/mysql/bin/mysql-uroot-p123456-v </root/bak.zyyshop.sql;          Note: Backup data is restored until the early morning (4:00) date. But what about the data swelling all day today (4:00--18:00)?    You have to mysql-bin.000023 the new diary mentioned in the previous article ... 6. Recover data recovery syntax format from Binlog log: # Mysqlbinlog mysql-bin.0000xx |                   Mysql-u User name-p password database name common options:--start-position=953 starting POS Point--stop-position=1437 End Pos Point--start-datetime= "2013-11-29 13:18:54" starting point--stop-datetime= "2013-11-29 13:21:53 "End point--database=zyyshop specifies that only the Zyyshop database is restored (there are often multiple databases on a single host, only local log logs) infrequently used options :-u--user=name connect to the remote server as username. The user name that is connected to the remoted host-P--password[=na Me] Password to connect to remote server. The password that is connected to the host-H--host=name Get the Binlog from server . From the remote host, obtainTake Binlog log--read-from-remote-server read binary logs from a MySQL server. Read the Binlog log summary from a MySQL servers: actually the read Binlog The log content, passed to the MySQL command via a pipe break. These commands, files as far as possible to write absolute path; A. Full recovery (This example is not reliable, because the last drop database Zyyshop also in the log, must find a way to remove this destruction statement, do partial recovery) #/usr/local/mysql/bin /mysqlbinlog/usr/local/mysql/data/mysql-bin.000021 |        /usr/local/mysql/bin/mysql-uroot-p123456-v zyyshop B. Specify POS end point recovery (partial recovery): @--stop-position=953 POS End point Note: This POS end point is between "import experimental data" and "Update Name= ' John Doe '" so that you can revert to "import test data" before changing "Name= ' John Doe '" #/usr/local/mysql/bin/mysqlbinlog--stop-pos ition=953--database=zyyshop/usr/local/mysql/data/mysql-bin.000023 | /usr/local/mysql/bin/mysql-uroot-p123456-v zyyshop Login At another terminal view results (successfully restored): mysql> select * from Zyys        Hop.tt; +----+----------+-----+-----+---------+        | ID | name | sex | Age |        ClassID |  +----+----------+-----+-----+---------+        | 1 | Yiyi |  W | 20 |        CLS1 |  | 2 | Xiaoer |  m | 22 |  Cls3  |  | 3 | Zhangsan |  W | 21 |        CLS5 |  | 4 | Lisi |  m | 20 |        CLS4 |  | 5 | Wangwu |  W | 26 |        CLS6 | +----+----------+-----+-----+---------+ C. Specify PSO point interval recovery (partial recovery): Update name= ' John Doe ' This data, log interval is pos[1038] and End_log_po S[1164], by transaction interval is: pos[953] and end_log_pos[1195]; update name= ' small two ' this data, the log interval is pos[1280]-and end_log_pos[1406], by transaction interval is: POS[1195]-end_log_pos[1437]; C1. Recover name= ' John Doe ' individually, but this way: #/usr/local/mysql/bin/mysqlbinlog--star t-position=1038--stop-position=1164--database=zyyshop/usr/local/mysql/data/mysql-bin.000023 | /usr/local/mysql/bin/mysql-uroot-p123456-v Zyyshop can also be recovered individually by transaction interval, as follows: #/usr/local/mysql/bin/mysqlbin Log--start-position=953--stop-position=1195--database=zyyshop/usr/local/mysql/data/mysql-bin.000023 | /usr/local/mysql/bin/mysql-uroot-p123456-v zyyshop c2. Recover name= ' Little two ' this step alone, but this way: #/usr/local/mysql/bin/ Mysqlbinlog--start-position=1280 --stop-position=1406--database=zyyshop/usr/local/mysql/data/mysql-bin.000023 | /usr/local/mysql/bin/mysql-uroot-p123456-v Zyyshop can also be recovered individually by transaction interval, as follows: #/USR/LOCAL/MYSQL/BIN/MYSQ Lbinlog--start-position=1195--stop-position=1437--database=zyyshop/usr/local/mysql/data/mysql-bin.000023 | /usr/local/mysql/bin/mysql-uroot-p123456-v zyyshop C3. The name= ' John Doe ', name= ' small two ' multi-step operations are restored together, and need to be by transaction interval, so: #/U Sr/local/mysql/bin/mysqlbinlog--start-position=953--stop-position=1437--database=zyyshop/usr/local/mysql/data/ mysql-bin.000023 | /usr/local/mysql/bin/mysql-uroot-p123456-v zyyshop D. Login at another terminal view current results (two names also resumed): mysql> select * from Zyysho        P.tt; +----+----------+-----+-----+---------+        | ID | name | sex | Age |        ClassID |  +----+----------+-----+-----+---------+        | 1 | Yiyi |  W | 20 |        CLS1 |  | 2 | Small two |  m | 22 |        CLS3 |  | 3 | Zhangsan |  W | 21 |        CLS5 |  | 4 |   John doe  |  m | 20 |        CLS4 |  | 5 | Wangwu |  W | 26 |        CLS6 | +----+----------+-----+-----+---------+ E. Time interval recovery can also be specified (partial recovery): In addition to the point-of-sale method for recovery, you can also restore by specifying a time interval,        Recovery by time requires reading the Binlog log content with the Mysqlbinlog command and finding the time node.        For example, I deleted the newly recovered TT table and then resumed the Mysql> drop table TT with the time interval point; @--start-datetime= "2013-11-29 13:18:54" starting point @--stop-datetime= "2013-11-29 13:21:53" End time Point #/usr/local /mysql/bin/mysqlbinlog--start-datetime= "2013-11-29 13:18:54"--stop-datetime= "2013-11-29 13:21:53"--database= zyyshop/usr/local/mysql/data/mysql-bin.000021 | /usr/local/mysql/bin/mysql-uroot-p123456-v Zyyshop Summary: The so-called recovery is to allow MySQL to save in the Binlog log in the specified paragraph interval of the SQL statements are re-executed once.

Transfer from https://www.cnblogs.com/martinzhang/p/3454358.html

MySQL Binlog log < go >