MySQL Full backup + Incremental backup + Full recovery

Brief introduction

1 • Concept of full and incremental backups
2 • Full backup with mysqldump
3 · Using Mysqldump for table structure backup
4 • Two methods of full backup recovery
5 • Incremental backup with flush logs
6 • Incremental Backup recovery
7 · Based on point-in-time and seat recovery
8 · MySQL Backup Ideas

Concepts of full and incremental backups

1 · MySQL Full backup: A backup of the entire database, the structure of the database and the structure of the file, saving the database at the time of backup completion, which is also the basis for incremental backups. Its advantages: Backup and recovery operation is simple, the disadvantage is that there is a lot of duplication of data, take up a lot of backup space, backup time is long.
2 · MySQL Incremental Backup: Files that were modified after the last full or incremental backup are backed up. Because the drawback of full backups consumes a lot of resources, the combination of incremental and full backups solves this shortcoming.
3 · MySQL is not a way to provide a direct incremental backup, but incremental backups can be implemented indirectly through MySQL's binary logs. Binary logs save all updates or possibly update the database, using flush logs to split the log so that each update can be identified in another log file.

Preparation before the experiment:

Since this involves the table of the database, a table has been created before that, as shown in:

Mysqldump full backup and recovery

1 • Full backup command format for one library:
mysqldump-uroot-p123123--databases KGC (library name) >/opt/kgc.sql (where to back up)
2 • Analog KGC The table in this library is missing.
mysql> Drop Tabale School;

3 · The tables in the database are missing and the command format of the recovery table is:
mysql-uroot-p123123 </opt/kgc.sql----(Restore the tables in the KGC library and import them where you backed up)
4 • Verify the recovery backup:
Mysql> Show tables;
Mysql> select * from school;

5 · Backing up all tables in the database
mysqldump-uroot-p123123--all-databases >/opt/all.sql----(add option to all library backups--all)

6 · Back up the table structure of a table in the library, note that this is just a backup table structure
mysqldump-uroot-p123123-d KGC (library name) school (table name) >/opt/desc.sql

MySQL Incremental Backup

1 • Incremental backups are backed up based on the modified data after a full backup, so there must be a full backup before doing an incremental backup. The above experiment has done a full backup, now you can do incremental points directly.
2 · You need to turn on the binary logging feature to perform an incremental MySQL backup. Need to include in the main profile "Mysqld" entry: Log-bin=mysql-bin such as:
[Email protected] ~]# VIM/ETC/MY.CNF

3 • Restart MySQL service, view binary log files
[Email protected] ~]# systemctl restart Mysqld.service

4 · Simulates adding data to a table and then just backing up the added data.
Mysql> INSERT into school (name, score) VALUES (' Cheng Long ', 79);

5 • Simulate failure due to personnel misoperation or other factors that cause the table to be deleted.
Use the command mysqladmin-uroot-p123123 flush-logs to generate a log file again, so that the next record for MySQL will be written in the new log file
mysql> drop table School;
Mysql> Show tables;

6 • Data recovery. Recovery ideas
(1) First need to restore the full backup
(2) Second, we need to know that all the actions we had before inserting the data were disciplined in the log file.
(3) Finally we need to redo the data that was inserted in the log file before recovering.
(4) This will completely restore the data from the previous table.

7 • Verify that the data is restored successfully.

8 • Recovery based on point-in-time and location
column, such as: need to insert two data into the database, but due to misoperation, the middle of the two inserted statements delete a piece of data, and this data should not be deleted, this time need to revert to the wrong operation, skip the deletion of this data this command, and then restore the subsequent correct operation.
(1) Split the log again:
[Email protected] data]# mysqladmin-uroot-p123123 flush-logs
(2) Analog misoperation, the process of adding data, mistakenly delete a piece of data, because the careless attention to delete the data also need to retain, continue to write data

9 · When you find that the table data is missing, you need to restore the deleted data in the table. Recovery ideas:
(1) Splitting the log again is the next operation attributed to another binary log file
(2) View the binary log files that need to be recovered, find the point in time or location that need to be recovered, and record
(3) If you are using point-in-time recovery, use the command:
--stop-datetime (stop at the point where this error has been done)
--start-datetime (and start at the next right point)
(4) If location-based recovery uses the command:
--stop-position---(the last seat that can be performed correctly)
--start-position---(the next seat that can be performed correctly)

10 · Use the following command to view the binary log files to find the right and wrong time and seat points:
[Email protected] data]# mysqlbinlog--no-defaults--base64-output=decode-rows-v mysql-bin.000003
Find the content such as:

11 • Simulate the loss of a table in a failed database and start recovering data to restore ideas:
(1) First full backup recovery
(2) Second to find the point of restoration or seat
(3) Start the recovery of the incremental backup seat point (here I choose the seat point recovery)
[Email protected] data]# mysqlbinlog--no-defaults--stop-position= ' 344 '/usr/local/mysql/data/mysql-bin.000003 | mysql-uroot-p123123---(Restore correct seat point)
[Email protected] data]# mysqlbinlog--no-defaults--start-position= ' 835 '/usr/local/mysql/data/mysql-bin.000003 | Mysql-uroot-p123123---(next correct seat point)

Summarize

1 · MySQL is backed up using the Mysqldump tool, which generates SQL script files
2 • Recover data using MySQL
3 • Backups can be backed up for the entire library, some libraries, or tables, and table structures.
4 • Incremental backups need to be backed up using split logs
5 • Incremental recovery needs to be performed according to the log file's sequence.
6 • Recover data more accurately with time-and seat-based recovery
7 · Large enterprises should do a weekly full-time, incremental backup every day; small and medium-sized enterprises should be fully prepared every day
8 · can be combined with periodic scheduled tasks for backup, scheduled backup. Time is mainly in the time period when the volume of business requirements is small.

MySQL Full backup + Incremental backup + Full recovery