MySQL之备份与还原

最新推荐文章于 2024-08-23 14:01:54 发布

Pod️

最新推荐文章于 2024-08-23 14:01:54 发布

阅读量52

点赞数

分类专栏： MySQL 文章标签：运维 linux

本文链接：https://blog.csdn.net/m0_54232496/article/details/129427635

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

MySQL之备份与还原

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

root@(none) 10:33  sc-mysql>show master status;
+---------------------+----------+--------------+------------------+-------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
| sc-mysql-bin.000004 |    23054 |              |                  |                   |
+---------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

root@(none) 10:34  sc-mysql>flush logs;
Query OK, 0 rows affected (0.01 sec)

root@(none) 10:35  sc-mysql>show master status;
+---------------------+----------+--------------+------------------+-------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
| sc-mysql-bin.000005 |      154 |              |                  |                   |
+---------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

root@(none) 10:35  sc-mysql>

2、给数据库做全备

[root@sc-mysql mysql]# mysqldump -uroot -p'Sanchuang123#' --databases sanchuang >/backup/sanchuang.sql

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

root@sanchuang 10:40  sc-mysql>insert into emp(id,name,deptid) values(3,'苏文洋',20);
Query OK, 1 row affected (0.00 sec)

root@sanchuang 10:40  sc-mysql>insert into emp(id,name,deptid) values(4,'qinjiahui',20);
Query OK, 1 row affected (0.00 sec)

root@sanchuang 10:40  sc-mysql>insert into emp(id,name,deptid) values(5,'chenran',20);
Query OK, 1 row affected (0.00 sec)

root@sanchuang 10:41  sc-mysql>show master status;
+---------------------+----------+--------------+------------------+-------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
| sc-mysql-bin.000005 |      998 |              |                  |                   |
+---------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

root@sanchuang 10:41  sc-mysql>

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

root@sanchuang 10:41  sc-mysql>drop database sanchuang;

5、开始取恢复数据

第1步：恢复全备

[root@sc-mysql backup]# mysql -uroot -p'Sanchuang123#' < sanchuang.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@sc-mysql backup]#

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

[root@sc-mysql mysql]# mysqlbinlog  -v sc-mysql-bin.000005|egrep -C 5 "drop database"
#210401 10:42:39 server id 1  end_log_pos 1063 CRC32 0x8623d686 	Anonymous_GTID	last_committed=3	sequence_number=4	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1063
#210401 10:42:39 server id 1  end_log_pos 1170 CRC32 0xf9eee347 	Query	thread_id=14	exec_time=0	error_code=0
SET TIMESTAMP=1617244959/*!*/;
drop database sanchuang
/*!*/;
# at 1170
#210401 10:44:13 server id 1  end_log_pos 1235 CRC32 0xa92ef3c4 	Anonymous_GTID	last_committed=4	sequence_number=5	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1235
[root@sc-mysql mysql]#

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

[root@sc-mysql mysql]# mysqlbinlog   --start-position=154 --stop-position=1063  sc-mysql-bin.000005|mysql -uroot -p'Sanchuang123#'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@sc-mysql mysql]#

6、查看数据是否恢复

root@sanchuang 10:54  sc-mysql>select * from emp;
+----+-----------+--------+
| id | NAME      | deptid |
+----+-----------+--------+
|  1 | 张三      |     10 |
|  2 | 李四      |     10 |
|  3 | 苏文洋    |     20 |
|  4 | qinjiahui |     20 |
|  5 | chenran   |     20 |
| 19 | 吴佩      |     10 |
+----+-----------+--------+
6 rows in set (0.00 sec)

root@sanchuang 10:55  sc-mysql>