RE: 設定xtrabackup for MariaDB/ MySQL

Test OK

[root@RepDB2 tmp]# ./backup_mysql.sh Sunday oracle

mv: target `/source/mysql-backups/last_week' is not a directory

xtrabackup version 2.0.8 for Percona Server 5.1.59 unknown-linux-gnu (x86_64) (revision id: 587)

xtrabackup: uses posix_fadvise().

xtrabackup: cd to /var/lib/mysql

xtrabackup: Target instance is assumed as followings.

xtrabackup:   innodb_data_home_dir = ./

xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend

xtrabackup:   innodb_log_group_home_dir = ./

xtrabackup:   innodb_log_files_in_group = 2

xtrabackup:   innodb_log_file_size = 1073741824

>> log scanned up to (136644115)

160325 17:17:36  InnoDB: Warning: allocated tablespace 22, old maximum was 9

[01] Copying ./ibdata1 to /source/mysql-backups/this_week/Sunday/ibdata1

>> log scanned up to (136650500)

>> log scanned up to (136652382)

>> log scanned up to (136652382)

>> log scanned up to (136652382)

>> log scanned up to (136652382)

[01]        ...done

[01] Copying ./test/t2#P#d2.ibd to /source/mysql-backups/this_week/Sunday/test/t2#P#d2.ibd

[01]        ...done

[01] Copying ./test/authors.ibd to /source/mysql-backups/this_week/Sunday/test/authors.ibd

[01]        ...done

[01] Copying ./test/tt.ibd to /source/mysql-backups/this_week/Sunday/test/tt.ibd

[01]        ...done

[01] Copying ./test/t2#P#d7.ibd to /source/mysql-backups/this_week/Sunday/test/t2#P#d7.ibd

[01]        ...done

[01] Copying ./test/t2#P#d0.ibd to /source/mysql-backups/this_week/Sunday/test/t2#P#d0.ibd

[01]        ...done

[01] Copying ./test/t2#P#d4.ibd to /source/mysql-backups/this_week/Sunday/test/t2#P#d4.ibd

[01]        ...done

[01] Copying ./test/t2#P#d6.ibd to /source/mysql-backups/this_week/Sunday/test/t2#P#d6.ibd

>> log scanned up to (136652382)

[01]        ...done

[01] Copying ./test/task_table2.ibd to /source/mysql-backups/this_week/Sunday/test/task_table2.ibd

[01]        ...done

[01] Copying ./test/t2#P#d1.ibd to /source/mysql-backups/this_week/Sunday/test/t2#P#d1.ibd

[01]        ...done

[01] Copying ./test/t2#P#d5.ibd to /source/mysql-backups/this_week/Sunday/test/t2#P#d5.ibd

[01]        ...done

[01] Copying ./test/t2#P#d3.ibd to /source/mysql-backups/this_week/Sunday/test/t2#P#d3.ibd

[01]        ...done

[01] Copying ./mysql/gtid_slave_pos.ibd to /source/mysql-backups/this_week/Sunday/mysql/gtid_slave_pos.ibd

[01]        ...done

[01] Copying ./mysql/innodb_table_stats.ibd to /source/mysql-backups/this_week/Sunday/mysql/innodb_table_stats.ibd

[01]        ...done

[01] Copying ./mysql/innodb_index_stats.ibd to /source/mysql-backups/this_week/Sunday/mysql/innodb_index_stats.ibd

[01]        ...done

[01] Copying ./mydb/authors.ibd to /source/mysql-backups/this_week/Sunday/mydb/authors.ibd

[01]        ...done

[01] Copying ./mydb/t3.ibd to /source/mysql-backups/this_week/Sunday/mydb/t3.ibd

[01]        ...done

[01] Copying ./mydb/t2.ibd to /source/mysql-backups/this_week/Sunday/mydb/t2.ibd

[01]        ...done

[01] Copying ./mydb/task_table1.ibd to /source/mysql-backups/this_week/Sunday/mydb/task_table1.ibd

[01]        ...done

[01] Copying ./mydb/t1.ibd to /source/mysql-backups/this_week/Sunday/mydb/t1.ibd

[01]        ...done

xtrabackup: The latest check point (for incremental): '136652382'

xtrabackup: Stopping log copying thread.

.>> log scanned up to (136652382)

xtrabackup: Transaction log of lsn (136644115) to (136652382) was copied.

[root@RepDB2 tmp]# ls -l    /source/mysql-backups/this_week/Sunday

total 77852

-rw-r----- 1 root root 79691776 Mar 25 17:17 ibdata1

drwx------ 2 root root     4096 Mar 25 17:17 mydb

drwx------ 2 root root     4096 Mar 25 17:17 mysql

drwx------ 2 root root     4096 Mar 25 17:17 test

-rw-r----- 1 root root       81 Mar 25 17:17 xtrabackup_checkpoints

-rw-r----- 1 root root    10752 Mar 25 17:17 xtrabackup_logfile

[root@RepDB2 tmp]#

Subject: 設定xtrabackup for MariaDB/ MySQL

#設定xtrabackup policy

#ref: http://petrovs.info/2016/01/07/Example-of-MySQL-incremental-backup-with-XtraBackup/

# percona-xtrabackup-2.2.12-1.el7.x86_64.rpm 是從 https://mariadb.com/my_portal/download/ 下載的

#Prerequisite for perxona-xtrabackup

[root@db1 mysql]#

yum install perl-DBD-MySQL.x86_64

yum install perl-Digest-MD5.x86_64

[root@db1 mysql]#

rpm -ivh /source/percona-xtrabackup-2.2.12-1.el7.x86_64.rpm

warning: /source/percona-xtrabackup-2.2.12-1.el7.x86_64.rpm: Header V3 RSA/SHA1 Signature, key ID e3c94f49: NOKEY

Preparing...                          ################################# [100%]

Updating / installing...

   1:percona-xtrabackup-2.2.12-1.el7  ################################# [100%]

[root@db1 mysql]#

[root@db1 source]# mkdir -p /source/mysql-backups/this_week/Monday

[root@db1 source]# mkdir -p /source/mysql-backups/this_week/Tuesday

[root@db1 source]# mkdir -p /source/mysql-backups/this_week/Wednesday

[root@db1 source]# mkdir -p /source/mysql-backups/this_week/Thursday

[root@db1 source]# mkdir -p /source/mysql-backups/this_week/Friday

[root@db1 source]# mkdir -p /source/mysql-backups/this_week/Saturday

[root@db1 source]# mkdir -p /source/mysql-backups/last_week

[root@db1 source]#

[root@db1 source]# cat backup_mysql.sh

#!/bin/bash

##

#####################################################################################

# "THE BEER-WARE LICENSE" (Revision 42):

# <blagovest@petrovs.info> wrote this file.  As long as you retain this notice you

# can do whatever you want with this stuff. If we meet some day, and you think

# this stuff is worth it, you can buy me a beer in return.   Blagovest Petrov

#####################################################################################

##

DAY=$1

MYSQL_PASS=$2

MYSQL_USER="root"

BACKUPS_DIR=/source/mysql-backups

THIS_WEEK=$BACKUPS_DIR/this_week

LAST_WEEK=$BACKUPS_DIR/last_week

function do_full_backup() {

  if [ -d $LAST_WEEK ]; then

    rm -rf $LAST_WEEK

    mkdir $LAST_WEEK

  fi

  if [ -d $THIS_WEEK ]; then

    mv $THIS_WEEK/* $LAST_WEEK

    mkdir -p $THIS_WEEK/Sunday

  fi

  xtrabackup --backup --target-dir=$THIS_WEEK/Sunday \

  --datadir=/var/lib/mysql \

  --user=$MYSQL_USER --password=$MYSQL_PASS

}

function do_incremental_backup() {

  if [ -d $THIS_WEEK/$DAY ]; then

    rm -rf $THIS_WEEK/$DAY

    mkdir $THIS_WEEK/$DAY

  fi

  xtrabackup --backup --target-dir=$THIS_WEEK/$DAY \

  --incremental-basedir=$THIS_WEEK/Sunday \

  --datadir=/var/lib/mysql \

  --user=$MYSQL_USER --password=$MYSQL_PASS

}

case $1 in

  Sunday)

    do_full_backup

    ;;

  *)

    do_incremental_backup

esac

手動測試

#記得要reset logfile size 到 innodb_log_file_size = 50331648 才能備份

#ref https://www.percona.com/forums/questions-discussions/percona-xtrabackup/17446-got-ib_logfile0-is-of-different-size-when-doing-stream-backup

#MySQL Server depending on version has different default value for innodb_log_file_size: 5242880 (MySQL < 5.6.8), 50331648 (MySQL >= 5.6.8) http://dev.mysql.com/doc/refman/5.6/..._log_file_size

[root@db1 source]# ./backup_mysql.sh Sunday root123

xtrabackup version 2.2.12 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 8726828)

xtrabackup: uses posix_fadvise().

xtrabackup: cd to /var/lib/mysql

xtrabackup: open files limit requested 0, set to 1024

xtrabackup: using the following InnoDB configuration:

xtrabackup:   innodb_data_home_dir = ./

xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend

xtrabackup:   innodb_log_group_home_dir = ./

xtrabackup:   innodb_log_files_in_group = 2

xtrabackup:   innodb_log_file_size = 50331648

>> log scanned up to (2060136)

xtrabackup: Generating a list of tablespaces

[01] Copying ./ibdata1 to /source/mysql-backups/this_week/Sunday/ibdata1

[01]        ...done

[01] Copying ./mysql/innodb_table_stats.ibd to /source/mysql-backups/this_week/Sunday/mysql/innodb_table_stats.ibd

[01]        ...done

[01] Copying ./mysql/innodb_index_stats.ibd to /source/mysql-backups/this_week/Sunday/mysql/innodb_index_stats.ibd

[01]        ...done

[01] Copying ./mysql/gtid_slave_pos.ibd to /source/mysql-backups/this_week/Sunday/mysql/gtid_slave_pos.ibd

[01]        ...done

[01] Copying ./test/tab1.ibd to /source/mysql-backups/this_week/Sunday/test/tab1.ibd

[01]        ...done

>> log scanned up to (2060136)

xtrabackup: The latest check point (for incremental): '2060136'

xtrabackup: Stopping log copying thread.

.>> log scanned up to (2060136)

#Crontab

0 2 * * 0 /usr/local/bin/backup_mysql.sh Sunday root123 >/dev/null 2>&1

0 2 * * 1 /usr/local/bin/backup_mysql.sh Monday 12345 >/dev/null 2>&1

0 2 * * 2 /usr/local/bin/backup_mysql.sh Tuesday 12345 >/dev/null 2>&1

0 2 * * 3 /usr/local/bin/backup_mysql.sh Wednesday 12345 >/dev/null 2>&1

0 2 * * 4 /usr/local/bin/backup_mysql.sh Thursday 12345 >/dev/null 2>&1

0 2 * * 5 /usr/local/bin/backup_mysql.sh Friday 12345 >/dev/null 2>&1

0 2 * * 6 /usr/local/bin/backup_mysql.sh Saturday 12345 >/dev/null 2>&1