MySql 误操作回滚方法总结
drop 是直接删除表信息，速度最快，但是无法找回数据
truncate table
truncate 是删除表数据，不删除表的结构，速度排第二，但不能与where一起使用
delete from
delete 是删除表中的数据，不删除表结构，速度最慢，但可以与where连用，可以删除指定的行
效率：一般来说 drop > truncate> delete
是否删除表结构：truncate和delete 只删除数据不删除表结构，truncate 删除后将重建索引(新插入数据后id从0开始记起)，而 delete不会删除索引 (新插入的数据将在删除数据的索引后继续增加)，drop语句将删除表的结构包括依赖的约束，触发器，索引等。
drop和truncate删除时不记录MySQL日志，不能回滚，delete删除会记录MySQL日志，可以回滚。
DELETE 误删除回滚数据方法
登录mysql查看是否开启binlog
show variables like 'log_%';
ON: 开启状态，OFF:关闭状态
开启binlog：
修改my.cnf文件
在linux中可以通过命令查找文件位置
find / -name my.cnf
VIM /etc/my.cnf
在[mysqld]后面增加如下配置
server-id=1 -- 不能重复
log_bin=mysql-bin
binlog_format=ROW
expire_logs_days=10
max_binlog_size=100M
重启服务并验证
systemctl restart mysqld
重新查看是否开启binlog
show variables like 'log_%';
首先每次修改数据库之前先备份数据库
ysqldump -hlocalhost -uusername -pPassword --single-transaction --master-data=2 databasename > /home/sql/databasename_YYYYMMDD.sql
*注：默认备份文件会每个表生成一个DROP TABLE语句和CREATE TABLE 语句，以及数据INSERT语句
其中 CREATE TABLE 语句只会恢复表结构，不会恢复添加的索引。
加入 --no-create-info 参数 则每个表只会生成数据INSERT语句
加入 --skip-add-drop-table 参数 则每个表会生成CREATE TABLE 语句，以及数据INSERT语句
查看一下备份出来的文件所在时刻binlog日志的信息
-- Position to start replication or point-in-time recovery from
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=740;
此时查看一下数据库里binlog日志的位置
mysql> show master status \G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 6088
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
可以看到，在备份前和备份后，binlog日志并没有发生变化，还是停留在同一行里。这个Position和文件名被记录在了备份文件中，以后会用到。
然后开始操作数据库，直到发生误操作删除了数据
先停止数据库所有操作，还原之前的备份文件
mysql -hlocalhost -uusername -pPassword databasename < /home/sql/databasename_YYYYMMDD.sql
-- 此时可以不开启数据库的binlog，开了反而还会变慢。
从binlog里查找误操作语句时间点，并回滚该时间点前的数据
首先查看当前正在写入的日志文件名
show master status;
show master status \G
下载误操作时间段的binlog日志文件转为可读文件
/usr/local/mysql/bin/mysqlbinlog --no-defaults --base64-output=decode-rows -v --start-datetime="2022-11-01 17:18:00" --stop-datetime="2022-11-01 17:53:00" --database=databasename /usr/local/mysql/data/mysql-bin.000001 > /home/sql/log000001.sql
-- 如果不知道应该截取哪个时间段，就去除起止时间
查看log000001.sql文件，找到错误语句和position，时间点
# at 6105
# 221101 17:51:51 server id 1 end_log_pos 6105
### DELETE FROM `databasename`.`tablename`
类似这种就是position
手动指定binlog的重做时间点。
前面我们已经知道，从全备文件databasename_YYYYMMDD.sql中可以看到备份时间点的binlog文件和行数，也就是mysql-bin.000001的第6088行，所以我们就从这一行开始恢复
截止时间就是误操作（DELETE）语句的时间点之前的所有SQL语句。
/usr/local/mysql/bin/mysqlbinlog --no-defaults --start-position=6088 --stop-datetime="2022-11-01 17:51:50" /usr/local/mysql/data/mysql-bin.000001 | mysql =hlocalhost -uusername -pPassword
然后发现我们的恢复成功了
无备份情况下误操作数据恢复方法
前置条件：数据库开启binlog
详见上文开启binlog方法
误操作DELETE语句恢复数据方法
比如不小心执行了 DELETE FROM tablename;
不要慌，数据可以找回。
首先停止所有对数据库的操作，停止服务，使数据库处于静止状态
然后查看当前正在写入的日志文件名
show master status;
show master status \G
然后查看所有还没删除的日志文件名
show binary logs
使用binlog将日志中的DELETE操作语句下载为文本文件
/usr/local/mysql/bin/mysqlbinlog --no-defaults --base64-output=decode-rows -v -v /usr/local/mysql/data/mysql-bin.000001 |sed -n '/### DELETE FROM `databasename`.`tablename`/,/COMMIT/p' > /home/sql/table_delete.txt
*注意：如果日志太大需要时间段来锁定你得误操作语句的时间范围，可以使用参数 --start-datetime="2022-11-07 14:43:00" --stop-datetime="2022-11-07 15:00:00"
转换文本文件中的DELETE 语句为INSERT语句，生成数据恢复sql文件
cat /home/sql/table_delete.txt | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/WHERE/SELECT/g;' | sed ':N_R;N;s/\n/ /;b N_R' | sed 's/DELETE FROM/\n\nINSERT INTO/g;' | sed -r 's/(@9=\S*\s*),/\1;/g' | sed 's/@[1-9][0-9]*=//g' > /home/sql/recover_table.sql
*注意：其中的 sed -r 's/(@9=\S*\s*),/\1;/g'
这句中的@9，替换为你的这个表的字段总数：@你的表字段总数
然后执行这个数据恢复sql文件
mysql -hlocalhost -uusername -pPassword databasename < /home/sql/recover_table.sql >& /home/sql/error.log
执行完后查看是否报错
cat /home/sql/error.log
误操作UPDATE语句恢复数据方法
比如不小心执行了 UPDATE tablename SET COLUMN='';
不要慌，数据可以找回。
首先停止所有对数据库的操作，停止服务，使数据库处于静止状态
然后查看当前正在写入的日志文件名
show master status;
show master status \G
然后查看所有还没删除的日志文件名
show binary logs;
/usr/local/mysql/bin/mysqlbinlog --no-defaults --base64-output=decode-rows -v -v /usr/local/mysql/data/mysql-bin.000001 |sed -n '/### UPDATE `databasename`.`tablename`/,/COMMIT/p' > /home/sql/table_update.txt
*注意：如果日志太大需要时间段来锁定你得误操作语句的时间范围，可以使用参数 --start-datetime="2022-11-07 14:43:00" --stop-datetime="2022-11-07 15:00:00"
转换文本文件中的UPDATE 语句为更新前数据的UPDATE恢复语句，生成数据恢复sql文件
cat /home/sql/table_update.txt | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;' | sed ':N_R;N;s/\n/ /;b N_R' | sed -r 's/SET\s*@1=(\S*\s*),/W_HERE id=\1; -- /g' | sed 's/WHERE/SET/g;s/UPDATE/\n\nUPDATE/g;s/,\s*W_HERE/WHERE/g' > /home/sql/recover_table.sql
*注意：这里生产的SQL语句里面还存在@1=，@2=，... @9=这样的字符
将@1=，@2=，... @9= 按照你的表里字段的顺序，替换为你的字段名
比如：COL1=，COL2=，... COL9=
然后将WHERE id= 改为 WHERE 你的主键=
此方法适用于表里第一个字段是主键的表
然后执行这个数据恢复sql文件
mysql -hlocalhost -uusername -pPassword databasename < /home/sql/recover_table.sql >& /home/sql/error.log
执行完后查看是否报错
cat /home/sql/error.log
