Home PC Games Linux Windows Database Network Programming Server Mobile  
  Home \ Database \ MySQL to recover the data through binlog     - How to create a cloud encrypted file system in Linux systems (Linux)

- Ubuntu 14.04 installed Nvidia CUDA 7.5 and build Python Theano deep learning development environment (Linux)

- Ubuntu system process is bound CPU core (Linux)

- Ubuntu 15.04 / CentOS 7.0 to set custom boot (Linux)

- Comparison of Nginx and Nginx + (Server)

- Oracle set and remove columns unavailable (Database)

- ORA-28000 the account is locked fault simulation (Database)

- Kickstart automated installation and deployment RHEL 7.0 (Linux)

- Install Websphere MB required system rpm package under Linux (Linux)

- OpenvSwitch 2.1.2 shell script to start and stop (Linux)

- MogileFS system installation configuration example (Server)

- Ubuntu use three methods to install Ruby (Linux)

- Switching Virtual Machine + Ubuntu graphical interface and terminal interface (Linux)

- C ++: Postmodern systems programming language (Programming)

- How to install Virtualbox 4.3.20 in Ubuntu 14.04 (Linux)

- The principle Httpclient4.4 (execution request) (Programming)

- Ubuntu 14.04 / Linux Mint 17 How to install the MintMenu 5.5.2 menu (Linux)

- sed command (Linux)

- Use Hexo quickly build and deploy a blog to Github (Server)

- Linux (SUSE) mount NTFS mobile hard practice (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
- Creating and extracting archives 11 tar command examples in Linux (Linux)
- C ++ multithreading and critical resource instance (Programming)
- Linux 101 hack book reading notes (Linux)
- Use PuTTY to access the virtual machine Linux under Windows xp (Programming)
- Java application server WildFly (Server)
- HTTP Client Hints Introduction (Server)
- RHEL6.4 x86_64 build SVN service (Server)
- Create several practical points of high security PHP site (Linux)
- VMware virtual machine operating system log Error in the RPC receive loop resolve (Linux)
- Use Python automatically cleared Android Engineering excess resources (Programming)
- Install Java on RHEL6 (Linux)
- CentOS 6.7 install Nagios Tutorials (Server)
- Use $ BASH ENV variable to mention the right way under Linux (Linux)
- CentOS 7 x64 compiler installation Tengine 2.0.3 Comments (Server)
- Oracle study notes view (Database)
- Python system default encoding (Programming)
- Linux System Getting Started Tutorial: How do you know Shell which is currently being used (Linux)
- Changes in C # asynchronous programming model (Programming)
- The difference Docker save and export commands (Linux)
- Get basic information about Linux server script (Server)
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.