1. Overview of Xtrabackup:
Percona Xtrabackup is an open-source and free mysql Database Hot Backup Software. It can back up InnoDB and XtraDB storage engine databases in a non-blocking manner (Table locks are also required for MyISAM backup ). XtraBackup supports all Percona servers, MySQL, MariaDB, and Drizzle.
XtraBackup advantages:
1. No need to stop the database for InnoDB Hot Backup
2. Incremental backup of MySQL
3. stream compression to transfer to other servers
4. Easy to create master-slave Synchronization
5. The server load is not increased when MySQL is backed up.
Ii. Installation of Xtrabackup
First, list the following my current server environment
Download Xtrabackup
[Root @ localhost 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
........................ Some content output is omitted here ..................
100% [=====================>] 8,662,225 360 K/s in 32 s
10:07:43 (1448 KB/s)-'percona-xtrabackup-2.1.6-702.rhel5.i386.rpm 'saved [8662225/8662225]
Directly install the rpm package
[Root @ localhost 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
Here we need to solve the dependency package. I will install it directly using local yum
[Root @ localhost softs] # yum install perl-DBD-MySQL-y
[Root @ localhost softs] # rpm-ivh percona-xtrabackup-2.1.6-702.rhel5.i386.rpm
Preparing... ######################################## ### [100%]
1: percona-xtrabackup ##################################### ###### [100%]
Installation successful again
After XtraBackup is installed, there are actually several tools:
Innobackupex:
This is actually a perl script encapsulation of the following three tools. You can back up tables MyISAM, InnoDB, and XtraDB.
Xtrabackup:
A binary file compiled by C can only back up InnoDB and XtraDB data.
Xbcrypt:
Used to encrypt or decrypt the backup data.
Xbstream:
Used to decompress Or compress compressed files in xbstream format.
We recommend that you use innobackupex encapsulated in perl for database backup, because it is easier to use.
Iii. Description of innobackupex Parameters
-- Defaults-file: Specifies the location of the my. cnf parameter file.
-- Apply-log: Same as the -- prepare parameter of xtrabackup. Generally, after the backup is complete, the data cannot be used for restoration, because the backup data may contain uncommitted transactions or transactions that have been committed but not synchronized to the data file. Therefore, the data files are still inconsistent. -- Apply-log is used to roll back uncommitted transactions and synchronize committed transactions to data files so that the data files are consistent.
-- Copy-back: copy the backup data file to the datadir of the MySQL server during data recovery.
-- Remote-host = HOSTNAME: store the backup data to the process server through ssh.
-- Stream = [tar]: backup file output format, which can be obtained from the XtarBackup binary file. when using stream = tar for backup, your xtrabackup_logfile may be temporarily stored in the/tmp directory, xtrabackup_logfile may be large (5 GB +) and may occupy your/tmp directory. You can solve this problem by specifying the directory -- tmpdir parameter.
-- Tmpdir = DIRECTORY: When -- remote-host or -- stream is specified, the temporary DIRECTORY for storing transaction logs is tmpdir by default.
-- Redo-only -- apply-log: only redo logs are forced to be backed up. rollback is skipped, which is necessary for Incremental backup.
-- Use-memory = *: this parameter is used when prepare is used to control the memory used by the innodb instance during prepare.
-- Databases = LIST: lists the databases to be backed up. If this parameter is not specified, all databases including MyISAM and InnoDB tables will be backed up.
-- Slave-info: Backup slave database. An xtrabackup_slave_info file will be generated in the -- slave-info Backup Directory. The main log file and offset will be saved here. The file content is similar: change master to MASTER_LOG_FILE = '', MASTER_LOG_POS = 0
-- Socket = SOCKET: Specifies the location of mysql. sock so that the backup process can log on to mysql.
Iv. Full backup
[Root @ localhost tmp] # mkdir/backup
[Root @ localhost 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,200 9 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_su1-19ded_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: 10 M: 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
[01] 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)
[01]... done
[01] Copying./mysql/innodb_index_stats.ibd to/backup/2014-01-19_05-46-19/mysql/innodb_index_stats.ibd
[01]... done
[01] Copying./mysql/slave_worker_info.ibd to/backup/2014-01-19_05-46-19/mysql/slave_worker_info.ibd
[01]... done
[01] Copying./mysql/innodb_table_stats.ibd to/backup/2014-01-19_05-46-19/mysql/innodb_table_stats.ibd
[01]... done
[01] Copying./mysql/slave_relay_log_info.ibd to/backup/2014-01-19_05-46-19/mysql/slave_relay_log_info.ibd
[01]... done
[01] 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): '123'
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 '123'
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
V. Full Backup RecoveryNow I simulate deleting all files in the database by mistake, and then recover the data [root @ localhost database] # rm-rf *
[Root @ localhost database] # ls
[Root @ localhost database] #
[Root @ localhost 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,200 9 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 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! ### Recovery completed
Next, check whether the data is restored.
[Root @ localhost database] # ls
Ibdata1 ib_logfile0 ib_logfile1 mysql performance_schema test
[Root @ localhost database] # chown-R mysql. mysql/database/
[Root @ localhost ~] #/Etc/init. d/mysql5 restart
Shutting down MySQL... [OK]
Starting MySQL ......
Full Backup Recovery successful
