mysql全备+二进制日志恢复操作-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_44825767/article/details/115514736

该篇博客详细介绍了MySQL数据库的备份与恢复过程，包括使用`mysqldump`进行全备，通过二进制日志恢复数据，并在数据库出现故障后如何利用备份文件和binlog恢复到特定状态。整个流程涵盖了从flush logs、插入和删除操作，到模拟故障删除数据库，再到最终的数据恢复步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

备份和还原操作：

1-产生一个全新的二进制文件

root@nongda 11:21  mysql>flush logs;
Query OK, 0 rows affected (0.01 sec)

root@nongda 11:22  mysql>root@nongda 11:22  mysql>show master status;
+--------------------------+----------+--------------+------------------+-------------------+
| File                     | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------------+----------+--------------+------------------+-------------------+
| mysql-compile-bin.000006 |      154 |              |                  |                   |
+--------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

2.给数据库做全备

[root@mysql-compile backup]# mysqldump -uroot -p"Sanchuang123#" --databases nongda >/backup/nongda.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

3.让数据发生变化，进行insert 和删除操作等

root@nongda 11:28  mysql>insert into stu (id,name) values (5,"tnn");
Query OK, 1 row affected (0.00 sec)

root@nongda 11:28  mysql>insert into stu (id,name) values (6,"oz");
Query OK, 1 row affected (0.00 sec)


root@nongda 11:29  mysql>show master status;
+--------------------------+----------+--------------+------------------+-------------------+
| File                     | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------------+----------+--------------+------------------+-------------------+
| mysql-compile-bin.000006 |      683 |              |                  |                   |
+--------------------------+----------+--------------+------------------+-------------------+

4.模拟出现故障，删除数据库

root@nongda 11:30  mysql>drop database nongda;
Query OK, 9 rows affected (0.17 sec)
List item

5.开始取恢复数据

第一步恢复全备

[root@mysql-compile backup]# mysql -uroot -p"Sanchuang123#" >nongda.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.

第2步：查看二进制日志找到删除数据库之前的position 位置号

[root@mysql-compile mysql]# mysqlbinlog mysql-compile-bin.000006 |egrep -C 5 "drop database nongda"
#210408 11:30:51 server id 2  end_log_pos 748 CRC32 0x270c8afa 	Anonymous_GTID	last_committed=2	sequence_number=3	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
#at 748
#210408 11:30:51 server id 2  end_log_pos 846 CRC32 0xdaf459fa 	Query	thread_id=3	exec_time=0	error_code=0
SET TIMESTAMP=1617852651/*!*/;
drop database nongda
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
 End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
[root@mysql-compile mysql]#

第3步：使用二进制日志去恢复

> [root@mysql-compile mysql]# mysqlbinlog --start-position=154
> --stop-position=748 mysql-compile-bin.000006 |mysql -uroot -p"Sanchuang123#" mysql: [Warning] Using a password on the command line interface can be insecure.

6.查看数据是否恢复

root@nongda 11:30  mysql>select * from nongda;
+----+---------+
| id | name    |
+----+---------+
|  1 | cr      |
|  2 | cr2     |
|  3 | chenran |
|  4 | qjh     |
|  5 | tnn     |
|  6 | oz      |
+----+---------+
6 rows in set (0.00 sec)