How to automate daily backup of MySQL database in production environment

1. Description

I believe that a lot of friends at work will have this demand, the boss or the leader to make you back up the MySQL database every day, how can you do it, is every day to a certain time on the server to knock over the MySQL backup command, or want to write a script, timed fixed-point automatic backup it? I'm sure everyone wants it to be backed up automatically, and then I'm going to do a "shell script + timed task" to automatically back up the MySQL database.

2. Environment

Backup path:/data/mysqlbak/

Backup script:/data/mysqlbak/mysqlbak.sh

Backup time: Daily 23:59 Backup

Backup requirements: For example, the backed up data is kept for only 1 weeks

3. mysqlbak.sh Script

#!/bin/bash# database user name dbuser= ' root ' #数据库用密码dbpasswd = ' ******** ' #需要备份的数据库, multiple databases are separated by spaces Dbname= ' backdata01  Backdata02 ' #备份时间backtime = ' date +%y%m%d ' #日志备份路径logpath = '/data/mysqlbak/' #数据备份路径datapath = '/data/mysqlbak/' echo  ' ################## $backtime ########################## ' #日志记录头部echo   ' "Backup Time is ${backtime}, back Up database table  ${dbname}  start " >> ${logpath}/log.log# official backup database for table in  $dbname;  dosource= ' mysqldump -u ${dbuser} -p${dbpasswd} ${table}> ${logpath}/${ Backtime}.sql '  2>> ${logpath}/mysqllog.log; #备份成功以下操作if  [  "$?"  == 0 ];thencd  $datapath # to save hard disk space, compress the database tar zcf ${table}${backtime}.tar.gz $ {backtime}.sql > /dev/null# Delete the original file, leaving only the compressed file rm -f ${datapath}/${backtime}.sql# deleted seven days ago Backup, That is, save only 7 days of backup find  $datapath  -name  "*.tar.gz"  -type f -mtime +7 -exec  rm -rf {} \; > /dev/null 2>&1echo  "database table  ${dbname}  Backup succeeded!!"  >> ${logpath}/mysqllog.logelse# Backup fails the following actions echo  "database table  ${dbname}  Backup failed!!"  >> ${logpath}/mysqllog.logfidoneecho  ' ################# #完成 ############################# '

The script is written, and you have to remember to add execute permissions to the script:

#chmod +x/data/mysqlbak/mysqlbak.sh

4. Configure timed Task Execution script

#crontab-e59 * * */data/mysqlbak/mysqlbak.sh

Parameter description:

The format is: time-sharing Week command

* *: Automatically executes the script 23:59 minutes per day

M: Minutes (0-59). Every minute with * or */1

H: Hours (0-23). (0 means 0 points)

D: Day (1-31).

M: Month (1-12).

D: Day of the Week (0~6,0 is Sunday).

Tip: It is best that you run the script first, and then write to Crontab, and so on, and so on, go to the /data/mysqlbak/directory to see if there is a backup file, if there is, the script executes successfully, remember not to make the wrong backup user and password.

How to automate daily backup of MySQL database in production environment