MySQL 数据热备份

2023-03-20 09:43:27

mysqlhotcopy使用lock tables、flush tables和cp或scp来快速备份数据库.它是备份数据库或单个表最快的途径,完全属于物理备份,但只能运行在数据库目录所在的机器上.与mysqldump备份不同,mysqldump属于逻辑备份,备份时是执行的sql。

mysql5.7已经没有这个命令了，多用于mysql5.5之前。mysqlhotcopy使用lock tables、flush tables和cp或scp来快速备份数据库或单个表，属于裸文件备份（物理备份），只能用于MyISAM引擎的数据库。本质是使用锁表语句，然后cp或scp。

一、CentOs 7 mysqlhotcopy 安装

[root@localhost ~]# yum install perl-DBD* -y

或者编译安装

yum install -y gcc

yum install -y perl-ExtUtils-MakeMaker perl-Devel-CheckLib perl-CPAN

wget -i -c https://cpan.metacpan.org/authors/id/C/CA/CAPTTOFU/DBD-mysql-4.047.tar.gz

tar zxvf DBD-mysql-4.047.tar.gz

cd DBD-mysql-4.047

perl Makefile.PL

make && make install

二、备份

[root@localhost ~]# /bin/mysqlhotcopy -u root -p '' --addtodest mysqlname /opt/sql_back/

三、还原

[root@localhost~]# cp -arp /opt/sql_back/mysqlname /var/lib/mysql/        --将备份的数据移到mysql数据根目录

四、备份脚本

1.添加定时执行。

#每一小时进行mysql的备份

 * * * *  /data/shell/mysql_hotbackup.sh

2. 备份脚本

#!/bin/bash

HOST="192.168.1.128"

USER="root"

PASSWORD="root"

BACKUP_PARENT_DIR="/data/backup/";

BACKUP_DIR_NAME="mysql_data"

CURRENT_DIR=$(pwd)

CURRENT_DAY=$(date +%Y%m%d%H)

DELETE_DAY=$(date -d "2 days ago" +%Y%m%d)

#这里面有两个参数，-A、-N，-A的含义是不去预读全部数据表信息，这样可以解决在数据表很多的时候卡死的问题

#-N，很简单，Don't write column names in results，获取的数据信息省去列名称

SHOW_DATABASES_CMD="mysql -h${HOST} -u${USER} -p${PASSWORD} --default-character-set=utf8 -N -e \"SHOW DATABASES\""

db_result=$(eval "$SHOW_DATABASES_CMD")

#dump_data=./data.user.txt

#>$dump_data

#echo -e "$result" > $dump_data

##这里要额外注意，echo -e "$result" > $dump_data的时候一定要加上双引号，不让导出的数据会挤在一行

db_name_str=$(echo ${db_result} | sed 's/ /,/g' | sed 's/\n//g' | sed 's/-/@002d/g')

OLD_IFS="$IFS"

IFS=","

db_name_array=($db_name_str)

IFS="$OLD_IFS"

echo "Start MySql Hot Backup......";

echo ""

echo ""

#进入备份目录

echo "cd ${BACKUP_PARENT_DIR}";

cd ${BACKUP_PARENT_DIR}

#创建myql备份目录

echo ""

echo "mkdir MySql backup dir: ${BACKUP_DIR_NAME}"

mkdir -p ${BACKUP_DIR_NAME}

#备份数据

echo ""

echo ""

backup_dir=$(cd "${BACKUP_PARENT_DIR}/${BACKUP_DIR_NAME}/"; pwd)

for db_name in ${db_name_array[@]}

do

    ###跳过热备份

    if [ "$db_name"x = "information_schema"x ] || [ "$db_name"x = "mysql"x ] || [ "$db_name"x = "performance_schema"x ]; then

        continue

    fi

    echo "mysqlhotcopy -h ${HOST} -u ${USER} -p ${PASSWORD} --addtodest ${db_name} ${backup_dir}";

    mysqlhotcopy -h ${HOST} -u ${USER} -p ${PASSWORD} --addtodest ${db_name} ${backup_dir}

done

#压缩备份

echo ""

echo ""

echo "Current dir：$(pwd)"

echo ""

echo "Compress MySql backup data......"

echo "tar zcf ./mysql_data_${CURRENT_DAY}.tar.gz ${BACKUP_DIR_NAME}"

tar zcf "./mysql_data_${CURRENT_DAY}.tar.gz" ${BACKUP_DIR_NAME}

echo ""

#删除之前的备份

delete_file="./mysql_data_${DELETE_DAY}*.tar.gz"

echo "Delete MySql backup data of two days ago: ${delete_file}"

if [ -d "$delete_file" ];

then

    echo "不允许删除目录"

else

    echo "rm -rf ${delete_file}"

    rm -rf ${delete_file}

fi

#回到当前目录

echo ""

echo "Return to source directory:${CURRENT_DIR}";

cd ${CURRENT_DIR}

echo ""

echo ""

echo "MySql Hot Backup is Successfully !";

#https://blog.csdn.net/xfcy1990/article/details/52120581

#https://www.cnblogs.com/kissdodog/p/4174421.html

#https://www.cnblogs.com/fire909090/p/7202584.html

#https://blog.51cto.com/853056088/1952430

#https://www.cnblogs.com/jiayy/p/3766286.html

PS:

https://blog.csdn.net/xfcy1990/article/details/52120581
https://www.cnblogs.com/kissdodog/p/4174421.html
https://www.cnblogs.com/fire909090/p/7202584.html
https://blog.51cto.com/853056088/1952430
https://www.cnblogs.com/jiayy/p/3766286.html

码农公寓

一、CentOs 7 mysqlhotcopy 安装

二、备份

三、还原

四、备份脚本

相关文章