MySQL恢复表删除的部分数据记录

最新推荐文章于 2024-07-03 03:18:18 发布
最新推荐文章于 2024-07-03 03:18:18 发布
阅读量710 收藏 1
点赞数
分类专栏： mysql 文章标签： mysql 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/rgb_rgb/article/details/121873721
版权

背景介绍：
Red Hat Enterprise Linux Server release 7.9 (Maipo)
Server version: 10.4.25-MariaDB
操作过程：

MariaDB [(none)]> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [test]> select count(*) from dep;
+----------+
| count(*) |
+----------+
|   540000 |
+----------+
1 row in set (17.078 sec)

MariaDB [test]> 
MariaDB [test]> checksum table test.dep;
+----------+------------+
| Table    | Checksum   |
+----------+------------+
| test.dep | 4285120342 |
+----------+------------+
1 row in set (20.768 sec)
MariaDB [test]> show create table test.dep\G;
*************************** 1. row ***************************
       Table: dep
Create Table: CREATE TABLE `dep` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `depno` mediumint(8) unsigned NOT NULL DEFAULT 0,
  `depname` varchar(255) NOT NULL DEFAULT '',
  `memo` varchar(255) NOT NULL DEFAULT '',
  `memo1` varchar(255) NOT NULL DEFAULT '',
  `memo2` varchar(255) NOT NULL DEFAULT '',
  `memo3` varchar(255) NOT NULL DEFAULT '',
  `memo4` varchar(255) NOT NULL DEFAULT '',
  `memo5` varchar(255) NOT NULL DEFAULT '',
  `memo6` varchar(255) NOT NULL DEFAULT '',
  `memo7` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1266421 DEFAULT CHARSET=latin1
1 row in set (0.001 sec)



MariaDB [test]> delete from test.dep where id<40001;
Query OK, 20000 rows affected (7.088 sec)

MariaDB [test]> checksum table test.dep;
+----------+------------+
| Table    | Checksum   |
+----------+------------+
| test.dep | 1733503517 |
+----------+------------+
1 row in set (20.203 sec)



root@tc58:tools# ./my2sql -h
my2back V1.0
  -U    prefer to use unique key instead of primary key to build where condition for delete/update sql
  -add-extraInfo
        Works with -work-type=2sql|rollback. Print database/table/datetime/binlogposition...info on the line before sql, default false
  -big-trx-row-limit int
        transaction with affected rows greater or equal to this value is considerated as big transaction. Valid values range from 1 to 30000, default 10 (default 10)
  -databases string
        only parse these databases, comma seperated, default all.
  -do-not-add-prifixDb
        Prefix table name witch database name in sql,ex: insert into db1.tb1 (x1, x1) values (y1, y1). 
  -file-per-table
        One file for one table if true, else one file for all tables. default false. Attention, always one file for one binlog
  -full-columns
        For update sql, include unchanged columns. for update and delete, use all columns to build where condition.
        default false, this is, use changed columns to build set part, use primary/unique key to build where condition
  -host string
        mysql host, default 127.0.0.1 . (default "127.0.0.1")
  -ignore-databases string
        ignore parse these databases, comma seperated, default null
  -ignore-primaryKey-forInsert
        for insert statement when -workType=2sql, ignore primary key
  -ignore-tables string
        ignore parse these tables, comma seperated, default null
  -local-binlog-file string
        local binlog files to process, It works with -mode=file 
  -long-trx-seconds int
        transaction with duration greater or equal to this value is considerated as long transaction. Valid values range from 0 to 3600, default 1 (default 1)
  -mode string
        valid options are:  repl,file. repl: as a slave to get binlogs from master. file: get binlogs from local filesystem. default repl (default "repl")
  -mysql-type string
        valid options are:  mysql,mariadb. server of binlog, mysql or mariadb, default mysql (default "mysql")
  -output-dir string
        result output dir, default current work dir. Attension, result files could be large, set it to a dir with large free space
  -output-toScreen
        Just output to screen,do not write to file
  -password string
        mysql user password.
  -port uint
        mysql port, default 3306. (default 3306)
  -print-interval int
        works with -w='stats', print stats info each PrintInterval. Valid values range from 1 to 600, default 30 (default 30)
  -server-id uint
        this program replicates from mysql as slave to read binlogs. Must set this server id unique from other slaves, default 1113306 (default 1113306)
  -sql string
        valid options are:  insert,update,delete. only parse these types of sql, comma seperated, valid types are: insert, update, delete; default is all(insert,update,delete)
  -start-datetime string
        Start reading the binlog at first event having a datetime equal or posterior to the argument, it should be like this: "2020-01-01 01:00:00"
  -start-file string
        binlog file to start reading
  -start-pos uint
        start reading the binlog at position (default 4)
  -stop-datetime string
        Stop reading the binlog at first event having a datetime equal or posterior to the argument, it should be like this: "2020-12-30 01:00:00"
  -stop-file string
        binlog file to stop reading
  -stop-pos uint
        Stop reading the binlog at position (default 4)
  -tables string
        only parse these tables, comma seperated, DONOT prefix with schema, default all.
  -threads uint
        Works with -workType=2sql|rollback. threads to run (default 2)
  -tl string
        time location to parse timestamp/datetime column in binlog, such as Asia/Shanghai. default Local (default "Local")
  -user string
        mysql user. 
  -v    print version
  -work-type string
        valid options are:  2sql,rollback,stats. 2sql: convert binlog to sqls, rollback: generate rollback sqls, stats: analyze transactions. default: 2sql (default "2sql")
