MySQL的数据备份

MySQL数据备份和恢复

备份原因

1）备份就是为了恢复。

2）尽量减少数据的丢失（公司的损失）

备份类型

# 冷备
需要停机维护，停止服务，备份数据

# 温备
不需要停机，也不需要停服务，但是备份数据的时候，会锁表

# 热备（innodb）
不需要停机，也不需要停服务，备份数据的时候也不会锁表

备份方式

• 逻辑备份（MySQL的逻辑备份，不支持增备）

  • mysqlbinlog
  • replication（主从复制）
  • into outfile

  secure-file-priv=/tmp
  mysql> select * from world.city into outfile '/tmp/world_city.data';
  

  • mysqldump

• 物理备份

  • Xtrabackup

备份策略

• 全量备份
• 增量备份
• 差异备份

备份工具

1.mysqldump(逻辑)
mysql原生自带很好用的逻辑备份工具

2.mysqlbinlog（逻辑）
实现binlog备份的原生态命令

3.xtrabackup（物理）
precona公司开发的性能很高的备份工具

备份工具-mysqldump

-u：指定用户
-p：指定密码
-h：指定主机
-S：指定socker
-P：指定端口

备份选项

-A：备份所有库，全备
mysqldump -uroot -p456 -A > /tmp/full.sql

-B：备份指定数据库

指定单库备份

# 备份单库
[root@db02 tmp]# mysqldump -uroot -p123 -B world > /tmp/world1.sql

# 备份多个库
[root@db02 tmp]# mysqldump -uroot -p123 -B world wc > /tmp/world.sql

# 只备份表，不备份库
[root@db02 data]# mysqldump -uroot -p123 world > /tmp/world.sql

# 恢复时，要指定一个数据库
[root@db02 data]# mysql -uroot -p123 world < /tmp/world.sql

# 备份单表world库下的city表
[root@db02 tmp]# mysqldump -uroot -p123 world city > /tmp/city.sql

# 备份时刷新binlog日志
-F：刷新binlog
[root@db02 tmp]# mysqldump -A -F > /tmp/full.sql

# 备份表结构
-d：仅表结构
[root@db02 tmp]# mysqldump -A -d > /tmp/full.sql

# 备份表数据
-t：仅数据
[root@db02 tmp]# mysqldump -A -t > /tmp/full.sql

打点备份

--master-data=[0|1|2]
[root@db02 tmp]# mysqldump -A --master-data=0 > /tmp/full.sql // 关闭打点备份

[root@db02 tmp]# mysqldump -A --master-data=1 > /tmp/full.sql // 可以用来将数据恢复到准备做该库的从库上

[root@db02 tmp]# mysqldump -A --master-data=2 > /tmp/full.sql // 单纯只为了打点备份

快照备份

[root@db02 tmp]# mysqldump -A --master-data=2 --single-transaction > /tmp/full.sql1088

特殊备份选项

-R：备份存储过程和删除
--triggs：备份触发器
-x：锁表备份（温备，不用）

完整的备份语句

mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction >/tmp/full.sql

# 优化：压缩备份
[root@db02 data]# mysqldump -uroot -p456 -A -R --triggers --master-data=2 --single-transaction|gzip > /tmp/zls.sql.gz

# 恢复数据
[root@db02 data]# zcat /tmp/zls.sql.gz|mysql -uroot -p456

# 优化：加上日期
# 精确到天
[root@db02 data]# mysqldump -uroot -p123 -A -R --triggers --master-data=2 --singletransaction|gzip > /tmp/zls_$(date +%F).sql.gz

# 精确到小时
[root@db02 data]# mysqldump -uroot -p123 -A -R --triggers --master-data=2 --singletransaction|gzip > /tmp/zls_$(date +%F-%H).sql.gz

# 精确到分钟
[root@db02 data]# mysqldump -uroot -p123 -A -R --triggers --master-data=2 --singletransaction|gzip > /tmp/zls_$(date +%F-%H-%m).sql.gz

# 精确到秒
[root@db02 data]# mysqldump -uroot -p123 -A -R --triggers --master-data=2 --singletransaction|gzip > /tmp/zls_$(date +%F-%T).sql.gz

