CentOS 7
创建备份文件夹路径
mkdir /usr/local/mysqlbackup
mkdir /usr/local/mysqlbackup/backup_data
编写sh脚本
db_user=root
db_password=password@123
db_name=databasename
backup_dir=/usr/local/mysqlbackup/backup_data
date=$(date +%Y%m%d_%H%M%S)
/usr/bin/mysqldump -u$db_user -p$db_password $db_name>$backup_dir/$db_name_$date.sql
/usr/bin/mysqldump -u$db_user -p$db_password $db_name | gzip >$backup_dir/$db_name_$date.sql.gz
rm -f $backup_dir/$db_name_$date.sql
find $backup_dir/ -mtime +7 -name "*gz*" -exec rm -rf {} \;
- 如是远程连接这种方式会报密码安全错误
- 修改如下虽然还是会发出警告但是不影响导出
db_user=用户名
db_password=密码
db_name=数据库名称
db_host=连接地址
db_port=端口
backup_dir=/usr/local/mysqlbackup/backup_data
date=$(date +%Y%m%d_%H%M%S)
/usr/bin/mysqldump --defaults-extra-file=/etc/my.cnf -u$db_user -p$db_password -h$db_host -P$db_port $db_name>$backup_dir/$db_name_$date.sql
/usr/bin/mysqldump --defaults-extra-file=/etc/my.cnf -u$db_user -p$db_password -h$db_host -P$db_port $db_name | gzip >$backup_dir/$db_name_$date.sql.gz
rm -f $backup_dir/$db_name_$date.sql
find $backup_dir/ -mtime +7 -name "*gz*" -exec rm -rf {} \;
其实就是在导出和压缩时候加入
--defaults-extra-file=/etc/my.cnf -h$db_host -P$db_port
测试运行
cd /usr/local/mysqlbackup
./mysqlbackup.sh
报错权限问题
-bash: ./mysqlbackup.sh: 权限不够
运行命令
chmod +x ./mysqlbackup.sh
[root@izwz90h61n2yng0xztqyl4z mysqlbackup]
[root@izwz90h61n2yng0xztqyl4z backup_data]
总用量 1544
-rw-r--r-- 1 root root 787070 5月 10 16:14 20210510_161417.sql.gz
-rw-r--r-- 1 root root 787070 5月 10 16:20 20210510_162001.sql.gz
成功
vim /etc/crontab
输入 i 进行编辑 最底部新增定时执行命令
20 16 * * * root /usr/local/mysqlbackup/mysqlbackup.sh(表示每天16:20执行当前命令)
SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin
MAILTO=root
20 16 * * * root /usr/local/mysqlbackup/mysqlbackup.sh
~
~
点击esc输入 :wq 退出保存定时任务就生效了