MySQL数据自动备份

MySQL数据自动备份

创建备份脚本

backup.sh

#!/bin/bash

# 数据库地址存放地址
backupdir=/home/backup/sql
time=`date +%Y%m%d`

#需要备份的数据库的连接的用户名和密码和数据库
# /usr/bin/mysqldump -h [数据库主机] -u [数据库用户名] -p[数据库密码] [备份的数据库]  > $backupdir/mysql_$time.sql
/usr/bin/mysqldump -h 127.0.0.1 -u root -p123456 blue  > $backupdir/mysql_$time.sql


# 保留10日的数据库文件
# find $backupdir -name "mysql_*.sql" -type f -mtime +10 -exec rm -rf {} \;

将脚本设置为可执行

chmod +x backup.sh

创建Linux 的执行任务

查看定时任务是否启动

/etc/init.d/cron status

如果未启动，则启动服务

/etc/init.d/cron start

编辑定时任务 crontab -e

root@localhost:~# crontab -e
no crontab for root - using an empty one

Select an editor.  To change later, run 'select-editor'.
  1. /bin/nano        <---- easiest
  2. /usr/bin/vim.basic
  3. /usr/bin/vim.tiny

Choose 1-3 [1]: 

这里选择编辑器，我这里选择2 vim

在最后一行添加定时任务

# 每天半夜 1点半执行 数据库备份
30 1 * * * /home/backup/backup.sh

查看定时任务

crontab -l

如:

root@localhost:~# crontab -l
# Edit this file to introduce tasks to be run by cron.
#
# Each task to run has to be defined through a single line
# indicating with different fields when the task will be run
# and what command to run for the task
#
# To define the time you can provide concrete values for
# minute (m), hour (h), day of month (dom), month (mon),
# and day of week (dow) or use '*' in these fields (for 'any').
#
# Notice that tasks will be started based on the cron's system
# daemon's notion of time and timezones.
#
# Output of the crontab jobs (including errors) is sent through
# email to the user the crontab file belongs to (unless redirected).
#
# For example, you can run a backup of all your user accounts
# at 5 a.m every week with:
# 0 5 * * 1 tar -zcf /var/backups/home.tgz /home/
#
# For more information see the manual pages of crontab(5) and cron(8)
#
# m h  dom mon dow   command
30 1 * * * /home/backup/backup.sh

这样MySQL数据自动备份就完成了