MySQL逻辑备份mysqldump(2)

日期：2020-06-02 栏目：程序人生浏览：次

[root@Admin ~]# service mysqld restart
Shutting down MySQL.... [确定]
Starting MySQL..... [确定]

再次查看开启：

mysqlbinlog

--start-datetime=name 开始的时间
--stop-datetime=name 结束的时间
--start-position=# 开始的位置（POS）
--stop-position=# 结束的位置

示例1

备份

先做全量备份，然后更新数据并误操作，数据恢复

[root@Admin ~]# mysqldump -p123 --flush-logs --master-data=2 --all-databases > /tmp/mysqlback/all_back.sql
•--flush-logs 备份时先将内存中日志写回磁盘，然后截断日志，并产生新的日志文件
•--master-data=2 该选项将二进制日志的位置和文件名写入到备份文件，等于2表示CHANGE
•MASTER语句被写成SQL注释；1表示没有注释，默认是1.

查看完整备份文件中的字段

[root@Admin ~]# vim /tmp/mysqlback/all_back.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=120;

数据更改

添加几条数据，然后随便删除一个库

更改完后查看mysql-bin.000008日志文件找到误删除的POS值

[root@Admin DB]# mysqlbinlog --no-defaults mysql-bin.000008
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#180520 3:55:12 server id 1 end_log_pos 120 CRC32 0x077f82c8 Start: binlog v 4, server v 5.6.31-log created 180520 3:55:12
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
IIEAWw8BAAAAdAAAAHgAAAABAAQANS42LjMxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAciC
fwc=
'/*!*/;
# at 120
#180520 3:56:46 server id 1 end_log_pos 201 CRC32 0xa954edb5 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1526759806/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 201
#180520 3:56:46 server id 1 end_log_pos 316 CRC32 0xc34378c8 Query thread_id=1 exec_time=0 error_code=0
use `login`/*!*/;
SET TIMESTAMP=1526759806/*!*/;
insert into t1(id,name) values(6,'eee')
/*!*/;
# at 316
#180520 3:56:46 server id 1 end_log_pos 347 CRC32 0xea43bde1 Xid = 1227
COMMIT/*!*/;
# at 347
#180520 3:56:53 server id 1 end_log_pos 428 CRC32 0x5fd30851 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1526759813/*!*/;
BEGIN
/*!*/;
# at 428
#180520 3:56:53 server id 1 end_log_pos 543 CRC32 0x97402f36 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1526759813/*!*/;
insert into t1(id,name) values(8,'aaa')
/*!*/;
# at 543
#180520 3:56:53 server id 1 end_log_pos 574 CRC32 0xfa2cc4ba Xid = 1228
COMMIT/*!*/;
# at 574
#180520 3:57:00 server id 1 end_log_pos 655 CRC32 0x7ba6913f Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1526759820/*!*/;
BEGIN
/*!*/;
# at 655
#180520 3:57:00 server id 1 end_log_pos 771 CRC32 0x7856052f Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1526759820/*!*/;
insert into t1(id,name) values(10,'bbb')
/*!*/;
# at 771
#180520 3:57:00 server id 1 end_log_pos 802 CRC32 0x0b597d2b Xid = 1229
COMMIT/*!*/;
# at 802
#180520 3:57:19 server id 1 end_log_pos 894 CRC32 0x47136864 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1526759839/*!*/;
drop database db01
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

通过查看二进制日志，由于我刚刚是删除的一个db01库，删除db01库的那条操作上面的POS值是at 802，所以我应该恢复到802

恢复

先进行全库恢复

[root@Admin ~]# mysql -p123 < /tmp/mysqlback/all_back.sql

[root@Admin ~]# mysqlbinlog --start-position=120 --stop-position=802 /data/DB/mysql-bin.000008 |mysql -p123

恢复完成后进入数据库查看是否存在刚刚添加的数据

总结

转载注明出处：https://www.heiqu.com/94ae71e4372c4cf2fb4eb65da63c6abd.html

MySQL逻辑备份mysqldump(2)

相关推荐