分类: Mysql/postgreSQL
2013-07-10 14:21:06
- 4.2G ibdata1
- 5.1M ib_logfile0
- 5.1M ib_logfile1
- 1.8M monitor
- start time is 20130313-13:34:35
- start to stop mysql server
- start to restore database
- InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
- and Percona Ireland Ltd 2009-2012. All Rights Reserved.
- This software is published under
- the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
- IMPORTANT: Please check that the apply-log run completes successfully.
- At the end of a successful apply-log run innobackupex
- prints "completed OK!".
- 130313 13:34:35 innobackupex: Starting ibbackup with command: xtrabackup_51 --defaults-file="/usr/local/monitor/data/2013-03-13_13-28-28/backup-my.cnf" --defaults-group="mysqld" --prepare --target-dir=/usr/local/monitor/data/2013-03-13_13-28-28
- xtrabackup_51 version 2.0.5 for MySQL server 5.1.59 unknown-linux-gnu (x86_64) (revision id: undefined)
- xtrabackup: cd to /usr/local/monitor/data/2013-03-13_13-28-28
- xtrabackup: This target seems to be not prepared yet.
- xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(2 31133758)
- xtrabackup: Temporary instance for recovery is set 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 = 1
- xtrabackup: innodb_log_file_size = 2097152
- xtrabackup: Temporary instance for recovery is set 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 = 1
- xtrabackup: innodb_log_file_size = 2097152
- xtrabackup: Starting InnoDB instance for recovery.
- xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
- InnoDB: The InnoDB memory heap is disabled
- 130313 13:34:35 InnoDB: Initializing buffer pool, size = 100.0M
- 130313 13:34:35 InnoDB: Completed initialization of buffer pool
- InnoDB: Log scan progressed past the checkpoint lsn 2 31133758
- 130313 13:34:35 InnoDB: Database was not shut down normally!
- InnoDB: Starting crash recovery.
- InnoDB: Reading tablespace information from the .ibd files...
- InnoDB: Doing recovery: scanned up to log sequence number 2 31272048 (7 %)
- 130313 13:34:36 InnoDB: Starting an apply batch of log records to the database...
- InnoDB: Progress in percents: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 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 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
- InnoDB: Apply batch completed
- 130313 13:34:37 InnoDB: Started; log sequence number 2 31272048
- [notice (again)]
- If you use binary log and don't use any hack of group commit,
- the binary log position seems to be:
- xtrabackup: starting shutdown with innodb_fast_shutdown = 1
- 130313 13:34:37 InnoDB: Starting shutdown...
- 130313 13:34:42 InnoDB: Shutdown completed; log sequence number 2 31272048
- 130313 13:34:42 innobackupex: Restarting xtrabackup with command: xtrabackup_51 --defaults-file="/usr/local/monitor/data/2013-03-13_13-28-28/backup-my.cnf" --defaults-group="mysqld" --prepare --target-dir=/usr/local/monitor/data/2013-03-13_13-28-28
- for creating ib_logfile*
- xtrabackup_51 version 2.0.5 for MySQL server 5.1.59 unknown-linux-gnu (x86_64) (revision id: undefined)
- xtrabackup: cd to /usr/local/monitor/data/2013-03-13_13-28-28
- xtrabackup: This target seems to be already prepared.
- xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.
- xtrabackup: Temporary instance for recovery is set 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 = 5242880
- xtrabackup: Temporary instance for recovery is set 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 = 5242880
- xtrabackup: Starting InnoDB instance for recovery.
- xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
- InnoDB: The InnoDB memory heap is disabled
- 130313 13:34:42 InnoDB: Initializing buffer pool, size = 100.0M
- 130313 13:34:42 InnoDB: Completed initialization of buffer pool
- 130313 13:34:42 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
- InnoDB: Setting log file ./ib_logfile0 size to 5 MB
- InnoDB: Database physically writes the file full: wait...
- 130313 13:34:42 InnoDB: Log file ./ib_logfile1 did not exist: new to be created
- InnoDB: Setting log file ./ib_logfile1 size to 5 MB
- InnoDB: Database physically writes the file full: wait...
- InnoDB: The log sequence number in ibdata files does not match
- InnoDB: the log sequence number in the ib_logfiles!
- 130313 13:34:42 InnoDB: Database was not shut down normally!
- InnoDB: Starting crash recovery.
- InnoDB: Reading tablespace information from the .ibd files...
- 130313 13:34:42 InnoDB: Started; log sequence number 2 31272460
- [notice (again)]
- If you use binary log and don't use any hack of group commit,
- the binary log position seems to be:
- xtrabackup: starting shutdown with innodb_fast_shutdown = 1
- 130313 13:34:42 InnoDB: Starting shutdown...
- 130313 13:34:48 InnoDB: Shutdown completed; log sequence number 2 31272460
- 130313 13:34:48 innobackupex: completed OK!
- operation is success!
- operation is success!
- start to start mysql server
- Starting MySQL: [ OK ]
- operation is success!
- finish time is 20130313-13:34:49
- [root@beiyong shell]# cat xtrabackup_full.sh
- #!/bin/bash
- local_ip="$(/sbin/ifconfig eth0|grep 'inet addr'|awk -F : '{print $2}'|cut -d ' ' -f1)"
- email='denglei@ctfo.com'
- user='root'
- passwd='123456'
- database='monitor'
- my_config='/etc/my.cnf'
- log=$database-$(date +%Y%m%d%H%M).log
- str=$database-$(date +%Y%m%d%H%M).tar.gz
- backup_dir='/usr/local/monitor/data'
- echo "Start to backup at $(date +%Y%m%d%H%M)"
- if [ ! -d "$backup_dir" ];then
- mkdir -p $backup_dir
- fi
- #innobackupex --user=$user --password=$passwd --defaults-file=$my_config --database=$database --stream=tar $backup_dir 2>$backup_dir/$log | gzip 1>$backup_dir/$str
- innobackupex --user=$user --password=$passwd --defaults-file=$my_config --database=$database $backup_dir
- if [ $? -eq 0 ];then
- echo "Backup is finish! at $(date +%Y%m%d%H%M)"
- echo "Server_name:$(hostname) Server_ip:$local_ip $(date +"%y-%m-%d %H:%M:%S") mysql full backup Success!"|/bin/mail -s "Database: [$database} Daily Full Backup Success!" $email
- exit 0
- else
- echo "Backup is Fail! at $(date +%Y%m%d%H%M)"
- echo "Server_name:$(hostname) Server_ip:$local_ip $(date +"%y-%m-%d %H:%M:%S") mysql full backup Fail!"|/bin/mail -s "Database: [$database} Daily Full Backup Fail!" $email
- exit 1
- fi
- echo "Backup Process Done"
- [root@beiyong shell]# cat xtrabackup_incremental.sh
- #!/bin/bash
- local_ip="$(/sbin/ifconfig eth0|grep 'inet addr'|awk -F : '{print $2}'|cut -d ' ' -f1)"
- email='denglei@ctfo.com'
- user='root'
- passwd='123456'
- database='monitor'
- my_config='/etc/my.cnf'
- log=$database-$(date +%Y%m%d%H%M).log
- str=$database-$(date +%Y%m%d%H%M).tar.gz
- backup_dir='/usr/local/monitor/data/'
- last_day=$(date -d "1 days ago" +%Y-%m-%d)
- today=$(date +%Y%m%d)
- filename=$(find $backup_dir -name "$last_day*" -print|awk -F / '{print $NF}')
- echo "Start to backup at $(date +%Y%m%d%H%M)"
- if [ ! -d "$backup_dir" ];then
- mkdir -p $backup_dir
- fi
- #innobackupex --user=$user --password=$passwd --defaults-file=$my_config --database=$database --stream=tar $backup_dir 2>$backup_dir/$log | gzip 1>$backup_dir/$str
- innobackupex --user=$user --password=$passwd --defaults-file=$my_config --database=$database --incremental --incremental-basedir=$backup_dir/$filename $backup_dir
- if [ $? -eq 0 ];then
- echo "Backup is finish! at $(date +%Y%m%d%H%M)"
- echo "Server_name:$(hostname) Server_ip:$local_ip $(date +"%y-%m-%d %H:%M:%S") mysql incremental backup Success!"|/bin/mail -s "Database: [$database} Daily incremental Backup Success!" $email
- exit 0
- else
- echo "Backup is Fail! at $(date +%Y%m%d%H%M)"
- echo "Server_name:$(hostname) Server_ip:$local_ip $(date +"%y-%m-%d %H:%M:%S") mysql incremental backup Fail!"|/bin/mail -s "Database: [$database} Daily incremental Backup Fail!" $email
- exit 1
- fi
- echo "Backup Process Done"
- 10 00 * * 0 /usr/local/monitor/shell/xtrabackup_full.sh>> /usr/local/monitor/logs/xtrabackup_full.log 2>&1
- 10 00 * * 1-6 /usr/local/monitor/shell/xtrabackup_incremental.sh>>/usr/local/monitor/logs/xtrabackup_incremental.log 2>&1