星期五, 3月 25, 2016

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
# 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 才能備份
#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



沒有留言:

LinkWithin-相關文件

Related Posts Plugin for WordPress, Blogger...