mysqldump backup specifies MySQL database script

Source: Internet
Author: User
Tags mysql backup

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.