Mysql uses binlog to restore data _ MySQL

1. what is binlog1 and basic definition of binlog: binary logs are also binary logs that record SQL statements that have occurred or may change data and are saved in binary format on the disk; binary log information: file location: the default storage location is the database file. 1. what is binlog?

1. basic definition of binlog: binary logs are also binary logs that record SQL statements that occur or potentially change data and are saved in the disk in binary format;

Binary log information:

File location: the default storage location is under the Directory where the database files are located.

File naming: Name is hostname-bin.xxxxx (a new binlog is automatically generated once mysql is restarted)

2. configure binlog, set it in the configuration file my. cnf, and restart mysql.

3. view the status: mysql> show variables like '% log_bin % ';

II. three formats of mysql binlog (Statement, MiXED, and 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)

At the same time, INSERT... SELECT will generate more row-level locks than RBR.

2. Row: The context information of the SQL statement is not recorded. only the record that is saved is 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 problems in certain situations where stored procedures, functions, trigger calls and triggers cannot be correctly copied.

Disadvantage: when all statements executed are recorded in the log, they are all recorded with modifications recorded in each line. This may produce a large amount of log content, such as an update statement, if you modify multiple records, each modification to the binlog will be recorded, resulting in a large amount of binlog logs. especially when you execute statements such as alter table, because of the table structure modification, if each record changes, each record in 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 are recorded.

III. online instances

1. instance Introduction: The database machine suddenly fails due to a hardware fault and fails to start up in an emergency. after restarting the machine repeatedly, mysql data is damaged and the database is still an online machine, there is no way to urgently maintain online services,

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 use binlog to extract data.

2. restore data

Transmit binlog to a normal machine and restore it through binlog.

/app/mysql/bin/mysqlbinlog bin-log.xxx | /app/mysql/bin/mysql

3. use binlog to query the SQL statements executed in a certain period of time

When the bin-log mode is set to row, it is a little troublesome not only when the log is fast but also when you view the executed SQL:

binlog_format=row

1. many interference statements;

2. the encoding of the generated SQL statement needs to be decoded.

The SQL statement of the SQL statement executed by mysqlbinlog file is displayed in base64 encoding format. when the SQL record is fixed, you cannot generate it in the conventional way. you need to add relevant 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='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.sql

The above is the mysql data recovery through binlog _ MySQL content, for more information, please follow the PHP Chinese network (www.php1.cn )!