Idea: How to quickly write a comprehensive database add-on + full-ready + DELETE, automated scripts?

1. Data function:

Data for us is very important, if you are the management of data, operations personnel, data loss, can not be found, it means that your career is over, in order to avoid data loss, we also do a lot of maintenance, backup, such as the master from the copy, do full, add, etc. This can refer to my other article mysql master-slave replication.

I don't have much to say here, today how do we write a better data backup automation script, let us manage the database more.

2. mysql Backup script idea

First of all, there is a train of thought, my requirement is every Monday to Friday to do an incremental backup, Saturday to do full, on the first two days of the increase of the log deleted, two weeks before the full preparation also deleted

The entire process involves the shell command, so decisively abandoned python, using shell scripts, the database to do full preparation needs to apply the mysqldump command, here command MySQL comes with, if your backup data is larger than 10G, Please use Xtrackbackup to do the full preparation. Incremental commands are relatively simple, just refresh the log.

3. Backup script Writing

#!/bin/bash mysqldump ()  {   mysqldump -uroot -p123456 -- Single-transaction -r --triggers -e --master-data --flush-logs --all-databases  > /databack/data_all/' date +%f "-%h:%m" ' .sql}                    ## #这里写个函数, write the full name in $1                   ###$1 is the argument that we execute the script, This enables the function to perform a variability mysql_log  () {mysqladmin -u root -p123456  flush-logs}$1                   ## #增量备份, Also write the command in the # #下面创建计划任务, just write it to/etc/crontab. crontab_dump  ()  {          cat /etc/crontab | grep  "bash $0 mysqldump "      # #这里找一找使用有写执行全备的命令是否已经写到/etc/crontabInside.   if [ $? -eq 0 ]; then                             # #定义一个判断语句, if you find a script command in the/etc/crontab to execute the full backup, output a word, do not write the time task in      echo   "The port test has been written to the time task, and  00:00 will perform the task "   else     echo   "0 0 * * 6   bash $0 mysqldump "  >> / etc/crontab  fi     } crontab_dumpcrontab_mysqladmin  ()  {   cat /etc/crontab | grep  "bash $0 mysql_log "    # #这里找一找使用有写执行增备的命令是否已经写到/etc/crontab inside.   if [ $? -eq 0 ]; then                            # #定义一个判断语句, if you find a script command in/etc/crontab that performs the add-on, output a sentence without writing the time task in       echo  "The port test has been written to the time task,  and 00:00 will perform the task "  else      echo  "0 0 * * 1-5 bash $0 mysql_log "  >> /etc/ Crontab  fi     } crontab_mysqladmin

4, the daily generated log will accumulate, we need to remove the deletion, continue to improve.

#!/bin/bash mysqldump ()  {mysqldump -uroot -p123456 --single-transaction -R  --triggers -e --master-data --flush-logs --all-databases > /databack/data_all /' date +%f '-%h:%m "'. sqlnum1= ' ls -rt  /databack/data_all  | wc -l '      # #按时间列出, how many files are there in your fully prepared data? I've got it all ready for the/databack/data_all/directory file1= ' ls -rt   /databack/data_all | head  -n 1 '   # #使用ls命令结合head命令, find the longest file      for i in  $num 1 ; do                           ## #做一个for循环, if the file listed is greater than 2, then delete the oldest file         if  [  $i  -gt 2 ] ; then        rm -rf    /databack/data_all/$file 1        fi    done}$1mysql_log  () { mysqladmin -u root -p123456  flush-logsfor a in {1..5};d o                             ## #这里做一个for循环, that the cycle five times, should be two log, more, detect five times, or better, you can set your own num2= ' Ls -rt   /databack/data_logbin/ | wc -l '    # #按时间列出, how many files are there in your full data? I've got it all ready. databack/data_all/directory under File2 ' ls -rt  /databack/data_logbin/ | head  -n 1 '   # #使用ls命令结合head命令, find the longest file     for i in  $num 2; do         if  [  $i  -gt 3 ] ; then                     ## #做一个for循环, if a list ofThe file is greater than 3, and then deletes the oldest file, loops back out, and goes to the next loop. Five         rm -rf   /databack/data_logbin/$file 2 consecutive times        # else         #echo   "Log-bin was update"         fi     donedone}$1crontab_dump  ()  {  cat /etc/crontab | grep  "bash $0  Mysqldump  "    if [ $? -eq 0 ]; then      echo  "the port test has been written to the  Time task, and 00:00 will perform the task "  else      echo  "0 0 * * 6   bash $0 mysqldump "  >> /etc/crontab  fi     } crontab_dumpcrontab_mysqladmin  ()  {  cat /etc/crontab | grep  "Bash $0 mysql_ log  "    if [ $? -eq 0 ]; then      echo  "The port test has been written to the time task,  and 00:00 will perform the task "  else      echo  "0 0 * * 1-5 bash $0 mysql_log "  >> /etc/ Crontab  fi     } crontab_mysqladmin

5. Summary

A MySQL backup script is finished, it is not difficult, but, in writing any script, you need to clear up the most important, first the outline of the outlines, and then slowly add, I believe the more written the better.

Idea: How to quickly write a comprehensive database add-on + full-ready + DELETE, automated scripts?