“This is the 10th day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021.”

I believe that many students in the face of online database are afraid of hands and feet, even so are hard to avoid hand slip, accidentally a shake will be data or tables, library deleted. Of course, some normative companies will not give developers the permission to delete tables or libraries, but it is common to delete data by mistake, so this happens, what should we do? Run? Ha ha, of course, delete library run is a joke, this article for you to introduce some data error deletion recovery methods.

1 introduction

In a production environment, dbAs regularly generate full backups of data and enable binlog to record incremental data. During recovery, data can be restored to a large extent with the help of data backup and binlog. Of course, in general, developers do not have the permission to delete libraries, but generally have the permission to delete data. So we can’t panic when we meet this kind of emergency, we should try to remedy it.

2 the backup

The simplest and most practical way to do this is to back up your data whenever you need to clean it up or modify it. Backup is king. This will make our data recovery much easier. Typically in an enterprise, DBAs have backup scripts that periodically back up data over time to prevent tragedies.

3 Standard Operation

  1. Before operation, first backup, do not be afraid of trouble, error after regret at the beginning;
  2. When you drop a database or table, do not use the drop command. Instead, rename it to a dedicated archive.
  3. When deleting data, do not run the delete or TRUNCate command. In particular, the TRUNCate command does not support transactions and cannot be rolled back.
  4. When using the delete command to delete data, you should enable the transaction first, so that there is still a chance to roll back the data in case of misoperation.
  5. When you want to delete a lot of data, you can insert the data into a new table and delete it after confirming that it is correct. Or write the data to a new table, and rename the table to drop.

4 Basic restoration process

  • See if there’s a quick fix (see next item if there isn’t)
  • Check to see if there are regular backups, and binlog (if not, cool it)
  • Restore the backup data first
  • Use the mysqlbinlog command to export the above binlog file as an SQL file and discard the DROP statement
  • Restore the incremental data portion of the binlog

5 Remedial measures

  1. First consider whether you can restore only through binlog, if not, then consider others
  2. When you run the DROP DATABASE/DROP TABLE command to delete a DATABASE TABLE by mistake, if the DATABASE TABLE is in the shared tablespace mode, there is still a recovery opportunity. If not, simply restore from the backup file. In shared tablespace mode, kill -9 processes related to mysql (mysqLD_safe, mysqld) and try to restore data from the ibdataX file.
  3. MySQL > alter table ibd or ibdataX Take advantage of the proc file characteristics of Linux, copy the IBD file from memory and restore it, because the mysqld instance is kept open in memory at this time, remember not to close mysqld instance at this time. To restore the mode, stop online services from writing new data to the instance to prevent new data loss. Innodb_force_recovery test innodb_force_recovery step by step from 0 to 6 until all data of the entire instance or single table can be backed up, and then rebuild the instance or single table. Restore data.
  4. If the transaction mode is not enabled, data is mistakenly deleted by the delete command. As soon as you discover the severity of the problem, kill the mysqld (and mysqLD_safe) processes (kill -9) and use the tools to read the tablespace data. After the delete command is executed, actual data is not deleted from the disk but is labeled with deleted-mark before being deleted in a unified manner. Therefore, rapid process killing can prevent data from being physically deleted.
  5. Run truncATE to clear the entire table. If the shared tablespace mode is not used, use backup restore and binlog restore directly.
  6. Perform an UPDATE without a WHERE condition, or update wrong data. If the data size is too large to be remedied, it can only be restored through backup and binlog.

6 Related Operations

  1. Check whether binlog is enabled

    If # log_bin is ON, then OFF is OFF. show variables like 'log_bin'; Show variables like '%log_bin%'; SET SQL_LOG_BIN=1Copy the code

  2. Binlog Indicates the log location

    show variables like '%datadir%';
Copy the code

  3. Restore data based on binlog logs

    • CD to the binlog file directory

    • Log parsing tool mysqlbinlog can be found in mysql/ mysql/bin/

    • Run the mysqlBinlog tool command to parse the binlog log content according to the corresponding time and output it to a new file

      The tool also supports filtering records related to specific tables

      mysqlbinlog --no-defaults --database=test --start-datetime="2021-11-10 09:00:00" --stop-datetime="2021-11-10 20:00:00" /data/mysql/mysql-bin.000020    > binlog.txt
Copy the code

    • Use the parsed SQL for recovery or according to the location of the recovery, using commands for recovery

      mysqlbinlog --start-position=8000 --stop-position=8888 mysql-bin.000020 |mysql -uroot -p123456;
Copy the code

  4. Configure binlog logs through the configuration file

    In Statement mode, every SQL Statement that modifies data will be recorded in a binlog. # Row mode, which only started to be supported in MySQL 5.1.5, does not record information about the context of SQL statements, only which records were modified. For example, for some functions, statement cannot complete the master/slave copy operation, so row format is used to save the binlog. MySQL will use different types of logs for each SQL statement executed. That is, choose between Statement and Row. Binlog_format = mixed # set log path, Log-bin = /data/mysql/logs/mysql-bin.log # expire_logs_days = 7 # binlog Size of each log file Max_binlog_size = 100m # Binlog cache size binlog_cache_size = 4m # Max_binlog_cache_size = 512MBCopy the code

Everybody big guy, delete library please careful!!