Mysql uses binlog to restore Data

Mysql uses binlog to restore Data
I. What is binlog 1? basic definition of binlog: Binary logs are also binary logs that record SQL statements that occur or potentially change data, binary logs are stored in the disk in binary format. binary log information: File Location: the default storage location is the file naming method in the directory where the database files are located: name is hostname-bin.xxxxx (restart mysql will automatically generate a new binlog) 2. Configure binlog, in the configuration file my. set in cnf and restart mysql3. view the status: mysql> show variables like '% log_bin %'; 2. Three formats of mysql binlog (Statement, MiXED, ROW) 1. statement: Each SQL Statement that modifies data is recorded in the binlog. Advantage: you do not need to record changes in each row, which reduces the binlog volume, saves IO, and improves performance. (The performance and log volume can be reduced compared to the row, which depends on the application's SQL condition. The log volume generated by modifying the same record normally or inserting the row format is smaller than the log volume generated by Statement, however, considering the conditional update operations, as well as the entire table deletion and alter TABLE operations, the ROW format will generate a large number of logs, therefore, when considering whether to use the ROW format log, it should be based on the actual situation of the application, the amount of logs generated will increase, and the IO performance problems .) Disadvantage: Because only execution statements are recorded, in order that these statements can run correctly on slave, some related information of each statement must be recorded during execution, to ensure that all the statements can get the same results as the execution on the master end in slave. In addition, mysql replication, such as some specific function functions, slave can be consistent with the master, there will be many related problems (such as sleep () function, last_insert_id (), and user-defined functions (udf) problems ). the statements using the following functions cannot be copied: * LOAD_FILE () * UUID () * USER () * FOUND_ROWS () * SYSDATE () (unless the -- sysdate-is-now option is enabled at startup... SELECT generates more row-level locks than RBR 2.Row: does not record context-related information of SQL statements. Only the records that are saved are modified. Advantage: binlog does not record the context-related information of the executed SQL statement. You only need to record what the record is modified. Therefore, rowlevel logs Clearly record the details of each row of data modification. In addition, there will be no storage procedures, functions, and trigger calls and triggers that cannot be correctly copied in certain situations. Disadvantages: When all executed statements are recorded in logs, all records will be modified by the record in each row, which may produce a large amount of log content, such as an update statement. If multiple records are modified, each modification to the binlog will have a record, this results in a large amount of binlog logs. Especially when statements such as alter table are executed, each record is changed due to table structure modification, each record of the table is recorded in the log. 3. mixedlevel: mixed use of the above two levels. For general statement modification, binlog is saved in statment format. For example, if statement cannot complete master-slave replication, binlog is saved in row format, mySQL will differentiate the log format of the Record Based on each specific SQL Statement executed, that is, select one between Statement and Row. the row level mode of the new MySQL squadron is also optimized. Not all modifications are recorded by row level, as if the table structure is changed, it will be recorded in statement mode. For statements that modify data such as update or delete, changes to all rows will be recorded. 3. Online instance 1. instance Introduction: the DB machine suddenly fails due to hardware faults, it was not successful to start the system in an emergency. After the machine was restarted repeatedly, mysql DATA was damaged and it was still an online machine. There was no way to urgently maintain the online business, I used mysqldump to copy and replace the data on the machine. I didn't expect the data to be damaged, but the backup was not successful and the data could not be extracted. I suddenly thought that when I started it, I opened the binlog, I can extract data through binlog. 2. Restore data by transmitting binlog to a running machine, recover/app/mysql/bin/mysqlbinlog bin-log.xxx through binlog |/app/mysql/bin/mysql 3. query the SQL statement executed in a certain period of time through binlog When bin-log Mode when set to row, not only does the log grow fast, but it is a little troublesome to view the executed SQL: binlog _ Format = row 1. There are many interference statements; 2. the encoding of the generated SQL statement needs to be decoded. The SQL statement in the SQL statement executed by mysqlbinlog is displayed in base64 encoding format, when the SQL record is fixed, you cannot use the conventional method to generate it. You need to add the corresponding parameters to display the SQL statement -- base64-output = decode-rows-v -- start-date = 'start time' -- stop-date = 'end time' for example: /opt/mysql/bin/mysqlbinlog -- base64-output = decode-rows-v -- start-date = '2017-09-16 14:00:00 '-- stop-date = '2017-09-16 14:20:00 '/opt/mysql/log/mysql-bin.000017>/opt/mysql_bak/mysqlbinlogsql_restore_2014091614. SQL