恢复数据操作

# 先不记录二进制日志
mysql> set sql_log_bin=0;

# 库内恢复操作
mysql> source /backup/full.sql

# 库外恢复操作
[root@db01 ~]# mysql -uroot -p123 < /backup/full.sql

注意：

1）mysqldump在备份和恢复时都需要MySQL实例启动为前提

2）一般数据量级100G以内，大约15-30分钟可以恢
复（PB、EB就需要考虑别的方式）

3）mysqldump是以覆盖的形式恢复数据的

#!/bin/bash
num=1
while true;do
mysql -uroot -p123 -e "insert into proc.proc value($num);commit;"
(( num++ ))
sleep 1
done

# 1.准备新环境
[root@m01 scripts]# ./mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data

# 2.发送全备到新环境
[root@db02 tmp]# scp zls_2022-08-18-11-45-16.sql.gz 172.16.1.61:/tmp

# 3.恢复全备到新环境
[root@m01 mysql]# zcat /tmp/zls_2022-08-18-11-45-16.sql.gz |mysql

# 4.停掉连接数据库的程序


# 5.停掉旧的数据库
[root@db02 tmp]# /etc/init.d/mysqld stop

# 6.查看全备文件，找到起始位置点
[root@db02 tmp]# zcat /tmp/zls_2022-08-18-11-45-16.sql.gz |head -25
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000013',MASTER_LOG_POS=137455268;

# 7.查看binlog文件找到结束位置点
第一段结束位置点：137471450
第二段起始位置点：137479557
第二段结束位置点：137567163
mysqlbinlog --start-position=137455268 --stop-position=137471450 mysql-bin.000013 >
/tmp/inc1.sql
mysqlbinlog --start-position=137479557 --stop-position=137567163 mysql-bin.000013 >
/tmp/inc2.sql

# 8.发送到新环境
scp /tmp/inc*.sql 172.16.1.61:/tmp

物理备份

Xtrabackup安装

# 1.下载
[root@db02 ~]# wget http://test.driverzeng.com/MySQL_Package/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

# 2.安装
[root@db02 ~]# yum localinstall -y percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

# 重点
1）对于非innodb表（比如myisam）是直接锁表cp数据文件，属于一种温备。
2）对于innodb的表（支持事务），不锁表，cp数据页最终以数据文件方式保存下来，并且把redo和undo一并备走，
属于热备方式。
3）备份时读取配置文件/etc/my.cnf

# 老版本使用Xtrabackup会锁表属于温备
# 新版本，针对innodb有单独的备份命令innobackupex

全量备份

[root@db02 ~]# mkdir /backup

# 全备命令
[root@db02 ~]# innobackupex --user=root --password=123 --no-timestamp /backup/full
--user：指定用户
--password：指定密码
--socket：指定socket文件位置
--apply-log：模拟CSR
--copy-back：恢复数据
--no-timestamp：备份时，自定义目录名，不使用时间戳

# binlog信息文件
xtrabackup_binlog_info

# 记录binlog文件名和binlog位置点
xtrabackup_binlog_pos_innodb

# 备份详细信息
xtrabackup_info

# redo log
xtrabackup_logfile

# 备份信息文件
xtrabackup_checkpoints
[root@db01 2022-08-18_20-45-39]# cat xtrabackup_checkpoints 
backup_type = full-prepared
from_lsn = 0
to_lsn = 5627000
last_lsn = 5627009
compact = 0
recover_binlog_info = 0

# 恢复全备数据
# 手动模拟 CSR，重做redo，回滚undo
[root@db02 mysql]# innobackupex --apply-log /backup/2022-08-18_14-54-23/

前提1：被恢复的目录是空的
前提2：被恢复的数据库实例是关闭的

# 关闭数据库
[root@db01 backup]# systemctl stop mysqld.service

# 保证被恢复的目录不存在（建议MV移走）
[root@db01 backup]# cd /var/lib/
[root@db01 backup]# mv mysql /tmp

# 恢复全备
[root@db01 backup]# innobackupex --copy-back /backup/2022-08-18_20-45-39/

