MySQL auto-complete, add-on & log backup using shell

Database Hot Standby Script:

Vim backup.sh

#!/bin/sh

Time= ' Date ' +%y%m%d_%h%m%s "'

Host= ' hostname '

week= ' Date +%w '

Monitor= "/home/mysql/monitor/mysql_hotbackup_status.txt" # #zabbix监控文件

time_start= ' Date +%s '

N=0 # # #要周几做全备, Sunday is 0, Monday to Saturday in turn 1~6

function Check_status ()

{

Status= ' Cat ${1}|grep ' innobackupex:completed ok ' |wc-l '

If [$status-eq 1]

Then

echo 0 > ${monitor}

Else

echo 1 > ${monitor}

Fi

}

If [$week-eq $n]

Then

/bin/sh/home/mysql/shell/mysql_xtrabackup.sh Full >>/home/mysql/log/xtrabackup_log/${host}_${time}_ Full.log 2>&1

Check_status/home/mysql/log/xtrabackup_log/${host}_${time}_full.log;

Else

/bin/sh/home/mysql/shell/mysql_xtrabackup.sh incr >>/home/mysql/log/xtrabackup_log/${host}_${time}_ Incr.log 2>&1

Check_status/home/mysql/log/xtrabackup_log/${host}_${time}_incr.log;

Fi

time_end= ' Date +%s '

times=$ ((${time_end}-${time_start}))

If [$week-eq $n]

Then

echo "It takes ${times} seconds to backup the full backup!!!" >>/home/mysql/log/xtrabackup_log/${host}_${time}_ful L.log

Else

echo "It takes ${times} seconds to backup the INCR backup!!!" >>/home/mysql/log/xtrabackup_log/${host}_${time}_inc R.log

Fi

# # #数据库备份策略 # #

# # #周日全备, Monday ~ Saturday Incremental Backup # # #

Vim mysql_xtrabackup.sh

#!/bin/bash

If [$#-ne 1]

Then

echo "Usage: ' basename ' [FULL|INCR]"

Exit 1

Fi

Name= ' hostname ' # The name for Bakcup

CONF_FILE=/ETC/MY.CNF # the MySQL config file

Bakuser= "Backup"

bakpass= "Backup"

bakdir=/data/mysql/mysql3306/tmp/# The directory to backup

Baklog=/home/mysql/log

Host=localhost

Innobackupex=/usr/bin/innobackupex

Remotedir= "/mysqlbackup/databak/${name}"

Time= ' Date ' +%y%m%d_%h%m%s "' # don ' t modify

yesterday= ' date + '%y%m%d "-D"-1 days "'

chkpointdir=/home/mysql/chkpoint/

Full () {

SSH [email protected] "mkdir ${remotedir}/${name}_${time}_full/"

Innobackupex--defaults-file=${conf_file}--user=${bakuser}--password=${bakpass}--host=${host}--slave-info-- Stream=xbstream--compress--extra-lsndir=${chkpointdir}--parallel=16--throttle=120 ${bakdir}|ssh [email protected] \ "Xbstream-x-C ${remotedir}/${name}_${time}_full/"

SCP/ETC/MY.CNF mysql02:${remotedir}/${name}_${time}_full/

}

INCR () {

SSH [email protected] "mkdir/${remotedir}/${name}_${time}_incr/"

Lsn= ' cat ${chkpointdir}/xtrabackup_checkpoints|grep To_lsn|awk ' {print $NF} '

Innobackupex--defaults-file=${conf_file}--user=${bakuser}--password=${bakpass}--host=${host}--slave-info-- Stream=xbstream--compress--extra-lsndir=${chkpointdir}--parallel=16--throttle=120--incremental-- INCREMENTAL-LSN=${LSN} ${bakdir}|ssh [email protected] \ "Xbstream-x-C ${remotedir}/${name}_${time}_incr/"

SCP/ETC/MY.CNF mysql02:${remotedir}/${name}_${time}_incr/

}

Case "$" in

Full

Full

;;

incr

incr

;;

*)

echo "Usage: $ FULL|INCR"

Esac

Requirements Scenario:

Backup strategy: Sunday fully prepared, Monday to Saturday.

Backup path: Offsite backup, compressed backup.

Prerequisites: The MySQL server and the backup machine mysql02 configured SSH trust.

Database log backup script:

Vim binlog_mysql01.sh

#!/bin/sh

Backup_bin= "/usr/bin/mysqlbinlog"

Local_backup_dir= "/mysqlbackup/logbak/mysql01/"

Backup_log= "/home/mysql/log/binlog_log"

Remote_host= "192.168.1.1"

Remote_port= "3306"

Remote_user= "Backup"

remote_pass= "Backup"

First_binlog= "mysql-bin.000001"

#time to wait before reconnecting after failure

sleep_seconds=10

# #create Local_backup_dir If necessary

Mkdir-p ${local_backup_dir}

CD ${local_backup_dir}

# # Run While loop, wait for a specified time after the connection is disconnected, reconnect

While:

Do

If [' Ls-a ' ${local_backup_dir} "|wc-l '-eq 0];then

Last_file=${first_binlog}

Else

Last_file= ' Ls-l ${local_backup_dir} | Grep-v backuplog |tail-n 1 |awk ' {print $9} '

Fi

${backup_bin}--raw--read-from-remote-server--stop-never--host=${remote_host}--port=${remote_port}--user=${ Remote_user}--password=${remote_pass} ${last_file}

echo "' Date +"%y/%m/%d%h:%m:%s "' Mysqlbinlog Stop, return code: $?" | Tee-a ${backup_log}

echo "${sleep_seconds} seconds to connect again and continue backup" | Tee-a ${backup_log}

Sleep ${sleep_seconds}

Done

Nohup SH binlog_mysql01.sh mode startup script, real-time backup binlog data.

MySQL auto-complete, add-on & log backup using shell