blog.zhuohua.store - Powered by Discuz! Board

标题: MySQL恢复误删除的数据 [打印本页]

作者: admin 时间: 2019-9-17 22:33 标题: MySQL恢复误删除的数据

笺注：MySQL的安装可参考 LNMP一键安装包（lnmp_CentOS6.9) 查看MySQL的版本信息： [root@iZj6c1a39n0ss415rjbuoqZ ~]# mysql -V mysql Ver 14.14 Distrib 5.5.48, for Linux (x86_64) using readline 5.1 [root@iZj6c1a39n0ss415rjbuoqZ ~]# 查看MySQL的主配置文件： [root@iZj6c1a39n0ss415rjbuoqZ ~]# cat /etc/my.cnf |grep -v ^# |grep -v ^$ [client] port = 3306 socket = /tmp/mysql.sock [mysqld] port = 3306 socket = /tmp/mysql.sock datadir = /usr/local/mysql/var skip-external-locking key_buffer_size = 32M max_allowed_packet = 1M table_open_cache = 512 sort_buffer_size = 768K net_buffer_length = 8K read_buffer_size = 768K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M thread_cache_size = 16 query_cache_size = 16M tmp_table_size = 32M max_connections = 1000 max_connect_errors = 100 open_files_limit = 65535 log-bin=mysql-bin binlog_format=mixed server-id = 1 expire_logs_days = 10 default_storage_engine = InnoDB innodb_data_home_dir = /usr/local/mysql/var innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/var innodb_buffer_pool_size = 128M innodb_additional_mem_pool_size = 2M innodb_log_file_size = 32M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 32M sort_buffer_size = 768K read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout [root@iZj6c1a39n0ss415rjbuoqZ ~]# 笺注： log-bin=mysql-bin 代表开启binlog日志 binlog日志文件命名规则的前缀为 mysql-bin 服务器本地登录MySQL数据库：（使用的是数据库管理员root@localhost，密码888） [root@iZj6c1a39n0ss415rjbuoqZ ~]# mysql -uroot -p888 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3821 Server version: 5.5.48-log Source distribution Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> 查看binlog日志是否已经开启： mysql> show variables like '%log_bin%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | log_bin | ON | | log_bin_trust_function_creators | OFF | | sql_log_bin | ON | +---------------------------------+-------+ 3 rows in set (0.00 sec) mysql> 注释： log_bin：ON 代表binlog日志已经开启了查看binlog日志文件的存放目录： [root@iZj6c1a39n0ss415rjbuoqZ ~]# mysql -uroot -p888 -e"show variables like '%datadir%';" +---------------+-----------------------+ | Variable_name | Value | +---------------+-----------------------+ | datadir | /usr/local/mysql/var/ | +---------------+-----------------------+ [root@iZj6c1a39n0ss415rjbuoqZ ~]# binlog日志文件： [root@iZj6c1a39n0ss415rjbuoqZ ~]# cd /usr/local/mysql/var [root@iZj6c1a39n0ss415rjbuoqZ var]# ls discuz iZj6c1a39n0ss415rjbuoqZ.err mysql-bin.001281 mysql-bin.001285 mysql-bin.001289 performance_schema ibdata1 iZj6c1a39n0ss415rjbuoqZ.log mysql-bin.001282 mysql-bin.001286 mysql-bin.001290 wordpress ib_logfile0 iZj6c1a39n0ss415rjbuoqZ.pid mysql-bin.001283 mysql-bin.001287 mysql-bin.001291 zabbix ib_logfile1 mysql mysql-bin.001284 mysql-bin.001288 mysql-bin.index [root@iZj6c1a39n0ss415rjbuoqZ var]# 注释：有三个自定义创建的库 discuz、wordpress、zabbix 服务器的主机名为 iZj6c1a39n0ss415rjbuoqZ 会保留一定天数的日志文件；递增数值越大代表越新： [root@iZj6c1a39n0ss415rjbuoqZ var]# du -sh mysql-bin.001291 26M mysql-bin.001291 [root@iZj6c1a39n0ss415rjbuoqZ var]# [root@iZj6c1a39n0ss415rjbuoqZ var]# du -sh mysql-bin.001290 80M mysql-bin.001290 [root@iZj6c1a39n0ss415rjbuoqZ var]# [root@iZj6c1a39n0ss415rjbuoqZ var]# du -sh mysql-bin.001289 79M mysql-bin.001289 [root@iZj6c1a39n0ss415rjbuoqZ var]# du -sh mysql-bin.001288 79M mysql-bin.001288 [root@iZj6c1a39n0ss415rjbuoqZ var]# du -sh mysql-bin.001287 78M mysql-bin.001287 [root@iZj6c1a39n0ss415rjbuoqZ var]# 笺注： binlog日志文件并非只有一个文件，而是会产生很多，太久的会被自动删除； binlog日志文件的命名规则: 前缀 + 递增数值服务器本地登录MySQL数据库后，查看最新的binlog日志文件“mysql-bin.001291”的前十条操作记录： mysql> show binlog Events in 'mysql-bin.001291' LIMIT 0,10; +------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------+ | mysql-bin.001291 | 4 | Format_desc | 1 | 107 | Server ver: 5.5.48-log, Binlog ver: 4 | | mysql-bin.001291 | 107 | Query | 1 | 177 | BEGIN | | mysql-bin.001291 | 177 | Query | 1 | 300 | use `zabbix`; delete from event_suppress where suppress_until<1671559718 | | mysql-bin.001291 | 300 | Query | 1 | 371 | COMMIT | | mysql-bin.001291 | 371 | Query | 1 | 441 | BEGIN | | mysql-bin.001291 | 441 | Query | 1 | 632 | use `zabbix`; insert into history (itemid,clock,ns,value) values (23259,1671559719,183821990,0.000000),(30459,1671559719,195618016,0.010747) | | mysql-bin.001291 | 632 | Xid | 1 | 659 | COMMIT /* xid=146 */ | | mysql-bin.001291 | 659 | Query | 1 | 729 | BEGIN | | mysql-bin.001291 | 729 | Query | 1 | 854 | use `zabbix`; delete from task where status in (3,4) and clock<=1671473320 | | mysql-bin.001291 | 854 | Query | 1 | 925 | COMMIT | +------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------+ 10 rows in set (0.00 sec) mysql> 注释：可以看到，命令“use `zabbix`; delete from task where status in (3,4) and clock<=1671473320”的Pos为729 ，End_log_pos为854 在日志文件“mysql-bin.001291”中，查询2022-12-21 02:08:00到2022-12-21 20:30:00之间，库为zabbix的操作日志，并输出到屏幕上： [root@iZj6c1a39n0ss415rjbuoqZ ~]# find / -name mysqlbinlog /usr/local/mysql/bin/mysqlbinlog [root@iZj6c1a39n0ss415rjbuoqZ ~]# [root@iZj6c1a39n0ss415rjbuoqZ ~]# /usr/local/mysql/bin/mysqlbinlog --no-defaults --database=zabbix --start-datetime="2022-12-21 2:08:00" --stop-datetime="2022-12-21 20:30:00" /usr/local/mysql/var/mysql-bin.001291 |more # at 729 #221221 2:08:40 server id 1 end_log_pos 854 Query thread_id=10 exec_time=0 error_code=0 SET TIMESTAMP=1671559720/*!*/; delete from task where status in (3,4) and clock<=1671473320 /*!*/; 注释：可以看到，命令“use `zabbix`; delete from task where status in (3,4) and clock<=1671473320”的执行时间为 2022-12-21 02:08:40 ###### 创建测试的表和数据： mysql> use zabbix; Database changed mysql> CREATE TABLE table_1 (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(30) NOT NULL,PRIMARY KEY (`id`)); Query OK, 0 rows affected (0.02 sec) mysql> mysql> insert into table_1(name) values('zhuohua'); Query OK, 1 row affected (0.01 sec) mysql> insert into table_1(name) values('Python'); Query OK, 1 row affected (0.00 sec) mysql> insert into table_1(name) values('Mary'); Query OK, 1 row affected (0.01 sec) mysql> mysql> select * from table_1; +----+---------+ | id | name | +----+---------+ | 1 | zhuohua | | 2 | Python | | 3 | Mary | +----+---------+ 3 rows in set (0.00 sec) mysql> ### 在日志文件“mysql-bin.001291”中，查询2022-12-21 02:08:00到2022-12-21 20:30:00之间，库为zabbix、表为table_1的操作日志，并输出到屏幕上： [root@iZj6c1a39n0ss415rjbuoqZ ~]# /usr/local/mysql/bin/mysqlbinlog --no-defaults --database=zabbix --start-datetime="2022-12-21 02:08:00" --stop-datetime="2022-12-21 20:30:00" /usr/local/mysql/var/mysql-bin.001291 |grep table_1 CREATE TABLE table_1 (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(30) NOT NULL,PRIMARY KEY (`id`)) insert into table_1(name) values('zhuohua') insert into table_1(name) values('Python') insert into table_1(name) values('Mary') [root@iZj6c1a39n0ss415rjbuoqZ ~]# ### 删除数据： mysql> use zabbix; Database changed mysql> mysql> delete from table_1 where name = 'Python'; Query OK, 1 row affected (0.01 sec) mysql> select * from table_1; +----+---------+ | id | name | +----+---------+ | 1 | zhuohua | | 3 | Mary | +----+---------+ 2 rows in set (0.00 sec) mysql> 在日志文件“mysql-bin.001291”中，查询2022-12-21 02:08:00到2022-12-21 20:30:00之间，库为zabbix、表为table_1的操作日志，并写入到文件a.txt中： [root@iZj6c1a39n0ss415rjbuoqZ ~]# /usr/local/mysql/bin/mysqlbinlog --no-defaults --database=zabbix --start-datetime="2022-12-21 02:08:00" --stop-datetime="2022-12-21 20:30:00" /usr/local/mysql/var/mysql-bin.001291 |grep table_1 > a.txt [root@iZj6c1a39n0ss415rjbuoqZ ~]# [root@iZj6c1a39n0ss415rjbuoqZ ~]# cat a.txt CREATE TABLE table_1 (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(30) NOT NULL,PRIMARY KEY (`id`)) insert into table_1(name) values('zhuohua') insert into table_1(name) values('Python') insert into table_1(name) values('Mary') delete from table_1 where name = 'Python' [root@iZj6c1a39n0ss415rjbuoqZ ~]# ### 在日志文件“mysql-bin.001291”中，查询2022-12-21 02:08:00到2022-12-21 20:30:00之间，库为zabbix的操作日志，并写入到文件1.txt中： [root@iZj6c1a39n0ss415rjbuoqZ ~]# /usr/local/mysql/bin/mysqlbinlog --no-defaults --database=zabbix --start-datetime="2022-12-21 02:08:00" --stop-datetime="2022-12-21 20:30:00" /usr/local/mysql/var/mysql-bin.001291 > 1.txt [root@iZj6c1a39n0ss415rjbuoqZ ~]# 查看文件1.txt： # at 33976713 #221221 12:13:29 server id 1 end_log_pos 33976884 Query thread_id=3821 exec_time=0 error_code=0 SET TIMESTAMP=1671596009/*!*/; CREATE TABLE table_1 (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(30) NOT NULL,PRIMARY KEY (`id`)) /*!*/; # at 34045288 #221221 12:14:27 server id 1 end_log_pos 34045396 Query thread_id=3821 exec_time=0 error_code=0 SET TIMESTAMP=1671596067/*!*/; insert into table_1(name) values('zhuohua') /*!*/; # at 34050885 #221221 12:14:41 server id 1 end_log_pos 34050992 Query thread_id=3821 exec_time=0 error_code=0 SET TIMESTAMP=1671596081/*!*/; insert into table_1(name) values('Python') /*!*/; # at 34054842 #221221 12:14:49 server id 1 end_log_pos 34054947 Query thread_id=3821 exec_time=0 error_code=0 SET TIMESTAMP=1671596089/*!*/; insert into table_1(name) values('Mary') /*!*/; # at 34263682 #221221 12:18:35 server id 1 end_log_pos 34263788 Query thread_id=3821 exec_time=0 error_code=0 SET TIMESTAMP=1671596315/*!*/; delete from table_1 where name = 'Python' /*!*/; ##### 从日志文件/usr/local/mysql/var/mysql-bin.001291中，重新对库zabbix执行Pos为34050885，End_log_pos为34050992之间的命令：（等于重新在库zabbix中执行命令“insert into table_1(name) values('Python')”） [root@iZj6c1a39n0ss415rjbuoqZ ~]# /usr/local/mysql/bin/mysqlbinlog --database=zabbix /usr/local/mysql/var/mysql-bin.001291 --start-position 34050885 --stop-position 34050992 | mysql -uroot -p888 [root@iZj6c1a39n0ss415rjbuoqZ ~]# 删除的数据恢复成功： mysql> use zabbix; Database changed mysql> mysql> select * from table_1; +----+---------+ | id | name | +----+---------+ | 1 | zhuohua | | 3 | Mary | | 4 | Python | +----+---------+ 3 rows in set (0.00 sec) mysql> ##### 从日志文件/usr/local/mysql/var/mysql-bin.001291中，重新对库zabbix执行Pos为34263682，End_log_pos为34263788之间的命令：（等于重新在库zabbix中执行命令“delete from table_1 where name = 'Python'”） [root@iZj6c1a39n0ss415rjbuoqZ ~]# /usr/local/mysql/bin/mysqlbinlog --database=zabbix /usr/local/mysql/var/mysql-bin.001291 --start-position 34263682 --stop-position 34263788 | mysql -uroot -p888 [root@iZj6c1a39n0ss415rjbuoqZ ~]# 效果如下： mysql> use zabbix; Database changed mysql> mysql> select * from table_1; +----+---------+ | id | name | +----+---------+ | 1 | zhuohua | | 3 | Mary | +----+---------+ 2 rows in set (0.00 sec) mysql>

欢迎光临 blog.zhuohua.store (http://blog.zhuohua.store/)