[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 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=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 157893, file name mysql-bin.000001
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 732501432
151106 14:51:12 [01] Copying /backup/xtrabackup/incr1/2015-11-06_11-33-16/mysql/columns_priv.frm to ./mysql/columns_priv.frm
151106 14:51:12 [01] ...done
151106 14:51:12 [01] Copying /backup/xtrabackup/incr1/2015-11-06_11-33-16/mysql/user.MYI to ./mysql/user.MYI
151106 14:51:12 [01] ...done
151106 14:51:12 [01] Copying /backup/xtrabackup/incr1/2015-11-06_11-33-16/mysql/general_log.frm to ./mysql/general_log.frm
151106 14:51:12 [01] ...done
[... ...]
151106 14:51:14 [01] Copying /backup/xtrabackup/incr1/2015-11-06_11-33-16/t/city.frm to ./t/city.frm
151106 14:51:14 [01] ...done
151106 14:51:14 [01] Copying /backup/xtrabackup/incr1/2015-11-06_11-33-16/t/db.opt to ./t/db.opt
151106 14:51:14 [01] ...done
151106 14:51:14 [01] Copying /backup/xtrabackup/incr1/2015-11-06_11-33-16/t/t.frm to ./t/t.frm
151106 14:51:14 [01] ...done
151106 14:51:14 completed OK!
3> 應用第二次(最後一次)增量備份的redo log，並且回滾進行崩潰恢復過程(沒有--redo-only選項): [root@localhost ~]# innobackupex --apply-log /backup/xtrabackup/full/2015-11-06_11-29-51/ --incremental-dir=/backup/xtrabackup/incr2/2015-1 1-06_11-43-22/ --user=bkpuser --password=digdeep
[root@localhost ~]# innobackupex --apply-log /backup/xtrabackup/full/2015-11-06_11-29-51/ --incremental-dir=/backup/xtrabackup/incr2/2015-1 1-06_11-43-22/ --user=bkpuser --password=digdeep
14:55:43 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 732501432 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=(732501432)
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/incr2/2015-11-06_11-43-22/
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/incr2/2015-11-06_11-43-22//ibdata1.delta is 16384 bytes
Applying /backup/xtrabackup/incr2/2015-11-06_11-43-22//ibdata1.delta to ./ibdata1...
xtrabackup: page size for /backup/xtrabackup/incr2/2015-11-06_11-43-22//mysql/innodb_index_stats.ibd.delta is 16384 bytes
Applying /backup/xtrabackup/incr2/2015-11-06_11-43-22//mysql/innodb_index_stats.ibd.delta to ./mysql/innodb_index_stats.ibd...
[... ...]
Applying /backup/xtrabackup/incr2/2015-11-06_11-43-22//t/city.ibd.delta to ./t/city.ibd...
xtrabackup: page size for /backup/xtrabackup/incr2/2015-11-06_11-43-22//t/t.ibd.delta is 16384 bytes
Applying /backup/xtrabackup/incr2/2015-11-06_11-43-22//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/incr2/2015-11-06_11-43-22/
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 732501432
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 732777035 (14%)
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
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6.24 started; log sequence number 732777035
xtrabackup: Last MySQL binlog file position 254400, file name mysql-bin.000001
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 732817046
14:55:47 [01] Copying /backup/xtrabackup/incr2/2015-11-06_11-43-22/mysql/columns_priv.frm to ./mysql/columns_priv.frm
14:55:47 [01] ...done
14:55:47 [01] Copying /backup/xtrabackup/incr2/2015-11-06_11-43-22/mysql/user.MYI to ./mysql/user.MYI
14:55:47 [01] ...done
[... ...]
14:55:50 [01] Copying /backup/xtrabackup/incr2/2015-11-06_11-43-22/t/db.opt to ./t/db.opt
14:55:50 [01] ...done
14:55:50 [01] Copying /backup/xtrabackup/incr2/2015-11-06_11-43-22/t/t.frm to ./t/t.frm
14:55:50 [01] ...done
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/incr2/2015-11-06_11-43-22/
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 /backup/xtrabackup/incr2/2015-11-06_11-43-22/ib_logfile101 size to 48 MB
InnoDB: Setting log file /backup/xtrabackup/incr2/2015-11-06_11-43-22/ib_logfile1 size to 48 MB
InnoDB: Renaming log file /backup/xtrabackup/incr2/2015-11-06_11-43-22/ib_logfile101 to /backup/xtrabackup/incr2/2015-11-06_11-43-22/ib_logfile0
InnoDB: New log files created, LSN=732817046
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 732817420
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 732817430
14:55:54 completed OK!
[root@localhost ~]# 然後 --copy-back: 先關閉mysqld: mysqladmin -uroot -pxxx shutdown [root@localhost mysql]# innobackupex --copy-back /backup/xtrabackup/full/2015-11-06_11-29-51/ --user=bkpuser --password=digdeep
[root@localhost mysql]# innobackupex --copy-back /backup/xtrabackup/full/2015-11-06_11-29-51/ --user=bkpuser --password=digdeep
15:10:23 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)
15:10:23 [01] Copying ibdata1 to /var/lib/mysql/ibdata1
15:10:28 [01] ...done
15:10:28 [01] Copying ./xtrabackup_info to /var/lib/mysql/xtrabackup_info
15:10:28 [01] ...done
[... ...]
15:10:41 [01] ...done
15:10:41 [01] Copying ./t/db.opt to /var/lib/mysql/t/db.opt
15:10:41 [01] ...done
15:10:41 [01] Copying ./t/t.frm to /var/lib/mysql/t/t.frm
15:10:41 [01] ...done
15:10:42 completed OK!
修改權限： chown -R mysql:mysql /var/lib/mysql 啟動：mysqld_safe --user=mysql & 最後驗證還原成功。 4. 部分備份 需要啟用 innodb_file_per_table，5.6默認啟用。另外在還原時，prepare之後，並不能直接 --copy-back，而只能一個表一個表的import來還原。 [root@localhost xtrabackup]# innobackupex --databases t /backup/xtrabackup/ --user=bkpuser --password=digdeep
[root@localhost xtrabackup]# innobackupex --databases t /backup/xtrabackup/ --user=bkpuser --password=digdeep
15:39:34 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!".
15:39:35 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).
15:39:35 version_check Connected to MySQL server
15:39:35 version_check Executing a version check against the server...
15:39:35 version_check Done.
15:39:35 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
15:39:35 >> log scanned up to (732817942)
xtrabackup: Generating a list of tablespaces
15:39:35 [01] Copying ./ibdata1 to /backup/xtrabackup//2015-11-06_15-39-34/ibdata1
15:39:36 >> log scanned up to (732817942)
15:39:37 >> log scanned up to (732817942)
15:39:38 [01] ...done
15:39:38 [01] Copying ./t/city.ibd to /backup/xtrabackup//2015-11-06_15-39-34/t/city.ibd
15:39:38 [01] ...done
15:39:38 [01] Copying ./t/t.ibd to /backup/xtrabackup//2015-11-06_15-39-34/t/t.ibd
15:39:38 [01] ...done
15:39:38 >> log scanned up to (732817942)
Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
15:39:38 Executing FLUSH TABLES WITH READ LOCK...
15:39:38 Starting to backup non-InnoDB tables and files
15:39:38 [01] Skipping ./mysql/slave_master_info.ibd.
15:39:38 [01] Skipping ./mysql/columns_priv.frm.
[... ...]
15:39:38 [01] Skipping ./aazj/model_buyers_credit.ibd.
15:39:38 [01] Skipping ./aazj/Users.frm.
15:39:38 [01] Skipping ./aazj/model_recruiting_program.ibd.
15:39:38 [01] Skipping ./aazj/model_model.ibd.
15:39:38 [01] Skipping ./aazj/Customer.frm.
15:39:38 [01] Skipping ./performance_schema/events_waits_summary_by_host_by_event_name.frm.
[... ...]
15:39:38 [01] Skipping ./performance_schema/events_statements_summary_by_account_by_event_name.frm.
15:39:38 [01] Copying ./t/city.frm to /backup/xtrabackup//2015-11-06_15-39-34/t/city.frm
15:39:38 [01] ...done
15:39:38 [01] Copying ./t/db.opt to /backup/xtrabackup//2015-11-06_15-39-34/t/db.opt
15:39:38 [01] ...done
15:39:38 [01] Copying ./t/t.frm to /backup/xtrabackup//2015-11-06_15-39-34/t/t.frm
15:39:38 [01] ...done
15:39:38 Finished backing up non-InnoDB tables and files
15:39:38 [00] Writing xtrabackup_binlog_info
15:39:38 [00] ...done
15:39:38 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '732817942'
xtrabackup: Stopping log copying thread.
.151106 15:39:38 >> log scanned up to (732817942)
15:39:39 Executing UNLOCK TABLES
15:39:39 All tables unlocked
15:39:39 Backup created in directory '/backup/xtrabackup//2015-11-06_15-39-34'
MySQL binlog position: filename 'mysql-bin.000001', position '120'
15:39:39 [00] Writing backup-my.cnf
15:39:39 [00] ...done
15:39:39 [00] Writing xtrabackup_info
15:39:39 [00] ...done
xtrabackup: Transaction log of lsn (732817942) to (732817942) was copied.
15:39:39 completed OK!
數據庫 t 中只有兩個表：city, t 都被備份了。 下面我們來看如何還原： 4.1 部分prepare: [root@localhost xtrabackup]# innobackupex --apply-log --export /backup/xtrabackup/2015-11-06_15-39-34/ --user=bkpuser --password=digdeep
[root@localhost xtrabackup]# innobackupex --apply-log --export /backup/xtrabackup/2015-11-06_15-39-34/ --user=bkpuser --password=digdeep
15:49:43 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: auto-enabling --innodb-file-per-table due to the --export option
xtrabackup: cd to /backup/xtrabackup/2015-11-06_15-39-34/
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(732817942)
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 732817430 and 732817430 in ibdata files do not match the log sequence number 732817942 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...
InnoDB: Table aazj/Accounting_journal in the InnoDB data dictionary has tablespace id 117, but tablespace with that id or name does not exi st. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL auto matically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
[... ...]
InnoDB: Table mysql/slave_relay_log_info in the InnoDB data dictionary has tablespace id 3, but tablespace with that id or name does not ex ist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL aut omatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table mysql/slave_worker_info in the InnoDB data dictionary has tablespace id 5, but tablespace with that id or name does not exist . Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automa tically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6.24 started; log sequence number 732817942
xtrabackup: export option is specified.
xtrabackup: export metadata of table 't/city' to file `./t/city.exp` (2 indexes)
xtrabackup: name=PRIMARY, id.low=267, page=3
xtrabackup: name=PK_CITY, id.low=268, page=4
xtrabackup: export metadata of table 't/t' to file `./t/t.exp` (1 indexes)
xtrabackup: name=GEN_CLUST_INDEX, id.low=131, page=3
xtrabackup: Last MySQL binlog file position 254400, file name mysql-bin.000001
xtrabackup: starting shutdown with innodb_fast_shutdown = 0
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 732957307
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=732957307
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 732957708
xtrabackup: starting shutdown with innodb_fast_shutdown = 0
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 732957718
15:49:49 completed OK!
4.2 下面我們將其 import 到一個新的數據庫中： (root@localhost)[t]mysql>create database partial; (root@localhost)[t]mysql>use partial; Database changed (root@localhost)[partial]mysql>create table city like t.city; (root@localhost)[partial]mysql>alter table partial.city discard tablespace; 然後將 city.exp 和 city.ibd 拷貝到 /var/lib/mysql/partial/ 目錄下，並修改權限： [root@localhost t]# cp city.exp city.ibd /var/lib/mysql/partial/ [root@localhost partial]# chown -R mysql:mysql /var/lib/mysql 然後 (root@localhost)[aazj]mysql>alter table partial.city import tablespace; Query OK, 0 rows affected, 1 warning (0.11 sec) (root@localhost)[aazj]mysql>select count(*) from partial.city; +----------+ | count(*) | +----------+ | 3285 | +----------+ 1 row in set (0.01 sec) 可以看到import 成功了。部分恢復成功。 低於t表也同樣操作就行了。 (root@localhost)[partial]mysql>select count(*) from t; +----------+ | count(*) | +----------+ | 11 | +----------+ 1 row in set (0.00 sec) 可以看到，這種部分備份/恢復，操作起來比較麻煩，步驟比較多，還需要一個表一個表的處理。對少數表處理還可以，如果很多表，就不方面了。 5. point-in-time 恢復 利用全備、增量備份最多只能恢復到全備完成時的那一刻，或者增量備份完成時的那一刻的數據。備份之後產生的數據，我們需要結合binlog，來恢復。我們可以從binlog中獲得innobackupex最後一次備份完成時的position，它之後的所有的sql，應用完，這些sql，就能將數據庫恢復到最新的狀態，或者我們想要的某個時間的狀態。 1> 先來一個全備： [root@localhost xtrabackup]# innobackupex /backup/xtrabackup/full --user=bkpuser --password=digdeep 2> 再來一個增量： 將t表數據刪除一行：delete from t where i=11; [root@localhost xtrabackup]# innobackupex --incremental /backup/xtrabackup/incr1/ --incremental-basedir=/backup/xtrabackup/full/2015-11-06_16-26-08 --user=bkpuser --password=digdeep 3> 再來一個增量： 將t表數據刪除一行：delete from t where i=10; [root@localhost xtrabackup]# innobackupex --incremental /backup/xtrabackup/incr2/ --incremental-basedir=/backup/xtrabackup/incr1/2015-11-06_16-31-13/ --user=bkpuser --password=digdeep 4> 備份完成之後，我們再來操作 t 表： (root@localhost)[t]mysql>delete from t where i>3; 此時的狀態： (root@localhost)[t]mysql>show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 927 | | mysql-bin.000002 | 688 | +------------------+-----------+ 2 rows in set (0.00 sec) (root@localhost)[t]mysql>show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000002 | 688 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) 假設此時數據庫表數據所在的磁盤發生故障，但是 binlog 文件是好的。那麼此時，我們就可以使用上面的全備、增量備份、還有binlog文件一起來將數據庫恢復到磁盤發生故障那一刻的最新狀態來。 5> 首先從全備、增量備份得到最後一次備份完成時的數據： 1）應用全備的redo log: [root@localhost xtrabackup]# innobackupex --apply-log --redo-only /backup/xtrabackup/full/2015-11-06_16-26-08 --user=bkpuser --password=digdeep 2）應用第一次增量備份的redo log: [root@localhost xtrabackup]# innobackupex --apply-log --redo-only /backup/xtrabackup/full/2015-11-06_16-26-08 --incremental-dir=/backup/xtr abackup/incr1/2015-11-06_16-31-13/ --user=bkpuser --password=digdeep 3）應用第二次增量備份的 redo log，並且僅限回滾(去掉 --redo-only選項)： [root@localhost xtrabackup]# innobackupex --apply-log /backup/xtrabackup/full/2015-11-06_16-26-08 --incremental-dir=/backup/xtrabackup/incr2/2015-11-06_16-33-57/ --user=bkpuser --password=digdeep 此時已經恢復到了最後一次備份完成時的狀態了。 我們看一下最後一次增量備份時的 xtrabackup_binlog_info 文件信息： [root@localhost xtrabackup]# cat incr2/2015-11-06_16-33-57/xtrabackup_binlog_info mysql-bin.000002 482 可以看到對應的binlog postion為：mysql-bin.000002 482 而崩潰那一刻的binlog postion為： mysql-bin.000002 688 所以我們需要應用 mysql-bin.000002 文件的 482 到 688之間的sql： 4）先 --copy-back [root@localhost mysql]# innobackupex --copy-back /backup/xtrabackup/full/2015-11-06_16-26-08 --user=bkpuser --password=digdeep 修改權限： [root@localhost ~]# chown -R mysql:mysql /var/lib/mysql 啟動msyqld: mysqld_safe --user=mysql & 然後驗證，t 表的數據，應該有i<10 & i>3 的數據： (root@localhost)[t]mysql>select * from t; +------+ | i | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | +------+ 9 rows in set (0.00 sec) 如我們所期待的結果一樣。說明到此時，前面的操作完全是正確的。 5）應用 mysql-bin.000002 文件的 482 到 688之間的sql [root@localhost mysql]# mysqlbinlog /backup/xtrabackup/mysql-bin.000002 --start-position=482 > bin.sql (root@localhost)[(none)]mysql>source /var/lib/mysql/bin.sql 然後在查看 t 表數據： (root@localhost)[t]mysql>select * from t; +------+ | i | +------+ | 1 | | 2 | | 3 | +------+ 3 rows in set (0.00 sec) 一切完美完成，數據庫被我們回復到了最新的狀態。 6. innobackupex 選項優化/最佳實踐 6.1 優化FTWRL鎖： 在備份非innodb數據庫時，會使用：flush tables with read lock 全局鎖鎖住整個數據庫。如果數據庫中有一個長查詢在運行，那麼FTWRL就不能獲得，會被阻塞，進而阻塞所有的DML操作。此時即使我們kill掉FTWRL全局鎖也是無法從阻塞中恢復出來的。另外在我們成功的獲得了FTWRL全局鎖之後，在copy非事務因為的文件的過程中，整個數據庫也是被鎖住的。所以我們應該讓FTWRL的過程盡量的短。(在copy非事務引擎數據的文件時，會阻塞innodb事務引擎。當然也會阻塞所有其他非事務引擎。) 1> 防止阻塞： innobackupex 提供了多個選項來避免發生阻塞： --ftwrl-wait-timeout=# 替換 --lock-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=# 替換 --lock-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. --lock-wait-timeout=60 該選項表示：我們在FTWRL時，如果有長查詢，那麼我們可以最多等待60S的時間，如果60秒之內長查詢執行完了，我們就可以成功執行FTWRL了，如果60秒之內沒有執行完，那麼就直接報錯退出，放棄。默認值為0 --lock-wait-threshold=10 該選項表示運行了多久的時間的sql當做長查詢；對於長查詢最多再等待 --lock-wait-timeout 秒。 --kill-long-queries-timeout=10 該選項表示發出FTWRL之後，再等待多時秒，如果還有長查詢，那麼就將其kill掉。默認為0，not to kill. --kill-long-query-type={all|select} 該選項表示我們僅僅kill select語句，還是kill所有其他的類型的長sql語句。 這幾個選項，我們沒有必要都是有，一般僅僅使用 --lock-wait-timeout=60 就行了。 注意 --lock-* 和 --kill-* 選項的不同，一個是等待多時秒再來執行FTWRL，如果還是不能成功執行就報錯退出；一個是已經執行了FTWRL，超時就進行kill。 2> 縮短FTWRL全局鎖的時間： --rsync 使用該選項來縮短備份非事務引擎表的鎖定時間，如果需要備份的數據庫和表數量很多時，可以加快速度。 --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. 3> 並行優化： --parallel=# 在備份階段，壓縮/解壓階段，加密/解密階段，--apply-log，--copy-back 階段都可以並行 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. 4> 內存優化： --use-memory=# 在crash recovery 階段，也就是 --apply-log 階段使用該選項 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. 3> 備份slave: --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. --safe-slave-backup-timeout=# How many seconds --safe-slave-backup should wait for Slave_open_temp_tables to become zero. (default 300) --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. 7. 備份原理： 1）innobackupex 是perl寫的腳本，它調用xtrabackup來備份innodb數據庫。而xtrabackup是C語言寫的程序，它調用了innodb的函數庫和mysql客戶端的函數庫。innodb函數庫提供了向數據文件應用的redo log的功能，而mysql客戶端函數庫提供了解析命令行參數的功能。innobackupex備份innodb數據庫的功能，都是通過調用 xtrabackup --backup和xtrabackup --prepare來完成的。我們沒有必要直接使用xtrabackup來備份，通過innobackupex更方便。xtrabakup 通過跳轉到datadir目錄，然後通過兩個線程來完成備份過程： 1> log-copy thread: 備份開始時，該後台線程一直監控redo log(每秒check一次redo log)，將redo log的修改復制到備份之後的文件 xtrabackup_logfile 中。如果redo log生成極快時，有可能log-copy線程跟不上redo log的產生速度，那麼在redo log文件切換進行覆蓋時，xtrabakcup會報錯。 2> data-file-copy thread: 前後有一個復制data file的線程，注意這裡並不是簡單的復制，而是調用了innodb函數庫，像innodb數據庫那樣打開數據文件，進行讀取，然後每次復制一個page，然後對page進行驗證，如果驗證錯誤，會最多重復十次。 當數據文件復制完成時，xtrabackup 停止log-copy 線程，並建立一個文件 xtrabackup_checkpoints記錄備份的類型，開始時的lsn和結束時的lsn等信息。 而備份生成的 xtrabackup_binlog_info 文件則含義備份完成時對應的binlog的position信息，類似於：mysql-bin.000002 120 在備份開始時記錄下LSN，然後一個線程復制數據文件，一個線程監控redo log，復制在備份過程中新產生的redo log。雖然我們的到的數據文件顯然不是一致性的，但是利用innodb的crash-recovery功能，應用備份過程中產生的redo log文件，就能得到備份完成時那一刻對應的一致性的數據。 注意復制數據文件分成了兩個過程： 一個是復制innodb事務引擎的數據文件，是不需要持有鎖的；另一個是復制非事務引擎的數據文件和table的定義文件.frm，復制這些文件時，是需要先通過FTWRL，然後在進行復制的，所以會導致整個數據庫被阻塞。 增量備份時，是通過對表進行全掃描，比較LSN，如果該page的LSN大於上一次別分時的LSN，那麼就將該page復制到table_name.ibd.delta文件中。回復時.delta會和redo log應用到全備是的數據文件中。 增量備份在恢復時，除了最後一次增量備份文件之外，其它的增量備份在應用時，只能前滾，不能執行回滾操作，因為沒有提交的事務，可能在下一個增量備份中進行了提交，如果你在上一個增量備份時回滾了，那麼下一個增量備份應用時，顯然就報錯了，因為他無法提交事務，該事務以及被回滾了。 8. 總結： 1）權限： 備份需要兩個層面的權限，Linux層面的權限，mysql層面的權限。 2）全備和恢復 全備：innobackupex /backup/xtrabackup/full --user=bkpuser --password=digdeep 應用日志進行prepare: innobackupex --apply-log /backup/xtrabackup/full/2015-11-05_22-38-55/ --user=bkpuser --password=digdeep 關閉mysqld: copy-back: innobackupex --copy-back /backup/xtrabackup/full/2015-11-05_22-38-55/ --user=bkpuser --password=digdeep 修改權限：chown -R mysql:mysql /var/lib/mysql 3）增量備份和恢復： 全備： innobackupex --user=bkpuser --password=digdeep /backup/xtrabackup/full 第一次增量備份： innobackupex --incremental /backup/xtrabackup/incr1/ --incremental-basedir=/backup/xtrabackup/full/2015-11-06_11-29-51/ --user=bkpuser --password=digdeep 第二次增量備份： innobackupex --incremental /backup/xtrabackup/incr2 --incremental-basedir=/backup/xtrabackup/incr1/2015-11-06_11-33-16/ --user=bkpuser --password=digdeep 恢復： 應用全備redo log: innobackupex --apply-log --redo-only /backup/xtrabackup/full/2015-11-06_11-29-51/ --user=bkpuser --password=digdeep 應用第一次增量備份的redo log: 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 應用第二次(最後一次)增量備份的redo log: innobackupex --apply-log /backup/xtrabackup/full/2015-11-06_11-29-51/ --incremental-dir=/backup/xtrabackup/incr2/2015-11-06_11-43-22/ --user=bkpuser --password=digdeep 關閉mysqld, innobackupex --copy-back /backup/xtrabackup/full/2015-11-06_11-29-51/ --user=bkpuser --password=digdeep 4）部分備份 innobackupex --databases t /backup/xtrabackup/ --user=bkpuser --password=digdeep innobackupex --apply-log --export /backup/xtrabackup/2015-11-06_15-39-34/ --user=bkpuser --password=digdeep 新建表結構：create table city like t.city; alter table partial.city discard tablespace; 然後將 city.exp 和 city.ibd 拷貝到 /var/lib/mysql/partial/ 目錄下，並修改權限：chown -R mysql:mysql /var/lib/mysql alter table partial.city import tablespace; 5）point-in-time 恢復 在--copy-back之後，引用binlog文件 mysqlbinlog /backup/xtrabackup/mysql-bin.000002 --start-position=482 > bin.sql (root@localhost)[(none)]mysql>source bin.sql 6） innobackupex 選項優化/最佳實踐 --ftwrl-wait-timeout=60 防止發生阻塞 --rsync 減少FTWRL時間 縮短備份非事務引擎表的鎖定時間 --parallel=4 開並行 --use-memory=4G crash recovery 期間使用的內存
