mylvmbackup-discuss team mailing list archive

[Linda Xu <lxu8@xxxxxxxxx>]

Hi, Lenz
For MySQL backup with flush table with read locks methods, there is knowing issue/risk that the command is conflict with long run query. Sometime it could cause concerns.
I used to work with a company use NFS which support a quick snapshot, and wrote a scripts to handle it by fork a child process to put FTWRL and use parent to monitor if the lock is success. If not, we killed the child process because our business is more care about no interrupt any application and backup will try multiple time until it is success. 
Now my current company use mylvmbackup script to take the MySQL backup. The step is a manual process because long run query could occasionally happen.So I spend some time to modify the current 0.16 version to make it less painful when handle Flush table with read locks .
Here is the highlight of the changes:
1. It provide an option to stop replication before backup or not which can minimum the write conflict2. it provide 3 different way to handle flush table/flush table with read lock conflict    mode 0, will put lock then check if there is any long run query cause it wait, if so, after wait for configurable time and recheck, it will kill LONG RUN QUERY    mode 1, will put lock then check if there is any long run query cause it wait, if so, alter wait for configurable time and recheck, it will kill the child session of mylvmbackup and exist with error    mode 2, before put the lock, it will check any active query, if so then the backup will wait and recheck. If after configurable times of check and still find long run query, the backup script will exit with error 3. It also provide the alternative files that customer can define how to find a long run query.  
Are you interesting to take the changes to your next distribution?
# 09-01-2015 modified by Lou#       fork a child process to completed all tasks start from flush_tables subroutine#       parent process will monitor if the child process get block by other long run query, if so, kill the long run »# 09-04-2015 modified by lxu#       add new options#       1. flush_ftwrl_mode  :#               0: put lock first, will kill long run query if any blocker. (backup has higher priority)#               1: put lock first, will wait n seconds and trials before kill backup scripts (running app has»#               2: not put lock, check long run query first, if exists, wait n seconds and trials before put lock (la»#               default 2#       2. flush_ftwrl_wait  :#               wait n seconds before next triial#               default 10#       3. flush_ftwl_trials :#              n times trials before exit the script#              default 6#       4. stop_replication :#             0: default, do not stop replication#             1: stop replication before lock tables#       5. query_override :#             the file path store the where condition to use find the long run process which block ftwrl which will o»#             default is#             where TIME>0 and  USER<>'system user' and STATE like '%ing%'#             and INFO not in ('flush tables with read lock','flush tables’)


example of to use new feature
mylvmbackup \        --lvsize=$LVSZ  \        --vgname $VG  \        --lvname $LV  \        --backuplv $BACKUPLV  \        --mountdir=/var/spool/mylvmbackup/mnt-$DB \        --port=$PORT \        --relpath $DB \        --backupdir=$BACKUPPATH \        --flush_ftwrl_mode=0 \        --flush_ftwrl_wait=5 \        --flush_ftwrl_trials=10 \        --stop_replication=1 \       
Linda Xu   
   -       
         - https://www.linkedin.com/pub/linda-yun-xu/4/303/8a5
         - 
         -