| |
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:
* LOAD_FILE ()
* UUID ()
* USER ()
* FOUND_ROWS ()
* 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'
E.g:
/ 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 |
|
|
|