1, Background
when we use the database, we will inevitably encounter the situation that the data is deleted by mistake, which may be business operation errors and code bug s; It may also be an operation and maintenance error, etc; However, we can divide all operations into the following categories:
- Data loss due to DML
- Data loss due to DDL
- Data file corrupted
2, Data loss due to DML
first, data loss caused by DML
2.1 binlog flashback
use the reverse parsing BINLOG tool to recover: such as binlog2sql, MyFlash, my2sql, etc
For details, please refer to my other articles:
binlog2sql,MyFlash:
https://xuehui.blog.csdn.net/article/details/85067775
my2sql:
https://xuehui.blog.csdn.net/article/details/114819239
3, Data loss / data file corruption caused by DDL
3.1 full backup + binlog
mysql has two full backup schemes: logical backup, mysqldump, mydumper, and physical backup xtrabackup ； However, for the full backup scenario, it is recommended to use physical backup xtrabackup and mysqldump to back up the core table; Add binlog to achieve full + incremental recovery at the specified point in time
The emergency plan can quickly restore some tables to the specified time point https://xuehui.blog.csdn.net/article/details/108102998
3.2 delayed slave storage [not commonly used, low cost performance]
mysql supports deferred slave Library: Add option CHANGE MASTER TO MASTER_DELAY = N； The instance will always be delayed by N seconds;
in case of data loss, recover to the specified location in the following ways:
UNTIL {
#Stop until the specified GTID position
SQL_BEFORE_GTIDS | SQL_AFTER_GTIDS} = gtid_set
#Stop until the specified binlog location
MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos
#Stop until the specified relay log location
RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos
#Stop until there is no delay gap before multiple parallel threads on the slave
#Because of multi-threaded replication, the replication progress of different threads is different, so there is a gap
SQL_AFTER_MTS_GAPS }
When we have neither backup nor delayed from the library, then data recovery will draw a question mark, because the remaining means can not guarantee 100% data recovery integrity and certainty according to the actual situation
3.3 innodb_force_recovery
#Configure InnoDB level by level_ force_ recovery
#mysqldump export data
#Backup and then delete ibdata1, ib_logfile0,ib_logfile1, undo file
#Comment out the configuration in step 1: innodb_force_recovery
#Restart database
#Log in to mysql, rebuild the database and import backup
innodb_ force_ Six settings for recovery:
1 (SRV_FORCE_IGNORE_CORRUPT): ignore the checked corrupt page. Force the service to run even though a corrupted page is detected. It is generally set to this value, and then the dump issue table is rebuilt. This option is particularly useful for backing up or transferring current data
2 (srv_force_no_backup group): prevent the main thread from running. If the main thread needs to perform a full purge operation, it will cause a crash. Prevent master thread and any purge thread from running. This value is used if the crash occurs in the purge phase. Prevent the main thread from running again. If it is cleared, the server will hang up
3 (SRV_FORCE_NO_TRX_UNDO): transaction rollback is not performed.
4 (SRV_FORCE_NO_IBUF_MERGE): do not perform the merge operation of inserting buffer. Do not do this, which may cause a crash. Do not perform statistical operations. This value may permanently damage the data file. If this value is used, the secondary index will be deleted and rebuilt in the future. If the merge operation inserted into the buffer will cause the system to crash, it will not be executed
5 (SRV_FORCE_NO_UNDO_LOG_SCAN): the InnoDB storage engine will treat uncommitted transactions as committed without viewing the redo log. At this time, InnoDB even handles unfinished transactions as committed. This value may permanently damage the data file.
6 (SRV_FORCE_NO_LOG_REDO): redo log roll forward is not performed during recovery. Making database pages obsolete may cause more damage to the B-tree or other database structures.
3.4 fd recovery
When the data file is physically deleted, if the database is not closed at this time, it is not actually deleted according to the principle, but only marked with a delete flag. You can try to recover through the file in / proc/$pid/fd. The steps are as follows:
#Test table root@mysql 22:52: [sbtest]> select * from t1; +------+ | id | +------+ | 1 | | 2 | | 2 | +------+ 3 rows in set (0.00 sec) #Confirm mysql pid [root@iZbp1ir1m7i4bke4pg4fcxZ ~]# ps -ef | grep -w mysqld | grep -v grep mysql 903 12825 0 2021 ? 00:03:03 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysql/mysql_data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/logs/error.log --open-files-limit=65535 --pid-file=mysql.pid --socket=/data/mysql/mysql_tmp/mysql.sock --port=3306 #Simulated false deletion [root@iZbp1ir1m7i4bke4pg4fcxZ sbtest]# rm -f /data/mysql/mysql_data/sbtest/t1.ibd #The file deleted by mistake was found [root@iZbp1ir1m7i4bke4pg4fcxZ ~]# ll /proc/903/fd/ | grep delete lrwx------ 1 root root 64 Dec 31 00:49 13 -> /data/mysql/mysql_tmp/ibXUqQQZ (deleted) lrwx------ 1 root root 64 Dec 31 00:49 40 -> /data/mysql/mysql_data/sbtest/t1.ibd (deleted) lrwx------ 1 root root 64 Dec 31 00:49 6 -> /data/mysql/mysql_tmp/ibKYQhVM (deleted) lrwx------ 1 root root 64 Dec 31 00:49 7 -> /data/mysql/mysql_tmp/ibskO54z (deleted) #At this time, the data table can still be read. Lock the file, copy it back to the data directory and enable it root@mysql 22:52: [sbtest]> select * from t1; +------+ | id | +------+ | 1 | | 2 | | 2 | +------+ 3 rows in set (0.00 sec) root@mysql 22:55: [sbtest]> lock tables t1 write; Query OK, 0 rows affected (0.00 sec) [root@iZbp1ir1m7i4bke4pg4fcxZ ~]# cp /proc/903/fd/40 /data/mysql/mysql_data/sbtest/t1.ibd [root@iZbp1ir1m7i4bke4pg4fcxZ ~]# chown -R mysql.mysql /data/mysql/mysql_data/sbtest/t1.ibd #Restart the database and the recovery is complete
3.5 recovery of third-party tools
3.5.1 extundelete(ext4)
for ext4 file system, you can use the extundelete tool to recover data on linux.
extundelete /dev/vda1 --inode 2 see vda1 Files and folders that can be recovered under the partition root directory extundelete /dev/vda1 --restore-inode 12 Restore correspondence inode File extundelete /dev/vda1 --restore-file filename Restore a single file extundelete /dev/vda1 --restore-directory Restore directory. Empty directory will not be restored extundelete /dev/vda1 --restore-all Recover all files
3.5.2 undrop-for-innodb
undo for innodb is a set of data recovery tools for innodb. It can recover from the file level, such as DROP/TRUNCATE table, deleting some records in the table, innodb file being deleted, file system damage, disk corruption, etc
Table structure recovery
#stream_parser analyzes ibd / ibdata files (or attached disks) ./stream_parser -f /data/mysql/mysql_data/ibdata1 #c_ The parser analyzes the user's row data in the page $./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t dictionary/SYS_TABLES.sql ./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t dictionary/SYS_INDEXES.sql ./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000002.page -t dictionary/SYS_COLUMNS.sql ./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000004.page -t dictionary/SYS_FIELDS.sql #sys_parser restores the CREATE TABLE statement ./sys_parser -h 127.0.0.1 -u root -P 56160 -d recover sakila/actor
data recovery
#stream_parser analyzes ibd / ibdata files (or attached disks) ./stream_parser -f /data/mysql/mysql_data/ibdata1 ./stream_parser -f /dev/vda1 -s 1G -t 50G
5, Summary
1. Principle of operation and maintenance automation and minimization of business account authority
2. Avoid secondary data pollution after wrong operation
3. Backup data and binlog;
4. The specification uses binlog: binlog_row_image=FULL,binlog_format=ROW； The retention time can be fully connected