Home PC Games Linux Windows Database Network Programming Server Mobile  
  Home \ Database \ MySQL to recover the data through binlog     - Ubuntu and Derivative Edition users install LMMS 0.4.15 (Linux)

- Protect your files, modify the Linux value Umask (Linux)

- RHEL5 stalled due to power service error system can not start (Linux)

- Use eCryptFS encrypt files and directories on Linux (Linux)

- Ant command-line compiler Android project (Programming)

- Linux Getting Started tutorial: Experience QEMU virtual machine articles (Linux)

- Installation Enpass secure password manager on Ubuntu (Linux)

- Linux environment MySQL master-slave synchronization (Database)

- Recent Consolidation Using Linux security module (Linux)

- Oracle Database Restore (Database)

- Integrated security administrator Linux accident management (Linux)

- Some practical tips Linux (Linux)

- ORA-01839 error caused by incorrect system date setting (Database)

- How to use jgit to manage Git submodule (Linux)

- 14 useful example Linux Sort command (Linux)

- KVM QEMU virtual machine installation configuration under CentOS (Linux)

- The Sublime Text 3 configuration file (Linux)

- Linux shell script to adjust the Task Scheduler (Linux)

- Function Getting the Linux shell (Programming)

- Linux System Getting Started Learning: After starting in Ubuntu or Debian, enter the command line (Linux)

  MySQL to recover the data through binlog
  Add Date : 2018-11-21      
  First, what is binlog

1, binlog basic definition: binary log, has become the binary log record data generating SQL statements or potential changes and binary form stored on disk;

Binary log information:

File location: The default storage location for the database file directory

The files are named: name hostname-bin.xxxxx (a restart mysql will automatically generate a new binlog)

2, the configuration binlog, set in the configuration file my.cnf and restart mysql

3. Check status: mysql> show variables like '% log_bin%';

Second, the three formats mysql binlog (Statement, MiXED, ROW)

1.Statement: Each sql will modify the data recorded in the binlog.

Advantages: Changes need to record each line, reducing the binlog log volume, saving IO, to improve performance. (Compared to the row number of performance logs and the amount of energy savings, depending on the SQL application, with a normal amount of log records to modify or insert a row format also produced less than the amount of log Statement produced, but considering the update if band conditions operation, as well as delete the entire table, alter table and other operations, ROW format will produce a large logs, in considering whether to use ROW with the format of the log should apply according to the actual situation, the log will increase the amount of how much they produce, and brought IO performance problems.)

Cons: Since the recording of the statement is executed, these statements in order to run correctly on a slave, you must also record some information about each statement in the course of implementation to ensure that all statements can be obtained at the slave and the master side when the same execution the result of. Further mysql replication, such as function-specific, slave master with the consistency will be many issues (such as sleep () function, last_insert_id (), and user-defined functions (udf) cause problems).

Use the following statement function can not be copied:


* UUID ()

* USER ()


* SYSDATE () (--sysdate-is-now option unless startup is enabled)

While INSERT ... SELECT will produce more than RBR row-level locking

2.Row: sql statement does not record contextual information, save only which records are modified.

Advantages: context-sensitive information may not be recorded in the binlog sql statement executed, only need to record that a record is modified into anything. So rowlevel contents of the log will be very clear to record details of each line of data modifications. And function, as well as issues trigger and trigger calls can not be reproduced correctly stored procedure does not occur under certain circumstances, or

Cons: All statements executed when logged when all will modify each row record to record, this may generate a lot of log content, such as an update statement to modify multiple records in each of the binlog changes will be recorded, so causing binlog log volume will be great, especially when the implementation of such alter table statements, since the table structure changes, each record change, then the table every record will be recorded in the log .

3.Mixedlevel: a mixture of the above two are level, the general statement to modify the format using statment save binlog, such as some functions, statement can not be completed master-slave replication operation is used to save the row format binlog, MySQL will be executed in accordance with each specific sql statement to distinguish treated logs record form, that is, between the statement Row and select a new version of MySQL squadron row level mode has also been optimized, not all changes are to row level to record, like when it came to the table structure changes will be recorded in statement mode. As the like update or delete data modification statements, or will record changes for all rows

Third, online examples

1, examples Introduction: DB machine suddenly hang up because of hardware failure, even the emergency power did not succeed, after repeated restart the machine caused mysql data corruption, and the machine is online, online business can not only emergency maintenance,

Originally wanted to be with mysqldump and then a change to the data copying machine, data corruption did not expect, did not even successful backup, data can not be extracted, had turned suddenly thought, when I opened the binlog, I can extract data binlog

2, the data recovery

By binlog transferred to the normal operation of the machine above, to recover by binlog

/ App / mysql / bin / mysqlbinlog bin-log.xxx | / app / mysql / bin / mysql

3, through binlog sql query execution at a certain period of time

When the bin-log mode is set to row, not only logs grow faster and also view the execution time sql little trouble:

binlog_format = row

1. Interference statement more;

2 generated sql code to be decoded.

Mysqlbinlog directly out of the file to perform sql sql displayed as part of the base64-encoded format, when the solid generated sql recording can not be used to generate the conventional way with the corresponding parameters need to show the sql statement

--base64-output = decode-rows -v

--start-date = 'Start Time'

--stop-date = 'end of time'


/ Opt / mysql / bin / mysqlbinlog --base64-output = decode-rows -v --start-date = '2014-09-16 14:00:00' --stop-date = '2014-09-16 14 : 20: 00 '/opt/mysql/log/mysql-bin.000017> /opt/mysql_bak/mysqlbinlogsql_restore_2014091614.s
- SQL in the specific internal Oracle process (Database)
- Getting Started with Linux system to learn: how to use tcpdump to capture TCP SYN, ACK and FIN packets (Linux)
- C ++ hash function (Programming)
- Swift used in the application to add a local push at the specified time (Programming)
- PHP interview questions of design patterns (Programming)
- How to download video youtube-dl in Linux (Linux)
- Oracle in the add & split partition on the impact of global & local index (Database)
- Bash mathematical extension (Programming)
- Linux kernel RCU (Read Copy Update) lock Brief - prequel (Linux)
- Ubuntu 14.04 install the NVIDIA driver + CUDA + MATLAB (Linux)
- Installation Flow N Play- interface stylized video player on Ubuntu (Linux)
- Understanding the Linux load average on other UNIX-Like systems (Linux)
- Linux (Debian) install software, missing dynamic link libraries .so (Linux)
- MongoDB uses aggregate, group, match mysql achieve in having (count (1)> 1) features (Database)
- CentOS yum install LNMP PHP5.4 version (Server)
- Polymorphism of the C ++ compiler and run-time polymorphism (Programming)
- CentOS yum source configuration (Linux)
- Gitlab installation under CentOS 7 (Linux)
- Create your own YUM repository (Linux)
- Transfer files and permissions from Windows to Linux system by Samba (Linux)
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.