mysql备份恢复常用

备份注意事项

基本原则

备份文件不要放在同一个主机上, 异地备份
备份文件需要校验hash并记录
需要偶尔去验证一下备份恢复流程是否可行
性能敏感性，避免在主库上备份, 去二级数据库上进行备份
注意binlog文件的连续性
对重点表进行单独表级别的备份
lvm或存储层的快照备份不可靠
重要业务可以考虑一个延迟同步实例

恢复场景

全量恢复
单库恢复
单表或多表
单表内的部分行
某1条数据

恢复方式

使用物理备份文件全覆盖
从逻辑备份文件恢复到指定库
从备份文件 -> 新的恢复专用空库 -> 提取数据 -> 恢复到生产库
从 binlog 中提取 sql –> 编辑sql –> 恢复(insert/update)

恢复校验: 基础校验(大小, 行数) + 业务校验(金额, 时间, 状态等业务指标)

其它

备份时附加记录的元数据:
备份时间, 文件大小, 文件hash, 行数, 表数量, 备份使用的完整命令等

热备份: 读写不受影响
温备份: 类似于只读从库, 实时同步但仅可以执行读操作
冷备份: 离线备份

备份组合: 每周全量 + 每日增量 + 实时备份(同步binlog)

常用工具简介

mysqldump
优点: 自带工具, 一般够用
缺点: 因为是逻辑备份, 所以备份和恢复耗时都比较长

xtrabackup
物理备份, 备份和恢复速度快, 可以不影响数据库服务情况下进行热拷贝

mysqlpump
5.7+ 新增的官方备份工具，是 mysqldump 的一个衍生；
支持基于库和表的并行导出

mydumper
早期的并行dump工具

逻辑备份和物理备份

逻辑备份的特点是: 恢复时内容是需要被执行的, 本质是插入sql

优点: 跨版本迁移方便, 适合升级前备份, 支持编辑后恢复

物理备份的特点是: 拷贝的底层数据文件, 恢复时可以直接启动, 不需要再执行一遍sql;(增量的部分是重放redo)

优点: 速度快，缺点: 空间占用大, 不能跨版本和平台进行迁移。

增量备份的方式

非侵入型

刷新binlog后，备份增量的binlog文件
xtrabackup 的增量备份

侵入型

纯插入场景, 记录下每次的 id 偏移量, 然后备份新增部分
表内如果有 update_time 字段, 备份时按时间筛选
准备一个备份状态表, 管理备份的阶段信息
触发器模式
etl 模式

恢复

1
2
3
4
5


# 直接加载文件
mysql>source url\file.sql

# 非交互式恢复
mysql -uroot -p123 -h 192.x.x.x < /backup/alldb.sql

在线恢复

禁止普通用户写, 只允许超级用户写(便于导入数据)

1
2


set read_only=on;
set super_read_only=off;

加速恢复速度；

可以先关闭普通索引

1
2
3


alter table t_name disable keys     -- 关闭普通索引
load data infile file.txt
alter table t_name enable keys      -- 开启普通索引

如果确定导入值不会冲突, 可以先关闭唯一索引检查; 仅适用于InnoDB, 必须在事务外使用;

1
2


set unique_checks=0     -- 关闭
set unique_checks=1     -- 开启

针对 innodb 表, 如果导入数据是按主键顺序排列，可以提高导入效率
关闭自动提交，在导入完成后，再开启也会加快导入速度
关闭 binlog
innodb_flush_log_at_trx_commit=0

基于 binlog 的恢复

导出mysql-bin.00000012二进制文件进行还原及时点

1
2
3


# 一般不这样搞, 除非场景特别简单
mysqlbinlog mysql-bin.00000012 > tmp.sql
mysql -uroot -p < tmp.sql

mysqldump

主要参数

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113



-A --all-databases      # 导出所有数据库
-B --databases          # 指定数据库, 支持多库; 单库时可以省略

--tables                # 指定表, 会覆盖 --databases or -B 选项
--ignore-table=db1.t1   # 忽略表, 指定多个

-F, --flush-logs
    # 开始导出前刷新 binlog 日志, 方便后续的增量备份或添加从库
    注意: 假如一次导出多个数据库(使用选项 --databases 或者 --all-databases),
    将会逐个数据库刷新日志;
    除使用 --lock-all-tables 或者 --master-data 外;
    在这种情况下, 日志将会被刷新一次, 相应的所以表同时被锁定;
    因此, 如果打算同时导出和刷新日志应该使用 --lock-all-tables 或者 --master-data 和 --flush-logs;
    会执行 FLUSH TABLES, 等待关闭全部表

