Mysql备份与恢复全库、单库方法。

fittec_vb

已于 2023-08-09 13:40:51 修改

阅读量1k

点赞数

文章标签： adb

于 2023-08-09 13:27:56 首次发布

本文链接：https://blog.csdn.net/fittec_vb/article/details/132185931

版权

Mysql备份与恢复全库、单库方法。

单库备份，恢复单库。

如果你的MySQL数据库中的一个库出现了问题，你可以通过以下步骤来恢复：

首先，通过mysqldump命令将该库导出为一个.sql文件，以便在后续操作中备份数据。命令如下：
mysqldump -u root -p db_name > db_backup.sql
在MySQL客户端中，执行以下命令来删除该库：
drop database db_name;
重新创建该库：
create database db_name;
将备份数据导入到新创建的库中：
mysql -u root -p db_name < db_backup.sql
完成以上步骤后，使用以下命令刷新权限：
flush privileges;
最后，通过以下命令查看新创建的库是否恢复成功：
show databases;
以上是通过备份数据来恢复MySQL库的方法。如果备份数据不可用，还可以尝试使用数据恢复工具来进行恢复。
但是注意，使用恢复工具可能会丢失部分或全部数据，所以需要慎重使用。

全库备份，恢复单库

MySQL全备份可以备份所有数据库和数据表，可以通过MySQL命令行或者图形界面进行备份。
下面是备份数据库的MySQL命令：
mysqldump -u [用户名] -p [密码] --all-databases >all.sql
这会将所有数据库备份到all.sql文件中。要恢复单个库，首先需要找到数据库备份文件中对应的库。

可以使用grep命令搜索库的名称：
grep “CREATE DATABASE `[数据库名称]`” all.sql

这会输出备份文件中数据库的信息，可以查看库的表和结构。要恢复单个库，请按如下步骤操作：

创建一个新的数据库
CREATE DATABASE [新数据库名称];
从备份文件中导入单个库的数据
mysql -u [用户名] -p [密码] [新数据库名称] --one-database < all.sql
–one-database 可以简写 -o

完成上述步骤后，单个库就被成功地恢复了。通过上述步骤，我们可以使用MySQL进行全备份，并实现只恢复单个库。这对于数据库管理来说是非常实用的，可以更好地保护和恢复数据。

DBA运维人员在备份，恢复的职责：

1.设计备份，容灾策略
1.1 备份策略：
备份工具选择
备份周期设计
备份监控方法
1.2 容灾策略
备份：用什么备份
架构：高可用，演示从库，灾备库

2.定期的备份，容灾检查
备份软件----->带库
3.定期的故障恢复演练
4.数据损坏时的快速准确恢复
5.数据迁移工作

mysql常用的备份工具：

1.逻辑备份方式
mysqldump(MDP)
mydumper
load data in file
主从方式replication
2.物理备份方式
MySQL Enterprise Backup(企业版)
Percona Xtrabackup (PBK,XBK)

只导出某个库的存储过程及自定义函数

mysqldump -uroot -pxxxxxx -d -t -R db1 > /tmp/db1_routine.sql

以下是从从 MySQL 官方文档整理的mysqldump 工具的参数。

 --user    -u    MySQL用户名
 --pasword  -p  (小写)MySQL密码
 --host   -h  MySQL 的ip地址 
 --port   -P(大写)    MySQL 端口号
 --databases   -B    指定要备份的数据库
 --all-databases  -A   备份MySQL服务器上的所有数据库
 --add-drop-databases     每个创建库之前添加drop语句（默认不开启）
 --add-drop-table      每个创建表之前添加drop语句（默认开启）
 --events   -E      导出事件（默认不开启）
 --routines    -R   导出存储过程和自定义函数（默认不开启）
 --triggers    导出触发器（默认开启）
 --extended-insert   -e   使用具有多个VALUES列的INSERT 语法（默认开启）
 --ignore-table          不导出指定表，指定忽略多个表时，需要重复多次
 --no-data   -d              不导出任何数据，指导出数据库表结构
 --no-create-info  -t            只导出数据，而不添加CREATE TABLE 语句
 --no-create-db               -n    不添加create database 语句
 --force   -f               在导出过程中忽略出现的SQL错误（默认不开启）
 --tz-utc           在导出顶部设置时区TIME_ZONE=‘00：00’（默认开启）
 --where    -w           只转储给定的where条件选择的纪录
 --set-gtid-purged             是否添加SET@@GLOBAL.GTID_PURGED 输出
 --single-transaction               通过在一个事务中导出所有表而创建一个一致性的快照，适用于 innodb引擎
 --master-data           该选项将当前服务器的binlog的位置和文件名追加到输出文件中。如果为1，则输出CHANGE MASTER 命令，如果为2，则输出CHANGE MASTER 命令前添加注释信息
--flush-logs        -F
  --flush logs命令的作用就是关闭当前使用的binary log，然后打开一个新的binary log文件，文件的序号加1.
使用此参数会在导出前刷新日志文件，如果导出多个库，则在每个库导出前都会刷新一次日志；例外的是如果用了 --lock-all-tables or --master-data，则仅会刷新一次日志，对应此时刻所有表都会被锁上。
为了获得导出数据和刷新日志的一致性(同时发生)，必须将 --flush-logs 选项和 --lock-all-tables 或者 --master-data 一起使用。

我们说过，备份的最终目的是"还原"，而只备份表中的数据，往往是不够的，因为在数据库中，可能还存在一些存储过程和存储函数、存在一些触发器、事件表，
这些"东西"也需要备份，以免最终的备份"不全"，在恢复的时候就"懵逼"了，那么，我们来看一些其他的常用的选项，总结如下。
　 --routines选项：表示备份时，存储过程和存储函数也会被备份。（默认不开启）
　　--triggers选项：表示备份时，触发器会被备份。（默认开启）
　　--events选项：表示备份时，事件表会被备份。（默认不开启）

生产运用案例：

库IP：192.168.1.1

备库脚本:

# cat /root/mysqlbak.sh
#!/bin/bash
HOST=192.168.1.1
USER=root
PASSWORD=XXXXXX
BACKUP="/mysqldata/mysql_full_bak"
mysqldump  -h $HOST  -u$USER -p$PASSWORD --single-transaction --flush-logs --set-gtid-purged=OFF --master-data=2 --routines --triggers --events -A |gzip > $BACKUP/mysql_bak_$(date +%Y%m%d_%H%M%S).sql.gz

-routines 导出存储过程和自定义函数（默认不开启）
-triggers 导出触发器（默认开启）
-events 导出事件（默认不开启）
-single-transaction 通过在一个事务中导出所有表而创建一个一致性的快照，适用于 innodb引擎
-master-data 该选项将当前服务器的binlog的位置和文件名追加到输出文件中。如果为1，则输出CHANGE MASTER 命令，如果为2，则输出CHANGE MASTER 命令前添加注释信息
-set-gtid-purged 是否添加SET@@GLOBAL.GTID_PURGED 输出
-flush-logs 为了获得导出数据和刷新日志的一致性(同时发生)，必须将 --flush-logs 选项和 --lock-all-tables 或者 --master-data 一起使用：

定时任务：

50 23 * * * /root/mysqlbak.sh 2>> /tmp/mysqlbak.log

解压文件：

]# gunzip mysql_bak_20230717_235001.sql.gz
]# ll
total 6115948
-rw-r–r-- 1 root root 6262727987 Jul 18 09:04 mysql_bak_20230717_235001.sql