root@tc58:tools# 

#解析binglog文件
./my2sql  -user tc_mon -password tc_mon -host tc58 -port 3306 -databases test -tables dep -mode repl -work-type 2sql -start-file binlog.000190 -output-dir /tmp

root@tc58:tools# ./my2sql  -user tc_mon -password tc_mon -host tc58 -port 3306 -databases test -tables dep -mode repl -work-type 2sql -start-file binlog.000190 -output-dir /tmp
[2022/11/25 15:14:50] [info] events.go:221 start thread to write redo/rollback sql into file
[2022/11/25 15:14:50] [info] events.go:61 start thread 1 to generate redo/rollback sql
[2022/11/25 15:14:50] [info] events.go:61 start thread 2 to generate redo/rollback sql
[2022/11/25 15:14:50] [info] stats_process.go:166 start thread to analyze statistics from binlog
[2022/11/25 15:14:50] [info] binlogsyncer.go:164 create BinlogSyncer with config {1113306 mysql tc58 3306 tc_mon   utf8 false false <nil> false Local false 0 0s 0s 0 false false 0 <nil> 0xc0000900c0 0x637c00}
[2022/11/25 15:14:50] [info] binlogsyncer.go:400 begin to sync binlog from position (binlog.000190, 4)
[2022/11/25 15:14:50] [info] repl.go:16 start to get binlog from mysql
[2022/11/25 15:14:50] [info] binlogsyncer.go:816 rotate to (binlog.000190, 4)
[2022/11/25 15:14:50] [info] events.go:255 finish processing binlog.000190 10488958
[2022/11/25 15:14:50] [info] events.go:255 finish processing binlog.000190 20977346
[2022/11/25 15:14:50] [info] events.go:255 finish processing binlog.000190 31465734
[2022/11/25 15:14:55] [info] repl.go:84 deadline exceeded.
[2022/11/25 15:14:55] [info] repl.go:18 finish getting binlog from mysql
[2022/11/25 15:14:55] [info] stats_process.go:266 exit thread to analyze statistics from binlog
[2022/11/25 15:14:55] [info] events.go:196 exit thread 1 to generate redo/rollback sql
[2022/11/25 15:14:55] [info] events.go:196 exit thread 2 to generate redo/rollback sql
[2022/11/25 15:14:55] [info] events.go:285 finish writing redo/forward sql into file
[2022/11/25 15:14:55] [info] events.go:288 exit thread to write redo/rollback sql into file
root@tc58:tools# 


