©2020-2024 ioDraw All rights reserved,
Privacy Policy
<>MysqL差异备份与恢复
binlog ：日志，记录了所有的写操作
<>1.开启mysql服务器的二进制日志功能 [root@clq ~]# cat /etc/my.cnf [mysqld] basedir = /usr/
local/mysql datadir = /opt/data socket = /tmp/mysql.sock port = 3306 pid-file =
/opt/data/mysql.pid user = mysql skip-name-resolve #skip-grant-tables server-id=
1 #服务id log-bin=mysql-bin #日志服务 <>
2.数据存放的位置/opt/data（二进制安装方式，其它安装路径不同）差异备份实现之前要先进行全量备份再进行差异备份，如下加数据：
#先进行全量备份(二进制日志方式) mysqldump -uroot -p --single-transaction --flush-logs
--master-data=2 --all-databases --delete-master-logs > all-$(date
"+%Y%m%d").sql [root@clq ~]# ll -rw-r--r--. 1 root root 876049 8月 26 09:40
all-20210826.sql 1.查看正在用的日志文件 [root@clq data]# cat mysql-bin.index ./mysql-bin
.000001 #误删之前的日志文件信息 2.查看mysql的数据表信息 mysql> select * from student02; +----+-----
-+------+-------+ | id | name | age | score | +----+------+------+-------+ | 5 |
tom| NULL | 80 | | 6 | xixi | NULL | 89.5 | | 7 | haha | NULL | 96 | | 8 | oppo
| NULL | 98 | | 9 | A | 1 | 99 | | 10 | B | 2 | 99 | | 11 | C | 1 | 99 | | 12 |
D| 2 | 99 | +----+------+------+-------+ 8 rows in set (0.00 sec) 3.
实现差异备份之前加点数据到表中(差异备份的数据) mysql> select * from student02; +----+------+------+---
----+ | id | name | age | score | +----+------+------+-------+ | 5 | tom | NULL
| 80 | | 6 | xixi | NULL | 89.5 | | 7 | haha | NULL | 96 | | 8 | oppo | NULL |
98 | | 9 | A | 1 | 99 | | 10 | B | 2 | 99 | | 11 | C | 1 | 99 | | 12 | D | 2 |
99 | | 13 | E | 1 | 99 | | 14 | F | 2 | 99 | | 15 | G | 1 | 99 | | 16 | H | 2 |
99 | +----+------+------+-------+ <>3.进行误删(切记生产环境不要做) [root@clq data]# mysql
-uroot -p -e 'drop database clq;' [root@clq data]# mysql -uroot -p -e 'show
databases;' +--------------------+ | Database | +--------------------+ |
information_schema| | mysql | | performance_schema | | sys | +------------------
--+ <>4.刷新创建新的二进制日志 [root@clq ~]# mysqladmin -uroot -p flush-logs [root@clq
data]# cat mysql-bin.index ./mysql-bin.000001 ./mysql-bin.000002 #刷新产生的日志=误删日志标识
<>5.恢复之前完全备份的数据 [root@clq ~]# mysql -uroot -p < all-20210506.sql #查看数据 mysql>
select* from student02; +----+------+------+-------+ | id | name | age | score |
+----+------+------+-------+ | 5 | tom | NULL | 80 | | 6 | xixi | NULL | 89.5 |
| 7 | haha | NULL | 96 | | 8 | oppo | NULL | 98 | | 9 | A | 1 | 99 | | 10 | B |
2 | 99 | | 11 | C | 1 | 99 | | 12 | D | 2 | 99 | +----+------+------+-------+ 8
rowsin set (0.00 sec) <>6.查看到误删前一刻的日志信息 mysql> show binlog events in
'mysql_bin.000001'; +------------------+------+----------------+-----------+----
---------+---------------------------------------+ | Log_name | Pos | Event_type
| Server_id | End_log_pos | Info | mysql-bin.000001 | 972 | Xid | 1 | 1003 |
COMMIT/* xid=446 */ | | mysql-bin.000001 | 1003 | Anonymous_Gtid | 1 | 1068 |
SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000001 | 1068 | Query (<--
要恢复的日志号) | 1 | 1157 | drop database clq（误删时的日志信息） | | mysql-bin.000001 | 1157 |
Rotate| 1 | 1204 | mysql-bin.000002;pos=4 <>7通过日志号恢复之前的增量的数据（增量备份恢复）
开始位置–start-position=# 暂停位置–stop-position=#
[root@clq ~]#mysqlbinlog --stop-position=1068（日志号） /opt/data/mysql_bin.000001
|mysql -uroot -p +----+------+------+-------+ | id | name | age | score | +----+
------+------+-------+ | 5 | tom | NULL | 80 | | 6 | xixi | NULL | 89.5 | | 7 |
haha| NULL | 96 | | 8 | oppo | NULL | 98 | | 9 | A | 1 | 99 | | 10 | B | 2 | 99
| | 11 | C | 1 | 99 | | 12 | D | 2 | 99 | | 13 | E | 1 | 99 | | 14 | F | 2 | 99
| | 15 | G | 1 | 99 | | 16 | H | 2 | 99 | +----+------+------+-------+ 完成！！！
<>8.修改日志备份的文件，来实现增量数据的还原
[root@mysql ~]# mysqlbinlog /opt/data/mysql-bin.000002 > diff.sql [root@clq
~]# vim diff.sql ... COMMIT/*!*/; # at 425 #210826 14:09:03 server id 1
end_log_pos 490 CRC32 0x77739e44 Anonymous_GTID last_committed=1
sequence_number=2 rbr_only=no SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at
490 #210826 14:09:03 server id 1 end_log_pos 579 CRC32 0x570b702e Query
thread_id=4 exec_time=0 error_code=0 SET TIMESTAMP=1629958143/*!*/; SET
@@session.pseudo_thread_id=4/*!*/; drop database clq #此处为删除库的语句 /*!*/; # at 579
#210826 14:09:25 server id 1 end_log_pos 626 CRC32 0xe9da1edc Rotate to
mysql-bin.000006 pos: 4 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by
mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET
COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET
@@SESSION.PSEUDO_SLAVE_MODE=0*/; #只留这几行 COMMIT/*!*/; /*!50003 SET
COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET
@@SESSION.PSEUDO_SLAVE_MODE=0*/; #进行差异备份的数据恢复 [root@mysql ~]# mysql -u root -p
< diff.sql mysql> select * from student02; +----+------+------+-------+ | id |
name | age | score | +----+------+------+-------+ | 5 | tom | NULL | 80 | | 6 |
xixi | NULL | 89.5 | | 7 | haha | NULL | 96 | | 8 | oppo | NULL | 98 | | 9 | A
| 1 | 99 | | 10 | B | 2 | 99 | | 11 | C | 1 | 99 | | 12 | D | 2 | 99 | | 13 | E
| 1 | 99 | | 14 | F | 2 | 99 | | 15 | G | 1 | 99 | | 16 | H | 2 | 99 |
+----+------+------+-------+