mysql（差异备份）

最新推荐文章于 2024-05-25 01:16:52 发布

wuli 阿勇

最新推荐文章于 2024-05-25 01:16:52 发布

阅读量767

点赞数

分类专栏：运维 linux

本文链接：https://blog.csdn.net/qq_43181762/article/details/87884843

版权

本文介绍了MySQL的差异备份概念，它备份自上次完全备份后发生改变的所有文件。在恢复时，仅需全量备份与最后一次差异备份。文章还演示了如何开启二进制日志，模拟数据增删改，并展示了误删数据后的恢复步骤，确保数据恢复到全量备份状态。

摘要由CSDN通过智能技术生成

差异备份

备份上一次的完全备份后发生变化的所有文件。
差异备份是指在一次全备份后到进行差异备份的这段时间内
对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。
开启mysql服务器的二进制的日志功能

[root@wuliyong ~]# vim /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

server-id =1
log-bin = mysql_bin
[root@wuliyong ~]# service mysqld restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL. SUCCESS!

查看数据库的内容

[root@wuliyong ~]# mysql -uroot -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.23-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wzy                |
+--------------------+
5 rows in set (0.00 sec)
mysql> use wzy;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------+
| Tables_in_wzy |
+---------------+
| yiyi          |
+---------------+
1 row in set (0.00 sec)

mysql> select * from yiyi;
+------+------+
| name | age  |
+------+------+
| tom  |   12 |
| mary |   16 |
| haha |   14 |
+------+------+
3 rows in set (0.00 sec)

读数据库进行完全备份

[root@wuliyong ~]# mysqldump -uroot -pyong123! --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-201902221511.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@wuliyong ~]# ll /opt/data/
总用量 122944
-rw-r-----. 1 mysql mysql       56 2月  21 20:47 auto.cnf
-rw-r-----. 1 mysql mysql      361 2月  21 21:05 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 2月  21 21:05 ibdata1
-rw-r-----. 1 mysql mysql 50331648 2月  21 21:05 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 2月  21 20:47 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 2月  21 21:08 ibtmp1
drwxr-x---. 2 mysql