热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

使用xtrabackup进行MySQL数据库物理备份_MySQL

0.xtrabackup的功能能实现的功能:非阻塞备份innodb等事务引擎数据库、备份myisam表会阻塞(需要锁)、支持全备、增量备份、压缩备份、快速增量备份(xtradb,原理类似于oracle:tracking上次备份之后发生
0. xtrabackup的功能

能实现的功能:

非阻塞备份innodb等事务引擎数据库、

备份myisam表会阻塞(需要锁)、

支持全备、增量备份、压缩备份、

快速增量备份(xtradb,原理类似于oracle:tracking 上次备份之后发生修改的page.)、

percona支持归档redo log的备份、

percona5.6+支持轻量级的backup-lock替代原来重量级的FTWRL,此时即使备份非事务引擎表也不会阻塞innodb的DML语句了、

支持加密备份、流备份(备份到远程机器)、并行本地备份、并行压缩、并行加密、并行应用备份期间产生的redo日志、并行copy-back

支持部分备份,只备份某个库,某个表

支持部分恢复

支持备份单个表分区

支持备份速度限制,指备份产生的IO速度的限制

支持point-in-time恢复

支持compat备份,也即使不备份索引数据,索引在prepare时--rebuild-indexs

支持备份buffer pool

支持单表export, import到其它库

支持 rsync 来缩短备份非事务引擎表的锁定时间

1. 物理备份需要的权限

使用innobackupex/xtrabackup进行备份,必须先配置好权限。需要的权限分为两部分:

1>系统层面的权限: 执行 innobackupex/xtrabackup 命令的Linux用户需要对mysql datadir和保存备份的目录有读写执行的权限,当然需要对这些命令要有执行权限;

2>mysqld层面的权限:innobackupex/xtrabackup --user=bkpuser 该用户bkpuser是指mysql.user表中的用户,不是系统层面的用户;需要一些基本的权限来执行备份过程:

最基本的权限:

create user 'bkpuser'@'localhost' identified by 'xxx';

grant reload,lock tables,replication client on *.* to 'bkpuser'@'localhost';

这些权限仅仅只能完成:全备,增量备份,恢复;

一般如果需要部分备份,export表,import表,还需要:grant create tablespace on *.* to 'bkpuser'@'localhost';

如果还需要对备份的过程中对锁进行一些优化,防止发生阻塞所有DML的情况,则还需要:

grant process,super on *.* to 'bkpuser'@'localhost';

(root@localhost)[(none)]mysql>show grants for 'bkpuser'@'localhost'\G

*************************** 1. row ***************************

Grants for bkpuser@localhost: GRANT RELOAD, PROCESS, SUPER, LOCK TABLES, REPLICATION CLIENT, CREATE TABLESPACE ON *.* TO 'bkpuser'@'localhost' IDENTIFIED BY PASSWORD '*BDC62F68AF8F0B8BFAE27FF782C5D8CE9F4BAFCB'

1 row in set (0.00 sec)

2. innobackupex 命令选项:

[root@localhost ~]# innobackupex --help
Open source backup tool for InnoDB and XtraDB
[... ...]
innobackupex - Non-blocking backup tool for InnoDB, XtraDB and HailDB databases

SYNOPOSIS(使用方法)

innobackupex [--compress] [--compress-threads=NUMBER-OF-THREADS] [--compress-chunk-size=CHUNK-SIZE]
             [--encrypt=ENCRYPTION-ALGORITHM] [--encrypt-threads=NUMBER-OF-THREADS] [--encrypt-chunk-size=CHUNK-SIZE]
             [--encrypt-key=LITERAL-ENCRYPTION-KEY] | [--encryption-key-file=MY.KEY]
             [--include=REGEXP] [--user=NAME]
             [--password=WORD] [--port=PORT] [--socket=SOCKET]
             [--no-timestamp] [--ibbackup=IBBACKUP-BINARY]
             [--slave-info] [--galera-info] [--stream=tar|xbstream]
             [--defaults-file=MY.CNF] [--defaults-group=GROUP-NAME]
             [--databases=LIST] [--no-lock]
             [--tmpdir=DIRECTORY] [--tables-file=FILE]
             [--history=NAME]
             [--incremental] [--incremental-basedir]
             [--incremental-dir] [--incremental-force-scan] [--incremental-lsn]
             [--incremental-history-name=NAME] [--incremental-history-uuid=UUID]
             [--close-files] [--compact]
             BACKUP-ROOT-DIR

innobackupex --apply-log [--use-memory=B]
             [--defaults-file=MY.CNF]
             [--export] [--redo-only] [--ibbackup=IBBACKUP-BINARY]
             BACKUP-DIR

innobackupex --copy-back [--defaults-file=MY.CNF] [--defaults-group=GROUP-NAME] BACKUP-DIR

innobackupex --move-back [--defaults-file=MY.CNF] [--defaults-group=GROUP-NAME] BACKUP-DIR

innobackupex [--decompress] [--decrypt=ENCRYPTION-ALGORITHM]
             [--encrypt-key=LITERAL-ENCRYPTION-KEY] | [--encryption-key-file=MY.KEY]
             [--parallel=NUMBER-OF-FORKS] BACKUP-DIR

DESCRIPTION

The first command line above makes a hot backup of a MySQL database.
By default it creates a backup directory (named by the current date
        and time) in the given backup root directory.  With the --no-timestamp
option it does not create a time-stamped backup directory, but it puts
the backup in the given directory (which must not exist).  This
command makes a complete backup of all MyISAM and InnoDB tables and
indexes in all databases or in all of the databases specified with the
--databases option.  The created backup contains .frm, .MRG, .MYD,
.MYI, .MAD, .MAI, .TRG, .TRN, .ARM, .ARZ, .CSM, CSV, .opt, .par, and
InnoDB data and log files.  The MY.CNF options file defines the
location of the database.  This command connects to the MySQL server
using the mysql client program, and runs xtrabackup as a child
process.

The --apply-log command prepares a backup for starting a MySQL
server on the backup. This command recovers InnoDB data files as specified
in BACKUP-DIR/backup-my.cnf using BACKUP-DIR/xtrabackup_logfile,
and creates new InnoDB log files as specified in BACKUP-DIR/backup-my.cnf.
The BACKUP-DIR should be the path to a backup directory created by
xtrabackup. This command runs xtrabackup as a child process, but it does not
connect to the database server.

The --copy-back command copies data, index, and log files
from the backup directory back to their original locations.
The MY.CNF options file defines the original location of the database.
The BACKUP-DIR is the path to a backup directory created by xtrabackup.

The --move-back command is similar to --copy-back with the only difference that
it moves files to their original locations rather than copies them. As this
option removes backup files, it must be used with caution. It may be useful in
cases when there is not enough free disk space to copy files.

The --decompress --decrypt command will decrypt and/or decompress a backup made
with the --compress and/or --encrypt options. When decrypting, the encryption
algorithm and key used when the backup was taken MUST be provided via the
specified options. --decrypt and --decompress may be used together at the same
time to completely normalize a previously compressed and encrypted backup. The
--parallel option will allow multiple files to be decrypted and/or decompressed
simultaneously. In order to decompress, the qpress utility MUST be installed
and accessable within the path. This process will remove the original
compressed/encrypted files and leave the results in the same location.

On success the exit code innobackupex is 0. A non-zero exit code
indicates an error.

Usage: [innobackupex [--defaults-file=#] --backup | innobackupex [--defaults-file=#] --prepare] [OPTIONS]
  -v, --version       print xtrabackup version information
  -?, --help          This option displays a help screen and exits.
  --apply-log         Prepare a backup in BACKUP-DIR by applying the
                      transaction log file named "xtrabackup_logfile" located
                      in the same directory. Also, create new transaction logs.
                      The InnoDB configuration is read from the file
                      "backup-my.cnf".
  --redo-only         This option should be used when preparing the base full
                      backup and when merging all incrementals except the last
                      one. This forces xtrabackup to skip the "rollback" phase
                      and do a "redo" only. This is necessary if the backup
                      will have incremental changes applied to it later. See
                      the xtrabackup documentation for details.
  --copy-back         Copy all the files in a previously made backup from the
                      backup directory to their original locations.
  --move-back         Move all the files in a previously made backup from the
                      backup directory to the actual datadir location. Use with
                      caution, as it removes backup files.
  --galera-info       This options creates the xtrabackup_galera_info file
                      which contains the local node state at the time of the
                      backup. Option should be used when performing the backup
                      of Percona-XtraDB-Cluster. Has no effect when backup
                      locks are used to create the backup.
  --slave-info        This option is useful when backing up a replication slave
                      server. It prints the binary log position and name of the
                      master server. It also writes this information to the
                      "xtrabackup_slave_info" file as a "CHANGE MASTER"
                      command. A new slave for this master can be set up by
                      starting a slave server on this backup and issuing a
                      "CHANGE MASTER" command with the binary log position
                      saved in the "xtrabackup_slave_info" file.
  --incremental       This option tells xtrabackup to create an incremental
                      backup, rather than a full one. It is passed to the
                      xtrabackup child process. When this option is specified,
                      either --incremental-lsn or --incremental-basedir can
                      also be given. If neither option is given, option
                      --incremental-basedir is passed to xtrabackup by default,
                      set to the first timestamped backup directory in the
                      backup base directory.
  --no-lock           Use this option to disable table lock with "FLUSH TABLES
                      WITH READ LOCK". Use it only if ALL your tables are
                      InnoDB and you DO NOT CARE about the binary log position
                      of the backup. This option shouldn't be used if there are
                      any DDL statements being executed or if any updates are
                      happening on non-InnoDB tables (this includes the system
                      MyISAM tables in the mysql database), otherwise it could
                      lead to an inconsistent backup. If you are considering to
                      use --no-lock because your backups are failing to acquire
                      the lock, this could be because of incoming replication
                      events preventing the lock from succeeding. Please try
                      using --safe-slave-backup to momentarily stop the
                      replication slave thread, this may help the backup to
                      succeed and you then don't need to resort to using this
                      option.
  --safe-slave-backup Stop slave SQL thread and wait to start backup until
                      Slave_open_temp_tables in "SHOW STATUS" is zero. If there
                      are no open temporary tables, the backup will take place,
                      otherwise the SQL thread will be started and stopped
                      until there are no open temporary tables. The backup will
                      fail if Slave_open_temp_tables does not become zero after
                      --safe-slave-backup-timeout seconds. The slave SQL thread
                      will be restarted when the backup finishes.
  --rsync             Uses the rsync utility to optimize local file transfers.
                      When this option is specified, innobackupex uses rsync to
                      copy all non-InnoDB files instead of spawning a separate
                      cp for each file, which can be much faster for servers
                      with a large number of databases or tables.  This option
                      cannot be used together with --stream.
  --force-non-empty-directories
                      This option, when specified, makes --copy-back or
                      --move-back transfer files to non-empty directories. Note
                      that no existing files will be overwritten. If
                      --copy-back or --nove-back has to copy a file from the
                      backup directory which already exists in the destination
                      directory, it will still fail with an error.
  --no-timestamp      This option prevents creation of a time-stamped
                      subdirectory of the BACKUP-ROOT-DIR given on the command
                      line. When it is specified, the backup is done in
                      BACKUP-ROOT-DIR instead.
  --no-version-check  This option disables the version check which is enabled
                      by the --version-check option.
  --no-backup-locks   This option controls if backup locks should be used
                      instead of FLUSH TABLES WITH READ LOCK on the backup
                      stage. The option has no effect when backup locks are not
                      supported by the server. This option is enabled by
                      default, disable with --no-backup-locks.
  --decompress        Decompresses all files with the .qp extension in a backup
                      previously made with the --compress option.
  --user=name         This option specifies the MySQL username used when
                      connecting to the server, if that's not the current user.
                      The option accepts a string argument. See mysql --help
                      for details.
  --host=name         This option specifies the host to use when connecting to
                      the database server with TCP/IP.  The option accepts a
                      string argument. See mysql --help for details.
  --port=#            This option specifies the port to use when connecting to
                      the database server with TCP/IP.  The option accepts a
                      string argument. See mysql --help for details.
  --password=name     This option specifies the password to use when connecting
                      to the database. It accepts a string argument.  See mysql
                      --help for details.
  --socket=name       This option specifies the socket to use when connecting
                      to the local database server with a UNIX domain socket.
                      The option accepts a string argument. See mysql --help
                      for details.
  --incremental-history-name=name
                      This option specifies the name of the backup series
                      stored in the PERCONA_SCHEMA.xtrabackup_history history
                      record to base an incremental backup on. Xtrabackup will
                      search the history table looking for the most recent
                      (highest innodb_to_lsn), successful backup in the series
                      and take the to_lsn value to use as the starting lsn for
                      the incremental backup. This will be mutually exclusive
                      with --incremental-history-uuid, --incremental-basedir
                      and --incremental-lsn. If no valid lsn can be found (no
                      series by that name, no successful backups by that name)
                      xtrabackup will return with an error. It is used with the
                      --incremental option.
  --incremental-history-uuid=name
                      This option specifies the UUID of the specific history
                      record stored in the PERCONA_SCHEMA.xtrabackup_history to
                      base an incremental backup on.
                      --incremental-history-name, --incremental-basedir and
                      --incremental-lsn. If no valid lsn can be found (no
                      success record with that uuid) xtrabackup will return
                      with an error. It is used with the --incremental option.
  --decrypt=name      Decrypts all files with the .xbcrypt extension in a
                      backup previously made with --encrypt option.
  --ftwrl-wait-query-type=name
                      This option specifies which types of queries are allowed
                      to complete before innobackupex will issue the global
                      lock. Default is all.
  --kill-long-query-type=name
                      This option specifies which types of queries should be
                      killed to unblock the global lock. Default is "all".
  --history[=name]    This option enables the tracking of backup history in the
                      PERCONA_SCHEMA.xtrabackup_history table. An optional
                      history series name may be specified that will be placed
                      with the history record for the current backup being
                      taken.
  --include=name      This option is a regular expression to be matched against
                      table names in databasename.tablename format. It is
                      passed directly to xtrabackup's --tables option. See the
                      xtrabackup documentation for details.
  --databases=name    This option specifies the list of databases that
                      innobackupex should back up. The option accepts a string
                      argument or path to file that contains the list of
                      databases to back up. The list is of the form
                      "databasename1[.table_name1] databasename2[.table_name2]
                      . . .". If this option is not specified, all databases
                      containing MyISAM and InnoDB tables will be backed up.
                      Please make sure that --databases contains all of the
                      InnoDB databases and tables, so that all of the
                      innodb.frm files are also backed up. In case the list is
                      very long, this can be specified in a file, and the full
                      path of the file can be specified instead of the list.
                      (See option --tables-file.)
  --kill-long-queries-timeout=#
                      This option specifies the number of seconds innobackupex
                      waits between starting FLUSH TABLES WITH READ LOCK and
                      killing those queries that block it. Default is 0
                      seconds, which means innobackupex will not attempt to
                      kill any queries.
  --ftwrl-wait-timeout=#
                      This option specifies time in seconds that innobackupex
                      should wait for queries that would block FTWRL before
                      running it. If there are still such queries when the
                      timeout expires, innobackupex terminates with an error.
                      Default is 0, in which case innobackupex does not wait
                      for queries to complete and starts FTWRL immediately.
  --ftwrl-wait-threshold=#
                      This option specifies the query run time threshold which
                      is used by innobackupex to detect long-running queries
                      with a non-zero value of --ftwrl-wait-timeout. FTWRL is
                      not started until such long-running queries exist. This
                      option has no effect if --ftwrl-wait-timeout is 0.
                      Default value is 60 seconds.
  --debug-sleep-before-unlock=#
                      This is a debug-only option used by the XtraBackup test
                      suite.
  --safe-slave-backup-timeout=#
                      How many seconds --safe-slave-backup should wait for
                      Slave_open_temp_tables to become zero. (default 300)
  --close-files       Do not keep files opened. This option is passed directly
                      to xtrabackup. Use at your own risk.
  --compact           Create a compact backup with all secondary index pages
                      omitted. This option is passed directly to xtrabackup.
                      See xtrabackup documentation for details.
  --compress[=name]   This option instructs xtrabackup to compress backup
                      copies of InnoDB data files. It is passed directly to the
                      xtrabackup child process. Try 'xtrabackup --help' for
                      more details.
  --compress-threads=#
                      This option specifies the number of worker threads that
                      will be used for parallel compression. It is passed
                      directly to the xtrabackup child process. Try 'xtrabackup
                      --help' for more details.
  --compress-chunk-size=#
                      Size of working buffer(s) for compression threads in
                      bytes. The default value is 64K.
  --encrypt=name      This option instructs xtrabackup to encrypt backup copies
                      of InnoDB data files using the algorithm specified in the
                      ENCRYPTION-ALGORITHM. It is passed directly to the
                      xtrabackup child process. Try 'xtrabackup --help' for
                      more details.
  --encrypt-key=name  This option instructs xtrabackup to use the given
                      ENCRYPTION-KEY when using the --encrypt or --decrypt
                      options. During backup it is passed directly to the
                      xtrabackup child process. Try 'xtrabackup --help' for
                      more details.
  --encrypt-key-file=name
                      This option instructs xtrabackup to use the encryption
                      key stored in the given ENCRYPTION-KEY-FILE when using
                      the --encrypt or --decrypt options.
  --encrypt-threads=# This option specifies the number of worker threads that
                      will be used for parallel encryption. It is passed
                      directly to the xtrabackup child process. Try 'xtrabackup
                      --help' for more details.
  --encrypt-chunk-size=#
                      This option specifies the size of the internal working
                      buffer for each encryption thread, measured in bytes. It
                      is passed directly to the xtrabackup child process. Try
                      'xtrabackup --help' for more details.
  --export            This option is passed directly to xtrabackup's --export
                      option. It enables exporting inpidual tables for import
                      into another server. See the xtrabackup documentation for
                      details.
  --extra-lsndir=name This option specifies the directory in which to save an
                      extra copy of the "xtrabackup_checkpoints" file. The
                      option accepts a string argument. It is passed directly
                      to xtrabackup's --extra-lsndir option. See the xtrabackup
                      documentation for details.
  --incremental-basedir=name
                      This option specifies the directory containing the full
                      backup that is the base dataset for the incremental
                      backup.  The option accepts a string argument. It is used
                      with the --incremental option.
  --incremental-dir=name
                      This option specifies the directory where the incremental
                      backup will be combined with the full backup to make a
                      new full backup.  The option accepts a string argument.
                      It is used with the --incremental option.
  --incremental-force-scan
                      This options tells xtrabackup to perform full scan of
                      data files for taking an incremental backup even if full
                      changed page bitmap data is available to enable the
                      backup without the full scan.
  --log-copy-interval=#
                      This option specifies time interval between checks done
                      by log copying thread in milliseconds.
  --incremental-lsn=name
                      This option specifies the log sequence number (LSN) to
                      use for the incremental backup.  The option accepts a
                      string argument. It is used with the --incremental
                      option. It is used instead of specifying
                      --incremental-basedir. For databases created by MySQL and
                      Percona Server 5.0-series versions, specify the LSN as
                      two 32-bit integers in high:low format. For databases
                      created in 5.1 and later, specify the LSN as a single
                      64-bit integer.
  --parallel=#        On backup, this option specifies the number of threads
                      the xtrabackup child process should use to back up files
                      concurrently.  The option accepts an integer argument. It
                      is passed directly to xtrabackup's --parallel option. See
                      the xtrabackup documentation for details.
  --rebuild-indexes   This option only has effect when used together with the
                      --apply-log option and is passed directly to xtrabackup.
                      When used, makes xtrabackup rebuild all secondary indexes
                      after applying the log. This option is normally used to
                      prepare compact backups. See the XtraBackup manual for
                      more information.
  --rebuild-threads=# Use this number of threads to rebuild indexes in a
                      compact backup. Only has effect with --prepare and
                      --rebuild-indexes.
  --stream=name       This option specifies the format in which to do the
                      streamed backup.  The option accepts a string argument.
                      The backup will be done to STDOUT in the specified
                      format. Currently, the only supported formats are tar and
                      xbstream. This option is passed directly to xtrabackup's
                      --stream option.
  --tables-file=name  This option specifies the file in which there are a list
                      of names of the form database.  The option accepts a
                      string argument.table, one per line. The option is passed
                      directly to xtrabackup's --tables-file option.
  --throttle=#        This option specifies a number of I/O operations (pairs
                      of read+write) per second.  It accepts an integer
                      argument.  It is passed directly to xtrabackup's
                      --throttle option.
  -t, --tmpdir=name   This option specifies the location where a temporary
                      files will be stored. If the option is not specified, the
                      default is to use the value of tmpdir read from the
                      server configuration.
  --use-memory=#      This option accepts a string argument that specifies the
                      amount of memory in bytes for xtrabackup to use for crash
                      recovery while preparing a backup. Multiples are
                      supported providing the unit (e.g. 1MB, 1GB). It is used
                      only with the option --apply-log. It is passed directly
                      to xtrabackup's --use-memory option. See the xtrabackup
                      documentation for details.
[root@localhost ~]#

3. 使用 innobackupex 备份

3.1 全备

(这里系统层面使用的root用户备份,msyql层面使用的是bkpuser用户,root需要对datadir /var/lib/mysql, 备份目录/backup/xtrabackup/full有读写执行权限;bkpuser也需在mysql中有相关权限)

[root@localhost ~]# innobackupex /backup/xtrabackup/full --user=bkpuser --password=digdeep

[root@localhost ~]# innobackupex  /backup/xtrabackup/full --user=bkpuser --password=digdeep
151105 22:38:55 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!".

151105 22:38:55  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/tmp/mysql.sock' as 'bkpuser'  (using password: YES).
151105 22:38:56  version_check Connected to MySQL server
151105 22:38:56  version_check Executing a version check against the server...
151105 22:38:56  version_check Done.
151105 22:38:56 Connecting to MySQL server host: localhost, user: bkpuser, password: set, port: 0, socket: /tmp/mysql.sock
Using server version 5.6.26-log
innobackupex version 2.3.2 based on MySQL server 5.6.24 Linux (i686) (revision id: 306a2e0)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 10240
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
151105 22:38:56 >> log scanned up to (731470240)
xtrabackup: Generating a list of tablespaces
151105 22:38:56 [01] Copying ./ibdata1 to /backup/xtrabackup/full/2015-11-05_22-38-55/ibdata1
151105 22:38:57 >> log scanned up to (731470240)
151105 22:38:58 >> log scanned up to (731470240)
151105 22:38:58 [01]        ...done
151105 22:38:58 [01] Copying ./mysql/slave_master_info.ibd to /backup/xtrabackup/full/2015-11-05_22-38-55/mysql/slave_master_info.ibd
151105 22:38:58 [01]        ...done
151105 22:38:58 [01] Copying ./mysql/innodb_index_stats.ibd to /backup/xtrabackup/full/2015-11-05_22-38-55/mysql/innodb_index_stats.ibd
151105 22:38:58 [01]        ...done
[... ...]
151105 22:38:59 [01] Copying ./aazj/group_union.ibd to /backup/xtrabackup/full/2015-11-05_22-38-55/aazj/group_union.ibd
151105 22:38:59 [01]        ...done
151105 22:38:59 [01] Copying ./aazj/SYS_PARAM.ibd to /backup/xtrabackup/full/2015-11-05_22-38-55/aazj/SYS_PARAM.ibd
151105 22:38:59 >> log scanned up to (731470240)
151105 22:38:59 [01]        ...done
151105 22:38:59 [01] Copying ./aazj/GroupBlog.ibd to /backup/xtrabackup/full/2015-11-05_22-38-55/aazj/GroupBlog.ibd
151105 22:38:59 [01]        ...done
[... ...]
151105 22:39:01 [01] Copying ./aazj/Accounting_paylog.ibd to /backup/xtrabackup/full/2015-11-05_22-38-55/aazj/Accounting_paylog.ibd
151105 22:39:01 [01]        ...done
151105 22:39:01 [01] Copying ./aazj/Customer.ibd to /backup/xtrabackup/full/2015-11-05_22-38-55/aazj/Customer.ibd
151105 22:39:01 [01]        ...done
151105 22:39:01 [01] Copying ./aazj/uuu.ibd to /backup/xtrabackup/full/2015-11-05_22-38-55/aazj/uuu.ibd
151105 22:39:02 >> log scanned up to (731634905)
151105 22:39:03 >> log scanned up to (731634905)
151105 22:39:04 >> log scanned up to (731634905)
151105 22:39:04 [01]        ...done
151105 22:39:04 [01] Copying ./aazj/Members.ibd to /backup/xtrabackup/full/2015-11-05_22-38-55/aazj/Members.ibd
151105 22:39:05 [01]        ...done
151105 22:39:05 [01] Copying ./aazj/tttt.ibd to /backup/xtrabackup/full/2015-11-05_22-38-55/aazj/tttt.ibd
151105 22:39:05 [01]        ...done
151105 22:39:05 [01] Copying ./aazj/uu_test.ibd to /backup/xtrabackup/full/2015-11-05_22-38-55/aazj/uu_test.ibd
151105 22:39:05 >> log scanned up to (731634905)
151105 22:39:06 >> log scanned up to (731685874)
151105 22:39:07 >> log scanned up to (731686008)
151105 22:39:08 >> log scanned up to (731686008)
151105 22:39:08 [01]        ...done
151105 22:39:08 [01] Copying ./aazj/Mess_Receive.ibd to /backup/xtrabackup/full/2015-11-05_22-38-55/aazj/Mess_Receive.ibd
151105 22:39:09 [01]        ...done
[... ...]
151105 22:39:09 >> log scanned up to (731686008)
Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
151105 22:39:09 Executing FLUSH TABLES WITH READ LOCK...
151105 22:39:09 Starting to backup non-InnoDB tables and files
151105 22:39:09 [01] Copying ./mysql/columns_priv.frm to /backup/xtrabackup/full/2015-11-05_22-38-55/mysql/columns_priv.frm
151105 22:39:09 [01]        ...done
151105 22:39:09 [01] Copying ./mysql/user.MYI to /backup/xtrabackup/full/2015-11-05_22-38-55/mysql/user.MYI
151105 22:39:09 [01]        ...done
[... ...]
151105 22:39:10 [01] Copying ./mysql/help_category.frm to /backup/xtrabackup/full/2015-11-05_22-38-55/mysql/help_category.frm
151105 22:39:10 [01]        ...done
151105 22:39:10 >> log scanned up to (731686008)
151105 22:39:10 [01] Copying ./mysql/proc.MYD to /backup/xtrabackup/full/2015-11-05_22-38-55/mysql/proc.MYD
151105 22:39:10 [01]        ...done
[... ...]
151105 22:39:10 [01]        ...done
151105 22:39:10 [01] Copying ./mysql/proxies_priv.MYI to /backup/xtrabackup/full/2015-11-05_22-38-55/mysql/proxies_priv.MYI
151105 22:39:10 [01]        ...done
151105 22:39:10 [01] Copying ./aazj/model_order.frm to /backup/xtrabackup/full/2015-11-05_22-38-55/aazj/model_order.frm
151105 22:39:10 [01]        ...done
151105 22:39:10 [01] Copying ./aazj/Comment.frm to /backup/xtrabackup/full/2015-11-05_22-38-55/aazj/Comment.frm
151105 22:39:10 [01]        ...done
[... ...]
151105 22:39:11 [01] Copying ./performance_schema/events_waits_summary_by_host_by_event_name.frm to /backup/xtrabackup/full/2015-11-05_22-38-55/performance_schema/events_waits_summary_by_host_by_event_name.frm
151105 22:39:11 [01]        ...done
[... ...]
151105 22:39:11 [01] Copying ./performance_schema/events_statements_summary_by_account_by_event_name.frm to /backup/xtrabackup/full/2015-11-05_22-38-55/performance_schema/events_statements_summary_by_account_by_event_name.frm
151105 22:39:11 [01]        ...done
151105 22:39:11 [01] Copying ./t/city.frm to /backup/xtrabackup/full/2015-11-05_22-38-55/t/city.frm
151105 22:39:11 [01]        ...done
151105 22:39:11 [01] Copying ./t/db.opt to /backup/xtrabackup/full/2015-11-05_22-38-55/t/db.opt
151105 22:39:11 [01]        ...done
151105 22:39:11 [01] Copying ./t/t.frm to /backup/xtrabackup/full/2015-11-05_22-38-55/t/t.frm
151105 22:39:11 [01]        ...done
151105 22:39:11 Finished backing up non-InnoDB tables and files
151105 22:39:11 [00] Writing xtrabackup_binlog_info
151105 22:39:11 [00]        ...done
151105 22:39:11 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '731686008'
xtrabackup: Stopping log copying thread.
.151105 22:39:11 >> log scanned up to (731686008)

151105 22:39:11 Executing UNLOCK TABLES
151105 22:39:11 All tables unlocked
151105 22:39:11 Backup created in directory '/backup/xtrabackup/full/2015-11-05_22-38-55'
MySQL binlog position: filename 'mysql-bin.000015', position '117940'
151105 22:39:11 [00] Writing backup-my.cnf
151105 22:39:11 [00]        ...done
151105 22:39:11 [00] Writing xtrabackup_info
151105 22:39:11 [00]        ...done
xtrabackup: Transaction log of lsn (731470240) to (731686008) was copied.
151105 22:39:11 completed OK!

3.2 恢复

1> 第一步prepare(两次prepare,第一次应用备份期间产生的redo log,进行前滚和回滚:replay在redo log中已经提交的事务,rollback没有提交的事务)

注意这里的路径,必须要包括最后那个timestamp目录,不然会下面的错误:



[root@localhost ~]# innobackupex --apply-log /backup/xtrabackup/full/ --user=bkpuser --password=digdeep
151106 10:41:48 innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.
           At the end of a successful apply-log run innobackupex
           prints "completed OK!".

innobackupex version 2.3.2 based on MySQL server 5.6.24 Linux (i686) (revision id: 306a2e0)
xtrabackup: cd to /backup/xtrabackup/full
xtrabackup: Error: cannot open ./xtrabackup_checkpoints
xtrabackup: error: xtrabackup_read_metadata()
xtrabackup: This target seems not to have correct metadata...
2015-11-06 10:41:48 b771e6d0  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
xtrabackup: Warning: cannot open ./xtrabackup_logfile. will try to find.
2015-11-06 10:41:48 b771e6d0  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
  xtrabackup: Fatal error: cannot find ./xtrabackup_logfile.
xtrabackup: Error: xtrabackup_init_temp_log() failed.

--apply-log会调用 xtrabackup --prepare两次,第一次前滚和回滚,第二次生成iblogfile[0|1]

[root@localhost ~]# innobackupex --apply-log /backup/xtrabackup/full/2015-11-05_22-38-55/ --user=bkpuser --password=digdeep

[root@localhost ~]# innobackupex --apply-log /backup/xtrabackup/full/2015-11-05_22-38-55/ --user=bkpuser --password=digdeep 
151106 10:43:32 innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.
           At the end of a successful apply-log run innobackupex
           prints "completed OK!".

innobackupex version 2.3.2 based on MySQL server 5.6.24 Linux (i686) (revision id: 306a2e0)
xtrabackup: cd to /backup/xtrabackup/full/2015-11-05_22-38-55/
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(731470240)
xtrabackup: using the following InnoDB configuration for recovery:
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: using the following InnoDB configuration for recovery:
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: Using atomics to ref count buffer pool pages
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Memory barrier is not used
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Not using CPU crc32 instructions
InnoDB: Initializing buffer pool, size = 100.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 731470240
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages
InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 731686008 (11%)
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 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
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6.24 started; log sequence number 731686008
xtrabackup: Last MySQL binlog file position 117940, file name mysql-bin.000015  ()

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 731724574
xtrabackup: using the following InnoDB configuration for recovery:
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
InnoDB: Using atomics to ref count buffer pool pages
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Memory barrier is not used
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Not using CPU crc32 instructions
InnoDB: Initializing buffer pool, size = 100.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Setting log file ./ib_logfile101 size to 48 MB
InnoDB: Setting log file ./ib_logfile1 size to 48 MB
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=731724574
InnoDB: Highest supported file format is Barracuda.
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6.24 started; log sequence number 731724812
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 731724822
151106 10:43:40 completed OK!
[root@localhost ~]#

3.3 恢复 --copy-back

直接将上面prepare好的所有文件,复制到mysqld的datadir目录(会读取my.cnf中的配置信息)。

--copy--back的注意事项:

1> datadir必须是空的,或者使用--force-non-empty-directories选项;

2> mysqld必须关闭,如果是--import部分恢复,则不能关闭;

3> --copy-back完成之后,需要修改datadir目录下的文件权限: chown -R mysql:mysql /var/lib/mysql

[root@localhost ~]# mysqladmin -uroot -pxxx shutdown (关闭mysqld)

[root@localhost ~]# cd /var/lib/mysql

[root@localhost mysql]# ls

aazj ib_logfile1 mysql-bin.000003 mysql-bin.000008 mysql-bin.000013 performance_schema

auto.cnf localhost-slow.log mysql-bin.000004 mysql-bin.000009 mysql-bin.000014 t

general.log mysql mysql-bin.000005 mysql-bin.000010 mysql-bin.000015 xtrabackup_binlog_pos_innodb

ibdata1 mysql-bin.000001 mysql-bin.000006 mysql-bin.000011 mysql-bin.000016 xtrabackup_info

ib_logfile0 mysql-bin.000002 mysql-bin.000007 mysql-bin.000012 mysql-bin.index

[root@localhost mysql]# mv * /backup/xtrabackup/ (进行清空)

[root@localhost mysql]# ls

[root@localhost mysql]# innobackupex --copy-back /backup/xtrabackup/full/2015-11-05_22-38-55/ --user=bkpuser --password=digdeep 

[root@localhost mysql]# innobackupex --copy-back /backup/xtrabackup/full/2015-11-05_22-38-55/ --user=bkpuser --password=digdeep 
151106 11:07:38 innobackupex: Starting the copy-back operation

IMPORTANT: Please check that the copy-back run completes successfully.
           At the end of a successful copy-back run innobackupex
           prints "completed OK!".

innobackupex version 2.3.2 based on MySQL server 5.6.24 Linux (i686) (revision id: 306a2e0)
151106 11:07:38 [01] Copying ib_logfile0 to /var/lib/mysql/ib_logfile0
151106 11:07:40 [01]        ...done
151106 11:07:40 [01] Copying ib_logfile1 to /var/lib/mysql/ib_logfile1
151106 11:07:41 [01]        ...done
151106 11:07:41 [01] Copying ibdata1 to /var/lib/mysql/ibdata1
151106 11:07:45 [01]        ...done
151106 11:07:45 [01] Copying ./xtrabackup_info to /var/lib/mysql/xtrabackup_info
151106 11:07:45 [01]        ...done
151106 11:07:45 [01] Copying ./mysql/slave_master_info.ibd to /var/lib/mysql/mysql/slave_master_info.ibd
151106 11:07:45 [01]        ...done
[... ...]
151106 11:07:57 [01] Copying ./t/db.opt to /var/lib/mysql/t/db.opt
151106 11:07:57 [01]        ...done
151106 11:07:57 [01] Copying ./t/t.frm to /var/lib/mysql/t/t.frm
151106 11:07:57 [01]        ...done
151106 11:07:57 completed OK!
[root@localhost mysql]# pwd
/var/lib/mysql
[root@localhost mysql]# ls
aazj  ibdata1  ib_logfile0  ib_logfile1  mysql  performance_schema  t  xtrabackup_binlog_pos_innodb  xtrabackup_info

可以看到恢复之后,没有 binlog 文件盒index文件

启动myqld之前需要修改权限:

[root@localhost mysql]# ls -l
total 176164
drwx------ 2 root  root      4096 Nov  6 11:07 aazj
-rw-rw---- 1 mysql mysql      543 Nov  6 11:13 general.log
-rw-r----- 1 root  root  79691776 Nov  6 11:07 ibdata1
-rw-r----- 1 root  root  50331648 Nov  6 11:07 ib_logfile0
-rw-r----- 1 root  root  50331648 Nov  6 11:07 ib_logfile1
-rw-rw---- 1 mysql mysql      543 Nov  6 11:13 localhost-slow.log
drwx------ 2 root  root      4096 Nov  6 11:07 mysql
-rw-rw---- 1 mysql mysql        0 Nov  6 11:12 mysql-bin.index
drwx------ 2 root  root      4096 Nov  6 11:07 performance_schema
drwx------ 2 root  root      4096 Nov  6 11:07 t
-rw-r----- 1 root  root        24 Nov  6 11:07 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root  root       487 Nov  6 11:07 xtrabackup_info
[root@localhost mysql]# chown -R mysql:mysql /var/lib/mysql
[root@localhost mysql]# ls -l
total 176164
drwx------ 2 mysql mysql     4096 Nov  6 11:07 aazj
-rw-rw---- 1 mysql mysql      543 Nov  6 11:13 general.log
-rw-r----- 1 mysql mysql 79691776 Nov  6 11:07 ibdata1
-rw-r----- 1 mysql mysql 50331648 Nov  6 11:07 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Nov  6 11:07 ib_logfile1
-rw-rw---- 1 mysql mysql      543 Nov  6 11:13 localhost-slow.log
drwx------ 2 mysql mysql     4096 Nov  6 11:07 mysql
-rw-rw---- 1 mysql mysql        0 Nov  6 11:12 mysql-bin.index
drwx------ 2 mysql mysql     4096 Nov  6 11:07 performance_schema
drwx------ 2 mysql mysql     4096 Nov  6 11:07 t
-rw-r----- 1 mysql mysql       24 Nov  6 11:07 xtrabackup_binlog_pos_innodb
-rw-r----- 1 mysql mysql      487 Nov  6 11:07 xtrabackup_info

不然启动会在error.log中报错:

2015-11-06 11:13:55 3542 [ERROR] InnoDB: ./ibdata1 can't be opened in read-write mode

2015-11-06 11:13:55 3542 [ERROR] InnoDB: The system tablespace must be writable!

2015-11-06 11:13:55 3542 [ERROR] Plugin 'InnoDB' init function returned error.

2015-11-06 11:13:55 3542 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

2015-11-06 11:13:55 3542 [ERROR] Unknown/unsupported storage engine: InnoDB

2015-11-06 11:13:55 3542 [ERROR] Aborting

启动成功之后,datadir目录下各种文件都产生了:

[root@localhost mysql]# pwd

/var/lib/mysql

[root@localhost mysql]# ls

aazj general.log ib_logfile0 localhost-slow.log mysql-bin.000001 performance_schema xtrabackup_binlog_pos_innodb

auto.cnf ibdata1 ib_logfile1 mysql mysql-bin.index t xtrabackup_info

3.4 innobackupex 增量备份

增量备份之前,必须建立一个全备,第一次增量备份是在全备的基础之上,第二次增量备份是在第一次增量备份的基础之上的,一次类推

全备:

[root@localhost mysql]# innobackupex --user=bkpuser --password=digdeep /backup/xtrabackup/full

[root@localhost mysql]# innobackupex --user=bkpuser --password=digdeep /backup/xtrabackup/full
第一次增量备份:
--incremental /backup/xtrabackup/incr1/ 指定增量备份的位置;
--incremental-basedir=指定上一次的全备或者增量备份:
[root@localhost mysql]# innobackupex --incremental /backup/xtrabackup/incr1/ --incremental-basedir=/backup/xtrabackup/full/2015-11-06_11-29-51/ --user=bkpuser --password=digdeep 
151106 11:33:16 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!".

151106 11:33:16  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/tmp/mysql.sock' as 'bkpuser'  (using password: YES).
151106 11:33:16  version_check Connected to MySQL server
151106 11:33:16  version_check Executing a version check against the server...
151106 11:33:16  version_check Done.
151106 11:33:16 Connecting to MySQL server host: localhost, user: bkpuser, password: set, port: 0, socket: /tmp/mysql.sock
Using server version 5.6.26-log
innobackupex version 2.3.2 based on MySQL server 5.6.24 Linux (i686) (revision id: 306a2e0)
incremental backup from 731724832 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 10240
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
151106 11:33:16 >> log scanned up to (732153217)
xtrabackup: Generating a list of tablespaces
xtrabackup: using the full scan for incremental backup
151106 11:33:17 [01] Copying ./ibdata1 to /backup/xtrabackup/incr1//2015-11-06_11-33-16/ibdata1.delta
151106 11:33:17 >> log scanned up to (732153217)
151106 11:33:18 [01]        ...done
151106 11:33:18 >> log scanned up to (732153217)
151106 11:33:18 [01] Copying ./mysql/slave_master_info.ibd to /backup/xtrabackup/incr1//2015-11-06_11-33-16/mysql/slave_master_info.ibd.delta
151106 11:33:18 [01]        ...done
151106 11:33:19 >> log scanned up to (732153217)
[... ...]
151106 11:33:30 [01] Copying ./aazj/Configuration.ibd to /backup/xtrabackup/incr1//2015-11-06_11-33-16/aazj/Configuration.ibd.delta
151106 11:33:30 [01]        ...done
151106 11:33:31 [01] Copying ./aazj/lx_test.ibd to /backup/xtrabackup/incr1//2015-11-06_11-33-16/aazj/lx_test.ibd.delta
151106 11:33:31 >> log scanned up to (732231774)
151106 11:33:32 [01]        ...done
151106 11:33:32 >> log scanned up to (732231774)
151106 11:33:32 [01] Copying ./aazj/Users.ibd to /backup/xtrabackup/incr1//2015-11-06_11-33-16/aazj/Users.ibd.delta
151106 11:33:32 [01]        ...done
[... ...]
151106 11:33:42 [01] Copying ./aazj/tttt.ibd to /backup/xtrabackup/incr1//2015-11-06_11-33-16/aazj/tttt.ibd.delta
151106 11:33:42 [01]        ...done
151106 11:33:42 >> log scanned up to (732501432)
151106 11:33:42 [01] Copying ./aazj/uu_test.ibd to /backup/xtrabackup/incr1//2015-11-06_11-33-16/aazj/uu_test.ibd.delta
[... ...]
151106 11:33:47 [01] Copying ./t/t.ibd to /backup/xtrabackup/incr1//2015-11-06_11-33-16/t/t.ibd.delta
151106 11:33:48 [01]        ...done
151106 11:33:48 >> log scanned up to (732501432)
Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
151106 11:33:48 Executing FLUSH TABLES WITH READ LOCK...
151106 11:33:48 Starting to backup non-InnoDB tables and files
151106 11:33:48 [01] Copying ./mysql/columns_priv.frm to /backup/xtrabackup/incr1//2015-11-06_11-33-16/mysql/columns_priv.frm
151106 11:33:48 [01]        ...done
[... ...]
151106 11:33:51 [01] Copying ./t/t.frm to /backup/xtrabackup/incr1//2015-11-06_11-33-16/t/t.frm
151106 11:33:51 [01]        ...done
151106 11:33:51 Finished backing up non-InnoDB tables and files
151106 11:33:51 [00] Writing xtrabackup_binlog_info
151106 11:33:51 [00]        ...done
151106 11:33:51 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '732501432'
xtrabackup: Stopping log copying thread.
.151106 11:33:51 >> log scanned up to (732501432)

151106 11:33:51 Executing UNLOCK TABLES
151106 11:33:51 All tables unlocked
151106 11:33:51 Backup created in directory '/backup/xtrabackup/incr1//2015-11-06_11-33-16'
MySQL binlog position: filename 'mysql-bin.000001', position '157893'
151106 11:33:51 [00] Writing backup-my.cnf
151106 11:33:51 [00]        ...done
151106 11:33:51 [00] Writing xtrabackup_info
151106 11:33:51 [00]        ...done
xtrabackup: Transaction log of lsn (732153217) to (732501432) was copied.
151106 11:33:51 completed OK!
[root@localhost mysql]#

第二次增量备份:

[root@localhost mysql]# innobackupex --incremental /backup/xtrabackup/incr2 --incremental-basedir=/backup/xtrabackup/incr1/2015-11-06_11-33-16/ --user=bkpuser --password=digdeep

[root@localhost mysql]# innobackupex --incremental /backup/xtrabackup/incr2 --incremental-basedir=/backup/xtrabackup/incr1/2015-11-06_11-33-16/ --user=bkpuser --password=digdeep
151106 11:43:22 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!".

151106 11:43:22  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/tmp/mysql.sock' as 'bkpuser'  (using password: YES).
151106 11:43:22  version_check Connected to MySQL server
151106 11:43:22  version_check Executing a version check against the server...
151106 11:43:22  version_check Done.
151106 11:43:22 Connecting to MySQL server host: localhost, user: bkpuser, password: set, port: 0, socket: /tmp/mysql.sock
Using server version 5.6.26-log
innobackupex version 2.3.2 based on MySQL server 5.6.24 Linux (i686) (revision id: 306a2e0)
incremental backup from 732501432 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 10240
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
151106 11:43:23 >> log scanned up to (732501432)
xtrabackup: Generating a list of tablespaces
151106 11:43:23 [01] Copying ./ibdata1 to /backup/xtrabackup/incr2/2015-11-06_11-43-22/ibdata1.delta
151106 11:43:23 [01]        ...done
151106 11:43:24 >> log scanned up to (732552856)
151106 11:43:24 [01] Copying ./mysql/slave_master_info.ibd to /backup/xtrabackup/incr2/2015-11-06_11-43-22/mysql/slave_master_info.ibd.delta
151106 11:43:24 [01]        ...done
151106 11:43:25 >> log scanned up to (732552974)
151106 11:43:25 [01] Copying ./mysql/innodb_index_stats.ibd to /backup/xtrabackup/incr2/2015-11-06_11-43-22/mysql/innodb_index_stats.ibd.delta
151106 11:43:25 [01]        ...done
151106 11:43:25 [01] Copying ./mysql/slave_relay_log_info.ibd to /backup/xtrabackup/incr2/2015-11-06_11-43-22/mysql/slave_relay_log_info.ibd.delta
151106 11:43:25 [01]        ...done
151106 11:43:26 >> log scanned up to (732552974)
151106 11:43:26 [01] Copying ./mysql/slave_worker_info.ibd to /backup/xtrabackup/incr2/2015-11-06_11-43-22/mysql/slave_worker_info.ibd.delta
151106 11:43:26 [01]        ...done
151106 11:43:26 [01] Copying ./mysql/innodb_table_stats.ibd to /backup/xtrabackup/incr2/2015-11-06_11-43-22/mysql/innodb_table_stats.ibd.delta
151106 11:43:26 [01]        ...done
151106 11:43:27 >> log scanned up to (732716925)
151106 11:43:27 [01] Copying ./aazj/u_test.ibd to /backup/xtrabackup/incr2/2015-11-06_11-43-22/aazj/u_test.ibd.delta
151106 11:43:27 [01]        ...done
[... ...]
151106 11:43:50 [01] Copying ./t/t.frm to /backup/xtrabackup/incr2/2015-11-06_11-43-22/t/t.frm
151106 11:43:50 [01]        ...done
151106 11:43:50 Finished backing up non-InnoDB tables and files
151106 11:43:50 [00] Writing xtrabackup_binlog_info
151106 11:43:50 [00]        ...done
151106 11:43:50 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '732777035'
xtrabackup: Stopping log copying thread.
.151106 11:43:50 >> log scanned up to (732777035)

151106 11:43:50 Executing UNLOCK TABLES
151106 11:43:50 All tables unlocked
151106 11:43:50 Backup created in directory '/backup/xtrabackup/incr2/2015-11-06_11-43-22'
MySQL binlog position: filename 'mysql-bin.000001', position '254400'
151106 11:43:50 [00] Writing backup-my.cnf
151106 11:43:50 [00]        ...done
151106 11:43:50 [00] Writing xtrabackup_info
151106 11:43:50 [00]        ...done
xtrabackup: Transaction log of lsn (732501432) to (732777035) was copied.
151106 11:43:50 completed OK!
[root@localhost mysql]#

3.5 innobackupex 增量备份的恢复

1> 应用全备的redo log:

[root@localhost ~]# innobackupex --apply-log --redo-only /backup/xtrabackup/full/2015-11-06_11-29-51/ --user=bkpuser --password=digdeep

[root@localhost ~]# innobackupex --apply-log --redo-only /backup/xtrabackup/full/2015-11-06_11-29-51/ --user=bkpuser --password=digdeep
151106 14:48:26 innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.
           At the end of a successful apply-log run innobackupex
           prints "completed OK!".

innobackupex version 2.3.2 based on MySQL server 5.6.24 Linux (i686) (revision id: 306a2e0)
xtrabackup: cd to /backup/xtrabackup/full/2015-11-06_11-29-51/
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(731724832)
xtrabackup: using the following InnoDB configuration for recovery:
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: using the following InnoDB configuration for recovery:
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: Using atomics to ref count buffer pool pages
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Memory barrier is not used
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Not using CPU crc32 instructions
InnoDB: Initializing buffer pool, size = 100.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Highest supported file format is Barracuda.
InnoDB: The log sequence numbers 731724822 and 731724822 in ibdata files do not match the log sequence number 731724832 in the ib_logfiles!
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages
InnoDB: from the doublewrite buffer...
xtrabackup: Last MySQL binlog file position 117940, file name mysql-bin.000015

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 731724832
151106 14:48:28 completed OK!

2> 应用第一次增量备份的redo log:

[root@localhost ~]# innobackupex --apply-log --redo-only /backup/xtrabackup/full/2015-11-06_11-29-51/ --incremental-dir=/backup/xtrabackup/incr1/2015-11-06_11-33-16/ --user=bkpuser --password=digdeep

[root@localhost ~]# innobackupex --apply-log --redo-only /backup/xtrabackup/full/2015-11-06_11-29-51/ --incremental-dir=/backup/xtrabackup/incr1/2015-11-06_11-33-16/ --user=bkpuser --password=digdeep
151106 14:51:08 innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.
           At the end of a successful apply-log run innobackupex
           prints "completed OK!".

innobackupex version 2.3.2 based on MySQL server 5.6.24 Linux (i686) (revision id: 306a2e0)
incremental backup from 731724832 is enabled.
xtrabackup: cd to /backup/xtrabackup/full/2015-11-06_11-29-51/
xtrabackup: This target seems to be already prepared with --apply-log-only.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(732153217)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = /backup/xtrabackup/incr1/2015-11-06_11-33-16/
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 2097152
xtrabackup: Generating a list of tablespaces
xtrabackup: page size for /backup/xtrabackup/incr1/2015-11-06_11-33-16//ibdata1.delta is 16384 bytes
Applying /backup/xtrabackup/incr1/2015-11-06_11-33-16//ibdata1.delta to ./ibdata1...
xtrabackup: page size for /backup/xtrabackup/incr1/2015-11-06_11-33-16//mysql/innodb_index_stats.ibd.delta is 16384 bytes
[... ...]
xtrabackup: page size for /backup/xtrabackup/incr1/2015-11-06_11-33-16//aazj/tttt.ibd.delta is 16384 bytes
Applying /backup/xtrabackup/incr1/2015-11-06_11-33-16//aazj/tttt.ibd.delta to ./aazj/tttt.ibd...
xtrabackup: page size for /backup/xtrabackup/incr1/2015-11-06_11-33-16//aazj/Users.ibd.delta is 16384 bytes
Applying /backup/xtrabackup/incr1/2015-11-06_11-33-16//aazj/Users.ibd.delta to ./aazj/Users.ibd...
xtrabackup: page size for /backup/xtrabackup/incr1/2015-11-06_11-33-16//aazj/Gis.ibd.delta is 16384 bytes
Applying /backup/xtrabackup/incr1/2015-11-06_11-33-16//aazj/Gis.ibd.delta to ./aazj/Gis.ibd...
[... ...]
xtrabackup: page size for /backup/xtrabackup/incr1/2015-11-06_11-33-16//t/t.ibd.delta is 16384 bytes
Applying /backup/xtrabackup/incr1/2015-11-06_11-33-16//t/t.ibd.delta to ./t/t.ibd...
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = /backup/xtrabackup/incr1/2015-11-06_11-33-16/
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: Using atomics to ref count buffer pool pages
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Memory barrier is not used
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Not using CPU crc32 instructions
InnoDB: Initializing buffer pool, size = 100.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 732153217
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages
InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 732501432 (18%)
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 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 8  6 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
xtrabackup: Last MySQL binlog file position 157        
推荐阅读
  • 一、Hadoop来历Hadoop的思想来源于Google在做搜索引擎的时候出现一个很大的问题就是这么多网页我如何才能以最快的速度来搜索到,由于这个问题Google发明 ... [详细]
  • 在Docker中,将主机目录挂载到容器中作为volume使用时,常常会遇到文件权限问题。这是因为容器内外的UID不同所导致的。本文介绍了解决这个问题的方法,包括使用gosu和suexec工具以及在Dockerfile中配置volume的权限。通过这些方法,可以避免在使用Docker时出现无写权限的情况。 ... [详细]
  • 推荐一个ASP的内容管理框架(ASP Nuke)的优势和适用场景
    本文推荐了一个ASP的内容管理框架ASP Nuke,并介绍了其主要功能和特点。ASP Nuke支持文章新闻管理、投票、论坛等主要内容,并可以自定义模块。最新版本为0.8,虽然目前仍处于Alpha状态,但作者表示会继续更新完善。文章还分析了使用ASP的原因,包括ASP相对较小、易于部署和较简单等优势,适用于建立门户、网站的组织和小公司等场景。 ... [详细]
  • 本文介绍了在开发Android新闻App时,搭建本地服务器的步骤。通过使用XAMPP软件,可以一键式搭建起开发环境,包括Apache、MySQL、PHP、PERL。在本地服务器上新建数据库和表,并设置相应的属性。最后,给出了创建new表的SQL语句。这个教程适合初学者参考。 ... [详细]
  • 本文介绍了如何在MySQL中将零值替换为先前的非零值的方法,包括使用内联查询和更新查询。同时还提供了选择正确值的方法。 ... [详细]
  • 本文介绍了数据库的存储结构及其重要性,强调了关系数据库范例中将逻辑存储与物理存储分开的必要性。通过逻辑结构和物理结构的分离,可以实现对物理存储的重新组织和数据库的迁移,而应用程序不会察觉到任何更改。文章还展示了Oracle数据库的逻辑结构和物理结构,并介绍了表空间的概念和作用。 ... [详细]
  • 在数据分析工作中,我们通常会遇到这样的问题,一个业务部门由若干业务组构成,需要筛选出每个业务组里业绩前N名的业务员。这其实是一个分组排序的 ... [详细]
  • 本文介绍了如何使用php限制数据库插入的条数并显示每次插入数据库之间的数据数目,以及避免重复提交的方法。同时还介绍了如何限制某一个数据库用户的并发连接数,以及设置数据库的连接数和连接超时时间的方法。最后提供了一些关于浏览器在线用户数和数据库连接数量比例的参考值。 ... [详细]
  • Oracle Database 10g许可授予信息及高级功能详解
    本文介绍了Oracle Database 10g许可授予信息及其中的高级功能,包括数据库优化数据包、SQL访问指导、SQL优化指导、SQL优化集和重组对象。同时提供了详细说明,指导用户在Oracle Database 10g中如何使用这些功能。 ... [详细]
  • 在说Hibernate映射前,我们先来了解下对象关系映射ORM。ORM的实现思想就是将关系数据库中表的数据映射成对象,以对象的形式展现。这样开发人员就可以把对数据库的操作转化为对 ... [详细]
  • 本文详细介绍了MysqlDump和mysqldump进行全库备份的相关知识,包括备份命令的使用方法、my.cnf配置文件的设置、binlog日志的位置指定、增量恢复的方式以及适用于innodb引擎和myisam引擎的备份方法。对于需要进行数据库备份的用户来说,本文提供了一些有价值的参考内容。 ... [详细]
  • 本文由编程笔记小编整理,介绍了PHP中的MySQL函数库及其常用函数,包括mysql_connect、mysql_error、mysql_select_db、mysql_query、mysql_affected_row、mysql_close等。希望对读者有一定的参考价值。 ... [详细]
  • Spring特性实现接口多类的动态调用详解
    本文详细介绍了如何使用Spring特性实现接口多类的动态调用。通过对Spring IoC容器的基础类BeanFactory和ApplicationContext的介绍,以及getBeansOfType方法的应用,解决了在实际工作中遇到的接口及多个实现类的问题。同时,文章还提到了SPI使用的不便之处,并介绍了借助ApplicationContext实现需求的方法。阅读本文,你将了解到Spring特性的实现原理和实际应用方式。 ... [详细]
  • Oracle分析函数first_value()和last_value()的用法及原理
    本文介绍了Oracle分析函数first_value()和last_value()的用法和原理,以及在查询销售记录日期和部门中的应用。通过示例和解释,详细说明了first_value()和last_value()的功能和不同之处。同时,对于last_value()的结果出现不一样的情况进行了解释,并提供了理解last_value()默认统计范围的方法。该文对于使用Oracle分析函数的开发人员和数据库管理员具有参考价值。 ... [详细]
  • 本文介绍了在Mac上搭建php环境后无法使用localhost连接mysql的问题,并通过将localhost替换为127.0.0.1或本机IP解决了该问题。文章解释了localhost和127.0.0.1的区别,指出了使用socket方式连接导致连接失败的原因。此外,还提供了相关链接供读者深入了解。 ... [详细]
author-avatar
谢谢巷议
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有