[mysql] 备份与恢复

无锋dkf
184 阅读8分钟

一、前言

备份与恢复无疑是学习mysql不可或缺的一项技能，有段子说从入门到放弃，从删库到跑路，如果一个应用存储着大量的业务数据，没有进行定期备份的话，一旦出现一些不可抗力因素，硬件故障，用户误删除数据等造成数据丢失，将会造成整个系统瘫痪，重则导致公司倒闭，这时如果你定期备份数据并且及时恢复数据，对于老板而言，你的功劳绝不亚于拯救世界。这里我们只分析常见的，像MySQL Enterprise Backup 这种企业版 ，MyISAM这种引擎等，以后有机会再分析。

二、备份基础知识

2.1 根据文件方式

从大体上我们分为逻辑备份（比如mysqldump）和物理备份|（直接拷贝文件）

1）：逻辑备份：

mysqldump（MDP）    
replication

优点

  • 不需要下载安装,mysql官方自带
  • 可移植性高，文件直接导出SQL语句，语句可直接适用于其他SQL服务器；
  • 在数据恢复之前可增加、修改数据；
  • 数据恢复粒度小可以是服务器、数据库、表级别；
  • 使用文本格式，可读性高；
  • 压缩比较高，节省备份的磁盘空间
缺点

  • 备份时需要访问mysql服务器，影响其他客户端；

  • 需要将数据转换成逻辑格式（SQL，CSV）；

  • 如果命令运行在客户端，mysql服务器还需要将数据发送给客户端；

  • 因为输出格式为文本文件，占用空间较大；

  • 依赖于数据库引擎，需要从磁盘把数据读出，然后转换成SQL进行转储，比较耗费资源，数据量大的话效率较低

2）：物理备份：

MySQL Enterprise Backup(企业版)
percona xtrabackup（PBK,XBK）
优点
  • 完整的Mysql文件和目录备份，只需要复制文件不需要转换，速度比逻辑备份更快；
  • 除了备份数据，还能备份配置文件和日志文件；
  • 不需要运行Mysql服务器就可以完成备份；
  • 备份工具简单使用cp、scp、tar命令即可完成备份；
缺点
  • 可移植性不高，恢复数据只适用于相同或类似的机器上；
  • 为了保持数据库文件的一致性，需要停机备份；
  • 恢复粒度不能按表或用户恢复；
  • 可读性差
  • 压缩率低

2.2 根据数据库的运行状态，我们可分为3种

1）：热备份

  • 备份不需要停机，可读可写（只读不写，其实还可以又细分为温备份，这里不做分析）；
  • 业务正常

2）：温备份

  • 备份不需要停停机，需要加写锁，或者设置read_only；
  • 业务只读不写

3）：冷备份

  • 关机情况下；关闭数据库业务,数据库没有任何变更的情况下,进行备份数据.
  • 业务停止

2.3 备份策略：

1）：全量备份：

2）：增量备份：

三、备份操作

3.1：mysqldump工具的使用

连接mysql方式：

#-u(用户名)  -p(密码)   -S(socker套接字文件)   -h(ip)  -P(端口) 
#本地备份:
mysqldump -uroot -p  -S /tmp/mysql.sock
#远程备份:
mysqldump -uroot -p  -h 10.0.0.51 -P8001
1. 备份所有数据库

若想用mysqldump备份整个实例，可以使用 --all-databases 或 -A 参数：

root@6fcbb41f6f2a:/data# mysqldump -uroot -p --all-databases > /data/backup/all_database.sql

如果需要压缩的话

mysqldump -uroot -p --master-data=2 --single-transaction -A | gzip > /data/backup/all_database.sql.gz
2. 备份部分数据库

有的时候我们会遇到只需要备份某些库的需求，这个时候我们就可以使用 --databases 或 -B参数了，该参数后面跟数据库名称，多个数据库间用空格隔开。

eg：备份neti mysql 数据库：mysqldump -uroot -p --databases neti mysql > /data/backup/neti.sql

root@6fcbb41f6f2a:/tmp# mysqldump -uroot -p --databases neti> /data/backup/neti.sql
3.备份部分表

其中neti是数据库 t_usert_shop 是数据表

