MySql数据库备份与还原
备份（mysqldump）
mysqldump 默认情况下只备份数据库的结构和数据，而不包括存储过程、函数和触发器等逻辑对象。这是因为这些对象是以二进制形式存储的，而 mysqldump 主要关注的是 SQL 语句的导出。
- 确保函数和存储过程也被包含在备份中，你需要在
mysqldump命令中添加--routines选项。例如：
-
mysqldump -u username -p --routines database_name > backup.sql
-
- 对于触发器，使用
--triggers选项：-
mysqldump -u username -p --triggers database_name > backup.sql
-
- 备份一个数据库的所有结构（包括表、存储过程、函数和触发器），可以使用以下命令：
-
mysqldump -u username -p --routines --triggers database_name > backup.sql
-
补充：备份恢复部分函数使用
log_bin_trust_function_creators 是一个系统变量，它控制着是否在二进制日志中记录存储过程或函数创建语句。当这个变量设置为 ON 时，即使存储过程或函数使用了不被二进制日志记录的命令（例如，NO SQL 或 READS SQL DATA），这些命令也会被记录。这对于复制和恢复非常重要，因为它确保了所有更改都被记录下来。
- 查看变量状态
-
SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
-
-
修改
log_bin_trust_function_creators变量的值- 运行时使用
SET命令。例如，要在运行时将其设置为ON，你可以执行：-
SET GLOBAL log_bin_trust_function_creators = ON; --刷新修改立即生效-- flush privileges;
-
- MySQL的配置文件（通常是
my.cnf）中设置它：-
[mysqld] log_bin_trust_function_creators = 1 # 或者使用 ON，这取决于你的MySQL版本和配置文件的语法要求。 - 配置保存后重启生效
-
- 运行时使用
实现指定数据库备份
实现功能：
1、备份指定的数据库
2、删除指定天数前的备份文件，默认设定了1天
脚本示例（mysql_bak.sh）
mysql_bak.sh
# 数据库备份根目录
BACKUP_DIR="/usr/local/mysqlbackups/tsmbak/"
#指定mysql所在主机的主机名
DB_HOSTNAME='hostname'
#指定mysql登录用户名
DB_USERNAME='root'
#指定mysql登录密码
DB_PASSWORD='xxxx'
#指定备份的数据库名
DB_NAME="xxxx"
#定义当前日期为变量
CURRENT_DATE=$(date +"%Y%m%d$H")
#定义删除N天前的文件变量
DEL_DAYS_BEFORE_FILES=1
#指定mysqldump所在目录
MYSQLDUMP_DIR="/usr/bin"
#按日期创建目录(数据库备份目录)
BASH_PATH=${BACKUP_DIR}/${CURRENT_DATE}
echo "---------------------------------------------------------"
# 判断目标路径是否存在
if [ ! -d ${BASH_PATH} ];then
echo "文件不存在，创建文件夹：${BASH_PATH}"
mkdir -p ${BASH_PATH}
else
echo "${BASH_PATH} 文件夹已存在"
fi
echo "开始备份 ${DB_NAME} 数据库"
#备份指定数据库
if $($MYSQLDUMP_DIR/mysqldump -h ${DB_HOSTNAME} -u${DB_USERNAME} -p${DB_PASSWORD} --routines --triggers ${DB_NAME} > "${BASH_PATH}/${DB_NAME}_${CURRENT_DATE}.sql");then
cd ${BASH_PATH}
gzip ${DB_NAME}_${CURRENT_DATE}.sql
echo "---------------------------------------------------------"
echo "${CURRENT_DATE}--Backup database ${DB_NAME} successfully!"
echo "---------------------------------------------------------"
else
echo "----------------------------------------------------------"
echo "${CURRENT_DATE}--Backup database ${DB_NAME} unsuccessfully"
echo "----------------------------------------------------------"
fi
#删除指定N天前的备份文件及目录
#如 +2：表示3天以前 -2：2天以内 1：1天以前的24小时 0：表示1天以内
find ${BACKUP_DIR} -name "*" -type f -mtime ${DEL_DAYS_BEFORE_FILES} -exec rm -rf {} \;
echo "已删除${DEL_DAYS_BEFORE_FILES}天前的备份文件及目录"
echo "---------------------------------------------------------"赋予文件执行权限
chmod +x mysql_bak.sh运行测试
# 当前目录下
./mysql_bak.sh如果报/bin/bash^M: 坏的解释器：没有那个文件或目录 ”换了几种编译方法就解决了，但这次还是不行，于是又换：
bash mysql_bak.sh
原因可能是因为我在win下操作的时候，修改到了此文件。
在win下编辑的时候，换行结尾是\n\r ， 而在linux下 是\n，所以才会有 多出来的\r
# 将文件中\r替换为空白
sed -i 's/\r$//' mysql_bak.sh再次编译！成功！！
find -mtime 介绍
find为查询命令，-time是根据时间查询，可作为条件，具体设置不同，查询结果不同
mtime参数的理解应该如下：
-mtime n 按照文件的更改时间来找文件，n为整数。
n 表示文件更改时间距离为n天
-n 表示文件更改时间距离在n天以内
+n 表示文件更改时间距离在n天以前
示例：
-mtime 0 表示文件修改时间距离当前为0天的文件，即距离当前时间不到1天（24小时）以内的文件。
-mtime 1 表示文件修改时间距离当前为1天的文件，即距离当前时间1天（24小时－48小时）的文件。
-mtime＋1 表示文件修改时间为大于1天的文件，即距离当前时间2天（48小时）之外的文件
-mtime -1 表示文件修改时间为小于1天的文件，即距离当前时间1天（24小时）之内的文件
为什么-mtime＋1 表示文件修改时间为大于1天的文件，即距离当前时间48小时之外的文件，而不是24小时之外的呢？ 因为n值只能是整数，即比1大的最近的整数是2，所以-mtime＋1不是比当前时间大于1天（24小时），而是比当前时间大于2天（48小时）
实现备份全部数据库
查看用户和权限：SELECT * FROM INFORMATION_SCHEMA.USER_PRIVILEGES
mysql_bak.sh
DAY_Y=`date +%Y%m%d`
#备份按天文件夹
backup_dir=/disk1/mysqlbackup/month/database_bk_$DAY_Y/
#如果文件夹不存在则创建
if [ ! -d $backup_dir ];
then
mkdir -p $backup_dir;
fi
# 1. 先获取所有数据库列表（排除 mysql、information_schema、performance_schema、sys）
mysql -u root -p'Abc@123456' -NBe "SHOW DATABASES WHERE \`Database\` NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')" > databases.txt
#2. 用获取的数据库列表进行备份（排除系统库） --routines --triggers （包含函数等）
mysqldump -uroot -p'Abc@123456' --routines --triggers --databases $(cat databases.txt) -x |gzip > $backup_dir/database_bk_$(date +%Y%m%d_%H%M%S).sql.gz
#全部数据库备份
#mysqldump -uroot -p'Abc@123456' --routines --triggers --all-databases -x |gzip > $backup_dir/database_bk_$(date +%Y%m%d_%H%M%S).sql.gz
#当前时间前7天
DEL_DAY=`date -d "7 days ago" +%Y%m%d`
#删除7天前备份文件
rm -rf /disk1/mysqlbackup/month/database_bk_$DEL_DAY备份过程中报错解决：
1、The 'INFORMATION_SCHEMA.GLOBAL_STATUS' feature is disabled;
-- 从mysql5.7.6开始information_schema.global_status已经开始被舍弃，为了兼容性，此时需要打开 show_compatibility_56
-- 解决步骤
--1、先看状态
mysql> show variables like '%show_compatibility_56%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| show_compatibility_56 | OFF |
+-----------------------+-------+
1 row in set (0.01 sec)
————————————————
--2、第一种就是直接输入开启命令（这种如果服务器或者数据库重启，就失效了。为了永久解决，就去mysql的配置文件中添加一条配置）
mysql> set global show_compatibility_56=on;
Query OK, 0 rows affected (0.00 sec)
--2、第二种永久解决
[root@master ~]# vim /etc/my.cnf
在配置文件内添加：
show_compatibility_56 = 1
然后保存，并重启mysql
定时任务
创建定时任务,比如每天凌晨两点执行备份操作
# Linux内置的用于定期执行程序的命令 -l 查看当前所有定时程序任务
crontab -e编辑一下内容并保存退出：
# 定时执行程序，且将日志保存到指定目录
* 2 * * * /usr/local/mysqlbackups/mysql_bak.sh &> /usr/local/mysqlbackups/tsmbak/mysql_bak.log编辑完之后按下Esc键，输入：冒号，输入wq 保存退出即可
还原
由于备份后的文件是经过压缩的如下，所以先解压
使用gunzip命令如下：
gunzip xxxxxx_20220303.sql.gz解压后为.sql后缀文件，没错查看内容其实就是sql语句，如下
接下来就是执行sql语句了：
mysql -u username -p database_name < file.sqldatabase_name：是要执行sql文件的数据库名
file.sql：是要执行的sql文件路径
或使用以下步骤：
1、进入mysql数据库
mysql -u root -p 回车，然后输入密码
2、查看当前所有数据库，并指定需要还原的数据库
show databases; ------ 查看所有数据库
use 'databaseName'; -----指定使用的数据库
create database test01; ----- 建立新库
3、使用source调用sql进行
source /usr/local/mysqlbackups/tsmbak/xxxxxxx_20220303.sql ----- 等待执行完成
4、查看还原的表或数据
show tables; ----- 查看当前库所有的表
至此，就完成啦
部分操作截图如下：
浙公网安备 33010602011771号