备份概述

备份决定了数据库的安全,在主从不一致的情况下删除从库的所有数据,进行数据重新整理。

对于 MyISAM 备份

1
FLUSH TABLES WITH READ LOCK;

备份策略

逻辑备份: 结果为 sql 文件,方法适用于所有 Engine

物理备份: 数据库目录的拷贝,对于内存表只备份结构

备份的内容:

  • 事务的 innodb 表,不带有事务的(无法保证一致性)
  • 函数(routines)、触发器、视图
  • 包含的表,有时无需对整个数据库所有的表进行备份,只需要备份重要的业务表,用于以后的恢复
  • 排除的表,在做全量备份的时候,排除掉日志表、记录表、备份表等与业务非强相关的表,提高备份的效率

备份的类型:

  • 热备份: 在线备份,不需要任何的服务停机时间,ibbackup 商业工具可实现
  • 冷备份: 备份 .frm, .idb 文件,需要离线备份
  • 温备份: 非离线备份,在线执行,会影响线上的数据库运行

备份的方案:

  • 全量备份
  • 增量备份
  • 压缩备份
  • 加密备份

备份的数据存放到另一块物理磁盘上或是传输到另一台机器上

1
2
3
4
5
6
7
8
9
10
SELECT data INTO OUTFILE xxx.txt
LOAD data INFILE xxx.txt INTO TABLE
select * from dc_mp_fans into outfile '/data/fans.txt';

zip fans.zip /data/fans.txt
scp fans.zip root@ip:/data/
unzip /data/fans.zip

load data infile '/data/fans.txt' into table wxa_fans(
id,appid,openid,unionid,created_at);

备份场景

备份业务中重要的业务表

日志表的归档,归档到另一个表中

备份/导出/ETL 到大数据平台

强制恢复

处理恢复过程中启动不起来的情况

MySQL 配置参数 innodb_force_recovery 控制

  • 1: (SRV_FORCE_IGNORE_CORRUPT): 忽略检查到的 corrupt 页。
  • 2: (SRV_FORCE_NO_BACKGROUND): 阻止主线程的运行,如主线程需要执行 full purge 操作,会导致 crash。
  • 3: (SRV_FORCE_NO_TRX_UNDO): 不执行事务回滚操作。
  • 4: (SRV_FORCE_NO_IBUF_MERGE): 不执行插入缓冲的合并操作。
  • 5: (SRV_FORCE_NO_UNDO_LOG_SCAN): 不查看重做日志,InnoDB 存储引擎会将未提交的事务视为已提交。
  • 6: (SRV_FORCE_NO_LOG_REDO): 不执行前滚的操作。

当 innodb_purge_threads 和 innodb_force_recovery 一起设置会出现一种loop 现象

1
2
innodb_force_recovery=6
innodb_purge_thread=0

当设置参数值大于0后,可以对表进行 select,create,drop 操作,但 insert,update 或者 delete 这类操作是不允许的

MySQL crash 或者 MySQL 数据库服务器 crash 会导致各种各样的问题 ,比如主备之间的 error 1594 (5.6 版本开启crash-safe ,会最大程度上避免 error 1594的问题),error 1236, 日志损坏,数据文件损坏等。

物理文件离线备份

离线方式的备份,需要停止整个服务 存在复制过程中的文件损坏,对应表空间没法使用,数据库服务一直尝试重启

注意数据库的 .frm, .idb 的权限 660;目录为 700;目录的属主为 mysql:mysql

基于日志点的恢复

增量恢复

备份二进制日志,使用 FLUSH LOGS 开始一个新的二进制日志

https://dba.stackexchange.com/questions/60722/how-to-recover-truncate-table-in-mysql

mysqlbinlog:

  • --start-position: 开始位置
  • --stop-position: 停止位置
1
2
3
4
5
6
7
8
9
10
11
mysqlbinlog -v mysqlbin.000002 | grep -B5 TRUNCATE --color

mysqlbinlog \\
--start-position=1603 \\
--stop-position=919664 \\
mysqlbin.000002 > /mysqlbackup/binlog_`date +%y%m%d%H`.sql
show binary logs;
show master logs;
SHOW BINLOG EVENTS;
show binlog events in "mysql-bin.000005";
mysqlbinlog binlog.[0-9]* | mysql -u root -p

导入导出