root@tc58:tools# ll /tmp
total 478068
-rw-r--r-- 1 root root       255 Nov 25 15:14 biglong_trx.txt
-rw-r--r-- 1 root root       288 Nov 25 15:14 binlog_status.txt
-rw-r--r-- 1 root root    854449 Nov 25 15:14 forward.190.sql
root@tc58:tools# cat /tmp/biglong_trx.txt 
binlog            starttime           stoptime            startpos   stoppos    rows     duration   tables
binlog.000190     2022-11-25_10:55:50 2022-11-25_10:55:50 395        35870601   20000    0          [test.dep(inserts=0, updates=0, deletes=20000)]
root@tc58:tools# 
root@tc58:tools# cat /tmp/biglong_trx.txt 
binlog            starttime           stoptime            startpos   stoppos    rows     duration   tables
binlog.000190     2022-11-25_10:55:50 2022-11-25_10:55:50 395        35870601   20000    0          [test.dep(inserts=0, updates=0, deletes=20000)]
root@tc58:tools# 

more -22 /tmp/forward.190.sql
root@tc58:tools# more -22 /tmp/forward.190.sql
DELETE FROM `test`.`dep` WHERE `id`=2;
DELETE FROM `test`.`dep` WHERE `id`=4;
DELETE FROM `test`.`dep` WHERE `id`=6;
DELETE FROM `test`.`dep` WHERE `id`=8;
DELETE FROM `test`.`dep` WHERE `id`=10;
DELETE FROM `test`.`dep` WHERE `id`=12;
DELETE FROM `test`.`dep` WHERE `id`=14;
DELETE FROM `test`.`dep` WHERE `id`=16;
DELETE FROM `test`.`dep` WHERE `id`=18;
DELETE FROM `test`.`dep` WHERE `id`=20;
DELETE FROM `test`.`dep` WHERE `id`=22;
DELETE FROM `test`.`dep` WHERE `id`=24;
DELETE FROM `test`.`dep` WHERE `id`=26;
DELETE FROM `test`.`dep` WHERE `id`=28;
DELETE FROM `test`.`dep` WHERE `id`=30;
DELETE FROM `test`.`dep` WHERE `id`=32;
DELETE FROM `test`.`dep` WHERE `id`=34;
DELETE FROM `test`.`dep` WHERE `id`=36;
DELETE FROM `test`.`dep` WHERE `id`=38;
DELETE FROM `test`.`dep` WHERE `id`=40;
DELETE FROM `test`.`dep` WHERE `id`=42;
DELETE FROM `test`.`dep` WHERE `id`=44;
DELETE FROM `test`.`dep` WHERE `id`=46;


# 生成闪回sql文件
./my2sql  -user tc_mon -password tc_mon -host tc58 -port 3306 -databases test -tables dep -mode repl -work-type rollback   -start-file binlog.000190  -output-dir /tmp/flashback/


