A few days ago have friends to help write a MySQL data backup script, so there is the following through the mysqldump command backup database script, paste out to communicate with you, if there is a problem, please correct me, thank you.
Implementation features:
1 backing up the specified database
2 Delete the backup file before the specified number of days, the default setting is 7 days
#!/bin/bash## file name: mysql_bak.sh# #数据库备份文件的目录, if not, create a directory or specify a backup directory backup_dir= "/bak/mysqlbak" #指定mysql所在主机的主机名DB_ Hostname= ' HOSTNAME ' #指定mysql登录用户名DB_USERNAME = ' backupuser ' #指定mysql登录密码DB_PASSWORD = ' PASSWORD ' #指定备份的数据库名DB_NAME = " DBName "#定义当前日期为变量CURRENT_DATE =$ (date +"%y%m%d$h ") #定义删除N天前的文件变量DEL_DAYS_BEFORE_FILES =7# Specify the directory where MYSQLDUMP is located mysqldump_dir= "/usr/bin" #备份指定数据库if $ ($MYSQLDUMP _dir/mysqldump -h ${db_hostname} -u${DB_USERNAME} -p${DB_PASSWORD} ${DB_NAME} > "${backup_dir}/${db_name}_${ Current_date}.sql ");thencd ${backup_dir}gzip ${db_name}_${current_date}.sqlecho " ${CURRENT_DATE }--backup database ${db_name} successfully! " elseecho "${current_date}--backup database ${db_name} unsuccessfully" fi# Delete the backup file specified n days ago find ${BACKUP_DIR} -name "${db_name}_*.sql.gz" -type f -mtime +${del_days_ Before_files} -exec rm {} \; > /dev/null 2>&1
Instructions for using MySQL backup scripts:
1 backup operation is done using the mysqldump command, with the default setting reserved for nearly 7 days of backup files
2 We recommend that you create a backup using the user Backupuser (the Host field suggests specifying IP)
>create USER ' backupuser ' @ '% ' identified by ' password ';
>grant Select,lock tables,file,reload on * * to ' backupuser ' @ '% ';
(Individuals think that these permissions are sufficient, if not enough to add themselves)
3 Note You need to specify the database backup directory in the script
4 Assigning Script Execution permissions
$chmod +x mysql_bak.sh
5 Create a scheduled task, such as performing a backup operation every two o'clock in the morning
Operation under Redhat
$crontab-E
# $PWD refers to the path where the backup script resides
# $BACKUP _dir refers to the database backup directory
* 2 * * * $PWD/mysql_bak.sh &> $BACKUP _dir/mysql_bak.log
Note: Run the script user to set permissions on the directory that you are manipulating.
This article is from "Rookie Roger" blog, please be sure to keep this source http://rogerwang.blog.51cto.com/5326353/1948955
mysqldump backup specifies MySQL database script