ubuntu mysql自动备份,Ubuntu上MySQL的简单自动备份

MySQL可以支持Master,Slave之间的动态Replication。这里就不研究了。简单备份主要依靠mysqldump来实现。

1. 创建具有backup权限的帐号：

因为backup脚本会明码显示密码，所以权限越低越好，以下是进行backup所需的的最小权限(针对Mysql 5.x)

mysql> GRANT LOCK TABLES, SELECT, FILE, RELOAD, SUPER, SHOW VIEW,CREATE VIEW

> ON  *.*

> TO ‘dba_backup‘ @localhost

> IDENTIFIED BY ‘password123‘;

> flush privileges;

> quit

这样密码为password123的用户dba_backup就建好了。

2. 编辑数据库备份脚本

1) 建一个备份目录 mkdir /var/backup

2) 编辑 mysql_backup.sh

#!/bin/sh

# mysql_backup.sh: backup mysql databases and keep newest 5 days backup.

# —————————–

PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin

db_user=”dba_backup”

db_passwd=”password123”

db_host=”localhost”

#mysql>show databases; to obtain the databases name, list the name below

#for the db you wish to backup. e.g. databases=”db1 db2 db3″

databases=”db1″

# the directory for story your backup file.

backup_dir=”/var/backup”

# date format for backup file (dd-mm-yyyy)

time=”$(date +”%d-%m-%Y”)”

######………$(date +”%d-%m-%Y”)…date…..

# mysql, mysqldump and some other bin’s path

#MYSQL=”$(which mysql)”

#MYSQLDUMP=”$(which mysqldump)”

#MKDIR=”$(which mkdir)”

#RM=”$(which rm)”

#MV=”$(which mv)”

#GZIP=”$(which gzip)”

#########………………………………..

# if the directory for storing backup does not exist and not writeable, quit

test ! -w $backup_dir && echo “Error: $backup_dir is un-writeable.” && exit 0

# otherwise make the directory to store the newest backup

test ! -d “$backup_dir” && mkdir “$backup_dir”

#####………..$backup_dir………..

# get all databases

for db in $databases

do

mysqldump -u $db_user -h $db_host -p$db_passwd $db | gzip -9 > “$backup_dir/$time.$db.gz”

done

#delete the oldest backup 7 days ago

find $backup_dir -name “*.gz” -mtime +7 |xargs rm -rf

exit 0;

3) chmod +x ./mysql_backup.sh

3. 添加到Cron

crontab -e

加入

0 5 * * * /root/mysql_backup.sh

每天凌晨5点执行数据库备份

因为3点留给Drupal的Cron(以www-data身份运行), 4点留给logrotate(系统默认设置)

Like this:

Like Loading...

Related