root@tc58:tools# ./my2sql  -user tc_mon -password tc_mon -host tc58 -port 3306 -databases test -tables dep -mode repl -work-type rollback   -start-file binlog.000190  -output-dir /tmp/flashback/
[2022/11/25 15:20:37] [info] events.go:221 start thread to write redo/rollback sql into file
[2022/11/25 15:20:37] [info] events.go:61 start thread 1 to generate redo/rollback sql
[2022/11/25 15:20:37] [info] events.go:61 start thread 2 to generate redo/rollback sql
[2022/11/25 15:20:37] [info] binlogsyncer.go:164 create BinlogSyncer with config {1113306 mysql tc58 3306 tc_mon   utf8 false false <nil> false Local false 0 0s 0s 0 false false 0 <nil> 0xc0000900c0 0x637c00}
[2022/11/25 15:20:37] [info] stats_process.go:166 start thread to analyze statistics from binlog
[2022/11/25 15:20:37] [info] binlogsyncer.go:400 begin to sync binlog from position (binlog.000190, 4)
[2022/11/25 15:20:37] [info] repl.go:16 start to get binlog from mysql
[2022/11/25 15:20:37] [info] binlogsyncer.go:816 rotate to (binlog.000190, 4)
[2022/11/25 15:20:38] [info] events.go:255 finish processing binlog.000190 10488958
[2022/11/25 15:20:38] [info] events.go:255 finish processing binlog.000190 20977346
[2022/11/25 15:20:38] [info] events.go:255 finish processing binlog.000190 31465734
[2022/11/25 15:20:43] [info] repl.go:84 deadline exceeded.
[2022/11/25 15:20:43] [info] repl.go:18 finish getting binlog from mysql
[2022/11/25 15:20:43] [info] stats_process.go:266 exit thread to analyze statistics from binlog
[2022/11/25 15:20:43] [info] events.go:196 exit thread 2 to generate redo/rollback sql
[2022/11/25 15:20:43] [info] events.go:196 exit thread 1 to generate redo/rollback sql
[2022/11/25 15:20:43] [info] events.go:270 finish writing rollback sql into tmp files, start to revert content order of tmp files
[2022/11/25 15:20:43] [info] rollback_process.go:15 start thread 1 to revert rollback sql files
[2022/11/25 15:20:43] [info] rollback_process.go:41 start to revert tmp file /tmp/flashback/.rollback.190.sql into /tmp/flashback/rollback.190.sql
[2022/11/25 15:20:43] [info] rollback_process.go:156 finish reverting tmp file /tmp/flashback/.rollback.190.sql into /tmp/flashback/rollback.190.sql
[2022/11/25 15:20:43] [info] rollback_process.go:25 exit thread 1 to revert rollback sql files
[2022/11/25 15:20:43] [info] events.go:283 finish reverting content order of tmp files
[2022/11/25 15:20:43] [info] events.go:288 exit thread to write redo/rollback sql into file
root@tc58:tools# 


