Shulou(Shulou.com)05/31 Report--

This article mainly shows you "mysql how to achieve innobackupex backup script", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "mysql how to achieve innobackupex backup script" this article.

Make an innobackupex script with full incremental backups, requiring a cycle a week, doing full backups on Saturday nights every week, incremental backups based on Saturday full on Sundays, incremental backups based on Sunday incremental backups on Monday, etc. Note that incremental backups can be based on incremental backups, and then each time you do a full backup, compress the backups from the previous cycle, that is, the most recent week, to another directory, and delete the backups for this week in that directory. The last directory where the compressed backup is stored needs to be kept for 1 month.

There are a few things you need to understand before you begin:

1) xtrabackup can only back up the tables of innodb and xtradb engines, but not the tables of myisam engine, but innobackupex is a Perl script that encapsulates xtrabackup and supports backing up innodb and myisam at the same time, but you need to add a global read lock when backing up myisam. In addition, myisam does not support incremental backup, does not support does not mean that there will be an error, although you perform incremental backup, but the relevant tables about myisam are fully backed up.

2) when using innobackupex backup, if the parameter-no-timestamp is not specified, a time-formatted subdirectory will be generated under your main backup directory. If the parameter-no-timestamp is specified, then the last level of the backup directory you specify, for example: innobackupex-- defaults-file=/etc/my.cnf-- no-timestamp-- user=root-- password=123456-- socket=/var/lib/mysql/mysql.sock / backup/mysqldump/back_2017-05-29 Note that the last level, that is, back_2017-05-29, must not exist. No error is reported: innobackupex: Error: Failed to create backup directory / backup/mysqldump/back_2017-05-29: File exists at / usr/bin/innobackupex line 4066

3) Note that xtrabackup generally needs to be installed and used by itself, which is different from oracle's rman. First, download the corresponding installation package according to your own operating system, download the packaged binary .tar file directly, and then unzip it to download the URL as follows:

Https://www.percona.com/downloads/XtraBackup/LATEST/

Don't download the latest unless your operating system is relatively new.

Tar zxvf percona-xtrabackup-2.2.11-Linux-x86_64.tar.gz

Yum deplist perl-DBD-MySQL

Yum-y install perl-Digest*

Yum install cmake gcc gcc-c++ libaio libaio-devel automake autoconf bzr bison libtool ncurses-devel zlib-devel

Specific script:

#! / bin/bash

Function date2days {

Echo "$*" | awk'{

Z=int ((14Mutual 2) / 12); yawning 11400MZ; m=$2+12*z-3

J=int ((153*m+2) / 5) + $3+y*365+int (yUnip 4)-int (yamp100) + int (yamp400)-2472633

Print j

}'

}

Db_user= "root"

Db_passwd= "123456"

Db_defaults_file= "/ etc/my.cnf"

Db_socket= "/ var/lib/mysql/mysql.sock"

Db_backup= "/ backup/mysqldump/"

Db_backup_fulldir= "/ backup/mysqldump/full/"

Db_backup_incrementaldir= "/ backup/mysqldump/incremental/"

Db_backup_gzfull= "/ backup/mysqldump/gzip/"

Db_backup_tarfull= "/ backup/mysqldump/tar.gzdb/"

Rm_num=7

Move_and_tar () {

If [$#! = 1]; then

Echo "incorrect parameters"

Exit 0

Fi

Time_rm= `date-d "$1 days ago" + "back_%d-%m-%Y" `

If [$1-eq 7]; then

If [- d ${db_backup_fulldir} ${time_rm}]; then

Tar-czPvf ${db_backup_tarfull} ${time_rm} _ full.tar.gz ${db_backup_fulldir} ${time_rm}

Rm-rf ${db_backup_fulldir} ${time_rm}

Echo "compressed directory rm $db_backup_fulldir$ {time_rm}" > > / backup/mysqldump/config/tar.log

Fi

Fi

If [$1-gt 0-a $a-lt 7]; then

If [- d $db_backup_incrementaldir$ {time_rm}]; then

Su-root-c "tar-czPvf ${db_backup_tarfull} ${time_rm} _ increment.tar.gz ${db_backup_incrementaldir} ${time_rm}"

Su-root-c "rm-rf ${db_backup_incrementaldir} ${time_rm}"

Echo "compressed directory rm $db_backup_incrementaldir$ {time_rm}" > > / backup/mysqldump/config/tar.log

Fi

Fi

}

Time= "$(date +" back_%d-%m-%Y ")"

Source / backup/mysqldump/config/config

_ Day=$ (date2days `echo ${backup_full:5:10} | awk 'BEGIN {FS= "-"} {print $3)

Day=$ (date2days `date + "% Y% m% d" `)

Echo $_ Day

Echo $Day

Let result=$Day-$_Day

Echo "difference of $result days"

If [- z ${backup_full}] | | [$result-ge 7]; then

Echo 'full backup'

Backup_full=$ {time}

Innobackupex-- defaults-file=$db_defaults_file-- no-timestamp-- user=$ {db_user}-password=$ {db_passwd}-- socket=$db_socket ${db_backup_fulldir} ${backup_full} /

If [$?-eq 0]; then

Echo "${time} backup succeeded!" > > / backup/mysqldump/config/results.log

Else

Echo "${time} backup failed?? > > / backup/mysqldump/config/results.log

Fi

Echo "backup_full=$ {backup_full}" > / backup/mysqldump/config/config

Echo "backup_pre_name=full/$ {backup_full}" > > / backup/mysqldump/config/config

While [${rm_num}-lt 8-a ${rm_num}-gt 0]

Do

Move_and_tar ${rm_num}

Rm_num= `expr ${rm_num}-1`

Done

Echo 'full backup'

Else

Echo 'incremental backup'

Innobackupex-- defaults-file=$db_defaults_file-- socket=$db_socket-- no-timestamp-- user=$ {db_user}-- password=$ {db_passwd}-- incremental ${db_backup_incrementaldir} ${time} /-- incremental-basedir=$ {db_backup} ${backup_pre_name}

If [$?-eq 0]; then

Echo "${time} incremental backup succeeded!" > > / backup/mysqldump/config/results.log

Else

Echo "${time} incremental backup failed? > > / backup/mysqldump/config/results.log

Fi

Echo "backup_full=$ {backup_full}" > / backup/mysqldump/config/config

Echo "backup_pre_name=incremental/$ {time}" > > / backup/mysqldump/config/config

Echo 'incremental backup'

Fi

The above is all the contents of the article "how to implement innobackupex backup script in mysql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.