现在公司的服务器没有上云，也没有专业的运维或dba 。 研究下mysql备份恢复技术，做个兜底保障，万一哪天机房、机器坏了。

数据库备份分类

  • 热备，冷备、温备（只读）
  • 逻辑备份（生成sql,或导出文件格式) 、物理备份 （备份底层文件）
  • 完全备份，增量备份
  • 日志备份（备份binlog,防止一些手误，代码写错，误删除数据，通过binlog可以恢复数据）

mysqldump做全量备份，binlog做增量备份

mysqldump是逻辑备份，热备份，全量备份（也可以部分备份的） 工具。

1mysqldump -uroot -p123456 --database d1 --single-transaction --flush-logs --master-data=2 > xxx.sql
  • –flush-logs 备份后切换binlog文件
  • –master-data 记录切换后的binlog文件名
  • –single-transaction 在rr隔离级别下进行（innodb）

切换binlog文件

需要增量备份时，切换binlog文件

1mysqladmin -uroot -p123456 flush-logs  # 切换新的binlog文件，（正在用的是不能搞的，因为他正在写）

把所有增量的binlog文件进行备份（最新的那个别备份）

还原

  1. 首先恢复旧的全量备份

    source xxx.sql

  2. 然后将binlog增量还原至数据库

    mysqlbinlog MySQL-bin.000002 MySQL-bin.000003... | mysql -u root -p 123456

xtrabackup 备份及恢复

xtrabackup 是 物理，热，全量+增量备份 的工具。percona公司开发的开源版本，实现了ibbackup的全部功能

xtrabackup 8.0->mysql8.0

xtrabackup 2.4 ->mysql5.1,5.5,5.6,5.7

  • 直接备份innodb 底层数据文件
  • 导出不需要转换，速度快
  • 工作时对数据库的压力较小
  • 更容易实现增量备份

直接拷贝裸文件可行吗

  • 理论上可行，但是与很多问题
  • 要同时备份frm文件，ibd文件，binlog文件，redolog文件等
  • 必须冷备份，影响业务。
  • 在不同版本的数据库和操作系统还原还要兼容问题。

如何实现物理+热+全量备份

思路：利用redo log，备份idb文件+备份期间的redo log

  • 1.启动redo log 监听线程
  • 拷贝idb数据文件
  • 停止收集redo log
  • 加FTWRL锁拷贝元数据frm

如何实现物理还原

思路：mysqld crash 崩溃恢复流程

  • 还原idb文件，重访redo log

安装xtrabackup

1wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.27/binary/tarball/percona-xtrabackup-2.4.27-Linux-x86_64.glibc2.12.tar.gz
2tar zxvf percona-xtrabackup-2.4.27-Linux-x86_64.glibc2.12.tar.gz
3mv zxvf percona-xtrabackup-2.4.27-Linux-x86_64.glibc2.12 /opt/xtrabackup

全量备份

1/opt/xtrabackup/bin/innobackupex --user=root --password=123456 /opt/xtrabackup/bakdir
2# /opt/xtrabackup/bakdir 指定备份到哪个目录，自己指定

数据还原

停掉mysqld

先要把原来的目录移动走

1innobackupex version 2.4.27 based on MySQL server 5.7.40 Linux (x86_64) (revision id: aae8e82)
2Original data directory /db/data is not empty!

 1mv /db/data /db/data_bak # 原来的目录不能为空
 2# 制定恢复的dir 路径
 3/opt/xtrabackup/bin/innobackupex --copy-back /opt/xtrabackup/bakdir/2022-12-10_10-43-09
 4# or 用这个
 5/opt/xtrabackup/bin/innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --copy-back /opt/xtrabackup/bakdir/2022-12-10_10-43-09
 6
 7systemctl start mysql  # 报错 the server quit without updating PID file 
 8#修改权限 
 9chown -R mysql:mysql /db/data
10systemctl start mysql

增量备份

1innobackupex --user=root --password=123456 --incremental bakdir/ --incremental-basedir='(全量路径)' #基于这个进行增量
2# bakdir 指定备份到哪个目录

增量备份合并到全量备份

分别看下全量和增量备份出来的 xtrabackup_checkpoints , xtrabackup_binlog_info文件内容，看看合并后的变化，

如果出问题了就用上面的还原命令即可，

1# --redo-only只应用redo日志,不执行undo回滚未提交的数据，等最后一次增量备份合并完成后再进行应用undo日志回滚数据。
2innobackupex --apply-log  --redo-only 全量路径
3innobackupex --apply-log  --incremental-dir=增量备份路径(bakdir/YYYY-YY-YY) 全量路径

Xtrlabackup采用了备份idb+备份期间到redo log 方式。

物理备份是一种高效的备份方式。

Xtrlabackup是最常用的mysql 物理备份工具

物理备份的缺点备份文件无法直接阅读。