按照数据库状态划分
热备
热备份(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次,查看结果
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 修复