Mysql-Mysqldump-备份恢复

Mysql-Mysqldump-备份恢复

mysqldump备份恢复

#!/bin/bash

ROOT_PATH=/mysqldata_backup
DATETIME=$(date +"%Y%m%d%H%M")

passwd=$(grep password /etc/my.cnf |awk  '{print $3}' |head -1)
mysqldump -uroot -p$passwd --all-databases --set-gtid-purged=OFF |gzip >$ROOT_PATH/mysqlbackup_all_show_$DATETIME.sql.gz

## 上传到aws s3
/usr/local/bin/aws s3 cp $ROOT_PATH/mysqlbackup_all_show_$DATETIME.sql.gz s3://aaa/backup/

## 清理过期
find $ROOT_PATH -mtime +1 -type f -name "*.sql.gz" | xargs rm -vf

## 恢复
gunzip < test.tar.gz |mysql -hlocalhost -uroot -pxxxxx

xtrabackup备份恢复

## 全量备份
$ xtrabackup --backup --target-dir=/data/backups/

## 异地恢复1-修改root密码为备份数据库密码
use mysql；
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '2014Picc!!!';
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '2014Picc!!!';
FLUSH PRIVILEGES;

## 异地恢复2-新建目录，修改my.cnf指向目录，并修改目录权限mysql:mysql

## 异地恢复3-准备步骤
$ xtrabackup --prepare --target-dir=/<备份文件所在目录>/

## 异地恢复4-拷贝文件
$ xtrabackup --copy-back --target-dir=/<备份文件所在目录>/

## 异地恢复5-修改目录权限mysql:mysql
chown -R mysql:mysql

## 启动数据库
systemctl start mysql