• 1
  • 2
  • 3
  • 4
  • 5
mssql数据库问题 首 页  »  帮助中心  »  数据库  »  mssql数据库问题
innobackupex备份恢复实战
发布日期:2016-4-21 16:4:24

  innobackupex备份恢复实战

  #innobackupex --user=root --password=root --host=172.17.210.112 --parallel=4 --throttle=400 --stream=tar /home 2>/home/bakup.log 1>/home/allbakup.tar

  [root@ykt ~]# innobackupex --user=root --defaults-file=/etc/my.cnf --host=192.168.0.200 --parallel=2 --throttle=400 --databases=yktgl /home

  InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy

  and Percona Inc 2009-2011. All Rights Reserved.

  This software is published under

  the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

  150113 14:15:30 innobackupex: Starting mysql with options: --defaults-file='/etc/my.cnf' --user='root' --host='192.168.0.200' --unbuffered --

  150113 14:15:30 innobackupex: Connected to database with mysql child process (pid=3410)

  150113 14:15:36 innobackupex: Connection to database server closed

  IMPORTANT: Please check that the backup run completes successfully.

  At the end of a successful backup run innobackupex

  prints "completed OK!".

  innobackupex: Using mysql Ver 14.14 Distrib 5.5.23, for Linux (i686) using readline 5.1

  innobackupex: Using mysql server version Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

  innobackupex: Created backup directory /home/2015-01-13_14-15-36

  150113 14:15:36 innobackupex: Starting mysql with options: --defaults-file='/etc/my.cnf' --user='root' --host='192.168.0.200' --unbuffered --

  150113 14:15:36 innobackupex: Connected to database with mysql child process (pid=3438)

  150113 14:15:41 innobackupex: Connection to database server closed

  150113 14:15:41 innobackupex: Starting ibbackup with command: xtrabackup_55 --defaults-file="/etc/my.cnf" --backup --suspend-at-end --target-dir=/home/2015-01-13_14-15-36 --throttle=400 --parallel=2

  innobackupex: Waiting for ibbackup (pid=3449) to suspend

  innobackupex: Suspend file '/home/2015-01-13_14-15-36/xtrabackup_suspended'

  xtrabackup_55 Ver 1.6 Rev undefined for 5.5.9 Linux (i686)

  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 = 5242880

  150113 14:15:41 InnoDB: Using Linux native AIO

  >> log scanned up to (1077419713)

  xtrabackup: Starting 2 threads for parallel data files transfer

  [01] Copying ./ibdata1

  to /home/2015-01-13_14-15-36/ibdata1

  [01] ...done

  >> log scanned up to (1077419713)

  150113 14:15:47 innobackupex: Continuing after ibbackup has suspended

  150113 14:15:47 innobackupex: Starting mysql with options: --defaults-file='/etc/my.cnf' --user='root' --host='192.168.0.200' --unbuffered --

  150113 14:15:47 innobackupex: Connected to database with mysql child process (pid=3466)

  150113 14:15:51 innobackupex: Starting to lock all tables...

  >> log scanned up to (1077419713)

  >> log scanned up to (1077419713)

  150113 14:16:01 innobackupex: All tables locked and flushed to disk

  150113 14:16:01 innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI,

  innobackupex: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files in

  innobackupex: subdirectories of '/var/lib/mysql'

  innobackupex: Backing up files '/var/lib/mysql/yktgl/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (231 files)

  >> log scanned up to (1077419713)

  150113 14:16:01 innobackupex: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSV, .CSM and .opt files

  innobackupex: Resuming ibbackup

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

  >> log scanned up to (1077419713)

  xtrabackup: Stopping log copying thread.

  xtrabackup: Transaction log of lsn (1077419399) to (1077419713) was copied.

  150113 14:16:04 innobackupex: All tables unlocked

  150113 14:16:04 innobackupex: Connection to database server closed

  innobackupex: Backup created in directory '/home/2015-01-13_14-15-36'

  innobackupex: MySQL binlog position: filename 'mysql-bin.000181', position 4991

  150113 14:16:04 innobackupex: completed OK!

  xtrabackup_55 Ver 1.6 Rev undefined for 5.5.9 Linux (i686)

  xtrabackup: cd to /home/2015-01-13_13-18-51

  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

  150113 13:59:54 InnoDB: Using Linux native AIO

  xtrabackup: Starting InnoDB instance for recovery.

  xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)

  150113 13:59:54 InnoDB: The InnoDB memory heap is disabled

  150113 13:59:54 InnoDB: Mutexes and rw_locks use InnoDB's own implementation

  150113 13:59:54 InnoDB: Compressed tables use zlib 1.2.3

  150113 13:59:54 InnoDB: Using Linux native AIO

  150113 13:59:54 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead

  150113 13:59:54 InnoDB: Initializing buffer pool, size = 100.0M

  150113 13:59:54 InnoDB: Completed initialization of buffer pool

  150113 13:59:54 InnoDB: highest supported file format is Barracuda.

  150113 13:59:54 InnoDB: Waiting for the background threads to start

  150113 13:59:55 Percona XtraDB (http://www.percona.com) 1.1.5-20.0 started; log sequence number 1077412369

  [notice (again)]

  If you use binary log and don't use any hack of group commit,

  the binary log position seems to be:

  InnoDB: Last MySQL binlog file position 0 43380, file name ./mysql-bin.000180

  xtrabackup: starting shutdown with innodb_fast_shutdown = 1

  150113 13:59:55 InnoDB: Starting shutdown...

  150113 14:00:00 InnoDB: Shutdown completed; log sequence number 1077413135

  150113 14:00:00 innobackupex: completed OK!

  [root@ykt ~]#service mysql stop #停止mysql数据库服务

  [

  root@ykt ~]#mv /var/lib/mysql/yktgl /var/lib/mysql/yktglbak #备份原来数据

  [root@ykt ~]# innobackupex --user=root --databases=yktgl --apply-log /home/2015-01-13_13-18-51/ #恢复备份日志

  [root@ykt ~]# innobackupex --user=root --databases=yktgl --copy-back /home/2015-01-13_13-18-51/ #开始拷贝日志文件

  InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy

  and Percona Inc 2009-2011. All Rights Reserved.

  This software is published under

  the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

  IMPORTANT: Please check that the copy-back run completes successfully.

  At the end of a successful copy-back run innobackupex

  prints "completed OK!".

  innobackupex: Starting to copy MyISAM tables, indexes,

  innobackupex: .MRG, .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV, .opt, and .frm files

  innobackupex: in '/home/2015-01-13_13-18-51'

  innobackupex: back to original data directory '/var/lib/mysql'

  innobackupex: Copying file '/home/2015-01-13_13-18-51/xtrabackup_checkpoints'

  innobackupex: Copying file '/home/2015-01-13_13-18-51/xtrabackup_binlog_info'

  innobackupex: Copying directory '/home/2015-01-13_13-18-51/yktgl'

  innobackupex: Copying file '/home/2015-01-13_13-18-51/xtrabackup_binlog_pos_innodb'

  innobackupex: Copying file '/home/2015-01-13_13-18-51/xtrabackup_binary'

  innobackupex: Starting to copy InnoDB tables and indexes

  innobackupex: in '/home/2015-01-13_13-18-51'

  innobackupex: back to original InnoDB data directory '/var/lib/mysql'

  innobackupex: Copying file '/home/2015-01-13_13-18-51/ibdata1'

  innobackupex: Starting to copy InnoDB log files

  innobackupex: in '/home/2015-01-13_13-18-51'

  innobackupex: back to original InnoDB log directory '/var/lib/mysql'

  innobackupex: Copying file '/home/2015-01-13_13-18-51/ib_logfile0'

  innobackupex: Copying file '/home/2015-01-13_13-18-51/ib_logfile1'

  innobackupex: Finished copying back files.

  [root@ykt mysql]# pwd

  /var/lib/mysql

  [root@ykt mysql]# chown -R mysql:mysql yktgl

  [root@ykt mysql]# pwd

  /var/lib/mysql

  [root@ykt mysql]# chown mysql:mysql mysql

  mysql目录中没有权限创建ibdata1文件

  [root@ykt ~]# service mysql start #启动数据库服务