可直接使用 DBver, Navicate, DataGrip 等图形化连接客户端进行各种格式的导出,包含 INSERT 语句、UPDATE 语句、HTML 表格、CSV(TSV)、JSON、格式化的text文本、Markdown 的表格等。

http://img.janhen.com/20210324225548Pk0vYq.png

SELECT … INTO OUTFILE

需要登录的 mysql 账号具有 FILE 权限

1
2
3
select * from entitylog into outfile '/var/lib/mysql/backup/entitylog.txt';
-- 指定分割符合换行符, 导出为 CSV 格式
select * from entitylog into outfile '/var/lib/mysql/backup/entitylog.txt' fields terminated by ',' enclosed by '"' lines terminated by '\\r\\n';

mysql 命令导出

1
2
-- 导出为 CSV 格式
mysql -uroot -p -e "select * from entitylog" --skip-column-names test|sed -e "s/[\\t]/,/" -e "s/$/\\r/" > entity.txt

mysqldump

MySQL 自带的备份工具,可以对数据库进行全备份和部分备份,不支持增量备份。

使用场景: 10G 以下的数据库操作简单

缺点: 数据量范围:30G –> TB级别 的时候备份、恢复操作很慢,效率低

基本配置:

  • -h / -P / -u / -p: 基本连接参数
  • --single-transaction: 保证导出的数据一致性,保证备份 InnoDB 的一致性逻辑备份,和 –lock-tables 选项是互斥的
  • -l / --lock-tables:一般用于 MyIsam 备份,与上面的互斥
  • x, --lock-all-tables: 数据库
  • -master-data: [1/2]

备份内容配置:

  • --all-databases: 备份全部的 DB
  • --databases <db1> <db2>: 备份指定的多个数据库
  • --tables a1 a2: 备份指定的多个 Table
  • -w, --where="": 备份表中筛选后的数据
  • -R, --routines: 备份 procedures, functions
  • --triggers: 备份触发器
  • -e, --events:
  • --no-data: 只导出表结构,不导出数据

控制输出内容配置:

  • --skip-add-drop-table: 取消每个数据表创建之前添加 drop 数据表语句(默认每个表之前存在drop语句),对于部分恢复的情况下需要注意。
  • --skip-add-locks: 跳过锁表语句
  • --no-create-info: 导出的 SQL 中不包含 create table 语句
  • --set-gtid-purged: 跳过导 GTID
  • --add-drop-database: 增加删除数据库 sql(默认不会)
  • -opt: 等同于 --add-drop-table, --add-locks, --create-options, --quick, --extended-insert, --lock-tables, --set-charset, --disable-keys,默认开启
  • -c, --complete-insert: 生成的 Insert 语句中带有字段名称, insert into T(col1,col2..) values(…)

其他配置

  • –-lock-all-tables: 锁住所有的表,表变为只读的
  • –master-data=: 将当前服务器的binlog的位置和文件名追加到输出文件,不会停止当前服务器的主从服务
  • -F: 生成新的 binlog 文件
  • -C: 启用压缩传递
  • --dump-slave

全量备份

1
2
3
4
mysqldump --all-databases \\
--master-data \\
--single-transaction \\
> backup_$(date +%y%m%d).sql

多服务器数据传输

1
2
3
4
mysqldump --host=h1 -uroot -proot --databases db1 | mysql --host=h2 -uroot -proot db2

# 压缩传输
mysqldump --host=192.168.80.137 -uroot -proot -C --databases test | mysql --host=192.168.80.133 -uroot -proot test

压缩备份

线上环境导入导出使用

1
2
3
4
5
6
7
8
9
# 压缩备份
mysqldump -P3306 \\
-uroot -p \\
-q -Q --set-gtid-purged=OFF \\
--default-character-set=utf8 \\
--hex-blob --skip-lock-tables \\
--databases abc 2>/abc.err |gzip >/abc.sql.gz
# 还原
gunzip -c abc.sql.gz | mysql -uroot -p -vvv -P3306 --default-character-set=utf8 abc 1> abc.log 2>abc.err

按条件备份

备份指定数据库

1
2
3
4
5
6
7
8
9
mysqldump  \\
-hlocalhost -P3306 \\
-uroot -p$MYSQL_ROOT_PASSWORD \\
--add-drop-table \\
--master-data=2 \\
--single-transaction \\
--routines --triggers --events \\
--databases account basic report \\
> backup_$(date +%y%m%d).sql

备份满足特定条件的表

