Home PC Games Linux Windows Database Network Programming Server Mobile  
  Home \ Database \ MySQL to recover the data through binlog     - Linux (RHEL5) general optimization system security (Linux)

- Linux user groups, file permissions Detailed (Linux)

- When Vim create Python scripts, vim autocomplete interpreter and encoding method (Programming)

- Detailed reference Oracle 11g Partition (Database)

- Hibernate Performance Optimization of EHCache cache (Programming)

- The basic method RHEL6 (CentOS6) used in the source package compiled RPM: Upgrade OpenSSH articles (Linux)

- Linux5 install MySQL 5.6 (RPM mode) (Database)

- Ten SCP transfer command example (Linux)

- SQLite (Database)

- A drop datafile Oracle bug (Database)

- How to Install Xombrero 1.6.4 (minimalist Web browser) on Ubuntu and Archlinux (Linux)

- Use virt-manager to create and manage virtual machines (Linux)

- Android Studio Getting Started Hello World (Programming)

- Oracle metadata Reconstruction experiments (Database)

- Function Getting the Linux shell (Programming)

- How to monitor Linux system performance Nmon (Linux)

- Do not find ifconfig eth0 and IP address under CentOS6.5 (Linux)

- DRBD + Heartbeat solve NFS single point of failure (Server)

- Spark and Hadoop comparison (Server)

- RegExp object implements regular match --JavaScript (Programming)

  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
- Using IntelliJ IDEA 13 integrated development environment to build Android (Linux)
- 11 examples in Linux df command (Linux)
- Sysdig: system troubleshooting tool (Linux)
- Installation Elementary OS Freya to do some settings (Linux)
- Use dump restore mode fast backup and recovery system FreeBSD (Linux)
- Taught you how to build your own VPS server (Server)
- After Ubuntu Password Forgot your way back (Linux)
- Forgot Linux root password (Linux)
- Python Django model within the class meta Detailed (Programming)
- Element content of Java HashSet change issues (Programming)
- Use PuTTY key authentication mechanism for remote login Linux (Linux)
- 10 useful tools for Linux users (Linux)
- Setup Qt development environment under Linux (Linux)
- Install Java 8 on Ubuntu using PPA (Linux)
- How to build Memcached Docker container (Server)
- Linux daemon (Linux)
- Puppet centralized configuration management system (Server)
- MySQL optimization tabs (Database)
- Linux system security audit tools scan nessus installation tutorial (Linux)
- Use matplotlib scientific drawing in Linux (Linux)
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.