# 修改权限
[root@db01 backup]# chown -R mysql.mysql mysql

增量备份

# 注意
1.基于上一次备份进行增量
2.增量备份无法单独恢复，必须基于全备进行恢复
3.所有增量必须要按顺序合并到全备中

--incrementtal：开启增量备份
--incrementtal-basedir：指定基于上一次备份目录

# 全备
[root@db02 ~]# innobackupex --user=root --password=456 --no-timestamp /backup/full

[root@db01 backup]# cat full/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 7304837
last_lsn = 7304846
compact = 0
recover_binlog_info = 0

# 第一次增备
[root@db02 mysql]# innobackupex --user=root --password=456 --no-timestamp --incremental --incremental-basedir /backup/full/ /backup/inc1

[root@db01 backup]# cat inc1/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 7304837
to_lsn = 7304837
last_lsn = 7304846
compact = 0
recover_binlog_info = 0

# 第二次增备
[root@db02 mysql]# innobackupex --user=root --password=456 --no-timestamp --incremental --incremental-basedir /backup/inc1/ /backup/inc2

[root@db01 backup]# cat inc2/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 7304837
to_lsn = 7304837
last_lsn = 7304846
compact = 0
recover_binlog_info = 0

# 第三次增备
[root@db02 mysql]# innobackupex --user=root --password=456 --no-timestamp --incremental --incremental-basedir /backup/inc2/ /backup/inc3

[root@db01 backup]# cat inc3/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 7304837
to_lsn = 7304837
last_lsn = 7304846
compact = 0
recover_binlog_info = 0

恢复增备

1.full + inc1 + inc2 + inc3
2.inc1 合并到full中
3.inc2 合并到full中
4.inc3 合并到full中
5.分步骤进行--apply-log

--redo-only：只做redo
--incremental-dir：指定增量数据的目录（要把该目录合并到full中）

6.先把全备模拟CSR，只做redo 不做undo
[root@db02 mysql]# innobackupex --apply-log --redo-only /backup/full/

7.将inc1合并到全备，只做redo 不做undo
[root@db02 mysql]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc1 /backup/full

8.将inc2合并到全备，只做redo 不做undo
[root@db02 mysql]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc2 /backup/full

9.将inc3合并到全备，redo和undo都做
[root@db02 mysql]# innobackupex --apply-log --incremental-dir=/backup/inc3 /backup/full

10.将全备目录，整体做一次redo和undo
[root@db02 mysql]# innobackupex --apply-log /backup/full

11.恢复数据
[root@db01 lib]# systemctl stop mysqld.service 
[root@db01 lib]# mv /var/lib/mysql /backup/
[root@db01 lib]# innobackupex --copy-back /backup/full/
[root@db01 lib]# chown -R mysql.mysql mysql
[root@db01 lib]# systemctl start mysqld

差异备份

# 全备
[root@db02 mysql]# innobackupex --user=root --password=456 --no-timestamp /backup/full1

[root@db01 backup]# cat full/xtrabackup_checkpoints 
backup_type = full-prepared
from_lsn = 0
to_lsn = 7304837
last_lsn = 7304846
compact = 0
recover_binlog_info = 0

# 第一次差异备
[root@db02 mysql]# innobackupex --user=root --password=456 --no-timestamp --incremental --incremental-basedir /backup/full1 /backup/chayi1

[root@db01 backup]# cat chayi1/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 7305806
to_lsn = 7305806
last_lsn = 7305815
compact = 0
recover_binlog_info = 0

# 第二次差异备份
[root@db02 mysql]# innobackupex --user=root --password=456 --no-timestamp --incremental --incremental-basedir /backup/full1 /backup/chayi2

[root@db02 mysql]# cat /backup/chayi2/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2335492165
to_lsn = 2335501775
last_lsn = 2335501775
compact = 0
recover_binlog_info = 0

## 第三次差异备份
[root@db02 mysql]# innobackupex --user=root --password=456 --no-timestamp --
incremental --incremental-basedir /backup/full1 /backup/chayi3
[root@db02 mysql]# cat /backup/chayi3/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2335492165
to_lsn = 2335505723
last_lsn = 2335505723
compact = 0
recover_binlog_info = 0