more -22 /tmp/flashback/rollback.190.sql
root@tc58:tools# more -22 /tmp/flashback/rollback.190.sql
INSERT INTO `test`.`dep` (`id`,`depno`,`depname`,`memo`,`memo1`,`memo2`,`memo3`,`memo4`,`memo5`,`memo6`,`memo7`) VALUES (40000,20001
,'iJVBVWYcopGozFDczrkbAwKhISoSyLmasKylSzWhWkkxJaYSriQFBTPqiWhXrQeWwuFVMXDZmmxEBUVUNcXIwlMfmVSCijuyivBvzkAZuaRhmOhvGSvyglMaSmFpLIkbBE
rUwAoRtsGfGOdYJABdMAqfBtlhXqNSaztvVthCrbhFGoALdkPrsONAlEoHsS','kzPDwwUhdVrVGrSmHxtBBazxNyeZItVvqrMJldJiOtEQqgHStolyFDbxhtwbSkvzmKPTX
hSQDsdplxxWqNRWhWlzHNUjmICPrlqRlCgWpIwlNhyTauZKEQoZeAmKNJjTQzXrOUihkFtcgBlCfQRKYMRVfMwWuhAioVLSfaIrIrJwgpgKgBmHzzbJrGfJdpmthDvtHd','
vSbFVNddfpljlFrSlNYFfNwVlCXiVdELSfaJwgqmluqwiCoLNGTzRJXIzyUdGVMUqUyMlcxixNxaJvYHoyAimNcXEhWkmGsVAQDuokgecWDarGiYpFhUbxixODzMkREwtCGx
wRRJTrfxaIqBNkKWCYljkBZvdelbuTjlMXAJVyISpXQmPkHHoyAfZJvaPYyyVidQWl','qVDhZzBilNdddkPrsLBxLmcCEoFmthHLMyjBcIiPBlCfSXjeThdUmDeLqzwNycS
hgkGBNmTJNPJaYSusFZhNsAzUavYHoAKZRm','KLDGxtDHCRCjpWNaNMvUksjPwNCxFKHiUWbrEUOiyTWcxiuAsooFkilFuiGIxlMcaTuprPZCOlNjFALdhDsfvQPBmGtaYO
aLBCfUjkzSPwODzOuGZevPNxWrTuqtXEgUcBDmxDyJXKHjVdGSxIXQlFteqrLEMXAKbZUxFIzzXlyEAMmYiTVXZgHTusERxNuLxecYNUijutNGTyMpmqRmKPVhXrSpSvyhoZ
gG','PfgqkdMAoUFrUxCwzgiyRMmXcsHjZuYJztxfkIMKqyvJgEDbzsotaYOaJvZLHcooDZkfVlCYlktkVZlfVkoQlQsoqPcRdPOAlDkqYVHyuFQpagIVDeOFLNIaewSaAyR
MjLekPswegswfjEtgBlJIouhAfbPUdJjTQwNydVumZkeSYrKDIHlsaaXOdWyEzKbdleFQlQsorQfcWAMi','HLKptYMRUbwdccchFCXfKkUUMaQeZKAxLlidOJgBlLPTWdBv
zlPmPlULWxxXufrrKyoWTFsYNVlDcAxJgAkATUTGDVVQqjYlteqqHoxwOFIBHHqGkfXxtBAZwjFxybKvZLGYaewSbERtuTkrexXxwMwWuhCpRpWRuwZHlqTtmeLrzyUbAxMr
zuArkXkgaKwflQvEKNLljhjzTTPuBxJerubYKIjWeJgECZrLFPmMbUs','hFEhYucdiIQcRbEVNehyUbAyPDyFGsTpRrhKgzcOODBTPtxixPGLLykDkrclarJvbUvsAyUZqE
allANmZjXmqTvxbPVjimNefodBwDCcFRuxeflYctKtMFRsmfSYpCRAeTeRUXdArjSLelbrGdBvAqcoloMSeTgXwqovlTJQdSfVoIwkIN','RRKaYQiuvXxyXufqovofLomth
DwydTlyIVFqKERvEKLByOyZEWVMaReZGljmHCMguDIFcwcTpQjyPFIwkKWANlaorTtliaDOlUMZKDMYGkgdXAMgxUgXudiEwyaGfHVIEXaiPzdSdOIYSvxaKxkJNNBusFXXV
IFbrEVQqjXkgdWyCqZWNYCRDsbfyZBJQcNJhHRmIFalgWpIwizbGdvWsbepllEhXrTrevPNtDOiyS');
INSERT INTO `test`.`dep` (`id`,`depno`,`depname`,`memo`,`memo1`,`memo2`,`memo3`,`memo4`,`memo5`,`memo6`,`memo7`) VALUES (39998,20000
,'hfcXEdGTzUWZhPAhkFvlUPphPCsiLfruVwxWpLMzlFvofKjQBioUEpJDOiySUVSCikyOAjtrCOkJQZCOoakZtVyCrclhUfQMmaqDULYHsQaHgMqsRgdbVAOtAxNuJmhaHi
UXeEHxrpBKWEhYuemXbrBJOSYpCRClIzyTYllwBpWNaPWoGpDWZgJckQyXqO','UfUifdZNQRKZTusDPpgJdldGUHyvKprOVmwwTfWoFkjoTDifbRggkJQYywKmfPIUAUWZg
IWKKtOPFIBHFfLquaUvwTgZBMcdhClKLylLToMTjlGwqovohSUSHIrLEOiwLolmElpTwEDhbIlrSoSyLmZmmxBpWNbTlLNIaeuMArfCwzislltkSHLIfCwCxGLMDDgYzA','
fbOPJaackTMgswfkINNBuuQRMjJVBTNjJQdRbFVPmPpfEIETFxuHagHPeaPZCOlWTDlvtGcsImklLQXpFgPHQglMbVxCtmcAxLojdOMtHcsHiRMkPqloIwkKUtiJVDdIgGOa
JwfmTFyBjslmwuLtNHVHAFzISmKLCDjmHzCpObORTPuFRqhMlfLlkkutKtPSUTKWzK','aXMTfUgZFamjilSxLmcylUKQZzxPFIyuCDhdSfYCOpgNtETCgaHkfUkpTATRFAN
lbtQUdIgGMPRLfvLrADqSpYXSvBuvThcSgb','OPHTusHfHUATSFAJWDdGXUElAOsyoZfApXTzTSHKCIGiYsSlDfUfUhZCPrpAGEeIevQSOuEPlKTlzPBlKNJfzhlRuxgoal
fPKgCqWKKwdaSoPglTHEapwphRNqqHowswegtCDjlMUoMPPFIzyUcBzTWcvXAHMMArgFJETGBOodBvygmRvCyOyaGgLlYlhfcXFkjrgDzLgzdVqQirlgWpKGZduLyivDEkoO
cU','qUBYoyCoNWvlWZiSNmVRzTYkfXtdmYfIabhGJDPlTGxwQNrvcZQhmMZLJmhbJqBKXHtVyEzNpmlFoCXdxfizXlAKbcfuGXVIFZjXiXrOTdJlbzsrDTHDVUKSjrgFGsU
vvOIackREumZowsvaPXtdhCoNWuhDtlXhPGKFVPlVLWzHKHdvRZxphRKZUxGNVmBWbuSdQRPwNBshLgAh','ivCzPAipbliaGeArfBshIUAQCoMUoJDPpdxecaUvwUkoRpZd
qxpgLlYhQIRjuwZHluiFEeLpsUvtGZgGLMAtqxpjYpDWamjlATUQtwbTpQmMWzGIyqkdLwXzGHqHozFDatRaDPqlfTdMxcTkxEFoEdGUEltmcDHBLbXKHeAlPkGCRBhgjEsd
lURzXpIsSjqewWrWGqNQPCtkVXakZsQbKuTjqZclhUfSYmpObQWoEeG','WOfkIJwiyRRHKDOiAcLykEqPbPVjgfjCkurzvHYVHAEvppDVUNfocvVrYObPUgTcGYYVKOOCwB
pXTzPBpSuuNJetGbolALdjMiEuoiZAAcKvVsckPvJhJXMSbERyQHRjwFKLzrhMokijvDDfSWfOBqbjSJUwzfcXFjfZEYgMlgSUVUJORR','KaWGrOUifcVupmrYRpZbhHPeb
VyGKIkcEQrkYpEbvXBOqlihlLUqUDhYywKolwtEOhwJgDxFIxoeDElBSIMOFLPPHQjvBtrCKUqYRqdrBIItRfYBMiBktqvhAdOMuLxdYKDLVwvPMlhUeMxbNNAoTzTUSErWK
IjZtWCWaorTsgCsgCtmcBzSRFCTMemUPoZgGMRZvgsxjBgbPUcDLTlzOtFVKPSUTLaTtlXfHXRrhL');
INSERT INTO `test`.`dep` (`id`,`depno`,`depname`,`memo`,`memo1`,`memo2`,`memo3`,`memo4`,`memo5`,`memo6`,`memo7`) VALUES (39996,19999
,'AAcLzmKReVoKIjUYiUZlkoRqfBskYljlMVulVTIJylTFvlbtNLomvqqJvbXFklwDyKfvMxbOSVYhOyZCNhxTXjaCJPXrQfdbVBSLZSoPiwHWLPRRGFhXoBSKXGoyExDxDy
IVFmvrveiExzeXCSFzFDcAuwYCRDqSqaexaJrIrHlkqYWOdaQbNIbkVYfGNZ','HltftDMXEeIbgEEgUeOGOaLFTCg'...............
.................省略

root@tc58:tools# ls -lrt /tmp/flashback
total 37668
-rw-r--r-- 1 root root      255 Nov 25 15:20 biglong_trx.txt
-rw-r--r-- 1 root root      288 Nov 25 15:20 binlog_status.txt
-rw-r--r-- 1 root root 38563347 Nov 25 15:20 rollback.190.sql
root@tc58:tools# cat /tmp/flashback/binlog_status.txt

root@tc58:tools# cat /tmp/flashback/biglong_trx.txt
binlog            starttime           stoptime            startpos   stoppos    rows     duration   tables
binlog.000190     2022-11-25_10:55:50 2022-11-25_10:55:50 395        35870601   20000    0          [test.dep(inserts=0, updates=0, deletes=20000)]
root@tc58:tools# 

root@tc58:tools# cat /tmp/flashback/binlog_status.txt
binlog            starttime           stoptime            startpos   stoppos    inserts  updates  deletes  database        table               
binlog.000190     2022-11-25_10:55:50 2022-11-25_10:55:50 495        35870570   0        0        20000    test            dep                 
root@tc58:tools# 



# 应用rollback文件进行数据恢复

mysql -htc58 -utc_mon -ptc_mon test < /tmp/flashback/rollback.190.sql
root@tc58:tools# mysql -htc58 -utc_mon -ptc_mon test < /tmp/flashback/rollback.190.sql
root@tc58:tools# 

# 检查恢复情况
MariaDB [test]> select count(*) from test.dep;
+----------+
| count(*) |
+----------+
|   540000 |
+----------+
1 row in set (11.256 sec)

MariaDB [test]> checksum table test.dep;
+----------+------------+
| Table    | Checksum   |
+----------+------------+
| test.dep | 4285120342 |
+----------+------------+
1 row in set (20.973 sec)
rgb_rgb
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL数据恢复的三种常用的方式
07-05
当数据表因各种原因如误删除、软件故障或硬件故障而丢失或损坏时，最可靠的方法是使用先前创建的备份进行恢复。备份可以是整个数据库的全备，也可以是单独表的备份。恢复过程包括： - 连接 MySQL 服务器。 - 选择...
使用binlog日志恢复MySQL数据库删除数据的方法
01-11
MySQL数据库的binlog日志是数据安全的重要保障，它记录了所有的DDL（Data Definition Language，如CREATE、ALTER、DROP等）和DML（Data Manipulation Language，如INSERT、UPDATE、DELETE等）语句，但不包括SELECT...
电脑误删mysql_如何恢复MySQL数据库下误删的数据
weixin_42098251的博客
01-20 361
直接上操作步骤及恢复思路(友情提示：数据库的任何操作都要提前做好备份)，以下是Mysql数据后的恢复过程：1. 找到binlog恢复数据的前提是必须开启Mysql的binlog日志，如果binlog日志没开启，请忽略此篇文档。binlog日志是否开启可以查看Mysql配置文件。日志位置一般在/var/lib/mysql目录或者编译安装的date目录下。也可登录Mysql用命令查看。# cat /e...
Mysql-表和表记录（创建 删除 查看）
ChenTalented的博客
05-12 363
创建表 CREATE TABLE 表名( 列名 数据类型, 列名 数据类型, ... 列名 数据类型 ); //最后一列不要加, create table stu( id INT(4), name VARCHAR(50), PASSWORD VARCHAR(20) ); 删除表 DROP TABLE 表名; drop table stu; 查看表 desc 表名; desc stu; 向stu表中插入数据 INSERT into 表名(字段名1,字段名2,字段名3
MySql 查询、删除表单
weixin_44564263的博客
05-18 378
查询（英文名：SELECT）是数据库当中比较重要的一部分，编程离不开增、删、查、改这四条语法，然后准备了一张部门表，表名为sys_department，表内的数据如下图： 总共十五条数据，然后再进行查询一条部门表中已有的数据。 下面就是查询语法： 以下为向MySQL用户表修改已有数据通用的 SELECT sys_department.id（部门表加部门id） SET SQL语法： SELECT （sys_department） FROM意思是：需要查询的表名名称。 WHERE意思是查询，要是SELECT
mysql 查看删除记录日志
最新发布
weixin_40453214的博客
07-03 26
数据库相关视频讲解：用python编程Excel有没有用处？如何实现mysql查看删除记录日志 一、流程图 开发者小白开发者小白#render_6_1347180273-svg{font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;fill:...
MySQL--数据表删除记录
Orz~~Orz
01-23 642
单表删除： delete from tb_name [where where_condition];同样的，不加where就会删除所以记录，加了where后会删除符合条件的记录。 比如删除id=1的记录：
MySQL删除表数据的方法
12-16
在MySQL数据库管理中，删除表数据是常见的操作，主要分为两种方法：DELETE语句和TRUNCATE TABLE语句。这两种方法各有特点，适用于不同的场景。 首先，DELETE语句提供了更大的灵活性，因为它允许通过WHERE子句指定...
关于mysql数据库误删除后的数据恢复操作说明
09-09
MySQL 数据库误删除后的数据恢复是一项关键操作，尤其是在业务连续性至关重要的情况下。本文将详细介绍如何在MySQL数据库发生误删除后进行数据恢复。 首先，预防总是优于治疗，定期的数据库备份是防止数据丢失的...
MySQL的查询及删除重复记录
萌兔兔MMQ！！
04-30 2613
查询及删除重复记录的方法 (一) 1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断 select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) 2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录 delete from people whe
window下Mysql 恢复Delete删除的数据
kenkingka的博客
08-08 497
MySql 误删除数据的恢复
MySQL数据误删恢复操作
xiaoyixiao_的博客
09-21 1299
记录一次不小心删除生产数据偷偷恢复解决方案
mysql binlog 查询已删除数据
ElcarimQ的博客
01-30 422
mysql 恢复已删除数据
数据库（mysql）数据记录的增，删，改，查操作
weixin_64428129的博客
04-16 4469
(关于mysql数据库、数据表以及记录的增、删、改、查链接：点击本链接即可跳转https://mp.csdn.net/mp_blog/creation/editor/123970880
MySQL8.0对数据库增删改查的基本操作
m0_67159981的博客
04-13 716
MySQL8.0对数据库增删改查的基本操作
MySQL恢复不小心误删的数据记录（binlog）-生产实操
wscwsc58888的专栏
10-23 1167
同事操作删除生产数据时，未及时备份导致误删除部份数据。现通过mysql的binlog日志回滚数据。
MySQL中查询、删除重复记录的方法大全
2401_84049088的博客
04-16 3042
互联网大厂比较喜欢的人才特点：对技术有热情，强硬的技术基础实力；主动，善于团队协作，善于总结思考。无论是哪家公司，都很重视高并发高可用技术，重视基础，所以千万别小看任何知识。面试是一个双向选择的过程，不要抱着畏惧的心态去面试，不利于自己的发挥。同时看中的应该不止薪资，还要看你是不是真的喜欢这家公司，是不是能真的得到锻炼。其实我写了这么多，只是我自己的总结，并不一定适用于所有人，相信经过一些面试，大家都会有这些感触。下面有部分截图希望能对大家有所帮助。
binlog2sql使用
YeJinYang的博客
01-20 834
binlog2sql使用
MySQL 联表查询重复数据并删除（子查询删除记录） SQL优化
人生快意事，读的好书，觅得良友
11-03 3179
MySQL 联表查询重复数据并删除（子查询删除记录） SQL优化
mysql如何通过binlog恢复表数据
06-09
MySQL通过binlog可以进行数据恢复，具体的步骤如下： 1. 首先需要确认你要恢复数据的时间点，例如你要恢复的时间点是2021年9月1日凌晨3点。 2. 打开MySQL的binlog功能，可以在MySQL的配置文件中设置或者直接在命令行中使用`--log-bin`参数。 3. 通过`mysqlbinlog`命令将binlog文件转换成SQL语句，例如： ``` mysqlbinlog binlog.000001 --start-datetime="2021-09-01 03:00:00" --stop-datetime="2021-09-01 03:01:00" > binlog.sql ``` 这个命令会将binlog.000001文件中2021年9月1日凌晨3点至3点1分的操作记录转换成SQL语句，并保存到binlog.sql文件中。 4. 在MySQL中执行binlog.sql文件中的SQL语句，即可恢复数据。 需要注意的是，这种方法只能用于恢复数据，无法恢复表结构。如果需要恢复整个数据库，可以使用mysqldump备份和恢复。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值