单表：

root@6fcbb41f6f2a:/tmp# mysqldump -uroot -p neti  t_user >/data/backup/t_user.sql

多表：

root@6fcbb41f6f2a:/tmp# mysqldump -uroot -p neti  t_user t_shop >/data/backup/neti_tb.sql

单表加 表中字段条件：

root@6fcbb41f6f2a:/tmp# mysqldump -uroot -p neti t_user --where=" create_time >= '2019-08-01 00:00:00' " > /tmp/t_user_create_time.sql

4 .参数解析

1）：只备份结构: --no-data 简写为 -d 选项；

root@6fcbb41f6f2a:/tmp# mysqldump -uroot -p neti --no-data > /data/backup/neti_no_data.sql

2）：只备份数据 --no-create-info 简写为 -t 选项。

root@6fcbb41f6f2a:/tmp# mysqldump -uroot -p neti --no-create-info > /data/backup/neti_no_data.sql

3）：备份存储过程 --routines 简写为 -R 选项。备份函数 --events 简写为 -E 选项。

mysqldump -uroot -pxxxxxx -R -E --databases testdb > /data/backup/testdb.sql

4）：以事务的形式备份

如果我们想在dump过程中保证数据的一致性，减少锁表，则可以用 --single-transaction 选项，这个选项对InnoDB的数据表很有用，且不会锁表。

mysqldump -uroot -pxxxxxx --single-transaction --databases testdb > /data/backup/testdb.sql

5）：以事务的形式备份-F 在备份开始时,刷新一个新binlog日志

6）：master-data

功能：

  • 备份是自动记录日志
  • 自动解表和锁表
  • 配合single transaction 可以减少锁表时间
mysqldump -uroot -p  -A  -R --triggers --master-data=2   >/data/backup/world.sql
[root@db01 ~]# grep 'CHANGE' /backup/world.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000035', MASTER_LOG_POS=194;

功能：
（1）在备份时，会自动记录，二进制日志文件名和位置号
    0 默认值
    1  以change master to命令形式，可以用作主从复制
    2  以注释的形式记录，备份时刻的文件名+postion号
（2） 自动锁表
（3）如果配合--single-transaction，只对非InnoDB表进行锁表备份，InnoDB表进行“热“”备，实际上是实现快照备份。

7）: --single-transaction

innodb 存储引擎开启热备(快照备份)功能       
master-data可以自动加锁
（1）在不加--single-transaction ，启动所有表的温备份，所有表都锁定
（1）加上--single-transaction ,对innodb进行快照备份,对非innodb表可以实现自动锁表功能
例子6: 备份必加参数
mysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=OFF >/data/backup/full.sql

8） --set-gtid-purged=auto

auto , on
off 
使用场景:
1. --set-gtid-purged=OFF,可以使用在日常备份参数中.
mysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=OFF >/data/backup/full.sql
2. auto , on:在构建主从复制环境时需要的参数配置
mysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=ON >/data/backup/full.sql

9）--max-allowed-packet=#

mysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=OFF --max-allowed-packet=256M >/data/backup/full.sql

mysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=OFF --max-allowed-packet=256M neti>/data/backup/full.sql

 --max-allowed-packet=64M
The maximum packet length to send to or receive from server.

mysqldump -uroot -P8001 -h47.98.137.146   -p  -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=OFF --max-allowed-packet=256M --databases neti>./full.sql

mysqldump -h47.98.137.146 -uroot -p123456  -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=OFF --max-allowed-packet=256M --databases neti >/data/backup/mysql/neti_20200421-151905.sql

3.6：增量备份

其实就是备份binlog文件

3.6：恢复

1）：全量恢复

mysql -uroot -p < /data/backup/all_database.sql

四、脚本备份

linux脚本一般用.sh来结尾的

1）：编写脚本

增量备份：

创建mysqlback.sh

date_now=$(date "+%Y%m%d-%H%M%S")
backUpFolder=/data/backup/mysql
host='47.98.137.146'
username="root"
password="123456"
db_name="neti"
port=8001
#定义备份文件名
fileName="${db_name}_${date_now}.sql"
#定义备份文件目录
backUpFileName="${backUpFolder}/${fileName}"
echo "starting backup mysql ${db_name} at ${date_now}."

