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 --helpOpen source backup tool for InnoDB and XtraDB[... ...]innobackupex - Non-blocking backup tool for InnoDB, XtraDB and HailDB databasesSYNOPOSIS(使用方法)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-DIRinnobackupex --apply-log [--use-memory=B] [--defaults-file=MY.CNF] [--export] [--redo-only] [--ibbackup=IBBACKUP-BINARY] BACKUP-DIRinnobackupex --copy-back [--defaults-file=MY.CNF] [--defaults-group=GROUP-NAME] BACKUP-DIRinnobackupex --move-back [--defaults-file=MY.CNF] [--defaults-group=GROUP-NAME] BACKUP-DIRinnobackupex [--decompress] [--decrypt=ENCRYPTION-ALGORITHM] [--encrypt-key=LITERAL-ENCRYPTION-KEY] | [--encryption-key-file=MY.KEY] [--parallel=NUMBER-OF-FORKS] BACKUP-DIRDESCRIPTIONThe 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-timestampoption it does not create a time-stamped backup directory, but it putsthe backup in the given directory (which must not exist). Thiscommand makes a complete backup of all MyISAM and InnoDB tables andindexes 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, andInnoDB data and log files. The MY.CNF options file defines thelocation of the database. This command connects to the MySQL serverusing the mysql client program, and runs xtrabackup as a childprocess.The --apply-log command prepares a backup for starting a MySQLserver on the backup. This command recovers InnoDB data files as specifiedin 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 byxtrabackup. This command runs xtrabackup as a child process, but it does notconnect to the database server.The --copy-back command copies data, index, and log filesfrom 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 thatit moves files to their original locations rather than copies them. As thisoption removes backup files, it must be used with caution. It may be useful incases when there is not enough free disk space to copy files.The --decompress --decrypt command will decrypt and/or decompress a backup madewith the --compress and/or --encrypt options. When decrypting, the encryptionalgorithm and key used when the backup was taken MUST be provided via thespecified options. --decrypt and --decompress may be used together at the sametime to completely normalize a previously compressed and encrypted backup. The--parallel option will allow multiple files to be decrypted and/or decompressedsimultaneously. In order to decompress, the qpress utility MUST be installedand accessable within the path. This process will remove the originalcompressed/encrypted files and leave the results in the same location.On success the exit code innobackupex is 0. A non-zero exit codeindicates 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 individual 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=digdeep151105 22:38:55 innobackupex: Starting the backup operationIMPORTANT: 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 server151105 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.sockUsing server version 5.6.26-loginnobackupex 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/mysqlxtrabackup: open files limit requested 0, set to 10240xtrabackup: using the following InnoDB configuration:xtrabackup: innodb_data_home_dir = ./xtrabackup: innodb_data_file_path = ibdata1:12M:autoextendxtrabackup: innodb_log_group_home_dir = ./xtrabackup: innodb_log_files_in_group = 2xtrabackup: innodb_log_file_size = 50331648151105 22:38:56 >> log scanned up to (731470240)xtrabackup: Generating a list of tablespaces151105 22:38:56 [01] Copying ./ibdata1 to /backup/xtrabackup/full/2015-11-05_22-38-55/ibdata1151105 22:38:57 >> log scanned up to (731470240)151105 22:38:58 >> log scanned up to (731470240)151105 22:38:58 [01] ...done151105 22:38:58 [01] Copying ./mysql/slave_master_info.ibd to /backup/xtrabackup/full/2015-11-05_22-38-55/mysql/slave_master_info.ibd151105 22:38:58 [01] ...done151105 22:38:58 [01] Copying ./mysql/innodb_index_stats.ibd to /backup/xtrabackup/full/2015-11-05_22-38-55/mysql/innodb_index_stats.ibd151105 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.ibd151105 22:38:59 [01] ...done151105 22:38:59 [01] Copying ./aazj/SYS_PARAM.ibd to /backup/xtrabackup/full/2015-11-05_22-38-55/aazj/SYS_PARAM.ibd151105 22:38:59 >> log scanned up to (731470240)151105 22:38:59 [01] ...done151105 22:38:59 [01] Copying ./aazj/GroupBlog.ibd to /backup/xtrabackup/full/2015-11-05_22-38-55/aazj/GroupBlog.ibd151105 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.ibd151105 22:39:01 [01] ...done151105 22:39:01 [01] Copying ./aazj/Customer.ibd to /backup/xtrabackup/full/2015-11-05_22-38-55/aazj/Customer.ibd151105 22:39:01 [01] ...done151105 22:39:01 [01] Copying ./aazj/uuu.ibd to /backup/xtrabackup/full/2015-11-05_22-38-55/aazj/uuu.ibd151105 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] ...done151105 22:39:04 [01] Copying ./aazj/Members.ibd to /backup/xtrabackup/full/2015-11-05_22-38-55/aazj/Members.ibd151105 22:39:05 [01] ...done151105 22:39:05 [01] Copying ./aazj/tttt.ibd to /backup/xtrabackup/full/2015-11-05_22-38-55/aazj/tttt.ibd151105 22:39:05 [01] ...done151105 22:39:05 [01] Copying ./aazj/uu_test.ibd to /backup/xtrabackup/full/2015-11-05_22-38-55/aazj/uu_test.ibd151105 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] ...done151105 22:39:08 [01] Copying ./aazj/Mess_Receive.ibd to /backup/xtrabackup/full/2015-11-05_22-38-55/aazj/Mess_Receive.ibd151105 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 files151105 22:39:09 [01] Copying ./mysql/columns_priv.frm to /backup/xtrabackup/full/2015-11-05_22-38-55/mysql/columns_priv.frm151105 22:39:09 [01] ...done151105 22:39:09 [01] Copying ./mysql/user.MYI to /backup/xtrabackup/full/2015-11-05_22-38-55/mysql/user.MYI151105 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.frm151105 22:39:10 [01] ...done151105 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.MYD151105 22:39:10 [01] ...done[... ...]151105 22:39:10 [01] ...done151105 22:39:10 [01] Copying ./mysql/proxies_priv.MYI to /backup/xtrabackup/full/2015-11-05_22-38-55/mysql/proxies_priv.MYI151105 22:39:10 [01] ...done151105 22:39:10 [01] Copying ./aazj/model_order.frm to /backup/xtrabackup/full/2015-11-05_22-38-55/aazj/model_order.frm151105 22:39:10 [01] ...done151105 22:39:10 [01] Copying ./aazj/Comment.frm to /bac<b>本文来源gao@dai!ma.com搞$代^码!网7</b>kup/xtrabackup/full/2015-11-05_22-38-55/aazj/Comment.frm151105 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.frm151105 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.frm151105 22:39:11 [01] ...done151105 22:39:11 [01] Copying ./t/city.frm to /backup/xtrabackup/full/2015-11-05_22-38-55/t/city.frm151105 22:39:11 [01] ...done151105 22:39:11 [01] Copying ./t/db.opt to /backup/xtrabackup/full/2015-11-05_22-38-55/t/db.opt151105 22:39:11 [01] ...done151105 22:39:11 [01] Copying ./t/t.frm to /backup/xtrabackup/full/2015-11-05_22-38-55/t/t.frm151105 22:39:11 [01] ...done151105 22:39:11 Finished backing up non-InnoDB tables and files151105 22:39:11 [00] Writing xtrabackup_binlog_info151105 22:39:11 [00] ...done151105 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 TABLES151105 22:39:11 All tables unlocked151105 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.cnf151105 22:39:11 [00] ...done151105 22:39:11 [00] Writing xtrabackup_info151105 22:39:11 [00] ...donextrabackup: 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=digdeep151106 10:41:48 innobackupex: Starting the apply-log operationIMPORTANT: 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/fullxtrabackup: Error: cannot open ./xtrabackup_checkpointsxtrabackup: 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 operationIMPORTANT: 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:autoextendxtrabackup: innodb_log_group_home_dir = ./xtrabackup: innodb_log_files_in_group = 1xtrabackup: innodb_log_file_size = 2097152xtrabackup: using the following InnoDB configuration for recovery:xtrabackup: innodb_data_home_dir = ./xtrabackup: innodb_data_file_path = ibdata1:10M:autoextendxtrabackup: innodb_log_group_home_dir = ./xtrabackup: innodb_log_files_in_group = 1xtrabackup: innodb_log_file_size = 2097152xtrabackup: 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 pagesInnoDB: The InnoDB memory heap is disabledInnoDB: Mutexes and rw_locks use GCC atomic builtinsInnoDB: Memory barrier is not usedInnoDB: Compressed tables use zlib 1.2.3InnoDB: Not using CPU crc32 instructionsInnoDB: Initializing buffer pool, size = 100.0MInnoDB: Completed initialization of buffer poolInnoDB: Highest supported file format is Barracuda.InnoDB: Log scan progressed past the checkpoint lsn 731470240InnoDB: Database was not shutdown normally!InnoDB: Starting crash recovery.InnoDB: Reading tablespace information from the .ibd files...InnoDB: Restoring possible half-written data pagesInnoDB: 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 99InnoDB: Apply batch completedInnoDB: 128 rollback segment(s) are active.InnoDB: Waiting for purge to startInnoDB: 5.6.24 started; log sequence number 731686008xtrabackup: Last MySQL binlog file position 117940, file name mysql-bin.000015 ()xtrabackup: starting shutdown with innodb_fast_shutdown = 1InnoDB: FTS optimize thread exiting.InnoDB: Starting shutdown...InnoDB: Shutdown completed; log sequence number 731724574xtrabackup: using the following InnoDB configuration for recovery:xtrabackup: innodb_data_home_dir = ./xtrabackup: innodb_data_file_path = ibdata1:10M:autoextendxtrabackup: innodb_log_group_home_dir = ./xtrabackup: innodb_log_files_in_group = 2xtrabackup: innodb_log_file_size = 50331648InnoDB: Using atomics to ref count buffer pool pagesInnoDB: The InnoDB memory heap is disabledInnoDB: Mutexes and rw_locks use GCC atomic builtinsInnoDB: Memory barrier is not usedInnoDB: Compressed tables use zlib 1.2.3InnoDB: Not using CPU crc32 instructionsInnoDB: Initializing buffer pool, size = 100.0MInnoDB: Completed initialization of buffer poolInnoDB: Setting log file ./ib_logfile101 size to 48 MBInnoDB: Setting log file ./ib_logfile1 size to 48 MBInnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0InnoDB: New log files created, LSN=731724574InnoDB: Highest supported file format is Barracuda.InnoDB: 128 rollback segment(s) are active.InnoDB: Waiting for purge to startInnoDB: 5.6.24 started; log sequence number 731724812xtrabackup: starting shutdown with innodb_fast_shutdown = 1InnoDB: FTS optimize thread exiting.InnoDB: Starting shutdown...InnoDB: Shutdown completed; log sequence number 731724822151106 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 operationIMPORTANT: 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_logfile0151106 11:07:40 [01] ...done151106 11:07:40 [01] Copying ib_logfile1 to /var/lib/mysql/ib_logfile1151106 11:07:41 [01] ...done151106 11:07:41 [01] Copying ibdata1 to /var/lib/mysql/ibdata1151106 11:07:45 [01] ...done151106 11:07:45 [01] Copying ./xtrabackup_info to /var/lib/mysql/xtrabackup_info151106 11:07:45 [01] ...done151106 11:07:45 [01] Copying ./mysql/slave_master_info.ibd to /var/lib/mysql/mysql/slave_master_info.ibd151106 11:07:45 [01] ...done[... ...]151106 11:07:57 [01] Copying ./t/db.opt to /var/lib/mysql/t/db.opt151106 11:07:57 [01] ...done151106 11:07:57 [01] Copying ./t/t.frm to /var/lib/mysql/t/t.frm151106 11:07:57 [01] ...done151106 11:07:57 completed OK![root@localhost mysql]# pwd/var/lib/mysql[root@localhost mysql]# lsaazj ibdata1 ib_logfile0 ib_logfile1 mysql performance_schema t xtrabackup_binlog_pos_innodb xtrabackup_info
可以看到恢复之后，没有 binlog 文件盒index文件
启动myqld之前需要修改权限：
[root@localhost mysql]# ls -ltotal 176164drwx------ 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.logdrwx------ 2 root root 4096 Nov 6 11:07 mysql-rw-rw---- 1 mysql mysql 0 Nov 6 11:12 mysql-bin.indexdrwx------ 2 root root 4096 Nov 6 11:07 performance_schemadrwx------ 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 -ltotal 176164drwx------ 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.logdrwx------ 2 mysql mysql 4096 Nov 6 11:07 mysql-rw-rw---- 1 mysql mysql 0 Nov 6 11:12 mysql-bin.indexdrwx------ 2 mysql mysql 4096 Nov 6 11:07 performance_schemadrwx------ 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 operationIMPORTANT: 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 server151106 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.sockUsing server version 5.6.26-loginnobackupex 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/mysqlxtrabackup: open files limit requested 0, set to 10240xtrabackup: using the following InnoDB configuration:xtrabackup: innodb_data_home_dir = ./xtrabackup: innodb_data_file_path = ibdata1:12M:autoextendxtrabackup: innodb_log_group_home_dir = ./xtrabackup: innodb_log_files_in_group = 2xtrabackup: innodb_log_file_size = 50331648151106 11:33:16 >> log scanned up to (732153217)xtrabackup: Generating a list of tablespacesxtrabackup: using the full scan for incremental backup151106 11:33:17 [01] Copying ./ibdata1 to /backup/xtrabackup/incr1//2015-11-06_11-33-16/ibdata1.delta151106 11:33:17 >> log scanned up to (732153217)151106 11:33:18 [01] ...done151106 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.delta151106 11:33:18 [01] ...done151106 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.delta151106 11:33:30 [01] ...done151106 11:33:31 [01] Copying ./aazj/lx_test.ibd to /backup/xtrabackup/incr1//2015-11-06_11-33-16/aazj/lx_test.ibd.delta151106 11:33:31 >> log scanned up to (732231774)151106 11:33:32 [01] ...done151106 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.delta151106 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.delta151106 11:33:42 [01] ...done151106 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.delta151106 11:33:48 [01] ...done151106 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 files151106 11:33:48 [01] Copying ./mysql/columns_priv.frm to /backup/xtrabackup/incr1//2015-11-06_11-33-16/mysql/columns_priv.frm151106 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.frm151106 11:33:51 [01] ...done151106 11:33:51 Finished backing up non-InnoDB tables and files151106 11:33:51 [00] Writing xtrabackup_binlog_info151106 11:33:51 [00] ...done151106 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 TABLES151106 11:33:51 All tables unlocked151106 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.cnf151106 11:33:51 [00] ...done151106 11:33:51 [00] Writing xtrabackup_info151106 11:33:51 [00] ...donextrabackup: 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=digdeep151106 11:43:22 innobackupex: Starting the backup operationIMPORTANT: 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 server151106 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.sockUsing server version 5.6.26-loginnobackupex 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/mysqlxtrabackup: open files limit requested 0, set to 10240xtrabackup: using the following InnoDB configuration:xtrabackup: innodb_data_home_dir = ./xtrabackup: innodb_data_file_path = ibdata1:12M:autoextendxtrabackup: innodb_log_group_home_dir = ./xtrabackup: innodb_log_files_in_group = 2xtrabackup: innodb_log_file_size = 50331648151106 11:43:23 >> log scanned up to (732501432)xtrabackup: Generating a list of tablespaces151106 11:43:23 [01] Copying ./ibdata1 to /backup/xtrabackup/incr2/2015-11-06_11-43-22/ibdata1.delta151106 11:43:23 [01] ...done151106 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.delta151106 11:43:24 [01] ...done151106 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.delta151106 11:43:25 [01] ...done151106 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.delta151106 11:43:25 [01] ...done151106 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.delta151106 11:43:26 [01] ...done151106 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.delta151106 11:43:26 [01] ...done151106 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.delta151106 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.frm151106 11:43:50 [01] ...done151106 11:43:50 Finished backing up non-InnoDB tables and files151106 11:43:50 [00] Writing xtrabackup_binlog_info151106 11:43:50 [00] ...done151106 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 TABLES151106 11:43:50 All tables unlocked151106 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.cnf151106 11:43:50 [00] ...done151106 11:43:50 [00] Writing xtrabackup_info151106 11:43:50 [00] ...donextrabackup: 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=digdeep151106 14:48:26 innobackupex: Starting the apply-log operationIMPORTANT: 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:autoextendxtrabackup: innodb_log_group_home_dir = ./xtrabackup: innodb_log_files_in_group = 1xtrabackup: innodb_log_file_size = 2097152xtrabackup: using the following InnoDB configuration for recovery:xtrabackup: innodb_data_home_dir = ./xtrabackup: innodb_data_file_path = ibdata1:10M:autoextendxtrabackup: innodb_log_group_home_dir = ./xtrabackup: innodb_log_files_in_group = 1xtrabackup: innodb_log_file_size = 2097152xtrabackup: 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 pagesInnoDB: The InnoDB memory heap is disabledInnoDB: Mutexes and rw_locks use GCC atomic builtinsInnoDB: Memory barrier is not usedInnoDB: Compressed tables use zlib 1.2.3InnoDB: Not using CPU crc32 instructionsInnoDB: Initializing buffer pool, size = 100.0MInnoDB: Completed initialization of buffer poolInnoDB: 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 pagesInnoDB: from the doublewrite buffer...xtrabackup: Last MySQL binlog file position 117940, file name mysql-bin.000015xtrabackup: starting shutdown with innodb_fast_shutdown = 1InnoDB: Starting shutdown...InnoDB: Shutdown completed; log sequence number 731724832151106 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=digdeep151106 14:51:08 innobackupex: Starting the apply-log operationIMPORTANT: 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:autoextendxtrabackup: innodb_log_group_home_dir = /backup/xtrabackup/incr1/2015-11-06_11-33-16/xtrabackup: innodb_log_files_in_group = 1xtrabackup: innodb_log_file_size = 2097152xtrabackup: Generating a list of tablespacesxtrabackup: page size for /backup/xtrabackup/incr1/2015-11-06_11-33-16//ibdata1.delta is 16384 bytesApplying /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 bytesApplying /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 bytesApplying /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 bytesApplying /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 bytesApplying /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:autoextendxtrabackup: innodb_log_group_home_dir = /backup/xtrabackup/incr1/2015-11-06_11-33-16/xtrabackup: innodb_log_files_in_group = 1xtrabackup: innodb_log_file_size = 2097152xtrabackup: 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 pagesInnoDB: The InnoDB memory heap is disabledInnoDB: Mutexes and rw_locks use GCC atomic builtinsInnoDB: Memory barrier is not usedInnoDB: Compressed tables use zlib 1.2.3InnoDB: Not using CPU crc32 instructionsInnoDB: Initializing buffer pool, size = 100.0MInnoDB: Completed initialization of buffer poolInnoDB: Highest supported file format is Barracuda.InnoDB: Log scan progressed past the checkpoint lsn 732153217InnoDB: Database was not shutdown normally!InnoDB: Starting crash recovery.InnoDB: Reading tablespace information from the .ibd files...InnoDB: Restoring possible half-written data pagesInnoDB: 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 99InnoDB: Apply batch completedxtrabackup: Last MySQL binlog file position 157搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：使用 xtrabackup 进行MySQL数据库物理备份_MySQL
