mysql备份xtrabackup

mysql文件拷贝备份

直接将mysql的数据目录进行打包备份
在新的服务器配置好mysql的配置文件
并将数据文件解压到数据目录启动即可

mysql热备：

MySQL冷备、mysqldump、MySQL热拷贝都无法实现对数据库进行增量备份。在实际生产环境中增量备份是非常实用的，如果数据大于50G或100G，存储空间足够的情况下，可以每天进行完整备份，如果每天产生的数据量较大，需要定制数据备份策略。例如每周实用完整备份，周一到周六实用增量备份。而Percona-Xtrabackup就是为了实现增量备份而出现的一款主流备份工具

mysqldump逻辑备份

[root@]> mysqldump -help

Usage: mysqldump [OPTIONS] database_name [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3…]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help

导出命令大全
#导出education数据库里面的users表的表数据和表结构
mysqldump -u[用户名] -h[ip] -p[密码] -P[端口号] 数据库名 表名 >导出的文件名.sql
mysqldump -uroot -h127.0.0.1 -proot -P3306 education users>d:/user.sql
#导出包括系统数据库在内的所有数据库（all.sql默认保存在bin文件夹下面）
mysqldump -uroot -proot --all-databases>all.sql
#导出多张表：
mysqldump -uroot -proot --databases test --tables t1 t2>two.sql
#只导出表结构不导表数据，添加“-d”命令参数
mysqldump -uroot -h127.0.0.1 -proot -P3306 -d education users>d:/user.sql
#只导出表数据不导表结构，添加“-t”命令参数
mysqldump -uroot -h127.0.0.1 -proot -P3306 -t education users>d:/user.sql
#只导出test数据库的表结构
导出：mysqldump -uroot -proot --no-data --databases test>s1.sql
导入：mysql -uroot -proot -h127.0.0.1 -P3306 test<s1.sql

导入命令大全
格式：mysql -h[ip] -P[(大写)端口] -u[用户名] -p[密码] [数据库名] < d:XX.sql(路径)
mysql -uroot -proot -h127.0.0.1 -P3306 education<d:/database.sql
mysql -uroot -proot -h127.0.0.1 -P3306 <d:/all_database.sql
#命令行导入
mysql> use test;
mysql> source /home/test/database.sql

**mysql中的表重命名
rename table test to new-test;

Percona-Xtrabackup安装：

到官网下载对应数据库版本的xtrabackup,mysql5.7下载xtrabckup2.4,mysql8.0下载xtrabackup8.0
安装依赖
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL rsync perl perl-Digest-MD5
wget ftp://rpmfind.net/linux/atrpms/el6-x86_64/atrpms/stable/libev-4.04-2.el6.x86_64.rpm （rpm -ivh libev-4.04-2.el6.x86_64.rpm） 然后下载xtrabackup的rpm包安装
xtrabackup2.4.28 a compiled C binary that provides functionality to backup a whole MySQL
database instance with MyISAM, InnoDB, and XtraDB tables.
从MySQL 5.5.5版本开始，默认的存储引擎被更改为InnoDB。

mysql> CREATE USER ‘bkpuser’@‘localhost’ IDENTIFIED BY ‘s3cret’;
mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON . TO
‘bkpuser’@‘localhost’;
mysql> FLUSH PRIVILEGES;

全量备份恢复：

备份：
xtrabackup --backup --target-dir=/data/backups/ --user=root --password=root
xtrabackup --host=10.72.88.200 --port=3306 --user=bkpuser --password=‘123456’ --backup --target-dir=/test/base (如果将远端mysql备份到当前服务器)
如果提示–no-server-version-check 是因为数据库版本比xtrabackup高可直接–no-server-version-check跳过
恢复：
xtrabackup --prepare --target-dir=/data/backups/ 先回滚未提交的和将未同步到文件的进行同步
xtrabackup --copy-back --target-dir=/data/backups/ 或者直接copy到mysql数据目录（记得修改用户属组）

增量备份恢复：

备份：
xtrabackup --backup --target-dir=/data/backups/base 先做个全量基础备份

xtrabackup --backup --target-dir=/data/backups/inc1
–incremental-basedir=/data/backups/base 然后做个增量备份

xtrabackup --backup --target-dir=/data/backups/inc2
–incremental-basedir=/data/backups/inc1 基于上一次增量在做增量
恢复：
xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base 记住除了最后一个增量外其他全要加–apply-log-only 执行redo重做

xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base
–incremental-dir=/data/backups/inc1 执行第一次增量准备到base

xtrabackup --prepare --target-dir=/data/backups/base
–incremental-dir=/data/backups/inc2 执行第二次增量到base (最后一个增量不需要 --apply-log-only）

然后将base下的所有copy到mysql数据目录（注意数据文件用户属组）

 **结合crontab进行备份的脚本**
#!/bin/bash

# 定义备份目录和日期格式
BACKUP_DIR="/path/to/backup"
DATE=$(date +%Y%m%d)

# 备份函数
function run_backup {
  local backup_type=$1
  local target_dir="$BACKUP_DIR/$backup_type/$DATE"

  # 创建备份目录
  mkdir -p "$target_dir"

  if [ "$backup_type" == "full" ]; then
    # 执行完全备份
    xtrabackup --backup \
      --target-dir="$target_dir"
  else
    # 执行增量备份
    back_day=$(date  +%Y%m%d)
     back_full_backup="$BACKUP_DIR/full/$back_day"
    
    xtrabackup --backup \
      --target-dir="$target_dir" \
      --incremental-basedir="$back_full_backup"
  fi
}

# 根据星期进行备份
day_of_week=$(date +%u)

case $day_of_week in
  7)  # 周天进行完全备份
    run_backup "full"
    ;;
  1|2|3|4|5)  # 周一到周五进行增量   
   run_backup "incremental"
    ;;
  *)  # 其他星期不执行备份
    echo "No backup scheduled for today."
    ;;
esac

通过binlog恢复

show master logs; 查看所有的日志
show binlog events in ‘mysql-bin.000002’; 查看每个日志中的position
比如其中有删除，更新的操作就恢复到操作之前的position
mysqlbinlog --no-defaults -v mysql-bin.000017 > info.txt 将binlog转换成可视的文件
mysqlbinlog --no-defaults ./mysql-bin.000013 --start-position=905 --stop-position=1101 |mysql -uroot -p 基于文件中的position恢复
mysqlbinlog --no-defaults --database=backup2 --start-datetime=“2022-01-14 14:20:07” --stop-datetime=“2022-01-14 14:20:38” /var/lib/mysql/mysql-bin.000012 |mysql -uroot -p 基于时间点的恢复