linux下面备份数据库

1.前置工作

1.登录账号

mysql -uroot -p

2.切换到mysql数据库

use mysql

3.删除默认用户

delete from user where user='';//否则新创建的用户无法登录，无法起作用
FLUSH PRIVILEGES；

4.创建新账号

grant select,show view,lock tables,trigger on skhosiptal.* to '*****'@'%' identified by '***';
FLUSH PRIVILEGES;

2.编写shell脚本

#!/bin/bash
BACKUP=/data/backup/skhospital
DATETIME=$(date +%Y_%m_%d_%H%M%S)
echo $DATETIME
echo "=================开始备份================"
echo "=================备份的路径是$BACKUP/$DATETIME.tar.gz"
HOST=localhost
DATABASE=******
DB_USER=*****
DB_PWD=*****
[ ! -d "$BACKUP/$DATETIME" ] && mkdir -p "$BACKUP/$DATETIME"
mysqldump -u$DB_USER -p$DB_PWD --host=$HOST $DATABASE | gzip >$BACKUP/$DATETIME/$DATETIME.sql.gz
cd $BACKUP
tar -zcvf $DATETIME.tar.gz $DATETIME
rm -rf $BACKUP/$DATETIME
find $BACKUP -mtime +10 -name "*.tar.gz" -exec rm -rf {} \;
echo "=======备份文件成功======"

设置定时器

[root@localhost shell]# crontab -e
10 2 * * * /home/learn/shell/mysql_db_backup.sh

启动定时器

bash-4.1# service crond restart
停止 crond：                                               [确定]
正在启动 crond：                                           [确定]
You have new mail in /var/spool/mail/root