Mysql database backup recovery (binlog-based incremental backup) _ MySQL

Mysql database backup and restoration (binlog-based incremental backup) 1 Overview

1.1 Introduction to incremental backup

Incremental Backup means that after a full backup or the last incremental backup, each subsequent backup only needs to back up files that are added or modified compared with the previous one. This means that the object for the first incremental backup is the files added and modified after full backup; the object of the second incremental backup is the added and modified files generated after the first incremental backup, and so on. The most significant advantage of this backup method is that there is no duplicate backup data, so the backup data volume is not large and the backup time is short. However, incremental backup data recovery is troublesome. You must have the last full backup and all incremental backup tapes (once one of them is lost or damaged, the recovery will fail ), they must be restored one by one in the chronological order from full backup to incremental backup, which greatly prolongs the recovery time.

If we have a database with 20 GB of data, we will increase the data by 10 MB every day, and the database will be backed up every day. in this case, the pressure on the server is relatively high, therefore, we only need to back up the added data to reduce the burden on the server.

1.2 binlog introduction

Binlog logs are enabled by the log-bin option of the configuration file, and the Mysql server creates two new file XXX-bin.001 and xxx-bin.index in the data root directory if the configuration option does not give a file name, mysql uses the host name to name these two files. the index file contains a list of all log files. Mysql records users' changes to the content and structure of all databases in the XXX-bin.n file without recording SELECT and UPDATE statements without actual updates.
When the MySQL database is stopped or restarted, the server will record the log file to the next log file, and Mysql will generate a new binlog log file at the time of restart, with the file serial number increasing. In addition, if the log file exceeds the upper limit configured by the max_binlog_size system variable, a new log file is generated.

2 binlog log operations

2.1 enable binlog

In my. cnf or my. ini, add

[mysqld]log-bin=mysql-binbinlog_format=mixed

Where: if log-bin does not display the specified storage directory, it is stored in the data directory of mysql by default.

Several formats of binlog_format: (STATEMENT, ROW, and MIXED)

STATEMENT: SQL statement-based replication (SBR)
ROW: row-based replication (RBR)
MIXED: mixed replication (MBR)

After mysql-bin. * is started, one or more files will be generated.

[root@localhost data]# ls -l| grep mysql-bin-rw-rw----. 1 mysql mysql       107 Jul  5 11:19 mysql-bin.000001-rw-rw----. 1 mysql mysql        19 Jul  5 11:19 mysql-bin.index[root@localhost data]# 

View binlog enabling status

mysql> show variables like 'log_bin%';+---------------------------------+-------+| Variable_name                   | Value |+---------------------------------+-------+| log_bin                         | ON    || log_bin_trust_function_creators | OFF   |+---------------------------------+-------+mysql> show variables like 'binlog%';+-----------------------------------------+-------+| Variable_name                           | Value |+-----------------------------------------+-------+| binlog_cache_size                       | 32768 || binlog_direct_non_transactional_updates | OFF   || binlog_format                           | MIXED || binlog_stmt_cache_size                  | 32768 |+-----------------------------------------+-------+

2.2 View binlog content

[root@localhost data]# mysqlbinlog /usr/local/mysql/data/mysql-bin.000001 ;mysqlbinlog: unknown variable 'default-character-set=utf8'

Here we have encountered a bug in mysqlbinlog. There are two solutions:

Method 1: Use the -- no-defaults option

[root@localhost data]# mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000001 

Method 2: Set my. the default-character-set = utf8 option in the [client] character group of cnf is temporarily blocked (this option takes effect immediately without restarting the database). it is restored after mysqlbinlog is used. Because the mysql configuration file my. cnf (my. ini in windows) will be re-read when you use the mysqlbinlog tool to view binary logs, rather than the configuration file that the server has loaded into the memory.

The output format is as follows:

# at 188#140705 11:23:55 server id 1  end_log_pos 271 Querythread_id=1exec_time=0error_code=0SET TIMESTAMP=1404573835/*!*/;create database test/*!*/;DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

It includes the following elements:

<关键字：binlog>
Reprinted please indicate the source: http://blog.csdn.net/jesseyoung/article/details/37106035