Xtrabackup Backup and restore MySQL
Xtrabackup has two main tools: Innobackupex and Xtrabackup,xtrabackup can only back up InnoDB and xtradb data tables, Innobackupex encapsulates Xtrabackup, You can back up MyISAM data tables.
The MySQL database itself provides tools that do not support true incremental backups, and binary log recovery is a point-in-time (point-in-time) recovery rather than an incremental backup. The Xtrabackup tool supports incremental backups of the InnoDB storage Engine, which works as follows:
1. Complete a full backup first and record the LSN (logsequence number) of the checkpoint at this point.
2. In the process incremental backup, compare the LSN of each page in the table space to the LSN at the time of the last backup, and if so, back up the page and record the LSN of the current checkpoint.
1. Download xtrabackup (download binary version without installation)
# wget http://www.percona.com/redir/downloads/XtraBackup/XtraBackup-1.5/Linux/binary/x86_64/xtrabackup-1.5.tar.gz
# tar Zxvfxtrabackup-1.5.tar.gz-c/usr/local/
Source installation process specific refer to the source directory under the BUILD.txt file.
The 2.xtrabackup options note is as follows:
--PRINT-DEFAULTS Displays the default options. Xtrabackup The default is to read the my.cnf file, the reading order is/etc/my.cnf/etc/mysql/my.cnf/usr/local/etc/my.cnf ~/.MY.CNF
--no-defaults ignore any my.cnf file options
--defaults-file= #读取指定的my. cnf file
--defaults-extra-file= #再读取另外一个文件
--target-dir=name destination directory, default directory in./xtrabackup_backupfiles/, relative to DataDir directory
--backup Backup
--stats Compute DATADIR Catalog Statistics Information
--prepare recovery from backup
--export Create a file to import to another database on recovery
--apply-log-only on restore, stop the recovery process without LSN using only log
--print-param Print Out parameters
--use-memory= #buffer_pool_size值
--suspend-at-end when backing up, create xtrabackup_suspended files until the backup is complete and erase
Total--throttle=# Limit IO
--log-stream record standard output information xtrabackup_logfile
--extra-lsndir=name only applies to backup, save another xtrabackup_checkpoints file
--incremental-lsn=name is only available for backup, incremental backup
--incremental-basedir=name only available for backup, incremental backup directory
--incremental-dir=name only applies to prepare, restoring. delta files and log files under the specified directory
--tables=name Filter Certain tables
--tables_file=name Filter database.table list file
--create-ib-logfile
-H,--datadir=name DataDir directory
-T,--tmpdir=name tmpdir Directory
--parallel=# defaults to 1. The number of parallel threads that transmit data files. No effect of any flow patterns
--innodb_* about InnoDB Parameters
The 3.innobackupex-1.5.1 options note is as follows:
INNOBACKUPEX-1.5.1[--SLEEP=MS] [--compress[=level]] [--include=regexp] [--user=name]
[--password=word] [--port=port] [--socket=socket] [--no-timestamp]
[--ibbackup=ibbackup-binary] [--slave-info] [--stream=tar]
[--SCPOPT=OPTIONS-FOR-SCP]
[--defaults-file=my. CNF]
[--databases=list] [--remote-host=hostname] [--no-lock] Backup-root-dir
Hot standby of the database. The backup root directory is created by default with the current timestamp. The above command fully backs up all MyISAM and InnoDB engine tables and indexes. Contains. frm,. MRG,. MyD,. Myi,. TRG,. TRN,. Arm. Arz,. Opt,. Par and InnoDB data and log files. Use the MySQL client program to connect to the MySQL server and run ibbackup (InnoDB hot standby) as a child process.
Innobackupex-1.5.1--apply-log [--USE-MEMORY=MB] [--uncompress] [--defaults-file=my. CNF]
[--export] [--redo-only] [--ibbackup=ibbackup-binary] Backup-dir
Restore from backup, run Ibbackup subprocess, but cannot connect to the database. Use Backup-dir/ibbackup_logfile to recover InnoDB data files and create new InnoDB log files, as defined by BACKUP-DIR/BACKUP-MY.CNF.
Innobackupex-1.5.1--copy-back [--defaults-file=my. CNF] Backup-dir
Copy data, index and log files from the backup directory to the original DataDir directory.
--defaults-file=[my. CNF] This option is passed to the Xtrabackup subprocess to read the default option from the specified file
--apply-log recovery from backup.
--redo-only This option forces the rollback phase to be skipped and only redo. This is necessary if you want to use incremental changes after the backup.
--copy-back copy data and index files from backup directory to DataDir directory
--remote-host=hostname Backup to remote host, using SSH
--stream=[tar|cpio (notimplemented)] Specify backup standard output format
--tmpdir=directory default is the same as Tmpdir. After using the-remote-host or-stream parameters, the transfer log files are stored in the temporary directory
The--USE-MEMORY=MB option is passed to the Xtrabackup subprocess. Restore Use memory size
The--parallel=number-of-threads option is passed to the Xtrabackup subprocess, specifying the total number of data transfer threads. Default is 1
The--throttle=ios option is passed to the Xtrabackup subprocess, limiting the number of IO threads
The--SLEEP=MS option is passed to the Xtrabackup subprocess. How much MS time is paused per copy 1MB data
--compress[=level] option is passed to the Xtrabackup subprocess. Compression level in 0-9.1 fast compression, 9 optimal compression, 0 uncompressed. The default is 1.
The--INCLUDE=REGEXP option is passed to the Xtrabackup subprocess. Match using a regular
--databases=list Specify a BACKUP database
--tables-file=file
The--uncompress option is passed to the Xtrabackup subprocess. Do not compress compressed InnoDB data files
--export is only used with the prepare option. option is passed to the Xtrabackup child process.
--user=name
--password=word
--host=host
--port=port
--slave-info backup replication from the service side, the master-slave information is recorded in the Ibbackup_slave_info file
--socket=socket
--no-timestamp a new backup directory with the current timestamp name is not created under the backup root directory
--ibbackup=ibbackup-binaryibbackup binary Path
--no-lock prevents table-level locks. All is InnoDB engine table and does not relate to binary log position use
--scpopt=scp-options Specify the SCP parameter
4.innobackupex Backup
#./innobackupex-1.5.1/www/mybak/
A timestamp-named directory will be created in the/www/mybak/directory
--stream=tar will be compressed using the TAR4IBD. Note: In the decompression time must be coupled with the-I parameter decompression.
5. Recovery
#./mysqladminshutdown
# Rm-rfinnodb
#./innobackupex-1.5.1--apply-log/www/mybak/2011-04-11_15-28-02/
#./innobackupex-1.5.1–copy-back/www/mybak/2011-04-11_15-28-02/
# Chown–r Mysql.mysql/www/mydata
6.xtrabackup Backup
#./XTRABACKUP--DEFAULTS-FILE=/ETC/MY.CNF--backup--target-dir=/mydata/mybak/
# ls
Cacti ibdata1 nagios Test xtrabackup_checkpoints xtrabackup_logfile
7. Recovery
#./XTRABACKUP--DEFAULTS-FILE=/ETC/MY.CNF--prepare--target-dir=/mydata/mybak/
./xtrabackup Ver 1.5 Rev undefined for 5.1.53unknown-linux-gnu (x86_64)
xtrabackup:cdto/mydata/mybak/
Xtrabackup:this target seems to is not prepared yet.
Xtrabackup:xtrabackup_logfile detected:size=2097152, start_lsn= (732446480)
Xtrabackup:temporary instance for recovery is set as followings.
Xtrabackup:innodb_data_home_dir =./
Xtrabackup:innodb_data_file_path =ibdata1:10m:autoextend
Xtrabackup:innodb_log_group_home_dir =./
Xtrabackup:innodb_log_files_in_group = 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:theinnodb Memory heap is disabled
Innodb:mutexes and Rw_locks use GCC atomic builtins
innodb:compressed Tables Use zlib 1.2.3
InnoDB:Warning:innodb_file_io_threads is deprecated. Please useinnodb_read_io_threads and innodb_write_io_threads instead
11041116:49:25 Innodb:highest supported FileFormat is barracuda.
Innodb:logscan progressed past the checkpoint LSN 732446480
11041116:49:25 Innodb:database is not shutdown normally!
Innodb:starting crash recovery.
innodb:reading tablespace information from the ... ibd files ...
InnoDB:Doingrecovery:scanned up to log sequence number 733504186 (56)
11041116:49:25 innodb:starting A apply Batchof log records to the database ...
Innodb:progress in Percents:13, 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 3132 33 34 35 36 37 38 39 40 41 42 43 4 4 45 46 47 48 49 50 51 52 53 54 55 56 5758 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 86 87 88 89 90 91 92 93 94 95 96 97 98-99
Innodb:applybatch completed
Innodb:lastmysql binlog file position 0 560768957, file name./mysql-bin.000001
11041116:49:25 Percona xtradb (http://www.percona.com) 1.0.13-12.4 started; Logsequence number 733504186
[Notice (again)]
If use binary log and don ' t to use the any HACKOF group commit,
The binary log position seems to be:
Innodb:lastmysql binlog file position 0 560768957, file name./mysql-bin.000001
xtrabackup:starting shutdown with Innodb_fast_shutdown = 1
11041116:49:32 innodb:starting shutdown ...
11041116:49:33 Innodb:shutdown completed; Logsequence number 733553275
# ls
Cacti ibdata1 nagios test Xtrabackup_binlog_pos_innodb xtrabackup_checkpoints xtrabackup_logfile
#./XTRABACKUP--DEFAULTS-FILE=/ETC/MY.CNF--prepare--target-dir=/mydata/mybak/