MySQL database backup script code under Ubuntu server

Ming:

I'm going to put the MySQL database in the directory/var/lib/mysql the following pw85 database back into/home/mysql_data, and save it as Mysqldata_bak_2012_04_11. The tar.gz compressed file format (2012_04_11 is the date of the day the backup was executed), and the last 7 days of backup is retained.

Implementation steps:

1. Create the directory where the backup files are saved:/home/mysql_data
Cd/home #进入目录
mkdir Mysql_data #创建目录
2. Create backup script file:/home/mysql_data/mysql_databak.sh
Cd/home/mysql_data #进入目录
Touch mysql_databak.sh #创建文件
Nano mysql_databak.sh #编辑文件, enter the following:

Copy CodeThe code is as follows:
#!/bin/sh
Dump=/usr/bin/mysqldump #mysqldump备份程序执行路径
Out_dir=/home/mysql_data #备份文件存放路径
Linux_user=root #系统用户名
Db_name=pw85 #要备份的数据库名字
Db_user=root #数据库账号 Note: The non-root user should use the backup parameter--skip-lock-tables, otherwise it may error
db_pass=123456 #数据库密码
Days=7 #DAYS = 7 means to delete a backup 7 days ago, that is, only the last 7 days of backup are retained
CD $OUT _dir #进入备份存放目录
Date= ' Date +%y_%m_%d ' #获取当前系统时间
Out_sql= "$DATE. SQL" #备份数据库的文件名
Tar_sql= "Mysqldata_bak_$date.tar.gz" #最终保存的数据库备份文件名
$DUMP-u$db_user-p$db_pass $DB _name--default-character-set=utf8--opt-q-R--skip-lock-tables> $OUT _sql #备份
TAR-CZF $TAR _sql./$OUT _sql #压缩为. tar.gz format
RM $OUT _sql #删除. SQL-formatted backup file
Chown $LINUX _user: $LINUX _user $OUT _dir/$TAR _sql #更改备份数据库文件的所有者
Find $OUT _dir-name "mysqldata_bak*"-type f-mtime + $DAYS-exec rm {} \; #删除7天前的备份文件

(note: {} \; There are spaces in the middle)
################################################################################# ######################
Ctrl+o #保存配置
ctrl+x #退出

3, modify the file properties to make it executable  

chmod +x/home/mysql_data/ mysql_databak.sh

4, modify the/etc/crontab

Nano/etc/crontab #编辑文件, add the following
* * * root/home/mysql_data/ mysql_databak.sh #表示每天22点45分执行备份
Ctrl+o #保存配置
ctrl+x #退出

5, restart Crond make settings effective

Service cron Stop #停止
Service cron start #启动
/etc/init.d/cron restart #重启
chkconfig cron on #设为开机启动

every day you/home/mysql_ A compressed file like mysqldata_bak_2012_04_11.tar.gz can be seen below the data directory
If you need to recover the file, simply unzip the file: TAR-ZXVF mysqldata_bak_2012_04_ The 11.tar.gz
is then imported into the database.

At this point, the MySQL database backup script under Ubuntu server is complete.