1
2
3
4
5
6
7
8
9
10
11
12
13
# 备份指定时间的日志数据
mysqldump -uroot -p$MYSQL_ROOT_PASSWORD \\
--databases basic \\
--tables interfacelog \\
--where="logtime < '2020-05-01 00:00:00' and logtime > '2020-04-08 00:00:00'" \\
> backup-interfacelog-`date +%y%m%d`

# 从重命名的表中备份数据
mysqldump -uroot -proot \\
--databases basic \\
--tables old_interfacelog \\
--where="logtime between '2020-04-13 00:00:00' and '2020-04-14 00:00:00'" \\
> backup-interfacelog-`date +%y%m%d`

备份表并在 insert 语句中插入字段

使用场景: 备份数据,并恢复到变更表结构的该表

  • 归档表重命名原始表名,对应 INSERT INTO 与生产库的表名一致
  • 对于表结构变更的,需要 Insert 增加字段名
  • 用于从归档到生产库的,需要去除逻辑备份前面的 Drop 语句
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# 测试输出的结果
db=account
table=entitylog
time mysqldump -uroot -p$MYSQL_ROOT_PASSWORD \\
--databases $db \\
--tables $table \\
--complete-insert \\
--skip-add-locks \\
--no-create-info \\
--skip-add-drop-table \\
--where="1=1 limit 0,1" \\
> test.sql
db=facility
table="stockoperlog "
time mysqldump -uroot -p$MYSQL_ROOT_PASSWORD \\
--databases $db \\
--complete-insert \\
--tables $table \\
--no-create-info \\
--skip-add-locks \\
--skip-add-drop-table \\
--where="logtime > '2021-01-01 00:00:00'" \\
> stockoperlog_210101_`date +%y%m%d`.sql

备份表结构

1
2
3
4
mysqldump -uroot -proot \\
--no-data \\
--databases account \\
> /tmp/account_schema.sql

备份

恢复

专门创建一个备份数据库,查询出必要的数据,之后插入到实际使用的表

使用 table 进行恢复,只对需要的进行恢复

进行时间点的恢复 前提:

  • 一个时间点的全备
  • 对应的 binlog
1
2
3
4
5
6
# 将 SQL 恢复到指定的数据库
mysql -uroot -proot \\
basic < backup_200604_basic.sql

mysql -uroot -proot \\
account < backup_200604_account.sql

xtrabackup

可对数据库进行全备和增量备份,使用 binlog 对数据库进行时间点的… 支持在线的物理备份

配置 在 my.cnf 中指定

1
2
[xtrabackup]
target_dir = /data/backups/mysql/

基础参数

  • --print-defaults: 打印程序参数和列表并退出
  • --target-dir=<path>: 备份到的目标文件夹
  • --backup: take backup to target-dir
  • --prepare: 准备备份以在备份上启动 mysql 服务器。
  • --export:
  • --print-param:
  • --rate-limit: 限制备份脚本的吞吐量

备份过滤的参数:

  • --tables=name / : 根据正则表达式过滤表名,过滤列表根据
  • --tables-file=name: 根据文件中的 database.table 进行过滤
  • --databases=name: 根据数据库列表过滤
  • --tables-exclude=name: 排除指定的表, 与 --tables 相反,优先级比 --tables
  • --databases-exclude=name: 排除指定的数据库

压缩备份参数

  • --compress[=name]: 是否进行压缩备份
  • --compress: 进行压缩备份
  • --compress-threads=N: 指定压缩的线程数
  • --decompress: 解压缩恢复备份数据

增量备份参数

  • --incremental-basedir=<path>: 增量备份基于的全量备份文件夹

数据库恢复参数

  • --copy-back: 复制之前所有的文件

其他参数

  • --slave-info: 主从复制备份有效
  • --safe-slave-backup:
  • –-binlog-info[=name]

全量备份

1
2
3
4
5
6
7
8
9
MYSQL_BACKUP_DIR=/var/log/mysql/backups/$dir_name/Full_$(date +%Y.%m.%d_%H.%M.%S)
mkdir -p $MYSQL_BACKUP_DIR
xtrabackup --backup \\
--target-dir=$MYSQL_BACKUP_DIR \\
-uroot -proot

xtrabackup --prepare \\
--target-dir=$MYSQL_BACKUP_DIR \\
-uroot -proot

有条件的全量备份