--extended-insert       # 将多行数据合并成一个 insert 语句, 默认有开启
--skip-extended-insert  # 关闭 sql 合并, 导出 sql 时, 将每行数据生成一个 insert 语句

-t, --no-create-info    # 只有数据, 不包含创建表等创建动作, 即只有 insert

--master-data=[1,2]
--source-data[=#]
    # 5.8+ 改名为 source-data
    # 将 binlog 的位置和偏移量 追加到输出文件中;
    # 1 输出 CHANGE MASTER 命令;
    # 2 输出的 CHANGE MASTER 命令前有添加注释信息;

    # 该选项将打开 --lock-all-tables 选项,
    # 除非 --single-transaction 也被指定, 该选项自动关闭 --lock-tables 选项;

    # 添加该选项时, 为了获取 show master status 信息, 会获取一次全局锁,
    # 不应该高频率地使用此选项, 单库备份这种频率较低的可以使用

--dump-slave[=#]
--dump-replica[=#]
    # 和 source-data 效果一样, 只是方便从库添加

--include-master-host-port
    # 配合 --dump-slave 使用, 在文件内添加主节点的ip和端口, 方便直接添加从库


-w, --where=name        # 只备份符合条件的, 一般用于增量备份

-d --no-data            # 只导出表结构

-q --quick
    # 强制 mysqldump 从服务器每次查询一行数据而不是查询整个表;
    # 避免大表导出时查询刷爆内存且热点数据被刷出缓冲池, 通常建议总是启用该选项;

--single-transaction
    # 在单个事务中执行动作; 保证一致性和解决 锁表的问题
    # 当只使用此选项进行备份时, 不会产生表锁
    # 适用于 InnoDB 存储引擎, 需要确保导出时无 DDL 操作
    # 此选项会自动关闭 --lock-tables

-l, --lock-tables
    # 写锁, 导出过程中依次锁住每个 schema 下所有表(只能保证各 schema 下表导出的一致性),
    # 被锁的表只能读, MyISAM 存储引擎常用

--skip-lock-tables

-x, --lock-all-tables
    # 提交请求锁定所有数据库中的所有表, 以保证数据的一致性;
    # 这是一个全局读锁, 并且自动关闭 --single-transaction 和 --lock-tables 选项;
    # 适合 MyISAM 存储引擎

--flush-privileges
    # 有与 mysql 这个库相关的数据导出时, 最好是添加这个
    # 在转储 mysql 数据库后, 发出一个 FLUSH PRIVILEGES 语句

--triggers          # 转储触发器, 默认关闭

--skip-triggers
    默认导出表中是带着触发器的, 不要触发器可以用 --skip-triggers

-n, --no-create-db

-Y, --all-tablespaces   导出全部表空间
-y, --no-tablespaces    # Do not dump any tablespace information 不导出任何表空间信息

--add-drop-database
--add-drop-database     Add a DROP DATABASE before each create.
--add-drop-table        # 在每个 create 语句之前增加一个 drop table; 默认为 if drop
--skip-add-drop-table   # 不添加 if drop; 直接就是 create


# data
--add-locks         # 在 INSERT 语句前添加 lock
--allow-keywords    # 允许使用关键字 做为列名
--ignore-error=name
--ignore-table=name

-E, --events        # Dump events 事务; mysqldump 默认没有导出事务和存储过程
-R --routines       # 转储存储过程(函数和过程)

--hex-blob
    # 以十六进制格式转储二进制字符串(BINARY, VARBINARY, BLOB);
    # 避免导出的二进制字符串乱码

--set-gtid-purged=OFF   # 关闭 gtid 相关的事件转储

--net-buffer-length=
    # TCP/IP和套接字通信的缓冲区大小
    # 导出值不能比目标数据库的值大

--max-allowed-packet=
    # 发送到服务器或从服务器接收的最大数据包长度

--tz-utc                # 需要跨时区导入导出时, 需要添加
                        # 会自动转换 TIMESTAMP 字段内容为 utc0 时区的时间,
                        # 并设置在导出文件顶部设置时区 TIME_ZONE='+00:00'
                        # 只影响 TIMESTAMP 类型, DATETIME 等不受影响

实例

单表

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20


# 普通备份
# DROP TABLE IF EXISTS; CREATE TABLE; LOCK TABLES; INSERT INTO; UNLOCK TABLES;
mysqldump db t1 > t1.sql

# 备份表数据 - 只有数据不含创建表
# LOCK TABLES; INSERT INTO; UNLOCK TABLES;
mysqldump -t db t1 > t1.sql

# 备份表结构
# DROP TABLE IF EXISTS; CREATE TABLE;
mysqldump -d db1 t_1 > t_1.sql

# 多表
mysqldump db t1 t2 t3 > t_all.sql

# 排除部分表
mysqldump db1 --ignore-table=db1.test1 --ignore-table=db1.test2 > /data/bak/db1.sql

# 指定 where 条件导出表的部分数据
mysqldump -w "id=6032" t_1 > /tmp/where.sql

单库

1
2
3
4
5
6
7
8


# 普通备份; 即多个 普通表 备份的组合
mysqldump db1 > db1-db.sql

# 仅导出单个 db 数据
mysqldump -u root -p db1 -t > /data/bak/db1.sql

# 单库备份 - 只备份表结构
mysqldump -d -B db1  > db1-db.sql

多库

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


# 全量备份所有内容 - 常用
mysqldump -u root -p \
    --all-databases --add-drop-table --routines --triggers --events \
    > /path/to/full_backup_$(date +%Y%m%d).sql

# 导出所有 db 结构和数据
mysqldump -uroot -p -A > /data/bak/all.sql

# 导出所有 db 结构
mysqldump -uroot -p -A -d > /data/bak/all_struct.sql

# 仅导出所有 db 数据不导表结构
mysqldump -uroot -p -A -t > /data/bak/all_data.sql

# 导出多个指定 db
mysqldump --databases db1 db2 > /data/bak/muldbs.sql

特殊场景

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32



# 单库备份命令(推荐); 在单个事务中执行备份, 并且刷新 binlog
mysqldump -h 10.2.1.5 -u root -p -A \
--source-data=2 --single-transaction --flush-logs > back-2023-03-21.sql

mysqldump --master-data=2 --lock-all-tables --flush-logs  --all-databases > /root/alldb.sql

# 普通备份, 不记录 gtid
mysqldump -u root -p -B doc \
    --set-gtid-purged=OFF \
    --single-transaction > db_doc-2023-02-09.sql


# 备份 -> 恢复 | 异机恢复
mysqldump -h host1 -u root -p -B db1 --tables t1 | mysql -h host2 -u user1 -p db2


# 备份-压缩-恢复 适合小数据量场景
mysqldump -h host1 -u user1 -p'pw' mydb | gzip > mydb.sql.gz
gunzip < mydb.sql.gz | mysql -h host2 -u user1 -p'pw' mydb


# 条件备份+过滤恢复+严格确认数据
mysqldump -u root -p -S /data/mysql_3306/mysql.sock \
--databases cmcc --tables t_name \
--where='VERIFY_TIME BETWEEN "2018-03-29 03:00:00" and "2018-03-29 03:59:59"' >> c1.sql

grep --ignore-case 'insert into `t_name`' c1.sql >> xxx.sql
sed -i '/t_name/s/t_name/t_name_0711/g' xxx.sql

/opt/app/mysql/bin/mysql -u root -p cmcc < xxx.sql
INSERT INTO t_name SELECT * FROM t_name_0711;

脚本实例

脚本1

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80


#!/bin/bash
# mysql_backup
# 0 3 * * * sh /bigdata/mysql_backup.sh >> /bigdata/mysql_backup.log 2>&1 &

# 完善的备份机制
# 1. 启动前检查实例是否具备可备份状态;
# 包括是否在线, 当前负载，磁盘空间是否足够，远程服务器是否能连接
# 2. 备份 -> 压缩 -> 异地同步
# 3. md5 检验, 避免以后文件传输过程中损坏


DB_USER="root"
DB_PASS="password"
DB_HOST="192.168.1.100"
BACK_BASE_DIR="/bigdata/mysql"
LOG_FILE=${BACK_BASE_DIR}/backup.log
DUMP="/usr/local/mysql/bin/mysqldump"

REMOTE_SERVER="10.2.1.5"
REMOTE_USER="user1"
REMOTE_DIR="/data/mysql_backup"


function log() {
    echo "$(date "+%F %H:%M:%S") $@" >> $LOG_FILE
}
#===================================================================
# pre
# 获取当前日期 2024-02-03
DATE=`date +%F`

BACK_DIR=${BACK_BASE_DIR}/${DATE}
BACK_FILE=${BACK_DIR}/mysql_backup_${DATE}.sql

log "start backup"
mkdir -p $BCK_DIR/$DATE
#===================================================================

$DUMP -u$DB_USER -p$DB_PASS -h$DB_HOST --all-databases > ${BACK_FILE}

log "backup end, start compress"
#===================================================================

gzip ${BACK_FILE}

# 可选并行压缩
# pigz -9 -p 24 ${BACK_FILE}

log "backup task compress end"

#==========================================================
# CHECK FILE MD5SUM

log "pre check file md5sum"

md5sum ${BACK_FILE}.gz >> $LOG_FILE

log "check file md5sum end"
#==========================================================
# old file clear

yestoday=$(date -d '-7 day' +%Y-%m-%d)  # 取 7 天前的时间

log "delete old backup file"
log "del ${BACK_BASE_DIR}/${yestoday}"

rm -rf ${BACK_BASE_DIR}/${yestoday}

log "del old backup file end"

# 只保留最近一周的备份
# find ${BACK_BASE_DIR} -mtime +2 -exec rm -rf {} \;

#==========================================================
log "start sync backup file to remote server"

scp -r ${BACK_DIR} ${REMOTE_USER}@${REMOTE_SERVER}:${REMOTE_DIR}

log "all task end"
#==========================================================

脚本2

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20


#!/bin/sh
# 1. 先停止复制 - 保障一致性
# 2. 备份完成后再开启同步

mkdir -p /server/backup/
mysql -u$USER -p$PASS -S $SOCK -e "stop slave SQL_THREAD;"

# 实际上还需要排除系统库 mysql|information_schema|performance_schema
DB_LIST=$(mysql -u$USER -p$PASS -S $SOCK  -e "show databases"|sed '1,2d'|/bin/egrep -v "${EXCLUDE[*]}")

for dbname in ${DB_LIST}
do
   TABLE_LIST=$(mysql -u$USER -p$PASS -S $SOCK  -e "show tables from $dbname;"|sed '1d')
   for tablename in ${TABLE_LIST}
   do
      mkdir -p /server/backup/$DATE/$dbname
      mysqldump --lock-tables=0 -u$USER -p$PASS -S $SOCK  ${dbname}  $tablename |gzip >/server/backup/$DATE/${dbname}/${dbname}_${tablename}_$DATE.sql.gz
   done
done
mysql -u$USER -p$PASS -S $SOCK  -e "start slave SQL_THREAD;"

关于备份时的锁

避免只使用 --master-data=2 选项, 此选项会获取全局锁, 大量表频繁获取时, 容易阻塞业务。

最好是始终使用 --single-transaction 参数, 避免一直锁定。

--lock-all-tables 或 --lock-tables 参数, 会加锁；

加 --master-data=2 或 --flush-logs 参数时, 会执行:
FLUSH TABLES;
FLUSH TABLES WITH READ LOCK;
不加锁, 但是会导致备份时间延长, 或无法备份完成

FLUSH TABLES

FLUSH TABLES 关闭所有打开的表, 强制关闭所有正在使用的表, 并刷新查询缓存和预准备语句缓存, 不会刷新脏块,
会被锁阻塞, 会等待所有正在运行的 SQL 执行结束

如果当前有正在运行的慢 SQL, 执行 FLUSH TABLES; 就会被阻塞, 需要等待 SQL 执行结束

FLUSH TABLES WITH READ LOCK
关闭所有打开的表并使用全局读锁锁定所有数据库的所有表, 不会刷新脏块,会被锁阻塞
如果存在锁, FLUSH 会直接返回错误

OUTFILE

OUTFILE 文本备份

理解为导出 csv

1
2
3
4
5
6
7
8
9


SELECT ... INTO OUTFILE

--- 查询输出到一个文件
select * from my_t1 into outfile "d:/my_t1.txt";

--- 指定分隔符
select id from test where id > 0
 into outfile 'c:\\test'
 FIELDS TERMINATED by ',' ENCLOSED by "" LINES TERMINATED by '\n';

OUTFILE控制格式

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


-- 控制格式
-- TERMINATED 生成的数据分隔符, 默认是制表符
-- ENCLOSED 包裹
-- ESCAPED 转义字符

-- FIELDS 子句的缺省值是,
FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'

-- LINES 控制行格式, 子句的缺省值是
LINES TERMINATED BY '\n'

load data 文本导入

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15


load data  [low_priority] [local] infile 'file_name.txt' [replace | ignore] into table t_name [控制格式]

-- local 在客户端查找文件, 未添加时在服务器查找文件
-- replace 和 ignore 关键词控制对现有的唯一键记录的重复的处理

load data infile "d:/home_my_email" into table wait.my_email;
load data infile "d:/home_my_email" into table wait.my_email(name);    -- 指定字段
load data local infile "F:/account_data/971.txt" into table t_name;

-- 指定使用,号 和 换行做分隔符；
load data infile '/home/data.txt' into table t_name fields terminated by ',' lines terminated by '\n';

-- 实例
mysql -uroot -p$pw -Dcmcc \
-e "load data local infile '/home/mysql/data/t1.txt' into table t_name fields terminated by ','";

恢复错误处理

1
2


ERROR at line 3130: Unknown command '\''.
# 字符集问题，使用  --default-character-set=utf8mb4 参数指定字符集