通过Mysql-bin日志恢复还原数据
提取出来后，里面就是所有对terminfo的sql语句了，把这些数据导入到测试库terminfo0730表中
事情是这样的：由于个人粗心，在7月30号那天协助其它部门批量更新一些数据，谁知道全局更新了，而这个问题竟然在9月26号才发现告知我。他们要求把更新有误的数据恢复到7月30号之前状态，并且7月30号到9月26号这段时间所做的增删改的操作也要更新进去。由于之前没啥经验，心里也没底，但是没办法，自己做错事自己承担。
做法思路：把备份的数据导到测试库里面去，然后把7月30号到9月26号之间的binlog日志提取出对这个表进行操作的sql语句，然后再导进去。
苦逼的还原过程开始了.........
1.幸好本人养成了个好习惯，无论改动的大小我都会先备份一份数据
-rw-r--r-- 1 root root 2473664 07-30 09:38 terminfo-bak0730.sql
找到了，果然是7月30号早上09点38分左右备份的，幸好有备份啊，要不然就悲催了.......先把备份的导到测试数据库上，表名改为terminfo0730，然后再把当前生产的数据导到，表名改为terminfo0926，这样的做法是在还原数据后匹配一下数据有没有对得上。
+----------------+
2.最重要的一步来了，，就是提取binlog日志。因为7月30号备份的，所以要找7月30号之后到9月26号的binlog。
利用mysqlbinlog命令先进行第一轮的sql语句提取
#mysqlbinlog --no-defaults --database=ecard --start-datetime='2012-07-30 09:38:00' mysql-bin.000086 > log86.txt # 这里要设置起始时间
#mysqlbinlog --no-defaults --database=ecard mysql-bin.000087 > log87.txt
#mysqlbinlog --no-defaults --database=ecard mysql-bin.000088 > log88.txt
#mysqlbinlog --no-defaults --database=ecard mysql-bin.000089 > log89.txt
#mysqlbinlog --no-defaults --database=ecard mysql-bin.000090 > log90.txt
#ls -l
提取出来后是全部的sql语句，而我需要的是只对terminfo操作的sql语句，所以要进行第二轮提取
#grep terminfo log86.txt > log86-terminfo.txt #依次grep出来
提取出来后，里面就是所有对terminfo的sql语句了，把这些数据导入到测试库terminfo0730表中
mysql > source log86-terminfo.txt; #依次source进去，务必要注意顺序问题！！！
导进去之后再比较一下terminfo0730和terminfo0926表的数据数量有没有一样，然后再叫部门同事验证一下数据正确性。
到此，数据成功恢复还原。一次难忘的经历啊，也同时告诫自己，细心细心再细心！！
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
How to use MySQL's binlog, redo log and undo log
Jun 03, 2023 pm 12:59 PM
1. Binlog Binlog is used to record write operations (excluding queries) information performed by the database and save it on the disk in binary form. Binlog is the logical log of mysql and is recorded by the server layer. Mysql databases using any storage engine will record binlog logs. Logical log: can be simply understood as a sql statement; physical log: data in MySQL is stored in the data page, and the physical log records changes on the data page; insert the code piece here and the binlog is written by appending Input, you can set the size of each binlog file through the max_binlog_size parameter. When the file size reaches the given value
What are the differences between redo log and binlog in MySQL?
Jun 03, 2023 pm 06:53 PM
Preface There are six types of log files in MySQL, namely: redo log (redolog), rollback log (undolog), binary log (binlog), error log (errorlog), slow query log (slowquerylog), general query log (generallog) ), relay log (relaylog). 1. What is redolog? Redolog, also known as redo log file, is used to record changes in transaction operations. It records the value after data modification. It will be recorded regardless of whether the transaction is submitted or not. Redolog files can come in handy when instances and media fail (mediafailure), such as database power outage, Inn
How to backup script for binlog in MySQL
Jun 03, 2023 pm 06:53 PM
Regarding the MySQL binary log (binlog), we all know that the binary log (binlog) is very important, especially when you need point-to-point disaster recovery, so we need to back it up. Regarding the backup of binary log (binlog), you can first switch the binlog based on the flushlogs method, and then copy & compress it to other storage on the remote server or local server, such as mounted NAS storage. You can also use mysqlbinlog to backup the binlog. Implement local backup or remote backup of MySQL binary log (binlog). Finally, the MySQL binary log (binlog
What are MySQL Binlog logs and master-slave replication?
May 27, 2023 pm 08:40 PM
1. Introduction to Binlog log Binlog is the abbreviation of Binarylog, that is, binary log. Binlog has three main functions: converting random IO into sequential IO during persistence, master-slave replication and data recovery. This article focuses on issues related to master-slave replication. The Binlog log consists of an index file and many log files. Each log file consists of a magic number and an event. Each log file ends with a Rotate type event. For each event, it can be divided into two parts: event header and event body: The structure of the event header is as follows: The structure of the event body includes two parts: fixed size and variable size. For the format of Binlog log, you can have a simple understanding. Interested students can go deeper.
What is the difference between slow commit in MySQL slow query and slow transaction in binlog?
May 30, 2023 am 08:07 AM
1. Source of the problem When analyzing performance problems, slow queries and binlog slow transactions are commonly used methods. Recently, I was analyzing a slow query and found that it contained a large number of commit statements that were slow, but the matching could not be completed when analyzing the binlog slow transactions. For example, there may be 1,000 commit statements during this period, but there may be only 100 slow transactions. This is too big a difference, so why does this phenomenon occur? 2. The respective determination methods for slow transactions are usually as follows for an explicitly submitted (insert) transaction: GTID_LOG_EVENT and XID_EVENT are the time when the command ‘COMMIT’ is initiated.
How to use docker to enable mysql binlog to solve data volume problems
May 27, 2023 pm 01:34 PM
Preface During development, it is necessary to monitor the data table by monitoring the binlog log file of mysql. Since mysql is deployed in a docker container, the problem of data volumes also needs to be solved. 1. Open a mysql image through the data volume dockerrun- p3307:3306--namemyMysql-v/usr/docker/mysql/data:/var/lib/mysql-eMYSQL_ROOT_PASSWORD=123456-dmysql:5.7.25 Note: You need to create a file in the host directory in advance to save mysql Data set, the directory I created here is /u
How to use mysql binlog to restore data
May 31, 2023 am 08:40 AM
If you want to restore data through mysql's binlog, you must first enable binlog. Set up a test environment here to learn how mysqlbinlog restores the database. The principle is relatively simple. Binlog will store changed data in mysql. For example, if you create a database and write some data, these will be stored in the binlog of mysql. When you need to recover, find two positions, a starting position and an ending position. Half of the ending position is the position before the data was destroyed or deleted. mysql8 has binlogmysql>showvariableslike'%l enabled by default
What is the difference between binlog/redolog/undolog in MySQL?
May 27, 2023 am 08:29 AM
What is the difference between MySQLbinlog/redolog/undolog? If I want to talk to you about the locking mechanism in InnoDB, it will inevitably involve the MySQL logging system, binlog, redolog, undolog, etc. I saw that these three logs summarized by some friends are not bad, so I quickly brought them to discuss with you. Share with partners. The log is an important part of the MySQL database, recording various status information during the operation of the database. MySQL logs mainly include error logs, query logs, slow query logs, transaction logs, and binary logs. As a developer, what we need to focus on is the binary log (binlog) and transaction log (including redolog and und
