mysql数据库物理及逻辑备份

一、理论知识
1.1：物理备份
备份数据库操作系统的物理文件（数据文件、日志文件等）：

冷备份（脱机备份）：关闭数据库进行
热备份（联机备份）：数据库正运行，依赖数据库的日志文件
温备份：数据库锁定表格（可读不可写）后进行
常见方法：

物理冷备：拷贝数据库文件打包备份，需进行锁表-备份-解表。恢复时拷贝到数据库文件原存放目录
第三方工具热备份：免费MySQL热备份软件Percona XtraBackup
1.2：逻辑备份
备份数据库逻辑组件（表等数据库对象），备份策略角度可分为：

完全备份：每次对数据进行完整备份
差异备份：备份自上次完全备份后被修改过的文件
增量备份：只有在上次完全备份或增量备份后被修改的文件
常见方法：

常用逻辑备份工具：使用mysqldump将数据表导成sql脚本文件
二进制日志增量备份：MYSQL主从复制，将DDL和DML操作通过二进制文件(bin-log)传送到服务器上，再从服务器上对这些日志做重新执行操作
也可以使用select…into outfile语句。
二、实操
2.1：物理冷备和恢复
tar数据备份（完全备份）
关闭数据库

systemctl stop mysqld
1
创建目录专门存放数据库的备份数据

mkdir /app/mysql_backup
1
切换到数据库的目录下，将数据拷贝到备份目录下

tar -zcvf /app/mysql_backup/mysql_all-$(date +%F).tar.gz /app/mysql/data/
#$(date +%F)先执行，以时间命名
1
2
启动数据库

systemctl start mysqld
1
备份成功
注意：备份数据应该复制到其他服务器或存储上

数据还原
启动数据库

systemctl start mysqld
1
清理原先数据库中的数据

rm -rf /app/mysql/data/*
1
查询导入备份数据mysql_all-2023-10-13tar.gz

cd /app/mysql_backup
ll
# 总用量 1288
# -rw-r--r-- 1 root root 1316616 10月 13 16:14 mysql_all-2023-10-13tar.gz
tar -xvf /app/mysql_backup/mysql_all-2023-10-13tar.gz -C /app/mysql/data
# -C 重定向，重定向到原数据库目录下
1
2
3
4
5
6


启动数据库

systemctl start mysql
1
登录时需重新找到初始密码，执行以下语句

cat /路径/mysqld.log | grep password | grep localhost:|awk -F"localhost:" '{print $NF}'
1
知识补充： tar命令参数⬅详细内容请点链接

必用其中之一，最后都要跟 -f 使用档案名字
-c：建立压缩档案（产生新的包）
-x：解压
-r：向压缩归档文件末尾追加文件（增加文件）
-u：更新原压缩包中的文件
可选参数
-z：有gzip属性的

2.2：mysqldump备份与恢复
mysqldump是MySQL自带的备份工具，可方便实现对MySQL的备份。可以将指定的库、表导出为SQL脚本。
使用命令mysql导入备份的数据

mysqldump -u root -p --all-databses > all-data-$(date +%F).sql ###备份所有数据库
mysqldump -u root -p -databases auth mysql > auth-mysql.sql ###备份auth和mysql库
mysqldump -u root -p auth > auth-$(data +%F).sql ###备份auth数据库
mysqldump -u root -p mysql user > mysql-user-$(date +%F).sql ###备份mysql的user表
mysqldump -u root -p -d mysql user > /tmp/desc-mysql-user.sql ###备份mysql库user表的结构
1
2
3
4
5
对test_db库进行备份

mysqldump -u root -p test_db > /app/mysql_backup/test_db-$(date +%F).sql
1
查看备份文件为test_db-2023-10-13.sql

cd /app/mysql_backup
ls
# mysql_all-2023-10-13tar.gz  test_db-2023-10-13.sql
1
2
3


连接MySQL，查看test_db数据库中的原有表及数据

mysql> use test_db;
mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| score             |
+-------------------+
1
2
3
4
5
6
7
删除原表（模拟数据丢失）

mysql> drop table score;
1
用source恢复

mysql> source /app/mysql_backup/test_db-2023-10-13.sql;
1
再次查看就可以发现数据已经恢复了