mysql使用脚本定时进行数据热备份

淡淡的烟草味﹌ 2022-01-29 23:27 423阅读 0赞

按照数据库状态划分  
热备  
热备份（Hot Backup）是指在数据库运行中直接备份，对正在运行的数据库没有任何影响。

冷备  
冷备份（Cold Backup）是指在数据库停止的情况下进行备份，这种备份最为简单，一般只需要拷贝相关的数据库物理文件即可  
**（由于要在数据库停止的情况下进行备份，所以一般用的非常少）**

冷备份的优点：  
1．是非常快速的备份方法（只需拷贝文件）  
2．容易归档（简单拷贝即可）  
3．容易恢复到某个时间点上（只需将文件再拷贝回去）  
4．能与归档方法相结合，作数据库“最新状态”的恢复。  
5．低度维护，高度安全。

冷备份的缺点：  
1．单独使用时，只能提供到“某一时间点上”的恢复。  
2．在实施备份的全过程中，数据库必须要作备份而不能作其它工作。也就是说，在冷备份过程中，数据库必须是关闭状态。  
3．若磁盘空间有限，只能拷贝到磁带等其它外部存储设备上，速度会很慢。  
4．**不能按表或按用户恢**复。  
值得注意的是**冷备份必须在数据库关闭的情况下进行**，当数据库处于打开状态时，执行数据库文件系统备份是无效的 。而且在恢复后一定要把数据库文件的属组和属主改为mysql。

**热备份 （on, 快）**  
热备份是在数据库运行的情况下，备份数据库操作的sql语句，当数据库发生问题时，可以重新执行一遍备份的sql语句。

**热备份的优点：**  
1．可在表空间或数据文件级备份，备份时间短。  
2．**备份时数据库仍可使用。**  
3．**可达到秒级恢复（恢复到某一时间点上**）。  
4．可对几乎所有数据库实体作恢复。  
5．恢复是快速的，在大多数情况下在数据库仍工作时恢复。

**按照备份方式划分**  
有两种常见的备份方式  
物理备份  
直接复制数据文件进行的备份。  
优点：无需额外工具，直接copy即可，恢复直接复制备份文件即可  
缺点：与存储引擎有关，跨平台能力较弱

逻辑备份  
从数据库中“导出”数据另存而进行的备份  
优点：能使用编辑器处理，恢复简单，能基于网络恢复，有助于避免数据损坏  
缺点：备份文件较大，备份较慢，无法保证浮点数的精度，使用逻辑备份数据恢复后，还需手动重建索引，十分消耗CPU资源。

**mysqldump热备份的操作步骤：**  
**一、创建备份目录**

mkdir   /data/mysql_bak
    
    cd /data/mysql_bak

**二、创建脚本**  
vi mysql\_auto\_back.sh

#!/usr/bin/env bash
    #########数据库基础信息#############
    DB_HOST=127.0.0.1
    DB_NAME=$1
    DB_USER=root
    DB_PASS=123456
    DATE=`date +%Y%m%d%H%M%S`
    MYSQL_PATH=/data/mysql_bak/
    #保存备份个数
    BACK_NUM=3
    
    ##########################
    back_up(){
    
    #bakup file absolute path
    BAKUP_FILE=${MYSQL_PATH}${DB_NAME}${DATE}.sql
    
    #执行mysqldump命令的参数
    SQL_OPT="-u${DB_USER} -p${DB_PASS} -h ${DB_HOST} ${DB_NAME}"
    
    #执行备份，整个DB所有表的备份，SQL语句的形式
    mysqldump ${SQL_OPT} > ${BAKUP_FILE}
    
    # 删除30天之前的就备份文件
    #find ${MYSQL_PATH}/* -mtime +10 -exec rm {} \;
    
    #写创建备份日志
    echo "create ${BAKUP_FILE}"  >> ${MYSQL_PATH}log.txt
    
    #找出需要删除的备份
    delfile=`ls -l -crt  ${MYSQL_PATH}$1*.sql | awk '{print $9 }' | head -1`
    #echo "${delfile}"
    
    #判断现在的备份数量是否大于$number
    count=`ls -l -crt  ${MYSQL_PATH}$1*.sql | awk '{print $9 }' | wc -l`
    if [[ $count -gt $BACK_NUM ]];then
         #删除最早生成的备份，只保留number数量的备份
         rm $delfile
         #写删除文件日志
         echo "delete $delfile" >> ${MYSQL_PATH}log.txt
    fi
    }
    back_up;
    #############脚本结束##############

**3、脚本赋予执行权限**

chmod  a+x    mysql_auto_back.sh

**4、测试脚本正确性,备份mysql库，执行4次，查看日志，看是否正确**

./    mysql_auto_back.sh   mysql

执行4次，查看结果  
![在这里插入图片描述][20190517112417909.png]

**5、配置定时任务**  
crontab -e  
\#每晚5点备份一次

30 5 * * * /data/mysql_bak/mysql_auto_back.sh   mysql

**说明：**  
最好本机和选一台远程机器都配置一个这样的定时备份任务。  
这样，当数据库服务器出现了故障，至少数据不会大面积的丢失。  
当然，如果你的mysql配置了主从，备份的数据更加实时。当主机出现故障后，可以直接切到备机上。

热备份也可以防止对数据库的误操作（由于主从同步，从机上的数据也会被删除）  
比如有同事故意或者粗心，将生产库误操作删除，那最起码我可以追回前一天的数据，而不会所有数据都丢失。

所以，针对线上环境的数据库，一般都是主从备份 + mysqldump多点热备份

**到这里使用mysql使用脚本定时进行数据冷备就配置完成了。**

**6、sql文件还原方法**  
1、  
（-f 参数表示在导出过程中忽略出现的SQL错误）

mysql -uroot -p"db_password"   -f dbname < backup.sql

（在导入较大的sql文件时可以放到后台执行）

nohup mysql -uroot -p"db_password" -f dbname < backup.sql > /dev/null 2>&1 &

2、  
mysql -uroot -p 先登录到数据库，  
use dbname; 切换到需要导入的库中  
然后执行 source /opt/backup.sql; 这样就可以导入了

**mysqldump备份常见用法**  
1、导出整个数据库(包括数据库中的数据）  
mysqldump -u username -p dbname > dbname.sql

2、导出数据库结构（不含数据）  
mysqldump -u username -p -d dbname > dbname.sql

–databases, -B 导出多少数据库，参数后面所有参量都被看作数据库名  
–no-data, -d 不导出任何数据，只导出数据库表结构  
–no-create-info, -t 只导出数据，而不添加CREATE TABLE 语句  
–no-create-db, -n 只导出数据，而不添加CREATE DATABASE 语句

**MySQL 5.6 警告信息 command line interface can be insecure 修复**  
[https://blog.51cto.com/880314/1348413][https_blog.51cto.com_880314_1348413]

[20190517112417909.png]: /images/20220130/feae12e82e56411ea82a01c35161c494.png
[https_blog.51cto.com_880314_1348413]: https://blog.51cto.com/880314/1348413