mysqldump -h${host} -u${username} -p${password} -P${port}  -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=OFF --max-allowed-packet=256M --databases ${db_name} >${backUpFileName}

#进入到备份文件目录
cd ${backUpFolder}
#压缩备份文件
tar zcvf ${fileName}.tar.gz ${fileName}

增量备份脚本mysqlIncreamBackup.sh

#!/bin/bash
#在使用之前，请提前创建以下各个目录
backupDir=/data/backup/mysql/daily
#增量备份时复制mysql-bin.00000*的目标目录，提前手动创建这个目录
mysqlDir=/data/mysql
#mysql的数据目录
logFile=/data/backup/mysql/log/bak.log
BinFile=/data/mysql/mysql-bin.index
#mysql的index文件路径，放在数据目录下的

host='47.98.137.146'
username="root"
password="123456"
port=8001
containerid='6fcbb41f6f2a'

mysqladmin -h${host} -u${username} -p${password} -P${port} flush-logs
#这个是用于产生新的mysql-bin.00000*文件
# wc -l 统计行数
# awk 简单来说awk就是把文件逐行的读入，以空格为默认分隔符将每行切片，切开的部分再进行各种分析处理。
Counter=`wc -l $BinFile |awk '{print $1}'`
NextNum=0
#这个for循环用于比对$Counter,$NextNum这两个值来确定文件是不是存在或最新的


#进入容器


for file in `cat $BinFile`
do
    base=`basename $file`
    echo $base
    #basename用于截取mysql-bin.00000*文件名，去掉./mysql-bin.000005前面的./
    NextNum=`expr $NextNum + 1`
    if [ $NextNum -eq $Counter ]
    then
        echo $base skip! >> $logFile
    else
        dest=$backupDir/$base
        if(test -e $dest)
        #test -e用于检测目标文件是否存在，存在就写exist!到$logFile去
        then
            echo $base exist! >> $logFile
        else
        	docker cp $containerid:$mysqlDir/$base $backupDir
            #cp $mysqlDir/$base $backupDir
            echo $base copying >> $logFile
         fi
     fi
done
echo `date +"%Y年%m月%d日 %H:%M:%S"` $Next Bakup succ! >> $logFile

2）：执行脚本

第一种（这种办法需要用chmod使得文件具备执行条件(x): chmod u+x datelog.sh）：

chmod u+x mysqlback.sh
./mysqlback.sh

第二种（这种办法不需要文件具备可执行的权限也可运行）：

sh datelog.sh

第三种加入系统定时器

定时任务操作

crontab -u //设定某个用户的cron服务 
crontab -l //列出某个用户cron服务的详细内容 
crontab -r //删除某个用户的cron服务 
crontab -e //编辑某个用户的cron服务
crontab -i //打印提示，输入yes等确认信息

我们将 crontab -e增加

0 0 * * * sh /disk/mysqlbackup.sh

参数解析

# For details see man 4 crontabs
# Example of job definition:
# .---------------- minute (0 - 59)
# | .------------- hour (0 - 23)
# | | .---------- day of month (1 - 31)
# | | | .------- month (1 - 12) OR jan,feb,mar,apr ...
# | | | | .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat
# | | | | |
# * * * * * user-name command to be executed
定时任务的每段为：分，时，日，月，周，用户，命令
第1列表示分钟1～59 每分钟用*或者 */1表示
第2列表示小时1～23（0表示0点）
第3列表示日期1～31
第4列表示月份1～12
第5列标识号星期0～6（0表示星期天）
第6列要运行的命令

*：表示任意时间都，实际上就是“每”的意思。可以代表00-23小时或者00-12每月或者00-59分
-：表示区间，是一个范围，00 17-19 * * * cmd，就是每天17,18,19点的整点执行命令
,：是分割时段，30 3,19,21 * * * cmd，就是每天凌晨3和晚上19,21点的半点时刻执行命令
/n：表示分割，可以看成除法，*/5 * * * * cmd，每隔五分钟执行一次

文章部分内容参考：Mysql备份还有这么多套路，还不了解下？

avatar
文章
阅读
粉丝