MySQL full backup, incremental backup and recovery instance

Scenario: Perform a full backup every Sunday and perform an incremental backup 1 o'clock in the afternoon every day

Configuration

The prerequisite for performing an incremental backup is that MySQL opens the log-bin log switch, for example in My.ini or my.cnf

Join

Log-bin=/opt/data/mysql-bin

The string after "log-bin=" is a log chronicle directory, and is generally recommended on a disk that is different from the MySQL data directory.

[Full backup]

It is assumed that a full backup is performed at Sunday 1 o'clock in the afternoon for the MyISAM storage engine.
Mysqldump–lock-all-tables–flush-logs–master-data=2-u root-p Test > Backup_sunday_1_pm.sql

For InnoDB to replace –lock-all-tables with –single-transaction
–flush-logs to end the current log, generate a new log file
The –master-data=2 option will record the name of the new log file after the full backup in the output SQL.
For future recovery purposes, such as the output of the backup SQL file contains:
–change MASTER to Master_log_file= ' mysql-bin.000002′, master_log_pos=106;
--master-data[=value]
Write the binary log filename and position to the output. This option requires the RELOAD privilege and the binary log must to be
Enabled. If the option value is equal to 1, the position and filename are written to the dump output in the form of a change MASTER
Statement. If The dump is from a master server and with it to set up a slave server, the change master statement causes the
Slave to start of the correct position in the Masters binary logs. If the option value is equal to 2, the change MASTER
Statement is written as a SQL comment. (This is the default action if value is omitted.)
Other Notes:

If Mysqldump plus –delete-master-logs clears the previous log to free up space. However, if the server is configured as a mirrored replication master server, it is dangerous to delete the MySQL binary log with Mysqldump–delete-master-logs because the contents of the binary log may not have been fully processed from the server. In this case, it is more secure to use the PURGE MASTER logs.

[Incremental backup]

Use Mysqladmin flush-logs daily to create a new log and end the previous log write process. And
To back up the previous log, for example, in the previous example, start saving the log file in the Data directory mysql-bin.000002 ...

[Recover from backup]
* Restore full backup

Mysql-u Root-p < Backup_sunday_1_pm.sql

* Restore Incremental backups
Mysqlbinlog mysql-bin.000002 ... | Mysql-u root-p
Note that this recovery process will also be written to the log file, if the volume of data is very large, we recommend that the log function first

Binlog Incremental backup requires attention

1. When you use a temporary table, the recovery binlog need to be aware that you can only recover data from all Binlog files in one session. Because a temporary table exists only in a single session time

---If you use the transaction data for a temporary table to span multiple binlog files, you need to recover the multiple Binlog files with one session

2. For the 5.0 version, it is important to note that the statement Binlog mode pairs CREATE TABLE B select * from A; Only statements, not data, can be logged. If Table A is deleted, it would be troublesome to recover the data from table B.

---if it's statement based Binlog, you're going to have to recover form A and then resume B.

See my example of a company

1. To useTemporary Tables, recovery binlog need to be aware that you can only recover data from all Binlog files in one session. Because a temporary table exists only in a single session time

2. For5.0Version of that must be notedStatement Binlog modeTo create TABLE B select * from A; Only statements, not data, can be logged. If Table A is deleted, it would be troublesome to recover the data from table B.

# # #刚开始binlog_format为row
CREATE TABLE tbl_a (a int)
/*!*/;
# at 195
#100422 23:23:20 Server ID 1 end_log_pos 263 Query thread_id=1 Exec_tim
E=0 error_code=0
SET timestamp=1271949800/*!*/;
BEGIN
/*!*/;
# at 263
# at 307
#100422 23:23:20 Server ID 1 end_log_pos 307 table_map: ' Test '. ' Tbl_a ' mapped
To number 135
#100422 23:23:20 Server ID 1 end_log_pos 351 write_rows:table ID 135 Flags:
Stmt_end_f

Binlog '
6gnqsxmbaaaalaaaadmbaaaaaicaaaaaaaaabhrlc3qabxribf9haaedaae=
6gnqsxcbaaaalaaaaf8baaaqaicaaaaaaaeaaf/+aqaaap4caaaa/gmaaaa=
'/*!*/;
# at 351
#100422 23:23:20 Server ID 1 end_log_pos 420 Query thread_id=1 Exec_tim
E=0 error_code=0
SET timestamp=1271949800/*!*/;
COMMIT
/*!*/;
# at 420
#100422 23:24:43 Server ID 1 end_log_pos 488 Query thread_id=1 Exec_tim
E=0 error_code=0
SET timestamp=1271949883/*!*/;
BEGIN
/*!*/;
# at 488


# # #binlog_format没变 to create a table from the CREATE table select from. Under row_based mode. Creates a statement that becomes the first table and then inserts a value
#100422 23:24:43 Server ID 1 end_log_pos 602 Query thread_id=1 Exec_tim
E=0 error_code=0
SET timestamp=1271949883/*!*/;
CREATE TABLE ' Tbl_b ' (
' A ' int (one) DEFAULT NULL
)
/*!*/;
# at 602
# at 646
#100422 23:24:43 Server ID 1 end_log_pos 646 table_map: ' Test '. ' Tbl_b ' mapped
To number 136
#100422 23:24:43 Server ID 1 end_log_pos 690 write_rows:table ID 136 Flags:
Stmt_end_f

Binlog '
O2rqsxmbaaaalaaaaiycaaaaaigaaaaaaaaabhrlc3qabxribf9iaaedaae=
O2rqsxcbaaaalaaaalicaaaqaigaaaaaaaeaaf/+aqaaap4caaaa/gmaaaa=
'/*!*/;
# at 690
#100422 23:24:43 Server ID 1 end_log_pos 759 Query thread_id=1 Exec_tim
E=0 error_code=0
SET timestamp=1271949883/*!*/;
COMMIT
/*!*/;

# # #接着把session的binlog_format设置成statement to create a table from the CREATE table select from. To create TABLE B select * from A; Only statements, not data, can be logged
# at 759
#100422 23:27:57 Server ID 1 end_log_pos 860 Query thread_id=1 Exec_tim
E=1 error_code=0
SET timestamp=1271950077/*!*/;
CREATE TABLE Tbl_c select * FROM Tbl_a
/*!*/;

# # #最后把session的binlog_format设置成mixed to create a table from the CREATE table select from. To create TABLE B select * from A; Only statements, not data, can be logged
# at 860
#100422 23:30:04 Server ID 1 end_log_pos 961 Query thread_id=1 Exec_tim
E=0 error_code=0
SET timestamp=1271950204/*!*/;
CREATE TABLE Tbl_d select * FROM Tbl_a
/*!*/;
DELIMITER;
# End of log file
ROLLBACK/* Added by Mysqlbinlog * *;
/*!50003 SET completion_type= @OLD_COMPLETION_TYPE * *;