xtrabackup 当前只能指定到表级别,没法对表的数据进行筛选 若需要对表的内容进行筛选,可考虑使用 mysqldump 进行逻辑备份,配合 xtrbackup 的物理备份实现

排除指定表进行备份

可根据实际需要列出需要排除的大表,拼接成正则或是到文件中进行过滤;

可通过 (.*log.*|.*record.*|.*bak.*|.*\\d+.*) 正则表达式进行排除不相关的表;

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
# 1. define back info
MYSQL_BACKUP_DIR=/var/lib/mysql/backup
back_up_dir=$MYSQL_BACKUP_DIR/Full_$(date +%Y.%m.%d_%H.%M.%S)

# 1.2 compare back info
# Filter pattern: (.*log.*|.*record.*|.*bak.*|.*\\d+.*)
mkdir -p $back_up_dir
table_exclude_names=(
"account.entitylog"
"account.mqlog"
"basic.interfacelog"
"basic.article_bak"
)
function join() {
local IFS="$1"
shift
echo "$*"
}
join_str=$(join \\| "${table_exclude_names[@]}")
exclude_table="($join_str)"
echo "exclude_table: $exclude_table"

# 2. 非压缩备份
time xtrabackup --backup \\
--host=127.0.0.1 \\
-uroot -proot \\
--tables-exclude="$exclude_table" \\
--datadir=/var/lib/mysql/ \\
--target-dir=$back_up_dir

备份指定的表

单独备份表的话需要表在独立的表空间,对应 innodb_file_per_table=1

压缩备份

需要使用 qpress 工具

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 安装 qpress
percona-release enable tools
apt-get update
apt-get install qpress
MYSQL_BACKUP_DIR=/var/log/mysql/backups/$dir_name/Full_$(date +%Y.%m.%d_%H.%M.%S)
mkdir -p $MYSQL_BACKUP_DIR
xtrabackup --backup \\
--compress \\
--compress-threads=4 \\
--target-dir=$MYSQL_BACKUP_DIR

# 解压缩
xtrabackup --decompress \\
--target-dir=/data/compressed/

xtrabackup --copy-back \\
--target-dir=/data/backups/

数据恢复

数据目录在恢复前必须为空

在执行恢复前,MySQL 服务需要关闭

1
2
xtrabackup --copy-back \\
--target-dir=$MYSQL_BACKUP_DIR

增量备份

增量并压缩备份

1
2
3
4
5
6
7
8
datadir=/var/lib/mysql/backup
xtrabackup --user=root \\
--password=root \\
--backup \\
--compress \\
--compress-threads=4 \\
--target-dir=${datadir}/inc${today} \\
--incremental-basedir=/var/lib/mysql/backup/Full_2020.08.23

比较偏移的点

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 10820498082
last_lsn = 10820498082
compact = 0
recover_binlog_info = 0
flushed_lsn = 10820475795
backup_type = incremental
from_lsn = 10820498082
to_lsn = 10820575291
last_lsn = 10820575291
compact = 0
recover_binlog_info = 0
flushed_lsn = 10820572909

数据库恢复

准备

  • 可以在任何机器上运行 prepare 命令,无需在原始服务器上, 可复制备份到指定机器上进行恢复
  • 准备步骤使用此嵌入式 InnoDB 对复制的数据文件执行崩溃恢复
1
xtrabackup --prepare --target-dir=/data/backups/

复制与授权

复制备份文件

方式一: 使用 xtraback 提供的功能

1
xtrabackup --copy-back --target-dir=/data/backups/

方式二: 使用 rsynccp 复制备份文件到指定目录

1
2
rsync -avrP /data/backup/ /var/lib/mysql/
# 2 cp

授权

1
chown -R mysql:mysql /var/lib/mysql

其他

对于数据量大的情况,可以考虑将大表的数据通过 ETL 工具导出到大数据平台进行存储,业务系统中只保留最近 1年的数据。

Ref

MySQL mysqldump数据导出详解

MySql数据库备份与恢复–使用mysqldump 导入与导出方法总结_helloxiaozhe的博客-CSDN博客_mysqldump备份数据库

MySql数据库备份与恢复——使用mysqldump 导入与导出方法总结

Percona XtraBackup - Documentation

Percona XtraBackup 2.4 官方文档

Partial Backups

Percona 官网部分备份说明文档

使用innobackupex对数据库进行部分备份(指定表或数据库)

使用innobackupex对数据库进行部分备份(指定表或数据库)

MySQL 导出数据

MySQL 导出数据