差异备份恢复

1.关闭数据库
[root@db02 mysql]# /etc/init.d/mysqld stop

2.清空data目录
[root@db02 backup]# mv mysql /tmp

3.先将全备只做redo不做undo
[root@db02 mysql]# innobackupex --apply-log --redo-only /backup/full1

4.合并最后一次差异备份，redo和undo都做
[root@db02 mysql]# innobackupex --apply-log --incremental-dir=/backup/chayi3 /backup/full1

5.将全备，redo和undo都做一次
[root@db02 mysql]# innobackupex --apply-log /backup/full1

6.恢复数据
[root@db02 mysql]# innobackupex --copy-back /backup/full1

7.授权
[root@db02 mysql]# chown -R mysql.mysql /var/lib/mysql

8.启动数据库
[root@db02 mysql]# systemctl start mysqld

模拟用户写入数据

root@db02 backup]# vim /root/mysql.sh
#!/bin/bash
num=1
while true;do
mysql -uroot -p123 -e "insert into zls.chayi value($num);commit;"
(( num++ ))
sleep 1
done

# 全备
[root@db02 backup]# innobackupex --user=root --password=456 --no-timestamp /backup/full2
[root@db02 backup]# cat /backup/full2/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 2335532269
last_lsn = 2335535141
compact = 0
recover_binlog_info = 0

# 第一次增备
[root@db02 backup]# innobackupex --user=root --password=456 --no-timestamp --incremental --incremental-basedir /backup/full2 /backup/inc1
[root@db02 backup]# cat /backup/zls_inc1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2335532269
to_lsn = 2335575438
last_lsn = 2335575438
compact = 0
recover_binlog_info = 0

# 第二次增量
[root@db02 backup]# innobackupex --user=root --password=456 --no-timestamp --incremental --incremental-basedir /backup/zls_inc1 /backup/zls_inc2
[root@db02 backup]# cat /backup/zls_inc2/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2335575438
to_lsn = 2335597445
last_lsn = 2335597445
compact = 0
recover_binlog_info = 0

恢复步骤

1.周六全备数据
[root@db01 backup]# innobackupex --user=root --password=456 --no-timestamp /backup/full
2.周一到周五进行增量备份
[root@db01 backup]# innobackupex --user=root --password=456 --no-timestamp --incremental --incremental-basedir /backup/full /backup/inc1

[root@db01 lib]# innobackupex --user=root --password=456 --no-timestamp --incremental --incremental-basedir /backup/inc1 /backup/inc2

3.移走data目录
[root@db01 backup]# mv /var/lib/mysql /backup/

4.将全备只做redo不做undo
[root@db02 backup]# innobackupex --apply-log --redo-only /backup/full

5.将Inc1合并到full中，只做redo不做undo
[root@db01 backup]# innobackupex --apply-log --redo-only --incrementaldir=/backup/inc1 /backup/full

6.将inc2合并到full中，redo和undo都做
[root@db02 backup]# innobackupex --apply-log --incremental-dir=/backup/zls_inc2 /backup/full

7.将full2整体，redo和undo都做
[root@db02 backup]# innobackupex --apply-log /backup/full

8.恢复数据
[root@db02 backup]# innobackupex --copy-back /backup/full

9.授权
[root@db01 backup]# chown -R mysql.mysql /var/lib/mysql

10.启动数据库
[root@db01 backup]# systemctl start mysqld

11.查询数据
mysql> show databases;

12.截取binlog
起始位置点：
[root@db02 full2]# cat /backup/full2/xtrabackup_binlog_pos_innodb
mysql-bin.000001 45318

结束位置点：
[root@db02 data]# mysqlbinlog -vvv --base64-output=decode-row mysql-bin.000001
54432

起始位置点：54524
结束位置点：61778

[root@db02 data]# mysqlbinlog --start-position=45318 --stop-position=54432 mysqlbin.000001 > /backup/binlog1.sql
[root@db02 data]# mysqlbinlog --start-position=54524 --stop-position=61778 mysqlbin.000001 > /backup/binlog2.sql