dbjobs.in 7.06 KB
Newer Older
1
#!/bin/bash
2 3 4 5 6 7 8

USER={{ parameter_dict['db-user'] }}
PASSWORD={{ parameter_dict['db-password'] }}
ERROLOG={{ parameter_dict['mysql-dir'] }}/.system/logs/errors.log
SLOWLOG={{ parameter_dict['mysql-dir']}}/.system/logs/sql-slow
BACKUPDIR={{ parameter_dict['mysql-dir'] }}/.system/backup
DATADIR={{ parameter_dict['mysql-dir'] }}/
9 10 11 12 13 14
{% if parameter_dict['use-ipv6'] == True -%}
{% set listen = "TCP6-LISTEN" -%}
{% else -%}
{% set listen = "TCP-LISTEN" -%}
{% endif -%}
export PATH={{ parameter_dict['socat-location'] }}/bin:{{ parameter_dict['mysql-location'] }}/bin:{{ parameter_dict['gzip-location'] }}/bin:$PATH
15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59
JOBS=( "xtrabackup" "mariabackup" "error" "slowquery" "zfssnapback" "optimize" "reseedxtrabackup" "reseedmariabackup" "reseedmysqldump" "flashbackxtrabackup" "flashbackmariadbackup" "flashbackmysqldump" "stop" "start")

doneJob()
{
  mysql --defaults-file={{ parameter_dict['dbjob-cnf'] }} -e "set sql_log_bin=0;UPDATE replication_manager_schema.jobs set end=NOW(), result=LOAD_FILE('{{ parameter_dict['log-dir'] }}/dbjob.out') WHERE id='$ID';" &
}

pauseJob()
{
  mysql --defaults-file={{ parameter_dict['dbjob-cnf'] }}  -e "select sleep(6);set sql_log_bin=0;UPDATE replication_manager_schema.jobs set result=LOAD_FILE('{{ parameter_dict['log-dir'] }}/dbjob.out') WHERE id='$ID';" &
}

