Home PC Games Linux Windows Database Network Programming Server Mobile  
  Home \ Database \ MySQL to recover the data through binlog     - CentOS 6 adds disk quota limit (Linux)

- Yii2 Advanced Version Copy New Project Problem Solved (Programming)

- Linux operating process information (Linux)

- Ubuntu 15.10 installation and deployment Swift development environment (Linux)

- Installation of network monitoring ntopng under CentOS 6.4 (Linux)

- Install the Red Hat Container Development Kit on OSX (Server)

- Fedora 20, Fedora 19, CentOS 6 and RHEL6 users how to install Wine 1.7.15 (Linux)

- Redis Design and Implementation study notes (Database)

- Ubuntu deployed under regular tasks with crontab (Linux)

- secureCRT remote login Linux must first open the connection protocol (Linux)

- Linux user opens a number of adjustment processes (Linux)

- SSH port forwarding application (Server)

- Use the command line MySQL database backup and recovery (Database)

- On the design of Oracle database backup (Database)

- Linux operating system buffer overflow attacks Countermeasures (Linux)

- Linux server security - the web server configuration (Linux)

- Use NTFS-3G to mount NTFS partitions under Linux U disk and removable hard disk (Linux)

- Java how to achieve bubble sort the problem Arraylist (Programming)

- How to modify the Sublime in Tab four spaces (Linux)

- awk pattern matching (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
- Android realize RippleEffect water (Programming)
- C language files update in real time (Programming)
- Linux foreground to background process switch (Linux)
- CentOS 6.5 start ActiveMQ being given to solve (Server)
- Revive Adserver ad server installation on Ubuntu 15.04 / CentOS7 (Server)
- GitHub multiplayer co-development configuration (Linux)
- Oracle 11g users to create, authorize and import dmp file (Database)
- Relationship between Linux permissions with the command (Linux)
- Ubuntu install Eclipse can not find JAVA_HOME problem (Linux)
- Linux system monitoring tool set cpu (Linux)
- How to configure Proftpd server on Fedora 22 (Server)
- STL spatial Configurator (Programming)
- How to clean up your Ubuntu 14.10 / 14.04 / 13.10 system (Linux)
- IDS Intrusion Detection System built (Linux) (Linux)
- C language programming entry - Common operators (Programming)
- Extended VMware Ubuntu root partition size (Linux)
- PostgreSQL Source Customization: Online global read only (Database)
- SQL Server 2012 failover looksalive check and is alive check (Database)
- Linux kernel TCP / IP parameters analysis and tuning (Linux)
- Infinispan 8 new Redis cache storage implementation (Linux)
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.