dbdao吧 关注:1,183贴子:4,068
  • 11回复贴,共1

mysql的热备份innobackupex问题

只看楼主收藏回复

innobackupex 只能恢复到备份那一时刻的数据么?
我测试怎么只能恢复到那一时刻,后面再写的都没有了? 不会自己读log-bin目录里的日志么


IP属地:北京1楼2015-08-24 16:47回复
    请查看Percona网站中,Preparing a Full Backup with innobackupex一文。其中提到关于innobackupex --apply-log的使用。
    ---------
    After creating a backup, the data is not ready to be restored. There might be uncommitted transactions to be undone or transactions in the logs to be replayed. Doing those pending operations will make the data files consistent and it is the purpose of the prepare stage. Once this has been done, the data is ready to be used.
    To prepare a backup with innobackupex you have to use the --apply-log and the path to the backup directory as an argument:
    ----------


    IP属地:上海2楼2015-08-28 13:46
    收起回复
      2026-04-23 08:11:08
      广告
      不感兴趣
      开通SVIP免广告
      以下是我的测试:
      备份
      [mysql@gfs1 log]$ innobackupex --user=root --password=123456 /home/mysql/innbackupdir
      InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
      and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.
      This software is published under
      the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
      150824 07:46:17 innobackupex: Executing a version check against the server...
      150824 07:46:17 innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root' (using password: YES).
      150824 07:46:17 innobackupex: Connected to MySQL server
      150824 07:46:17 innobackupex: Done.
      150824 07:46:17 innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root' (using password: YES).
      150824 07:46:17 innobackupex: Connected to MySQL server
      150824 07:46:17 innobackupex: Starting the backup operation
      IMPORTANT: Please check that the backup run completes successfully.
      At the end of a successful backup run innobackupex
      prints "completed OK!".
      innobackupex: Using server version 5.6.25-log
      innobackupex: Created backup directory /home/mysql/innbackupdir/2015-08-24_07-46-17
      150824 07:46:17 innobackupex: Starting ibbackup with command: xtrabackup --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/home/mysql/innbackupdir/2015-08-24_07-46-17 --tmpdir=/tmp --extra-lsndir='/tmp'
      innobackupex: Waiting for ibbackup (pid=13613) to suspend
      innobackupex: Suspend file '/home/mysql/innbackupdir/2015-08-24_07-46-17/xtrabackup_suspended_2'
      xtrabackup version 2.2.11 based on MySQL server 5.6.24 Linux (x86_64) (revision id: )
      xtrabackup: uses posix_fadvise().
      xtrabackup: cd to /usr/local/mysql/innodbdb
      xtrabackup: open files limit requested 65536, set to 1024
      xtrabackup: using the following InnoDB configuration:
      xtrabackup: innodb_data_home_dir = /usr/local/mysql/innodbdb
      xtrabackup: innodb_data_file_path = ibdata1:2G;ibdata2:16M:autoextend
      xtrabackup: innodb_log_group_home_dir = /usr/local/mysql/innodbdb
      xtrabackup: innodb_log_files_in_group = 2
      xtrabackup: innodb_log_file_size = 1073741824
      xtrabackup: using O_DIRECT
      >> log scanned up to (1631591)
      xtrabackup: Generating a list of tablespaces
      [01] Copying /usr/local/mysql/innodbdb/ibdata1 to /home/mysql/innbackupdir/2015-08-24_07-46-17/ibdata1
      >> log scanned up to (1631591)
      >> log scanned up to (1631591)
      >> log scanned up to (1631591)
      >> log scanned up to (1631591)
      >> log scanned up to (1631591)
      >> log scanned up to (1631591)
      >> log scanned up to (1631591)
      >> log scanned up to (1631591)
      >> log scanned up to (1631591)
      >> log scanned up to (1631591)
      >> log scanned up to (1631591)
      >> log scanned up to (1631591)
      >> log scanned up to (1631591)
      >> log scanned up to (1631591)
      >> log scanned up to (1631591)
      >> log scanned up to (1631591)
      >> log scanned up to (1631591)
      >> log scanned up to (1631591)
      >> log scanned up to (1631591)
      >> log scanned up to (1631591)
      [01] ...done
      [01] Copying /usr/local/mysql/innodbdb/ibdata2 to /home/mysql/innbackupdir/2015-08-24_07-46-17/ibdata2
      [01] ...done
      [01] Copying ./test/aaa.ibd to /home/mysql/innbackupdir/2015-08-24_07-46-17/test/aaa.ibd
      [01] ...done
      [01] Copying ./mysql/slave_master_info.ibd to /home/mysql/innbackupdir/2015-08-24_07-46-17/mysql/slave_master_info.ibd
      [01] ...done
      [01] Copying ./mysql/innodb_table_stats.ibd to /home/mysql/innbackupdir/2015-08-24_07-46-17/mysql/innodb_table_stats.ibd
      [01] ...done
      [01] Copying ./mysql/innodb_index_stats.ibd to /home/mysql/innbackupdir/2015-08-24_07-46-17/mysql/innodb_index_stats.ibd
      [01] ...done
      [01] Copying ./mysql/slave_worker_info.ibd to /home/mysql/innbackupdir/2015-08-24_07-46-17/mysql/slave_worker_info.ibd
      [01] ...done
      [01] Copying ./mysql/slave_relay_log_info.ibd to /home/mysql/innbackupdir/2015-08-24_07-46-17/mysql/slave_relay_log_info.ibd
      [01] ...done
      >> log scanned up to (1631591)
      xtrabackup: Creating suspend file '/home/mysql/innbackupdir/2015-08-24_07-46-17/xtrabackup_suspended_2' with pid '13613'
      150824 07:46:39 innobackupex: Continuing after ibbackup has suspended
      150824 07:46:39 innobackupex: Executing FLUSH TABLES...
      150824 07:46:39 innobackupex: Executing FLUSH TABLES WITH READ LOCK...
      150824 07:46:39 innobackupex: All tables locked and flushed to disk
      150824 07:46:39 innobackupex: Starting to backup non-InnoDB tables and files
      innobackupex: in subdirectories of '/usr/local/mysql/innodbdb/'
      innobackupex: Backing up file '/usr/local/mysql/innodbdb//test/aaa.frm'
      innobackupex: Backing up files '/usr/local/mysql/innodbdb//performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (53 files)
      innobackupex: Backing up files '/usr/local/mysql/innodbdb//mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (74 files)
      >> log scanned up to (1631591)
      150824 07:46:40 innobackupex: Finished backing up non-InnoDB tables and files
      150824 07:46:40 innobackupex: Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
      150824 07:46:40 innobackupex: Waiting for log copying to finish
      xtrabackup: The latest check point (for incremental): '1631591'
      xtrabackup: Stopping log copying thread.
      .>> log scanned up to (1631591)
      xtrabackup: Creating suspend file '/home/mysql/innbackupdir/2015-08-24_07-46-17/xtrabackup_log_copied' with pid '13613'
      xtrabackup: Transaction log of lsn (1631591) to (1631591) was copied.
      150824 07:46:41 innobackupex: All tables unlocked
      innobackupex: Backup created in directory '/home/mysql/innbackupdir/2015-08-24_07-46-17'
      innobackupex: MySQL binlog position: filename 'mysql-bin.000029', position 624
      150824 07:46:41 innobackupex: Connection to database server closed
      150824 07:46:41 innobackupex: completed OK!
      +------------------+----------+--------------+------------------+-------------------+
      | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
      +------------------+----------+--------------+------------------+-------------------+
      | mysql-bin.000031 | 120 | | | |
      +------------------+----------+--------------+------------------+-------------------+
      +------------------+----------+--------------+------------------+-------------------+
      | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
      +------------------+----------+--------------+------------------+-------------------+
      | mysql-bin.000031 | 573 | | | |
      +------------------+----------+--------------+------------------+-------------------+
      然后
      insert into aaa values(2);
      commit;


      IP属地:北京4楼2015-09-09 15:25
      回复
        这样太麻烦,mysql就不能在恢复时候直接读取在线日志么?发现他不光在线redolog不读 连binlog都不读?


        IP属地:北京6楼2015-09-09 15:30
        回复
          [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 549, file name mysql-bin.000029
          也就是说,当时binlog info文件中应该没有之后的日志文件记录,当然不会去读了。


          IP属地:上海7楼2015-09-10 11:31
          收起回复