linux 下定期脚本全量备份 mysql

1、创建mysql备份用户dumper

     在mysql命令行下输入以下命令：

        grant select, lock tables, show view, trigger on *.* to dumper@localhost identified by 'dumper';

2、创建mysqldump.sh脚本

#!/bin/sh
db_user="dumper"
db_passwd="dumper"
db_host="localhost"
back_dir="/data/backup/mysqlbak/"
time="$(date +%Y-%m-%d)"
cd $back_dir

mkdir $time

all_db="(mysql -u db_user -h db_host -pdb_passwd -Bse 'show databases')"

for db in $all_db

do

mysqldump -u db_user -h db_host -pdb_passwd db | gzip -9 > "back_dir/time/db.time.sql.gz"

done

3、创建计划任务

    mkdir -p /data/backup/mysqlbak

    crontab -e

    01 00 * * * sh /root/scripts/mysqldump.sh