在mysql当中进行备份恢复

前言

数据库备份不仅是为了防止数据丢失也是为了方便我们迁移

-------------------------------

备份工具：

mysqldump (MDP) , XBK (PBK) percona Xtrabackup  ,  MEB(MySQL Enterprise BACKUP  MEB) ,mysqlbinlog
 

备份方式有：

逻辑:
    全备   mysqldump                        
    增量   binlog (flush logs ,cp)             
物理备份:
    全备 : XBK 
    增量 : XBK

定期的恢复演练
开启测试环境，还原数据库，连接前台业务。                   

    --我们有时候需要测试下，如果万一不行还可以补救，万一真出了意外，那就包吃包住了

备份类型
    热备 : 对于业务影响最小   InnoDB
    温备 : 长时间锁表备份     MyISAM
    冷备 : 业务关闭情况下备份 

操作命令

基础备份参数
-A 全库(等于 --all-databases)
     mysqldump -uroot  -A  >/backup/full.sql
     
-B 单库或多个单库
     mysqldump -uroot  -B world  wordpress >/backup/db.sql
     
库  表
     mysqldump -uroot   world city country > /backup/tab.sql

测试恢复操作                ---这里举例，用测试库

1.首先开启二进制日志文件和GTID

vim /etc/my.cnf

log_bin=/data/binlog/mysql-bin                --这里的目录需要自己设置并且给权(chown)
gtid-mode=on
enforce-gtid-consistency=true

 准备数据
create database backup;
use backup
create table t1 (id int);
insert into t1 values(1),(2),(3);
commit;

全表备份

mysqldump -uroot -p123456 -A  -R  --triggers --set-gtid-purged=OFF --master-data=2  --single-transaction > /backup/full_$(date +%F).sql                 --备份所有的库到/backup下用full加时间命名

模拟数据变化
use backup
insert into t1 values(11),(22),(33);
commit;
create table t2 (id int);
insert into t2 values(11),(22),(33);
commit;
 

模拟故障,删除表(只是模拟，不代表生产操作)
drop database backup;
 

截取出来发生变化的数据

mysqlbinlog --skip-gtids --include-gtids='820f8917-d358-11ec-b243-000c29cbdce4:4-6' mysql-bin.000001 > /tmp/a.sql                --注意是从创建到提交不要到删除哪里 这里红的是需要看自己的gtid

开始恢复数据（在数据库里）

set sql_log_bin=0;                --关闭二进制日志
source /backup/full_2024-10-31.sql
source /tmp/a.sql                        --开启二进制日志

恢复了整表但是最近变化的数据没恢复，我们把截取出来的进行恢复

set sql_log_bin=0
source /backup/bak.sql;

set sql_log_bin=1

现在发生意外的数据就都回来了

备份脚本

vim for1.sh 
#!/bin/bash
db=$(mysql -uroot -p123 -e "show databases;"|sed 1d|grep -Ev ".*_schema|sys|mysql")     --把除去系统库以外的备份

for dbname in $db
do
    backdir=/backup/mysql/$dbname
    if [ ! -d $backdir ];then
        mkdir -p $backdir
    fi
    mysqldump -uroot -p123  -B $dbname > $backdir/${dbname}_db_$(date +%F).sql
    echo "$dbname 数据库已经备份完成"
    
    table=$(mysql -uroot -p123 -e "use ${dbname};show tables;"|sed 1d)
    for tablename in $table
    do
        mysqldump -uroot -p123 $dbname $tablename > $backdir/${dbname}_${tablename}_table_$(date +%F).sql
        echo "$dbname 库的 $tablename 表 已经备份成功"
    done
    
done