First, Xtrabackup Simple overview:
Percona Xtrabackup is open source free MySQL database hot backup software, it can be InnoDB and XTRADB storage Engine database non-blocking backup (for MyISAM backup also need to add a table lock). Xtrabackup supports all Percona servers, MySQL, mariadb, and drizzle.
Xtrabackup Advantages:
1, no need to stop the database for InnoDB hot standby
2. Incremental backup MySQL
3. Stream compression to other servers
4, it can be easier to create master-slave synchronization
5. Do not increase server load when backing up MySQL
Second, the installation of Xtrabackup
First list the following my current server environment
Download Xtrabackup
[Email protected] softs]# wget http://www.percona.com/downloads/XtraBackup/LATEST/RPM/rhel5/i386/ Percona-xtrabackup-2.1.6-702.rhel5.i386.rpm--2014-01-14 10:06:41--http://www.percona.com/downloads/XtraBackup/ latest/rpm/rhel5/i386/percona-xtrabackup-2.1.6-702.rhel5.i386.rpm
Resolving www.percona.com ... 74.121.199.234
........................ Part of the output of the content is omitted here ......
100%[==========================>] 8,662,225 360k/s in 32s
2014-01-14 10:07:43 (1448 kb/s)-' percona-xtrabackup-2.1.6-702.rhel5.i386.rpm ' saved [8662225/8662225]
RPM Package Direct Installation
[Email protected] softs]# RPM-IVH percona-xtrabackup-2.1.6-702.rhel5.i386.rpm
error:failed dependencies:
Perl (Dbd::mysql) is needed by percona-xtrabackup-2.1.6-702.rhel5.i386
There is a need to resolve the dependency package, I use the local yum installation directly
[email protected] softs]# Yum install perl-dbd-mysql-y
[Email protected] softs]# RPM-IVH percona-xtrabackup-2.1.6-702.rhel5.i386.rpm
Preparing ... ########################################### [100%]
1:percona-xtrabackup ########################################### [100%]
Try to install again after successful
After installing Xtrabackup, there will actually be several tools:
Innobackupex:
This is actually a Perl script package for the following three tools, which can be backed up MyISAM, InnoDB, xtradb tables.
Xtrabackup:
A binary file compiled from C that can only back up InnoDB and xtradb data.
Xbcrypt:
The data used to encrypt or decrypt the backup.
Xbstream:
A compressed file used to decompress or compress the Xbstream format.
It is recommended to use Perl-encapsulated Innobackupex as a database backup because it is easier to use.
Three, Innobackupex related parameter description
--defaults-file: Specify the location of the MY.CNF parameter file
--apply-log: The--prepare parameter with Xtrabackup, typically, after the backup is complete, the data is not yet available for recovery operations because the backed up data may contain transactions that have not been committed or that have been committed but have not been synchronized to the data file. Therefore, the data file still handles the inconsistent state at this time. The role of--apply-log is to keep the data file in a consistent state by rolling back uncommitted transactions and synchronizing the committed transactions to the data file. --copy-back: Copy the backup data file to the MySQL server's datadir --remote-host=hostname when doing data recovery: the backup data is stored on the process server via SSH -- Stream=[tar]: Backup file output format, which can be obtained in the Xtarbackup binary file. When using the parameter Stream=tar backup, your xtrabackup_logfile may temporarily put in the/tmp directory, if you back up when the concurrent write large, xtrabackup_logfile may be very large (5g+), will probably fill your The/tmp directory can be used to resolve this problem by specifying the directory with the parameter--tmpdir. --tmpdir=directory: When there is a specified--remote-host or--stream, the transaction log is temporarily stored in the directory, The default is to use the temporary directory specified in the MySQL configuration file tmpdir --redo-only--apply-log: Force backup log only redo, skip rollback, which is necessary to do an incremental backup -- use-memory=*: This parameter is used when prepare, controlling the memory --databases=list used by InnoDB instances when prepare: Lists the databases that need to be backed up, if the parameter is not specified, All databases that contain MyISAM and InnoDB tables will be backed up --slave-info: a backup from the library, plus a xtrabackup_slave_info file in the--slave-info backup directory, The main log file and the offset are saved here, and the file contents are similar to the following: Change master to master_log_file= ', master_log_pos=0 --socket= SOCKET: Specify the location of the Mysql.sock so that the backup process logs on to MySQL.
four, full-scale backup[Email protected] tmp]# Mkdir/backup
[Email protected] tmp]# Innobackupex--user=root--password=123456--defaults-file=/usr/local/mysql/etc/my.cnf-- Port=3306/backup
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All rights Reserved.
This software is published under
The GNU general public LICENSE Version 2, June 1991.
Get the latest version of Percona Xtrabackup, documentation, and help resources:
http://www.percona.com/xb/p
140119 05:46:11 innobackupex:connecting to MySQL server with DSN ' dbi:mysql:;mysql_read_default_file=/usr/local/mysql/ etc/my.cnf;mysql_read_default_group=xtrabackup;port=3306 ' as ' root ' (using Password:yes).
140119 05:46:11 innobackupex:connected to MySQL server
140119 05:46:12 innobackupex:executing A version check against the server ...
140119 05:46:17 Innobackupex:done.
Important:please Check that the backup run completes successfully.
At the end of a successful backup run Innobackupex
Prints "completed ok!".
innobackupex:using MySQL server version 5.6.12-debug-log
innobackupex:created Backup directory/backup/2014-01-19_05-46-19
140119 05:46:19 innobackupex:starting ibbackup with command:xtrabackup_56--defaults-file= "/usr/local/mysql/etc/ My.cnf "--defaults-group=" mysqld "--backup--suspend-at-end--target-dir=/backup/2014-01-19_05-46-19--tmpdir=/tmp
Innobackupex:waiting for Ibbackup (pid=2458) to suspend
Innobackupex:suspend file '/backup/2014-01-19_05-46-19/xtrabackup_suspended_2 '
xtrabackup_56 version 2.1.6 for MySQL server 5.6.11 Linux (i686) (revision id:702)
Xtrabackup:uses posix_fadvise ().
XTRABACKUP:CD To/database
Xtrabackup:using the following InnoDB configuration:
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
>> Log scanned up to (2000813)
innodb:allocated tablespace 2, old maximum was 0
[Copying]/ibdata1 to/backup/2014-01-19_05-46-19/ibdata1
>> Log scanned up to (2000813)
>> Log scanned up to (2000813)
>> Log scanned up to (2000813)
>> Log scanned up to (2000813)
>> Log scanned up to (2000813)
[... done]
[Copying]/mysql/innodb_index_stats.ibd to/backup/2014-01-19_05-46-19/mysql/innodb_index_stats.ibd
[... done]
[Copying]/mysql/slave_worker_info.ibd to/backup/2014-01-19_05-46-19/mysql/slave_worker_info.ibd
[... done]
[Copying]/mysql/innodb_table_stats.ibd to/backup/2014-01-19_05-46-19/mysql/innodb_table_stats.ibd
[... done]
[Copying]/mysql/slave_relay_log_info.ibd to/backup/2014-01-19_05-46-19/mysql/slave_relay_log_info.ibd
[... done]
[Copying]/mysql/slave_master_info.ibd to/backup/2014-01-19_05-46-19/mysql/slave_master_info.ibd
.............................. A large amount of content output is omitted here ......... .........
innobackupex:backing up file '/database/supor/productnotes. MYI '
innobackupex:backing up file '/database/supor/productnotes.frm '
innobackupex:backing up file '/database/supor/orders.frm '
innobackupex:backing up file '/database/supor/customers.frm '
innobackupex:backing up file '/database/supor/orderitems.frm '
innobackupex:backing up file '/database/supor/productnotes. MYD '
140119 05:46:26 innobackupex:finished backing up non-innodb tables and files
140119 05:46:26 innobackupex:waiting for log copying to finish
Xtrabackup:the Latest Check Point (for incremental): ' 2000813 '
xtrabackup:stopping log copying thread.
.>> Log scanned up to (2000813)
xtrabackup:creating suspend file '/backup/2014-01-19_05-46-19/xtrabackup_log_copied ' with PID ' 2458 '
Xtrabackup:transaction Log of LSN (2000813) to (2000813) was copied.
140119 05:46:27 innobackupex:all Tables Unlocked
Innobackupex:backup created in directory '/backup/2014-01-19_05-46-19 '
140119 05:46:27 innobackupex:connection to database server closed
140119 05:46:28 innobackupex:completed ok! Backup successful
Five, full-scale backup recoveryNow I simulate deleting all the files in the database and then recovering the data [[email protected] database]# RM-RF *[[email protected] database]# Ls[[email protected] database]# [[email protected] database]# Innobackupex--defaults-file=/usr/local/mysql/etc/my.cnf--user=root-- password=123456--apply-log/backup/2014-01-19_05-57-08/
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All rights Reserved.
Important:please Check that the Apply-log run completes successfully.
At the end of a successful apply-log run Innobackupex
Prints "completed ok!".
140119 06:00:47 innobackupex:starting ibbackup with command:xtrabackup_56--defaults-file= "/usr/local/mysql/etc/ My.cnf "--defaults-group=" mysqld "--prepare--target-dir=/backup/2014-01-19_05-57-08--tmpdir=/tmp
xtrabackup_56 version 2.1.6 for MySQL server 5.6.11 Linux (i686) (revision id:702)
Xtrabackup:starting InnoDB instance for recovery.
Xtrabackup:using 104857600 bytes for buffer pool (set by--use-memory parameter)
Innodb:the InnoDB memory heap is disabled
Innodb:mutexes and Rw_locks use GCC atomic builtins
innodb:compressed Tables Use zlib 1.2.3
Innodb:not using CPU CRC32 instructions
................................................................................................
[Notice (again)]
If You use the binary log and don ' t use any hack of group commit,
The binary log position seems to be:
xtrabackup:starting shutdown with Innodb_fast_shutdown = 1
Innodb:fts optimize thread exiting.
I ... ..... ..... ............................. ..... ..... ..... ..... ..... ............. ..........
innobackupex:creating directory '/database/mysql '
Innobackupex:copying '/backup/2014-01-19_05-57-08/mysql/help_keyword.frm ' to '/database/mysql/help_keyword.frm '
Innobackupex:copying '/backup/2014-01-19_05-57-08/mysql/user. MYI ' to '/database/mysql/user. MYI '
Innobackupex:copying '/backup/2014-01-19_05-57-08/mysql/help_relation.frm ' to '/database/mysql/help_relation.frm '
Innobackupex:copying '/backup/2014-01-19_05-57-08/mysql/slow_log. CSV ' to '/database/mysql/slow_log. CSV '
Innobackupex:copying '/backup/2014-01-19_05-57-08/mysql/time_zone_leap_second.frm ' to '/database/mysql/time_zone_ Leap_second.frm '
Innobackupex:copying '/backup/2014-01-19_05-57-08/mysql/time_zone. MYI ' to '/database/mysql/time_zone. MYI '
Innobackupex:copying '/backup/2014-01-19_05-57-08/mysql/time_zone_transition. MYI ' to '/database/mysql/time_zone_transition. MYI '
Innobackupex:copying '/backup/2014-01-19_05-57-08/mysql/slave_master_info.frm ' to '/database/mysql/slave_master_ Info.frm '
Innobackupex:copying '/backup/2014-01-19_05-57-08/mysql/help_category. MYI ' to '/database/mysql/help_category. MYI '
Innobackupex:copying '/backup/2014-01-19_05-57-08/mysql/procs_priv. MYI ' to '/database/mysql/procs_priv. MYI '
Innobackupex:copying '/backup/2014-01-19_05-57-08/mysql/help_category. MYD ' to '/database/mysql/help_category. MYD '
Innobackupex:copying '/backup/2014-01-19_05-57-08/mysql/func. MYI ' to '/database/mysql/func. MYI '
Innobackupex:copying '/backup/2014-01-19_05-57-08/mysql/tables_priv.frm ' to '/database/mysql/tables_priv.frm '
Innobackupex:copying '/backup/2014-01-19_05-57-08/mysql/time_zone_transition. MYD ' to '/database/mysql/time_zone_transition. MYD '
................................................ A large amount of data output is omitted here ......... .............
Innobackupex:copying '/backup/2014-01-19_05-57-08/performance_schema/performance_timers.frm ' to '/database/ Performance_schema/performance_timers.frm '
innobackupex:creating directory '/database/test '
innobackupex:starting to copy InnoDB system tablespace
Innobackupex:in '/backup/2014-01-19_05-57-08 '
Innobackupex:back to original InnoDB data directory '/database '
Innobackupex:copying '/backup/2014-01-19_05-57-08/ibdata1 ' to '/database/ibdata1 '
innobackupex:starting to copy InnoDB undo Tablespaces
Innobackupex:in '/backup/2014-01-19_05-57-08 '
Innobackupex:back to '/database '
innobackupex:starting to copy InnoDB log files
Innobackupex:in '/backup/2014-01-19_05-57-08 '
Innobackupex:back to original InnoDB log directory '/database '
Innobackupex:copying '/backup/2014-01-19_05-57-08/ib_logfile0 ' to '/DATABASE/IB_LOGFILE0 '
Innobackupex:copying '/backup/2014-01-19_05-57-08/ib_logfile1 ' to '/database/ib_logfile1 '
Innobackupex:finished copying back files.
140119 06:01:31 innobackupex:completed ok! # # #恢复完成 Next check to see if the data is restored
[[email protected] database]# ls
Ibdata1 ib_logfile0 ib_logfile1 mysql performance_schema test
[Email protected] database]# chown-r mysql.mysql/database/
[Email protected] ~]#/ETC/INIT.D/MYSQL5 restart
Shutting down MySQL. [OK]
Starting MySQL ....... [OK]
Full-scale backup recovery successful
http://blog.csdn.net/avilifans/article/details/18469549
Xtrabackup full-scale backup and recovery MySQL database
