Mysql数据备份与恢复

1、备份方式和分类

按备份方法分：

1. 冷备份（脱机备份）：是在关机数据库的时候进行的（可备份整个数据库）（不建议使用）
2. 热备份（联机备份）：数据库处于运行状态，依赖于数据库的日志文件
3. 温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作

按备份策略分：

1. 完全备份：每次对数据库进行完整的备份
2. 差异备份：备份自从上次 **完全备份** 之后被修改过的文件
3. 增量备份：只有在上次完全备份或者增量备份后被修改的文件才会被备份

2、mysqldump备份恢复

从分类上说，使用mysqldump备份一般属于：完全备份、温备份。

2.1、备份

导出指定数据库 dbName

mysqldump -uroot -p123456 dbName > /opt/backups.sql


# 省略参数 --databases

导出所有数据库

mysqldump -uroot -p123456 --all-databases > alldatabases.sql


# 参数  --all-databases

导出指定几张表

mysqldump -uroot -p123456 dbName table1 table2  > /opt/backups.sql


# 或者完整的参数
mysqldump -uroot -p123456 --databases dbName --tables table1 table2  > /opt/backups.sql

只导出表结构

mysqldump -uroot -p123456 -d dbName tableName  > /opt/backups.sql


# 参数 -d

只导出数据，没有表结构

mysqldump -uroot -p123456 -t dbName tableName  > /opt/backups.sql


# 参数 -t

标准命令行参数

mysqldump -u[用户名] -p[密码] [其他选项] [数据库名] > [备份文件.sql]

2.2、恢复

先登录mysql，再用source命令恢复

use dbName;
set names utf8;
source /opt/backups.sql;

使用source命令导入SQL文件时，对文件大小是有限制的。这个限制取决于服务器的配置和操作系统的限制。一般来说，MySQL有一个max_allowed_packet参数，它规定了MySQL服务器接受的最大数据包大小，默认为1GB。
如果要导入的SQL文件超过了max_allowed_packet参数设置的大小，那么可能会导致导入失败。在这种情况下，可以尝试将max_allowed_packet参数设置为更大的值，以便能够成功导入更大的SQL文件。

直接用mysql命令恢复

mysql --host=localhost --port=3306 --user=root --password=mysql --default-character-set=gbk -f < /opt/backups.sql

对于大于百M的SQL文件，如果光这样导入，速度是极其缓慢的。根据MySQL官方建议：

• 对于MyISAM，调整系统参数：bulk_insert_buffer_size（至少单个文件大小的2倍以上）
• 对于InnoDB，调整系统参数：innodb_log_buffer_size（至少单个文件大小的2倍以上，导入完成后可以改回默认的8M，注意不是innodb_buffer_pool_size。）
• 除主键外，删除其他索引，导入完成后重建索引。
• 关闭自动提交：autocommit=0。（请勿用set global autocommit=1;命令来关闭，否则整个MySQL系统都会停止自动commit，innodb log buffer很快就会爆满，5和6项也请仅在会话中有效，正确做法请往下看）
• 关闭唯一索引检查：unique_checks=0。（关闭了这一项会影响on duplicate key update的效果）
• 关闭外键检查：foreign_key_checks=0。
• 有自增列的，设置：innodb_autoinc_lock_mode的值为 2。

案例

# 备份脚本
set datevar=%date:~0,4%%date:~5,2%%date:~8,2%
MySQLdump -h127.0.0.1 -uroot -p123456 dbName --single-transaction -f --extended-insert=false  > "D:\data\backup_%datevar%.sql"
forfiles /p "D:\data" /m backup_*.sql -d -14 /c "cmd /c del /f @path"

linux脚本

#!/bin/bash


FILEDATE=$(date +%Y%m%d)
FILEDATETIME=$(date +%Y%m%d-%H%M%S)
if [ ! -d /opt/data_bak/"$FILEDATE"*.sql ]; then
/usr/bin/mysqldump -h127.0.0.1 -uroot -p123456 dbName --single-transaction -f --extended-insert=false  > /opt/data_bak/"$FILEDATETIME".sql
fi

linux脚本-docker定时备份

#!/bin/bash
 
#获取容器id 容器名称
container_id=`/usr/bin/docker ps -aqf "name=mysql"`
 
echo "mysql的镜像ID is $container_id"
 
 
mysql_host="127.0.0.1"
#登录用户名
mysql_user="root"
#登录密码(注意 如果密码包含特殊符号 前面要用'\')
mysql_password="123456"
mysql_port="3306"
#要备份的数据库名称，多个用空格分开隔开 如("db1" "db2" "db3")
backup_db_arr=("mydb1" "mydb2" "mydb3")
# 是否删除过期数据
expire_backup_delete="true"
#过期天数
expire_days=30
 
# 备份文件存放地址(根据实际情况填写)
backup_location="/opt/backup_data/mysql"
#定义备份详细时间
backup_time=`date +%Y%m%d%H%M`
#定义备份目录中的年月日时间
backup_Ymd=`date +%Y-%m-%d` 
#3天之前的日期
backup_3ago=`date -d '30 days ago' +%Y-%m-%d` 
#备份文件夹全路径
backup_dir=$backup_location/$backup_Ymd  
 
 
 
flag=`echo $?`
# 判断有没有定义备份的数据库，如果定义则开始备份，否则退出备份
if [ "$backup_db_arr" != "" ];then
 
for dbname in ${backup_db_arr[@]}
do
echo "开始执行数据库 $dbname 备份..."
`mkdir -p $backup_dir`
# 备份指定数据库中数据
 
`docker exec $container_id mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password -B $dbname --single-transaction -f --extended-insert=false > $backup_dir/backup-$dbname-$backup_time.sql`
 
 
flag=`echo $?`
if [ $flag == "0" ];then
echo "数据库 $dbname 成功备份到 $backup_dir/backup-$dbname-$backup_time.sql"
 
else
echo "数据库 $dbname 备份失败!"
fi
 
done
else
echo "没有待备份的数据库，停止数据库备份"
exit
fi
 
 
# 删除过期数据
if [ "$expire_backup_delete" == "true" -a "$backup_dir"!="" ];then
        `find $backup_dir/ -type f -mtime +$expire_days | xargs rm -rf`
        echo "删除失效的数据库备份信息!"
fi
 
echo "数据库已完成备份"
exit
fi

参考资料

1. Mysql数据库——数据备份与恢复
2. mysqldump 参数解析