partialRestore()
{
 mysql --defaults-file={{ parameter_dict['dbjob-cnf'] }}  -e "set sql_log_bin=0;install plugin BLACKHOLE soname 'ha_blackhole.so'"
 for dir in $(ls -d $BACKUPDIR/*/ | xargs -n 1 basename | grep -vE 'mysql|performance_schema|replication_manager_schema') ; do
 mysql --defaults-file={{ parameter_dict['dbjob-cnf'] }}  -e "set sql_log_bin=0;drop database IF EXISTS $dir; CREATE DATABASE $dir;"


  for file in $(find $BACKUPDIR/$dir/ -name "*.exp" | xargs -n 1 basename | cut -d'.' --complement -f2-) ; do
    cat $BACKUPDIR/$dir/$file.frm | sed -e 's/\x06\x00\x49\x6E\x6E\x6F\x44\x42\x00\x00\x00/\x09\x00\x42\x4C\x41\x43\x4B\x48\x4F\x4C\x45/g' > $DATADIR/$dir/mrm_pivo.frm
    mysql --defaults-file={{ parameter_dict['dbjob-cnf'] }}  -e "set sql_log_bin=0;ALTER TABLE $dir.mrm_pivo  engine=innodb;RENAME TABLE $dir.mrm_pivo TO $dir.$file; ALTER TABLE $dir.$file DISCARD TABLESPACE;"
    mv $BACKUPDIR/$dir/$file.ibd $DATADIR/$dir/$file.ibd
    mv $BACKUPDIR/$dir/$file.exp $DATADIR/$dir/$file.exp
    mv $BACKUPDIR/$dir/$file.cfg $DATADIR/$dir/$file.cfg
    mv $BACKUPDIR/$dir/$file.TRG $DATADIR/$dir/$file.TRG
    mysql --defaults-file={{ parameter_dict['dbjob-cnf'] }}  -e "set sql_log_bin=0;ALTER TABLE $dir.$file IMPORT TABLESPACE"
  done
  for file in $(find $BACKUPDIR/$dir/ -name "*.MYD" | xargs -n 1 basename | cut -d'.' --complement -f2-) ; do
    mv $BACKUPDIR/$dir/$file.* $DATADIR/$dir/
    mysql --defaults-file=/etc/mysql/dbjob.cnf  -e "set sql_log_bin=0;FLUSH TABLE $dir.$file"
  done
  for file in $(find $BACKUPDIR/$dir/ -name "*.CSV" | xargs -n 1 basename | cut -d'.' --complement -f2-) ; do
    mv $BACKUPDIR/$dir/$file.* $DATADIR/$dir/
    mysql --defaults-file={{ parameter_dict['dbjob-cnf'] }}  -e "set sql_log_bin=0;FLUSH TABLE $dir.$file"
  done
 done
 for file in $(find $BACKUPDIR/mysql/ -name "*.MYD" | xargs -n 1 basename | cut -d'.' --complement -f2-) ; do
    mv $BACKUPDIR/mysql/$file.* $DATADIR/mysql/
    mysql --defaults-file={{ parameter_dict['dbjob-cnf'] }}  -e "set sql_log_bin=0;FLUSH TABLE mysql.$file"
 done
 cat $BACKUPDIR/xtrabackup_info | grep binlog_pos | awk  -F, '{ print $3 }' | sed -e 's/GTID of the last change/set sql_log_bin=0;set global gtid_slave_pos=/g' | mysql -h{{ parameter_dict['ip'] }} -P{{ parameter_dict['port'] }} -p$PASSWORD -u$USER
 mysql --defaults-file={{ parameter_dict['dbjob-cnf'] }}   -e"flush privileges;start slave;"
}

60 61
kill -9 $(lsof -t -i:{{ parameter_dict['socat-port'] }} -sTCP:LISTEN)

62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79
for job in "${JOBS[@]}"
do

 TASK=($(echo "select concat(id,'@',server,':',port) from replication_manager_schema.jobs WHERE task='$job' and done=0 order by task desc limit 1" | mysql -h{{ parameter_dict['ip'] }} -P{{ parameter_dict['port'] }} -p$PASSWORD -u$USER -N))

 ADDRESS=($(echo $TASK | awk -F@ '{ print $2 }'))
 ID=($(echo $TASK | awk -F@ '{ print $1 }'))
 #purge de past
 mysql --defaults-file={{ parameter_dict['dbjob-cnf'] }}  -e "set sql_log_bin=0;UPDATE replication_manager_schema.jobs set done=1 WHERE done=0 AND task='$job';"

  if [ "$ADDRESS" == "" ]; then
    echo "No $job needed"
  else
    echo "Processing $job"
    case "$job" in
      reseedmysqldump)
       echo "Waiting backup." >  {{ parameter_dict['log-dir'] }}/dbjob.out
       pauseJob
80
       socat -u {{ listen }}:{{ parameter_dict['socat-port'] }},bind={{ parameter_dict['host'] }},reuseaddr STDOUT | gunzip | mysql -h{{ parameter_dict['ip'] }} -P{{ parameter_dict['port'] }} -p$PASSWORD -u$USER --init-command="reset master;set sql_log_bin=0" > {{ parameter_dict['log-dir'] }}/dbjob.out 2>&1
81 82 83 84 85
        mysql --defaults-file={{ parameter_dict['dbjob-cnf'] }}  -e 'start slave;'
      ;;
      flashbackmysqldump)
       echo "Waiting backup." >  {{ parameter_dict['log-dir'] }}/dbjob.out
       pauseJob
86
       socat -u {{ listen }}:{{ parameter_dict['socat-port'] }},bind={{ parameter_dict['host'] }},reuseaddr STDOUT | gunzip | mysql -h{{ parameter_dict['ip'] }} -P{{ parameter_dict['port'] }} -p$PASSWORD -u$USER --init-command="set sql_log_bin=0" > {{ parameter_dict['log-dir'] }}/dbjob.out 2>&1
87 88 89 90 91 92 93
       mysql --defaults-file={{ parameter_dict['dbjob-cnf'] }}  -e 'start slave;'
      ;;
      reseedmariabackup)
       rm -rf $BACKUPDIR
       mkdir $BACKUPDIR
       echo "Waiting backup." >  {{ parameter_dict['log-dir'] }}/dbjob.out
       pauseJob
94
       socat -u {{ listen }}:{{ parameter_dict['socat-port'] }},bind={{ parameter_dict['host'] }},reuseaddr STDOUT | mbstream -x -C $BACKUPDIR
95 96 97 98 99 100 101 102 103
       # mbstream -p, --parallel
       mariabackup --prepare --export --target-dir=$BACKUPDIR
       partialRestore
      ;;
      flashbackmariadbackup)
       rm -rf $BACKUPDIR
       mkdir $BACKUPDIR
       echo "Waiting backup." >  {{ parameter_dict['log-dir'] }}/dbjob.out
       pauseJob
104
       socat -u {{ listen }}:{{ parameter_dict['socat-port'] }},bind={{ parameter_dict['host'] }},reuseaddr STDOUT | xbstream -x -C $BACKUPDIR
105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128
       mariabackup --prepare --export --target-dir=$BACKUPDIR
       partialRestore
      ;;
      mariabackup)
       cd {{ parameter_dict['tmp-dir'] }}
       mariadb-backup --innobackupex --defaults-file={{ parameter_dict['dbjob-cnf'] }} --socket='{{ parameter_dict["mysqld-socket"] }}'  --no-version-check  --user=$USER --password=$PASSWORD --stream=xbstream {{ parameter_dict['tmp-dir'] }}/ | socat -u stdio TCP:$ADDRESS &>{{ parameter_dict['log-dir'] }}/dbjob.out
      ;;
      error)
       cat $ERROLOG| socat -u stdio TCP:$ADDRESS &>{{ parameter_dict['log-dir'] }}/dbjob.out
      ;;
      slowquery)
       cat $SLOWLOG| socat -u stdio TCP:$ADDRESS &>{{ parameter_dict['log-dir'] }}/dbjob.out
      ;;
      optimize)
       mysqlcheck --defaults-file={{ parameter_dict['dbjob-cnf'] }} -o --all-databases --skip-write-binlog &>{{ parameter_dict['log-dir'] }}/dbjob.out
      ;;
      restart)
        {{ parameter_dict['restart-script'] }} > {{ parameter_dict['log-dir'] }}/dbjob.out
      ;;
  esac
  doneJob
  fi

done