Copy Address: Https://www.linuxyw.com/download/xtrabackup.pdf
http://www.linuxyw.comemail:[email protected] Xtrabackup to MySQL database backup and restore this document, just a simple study of the next, for the database full and ready to add, the effect is good because there is no demand for this software, so, the document is not perfect, and so there is time, and then to improve it this document information, Internet collection into my blog: http://www.linuxyw.comxtrabackupxtrabackup is an open source project Percona, can be hot backup innodb,xtradb, and MyISAM (Will lock the table). The MyISAM storage engine will lock the table, is also very depressed because the online use of InnoDB and MyISAM two kinds of storage engine, compare headaches!! Xtrabackup is a data backup tool for InnoDB, which supports online hot backup (which does not affect data reading and writing during backup) and is a good alternative to commercial backup tools Innodbhotbackup. Official principle The following content is reproduced: http://www.baidu-ops.com/2013/05/26/xtrabackup/in the InnoDB will maintain a redo log file, we can also be called transaction log files. The transaction log stores record modifications for each of the InnoDB table data. When InnoDB starts, InnoDB examines the data file and transaction log and performs two steps: it applies (rolls forward) the transaction log to the data file that has been committed, and rolls back the data that has been modified but not committed. Xtrabackup remembers the Logsequencenumber (LSN) at startup, and copies all the data files. The copy process takes some time, so if the data file changes during this period, the database will be at a different point in time. At this point, Xtrabackup runs a background process that monitors the transaction log and copies the latest modifications from the transaction log. Xtrabackup must continue to do this because the transaction log is rotated repeatedly and the transaction log can be reused. As a result, Xtrabackup has kept track of changes to every data file in the transaction log since it started. The above is the Xtrabackup backup process. Next is the preparation (prepare) process. In this process, Xtrabackup uses the previously copied transaction log to perform a disaster recovery on individual data files (just as it did when MySQL was started). When this process is over, the database can be restored and restored. The above process isXtrabackup is implemented in a compiled binary program. The program Innobackupex allows us to back up MyISAM and frm files, thus adding convenience and functionality. Innobackupex starts xtrabackup until xtrabackup copies the data file, then executes Flushtableswithreadlock to block new writes and brushes the MyISAM table data to the hard disk. Then copy the MyISAM data file and finally release the lock. The backup MyISAM and InnoDB tables will eventually be consistent, and after the preparation (prepare) process is complete, the InnoDB table data has been rolled forward to the point where the entire backup ended, rather than rolling back to the point at which the Xtrabackup was first started. This point of time is the same as the point at which the flushtableswithreadlock is executed, so the MyISAM table data is synchronized with the InnoDB table data. Like Oracle, INNODB's prepare process can be called recover (recovery), and the MyISAM data replication process can be called restore. Both the Xtrabackup and Innobackupex tools offer a number of features that are not mentioned in the previous article. The manual has http://www.linuxyw.comemail:[email protected] detailed descriptions of each function. In brief, these tools provide streaming (streaming) backups, incremental (incremental) backups, and so on, by replicating data files, copying log files, and committing logs to data files (roll forward) to achieve a variety of composite backup methods. Own understanding Xtrabackup can only back up and restore the InnoDB table, and only the IBD file, frm file it regardless, the DBA is required to provide the frm when recovering. Innobackupex can back up and restore MyISAM tables and frm files, and xtrabackup is also well encapsulated, so you can use Innobackupex to back up the MySQL database. Another problem is that innobackupex back up the MyISAM table before the whole library to add readlock, blocking the write operation, if the backup is from the library, it will affect the master-slave synchronization, resulting in delay. Backup to InnoDB table does not block read and write. Xtrabackup Incremental Backup is the principle of: 1), complete a full backup first, and then record the checkpoint at this point lsn;2), and then incremental backup, compare the LSN of each page in the tablespace is greater than the LSN of the last backup, if the page is backed up and the LSN of the current checkpoint is logged. Specifically, the last C is found and recorded in the logfile firstHeckpoint ("Lastcheckpointlsn"), and then start copying InnoDB logfile to xtrabackup_logfile from the location of the LSN And then start copying all of the data files. ibd; Copy logfile is not stopped until the end of the copy of all data files. Therefore, the Xtrabackup_logfile file can become very large when the concurrent writes are large, taking up a lot of space and needing attention. In addition, when we use--stream=tar or remote backup--remote-host by default,/tmp, but it is best to display parameters--tmpdir specified, so as to avoid the/tmp directory full Impact backup and other normal system services. Because LogFile records all of the data modifications, even though the data files have been modified during the backup process, it is still possible to maintain the consistency of the data by parsing the xtrabackup_logfile when it is restored. An incremental backup of xtrabackup can only be used with InnoDB tables and cannot be used on MyISAM tables. When using an incremental backup of the MySQL database, Xtrabackup will perform an incremental backup of the InnoDB table based on the last full or incremental backup directory, and the MyISAM table will be replicated in full table. Stream Backup (streaming) to save the backup directly to the remote server. When performing a restore, the data files are inconsistent at this time because the replication is not locked, and Xtrabackup uses the previously saved redolog to check whether the individual data files are consistent with the checkpoint of the transaction log, performing the recovery: 1), Rolls forward based on the transaction log that was generated when the data file was copied and after the transaction was committed, 2), and the uncommitted transaction is rolled back. This process is the crashrecovery that is executed after the MySQL database is down. Incremental backups in InnoDB, the LSN information is recorded in each page, and the LSN of the page is automatically incremented whenever the relevant data changes, and the Xtrabackup incremental backup is based on this principle. Xtrabackup back up the page that the LSN changed since the last backup (full backup set or also an incremental backup set). So, to do an incremental backup for the first time, make a full backup (that is, make a full copy of the MySQL instance or the database table that you want to back up, and record the LSN), and then you can make incremental backups and recoveries based on this. http://www.linuxyw.comemail:[email protected] Incremental Backup advantages: 1), the database is too large not enough space for full backup, incremental backup can effectively save space, and high efficiency; 2), Supports hot backup, the backup process does not lock the table (for inNODB), do not block the database read and write, 3), daily backup only produce a small amount of data, can also use remote backup, save local space, 4), backup recovery based on file operations, reduce the risk of direct database operations, 5), backup more efficient, more efficient recovery. Recovery and restore of backup includes two parts of recovery and restore process. As we've said before, Xtrabackup only backs up IBD files for InnoDB tables, and Innobackupex can back up all data files for tables of other storage engines, including InnoDB tables. The recovery process may look different because of the differences in the backup of the engine tables. Let's take a look at the recovery of the full backup set. The database is in an inconsistent state during a backup of the InnoDB table or more directly in the case of IBD data File replication, so the transactions that have not yet been committed in Xtraback_logfile are rolled back and the committed transactions are rolled forward, leaving each data file in a consistent state. This process is called "preparation" (prepare). If you are performing a backup from a library, that means you have nothing to roll back, just a simple applyredolog. In addition, parameter--use-memory can be used in the prepare process to increase the amount of memory used in the system to improve recovery speed. After that, we can copy the data file back to the corresponding directory according to the configuration in backup-my.cnf, of course you can copy it back, but Innobackupex will help us to finish it. Here, for the InnoDB table is the completion of the "Ready" action, we call "recovery (recovery)", and for the MyISAM table because the backup is a lock table copy, so this is simply copied back, do not need applylog, this we call "restore (Restore). Note: The use of recovery and restore in this document is similar to other databases like Oracle. The recovery process for an incremental backup is similar to a recovery for a full backup set, except that there is a slight difference: 1), the recovery process requires a full backup set and each incremental backup set, and the recovery of each backup set is the same as before (roll forward and rollback), after which the redolog of each incremental backup set is applied to the full backup set; ), for new tables generated after a full standby set, special handling is needed so that the table is not dropped after recovery, 3), to be based on a full backup set, and then to apply each incremental backup set sequentially. Stream backup and compression refer to stream backup (streaming) to say remote backup and backup compression, first say stream backup bar. Stream backup means that the backed up data is transferred to the TAR program through the standard output stdout, rather than simply saving the data file to the specified backup directory, and the parameter--stream=tar represents the OpenStream backup functionality and package. You can also use streaming backups to a remote server. For example, $innobackupex--stream=tar${backup_dir}/base|gzip>${backup_dir}/base.tar.gz$innobackupex--stream=tar${ Backup_dir}/base|sshsomebackupaddr "Cat>${dir}/base.tar" http://www.linuxyw.comEmail:[email Protected] Of course, if you use a stream backup, then the incremental backup will not work, because the incremental backup needs to refer to the backup situation, and the last backup was packaged or compressed. In our reality use, more use of incremental backup, as for archive compression we can be done by the script autonomously. Partial backup and recovery xtrabackup can only back up/restore part of the library table, you can match the regular pattern or you want to back up the list of library tables, but the InnoDB table must be a stand-alone table space and cannot use the stream backup feature. 1), using regular mode matching backup part of the library table, you need to use the parameter--include, the statement is similar to the following: $innobackupex--include= ' ^qb.* ' ${backup_dir}/part-base2), using the database list to back up some libraries, You need to use the parameter--databases, which is similar to the following: $innobackupex--databases=qb0qb1qb2qb3${backup_dir}/part-base3), using a table list to back up some tables, You need to use the parameter--tables-file, which is similar to the following: $innobackupex--tables-list=${conf_dir}/tab.conf${backup_dir}/part-base Note: In our real-world applications, It is very rare to back up only some of the library tables in the cluster, so just understand this feature, and if you have a realistic need to refer to Percona official information for more information. Can back up some of the library table, also can be based on a full backup set of partial library table recovery, in reality rarely used, but still say it. First, in the "Prepare for prepare" process, use the parameter--export to export the table, which will create each InnoDB table a file ending with. Exp, which serves as the subsequent import process. $innobackupex--apply-log--export${backup_dir}/base then will you need to restore the table of IBD and EXPThe file is copied to the target machine, and the import is performed on the target machine: Mysql>createtablet () engine=innodb;//here requires the DBA to manually create a table or table of the same structure that already exists mysql> Altertabletdiscardtablespace, $cpt .ibdt.exp${data_dir}/${db}/mysql>altertabletimporttablespace; Such an export import will keep the recovered table consistent with the rest of the database table. Parallel backup Xtrbackup also supports parallel backups, by default Xtrabackup only one process is opened for backup of data files, and if the configuration parameter--parallel=n allows Xtrabackup to turn on N sub-processes for concurrent backups of multiple data files, This can speed up the backup. Of course, the server's IO processing power and the impact on the server is also to be considered, so the other parameter--throttle=ios will be used with it, this parameter is used to limit the number of read/write IO per second during the backup process, the server IO is a protection. Both parameters Xtrabackup and Innobackupex are supported, for example: $innobackupex--parallel=4--throttle=400${backup_dir}/ Part-base Note: There will only be one process in the backup for the same data file. Other http://www.linuxyw.comemail:[email protected]xtrabackup in the backup of the main work is to do data file replication, It will only read and write 1MB data (that is, 64 pages, cannot be modified), Xtrabackup access to 1MB data per page, using the InnoDB buf_page_is_corrupted () function to check whether the data on this page is normal, if the data is not normal, Read this page again, up to 10 reads, and if it fails, the backup fails and exits. When copying the transaction log, it is also not configurable to read and write 512KB of data each time. Before I maintain the MySQL database, use mysqldump for backup and restore, lock the table when the backup, and then all back up, when the data is small, no problem, but if the data are many, do not allow the lock table, while the need to recover data block situation, mysqldump is not suitable, When I recovered a 4G data file database, the recovered data was using mysqldump data, resumed 3 hours have not responded, the impact is very serious, so I began to look for otherSoftware to meet the above requirements, fortunately found, is the use of xtrabackup to backup and recovery, recovery of 4G data files database, only 14 seconds, while in the backup of the time will not lock the table, and support incremental backup, so my comparison to share to everyone, I hope it is beneficial to everyone! Official address: http://www.percona.com/downloads/XtraBackup/Download: http://www.percona.com/redir/downloads/XtraBackup/ Xtrabackup-2.1.5/binary/linux/x86_64/percona-xtrabackup-2.1.5-680-linux-x86_64. tar.gz Installation: yuminstallperl-time-hires-yyum-yinstallperl-dbd-mysql.x86_ 64tarxvfpercona-xtrabackup-2.1.5-680-linux-x86_64.tar.gzcdpercona-xtrabackup-2.1.5-linux-x86_64/cpbin/*/usr/ The Bin/innobackupex uses parameters to describe the tables that Innobackupex can back up both the InnoDB and MyISAM engines at the same time, where the emphasis is on Innobackupex backup and recovery use generally directly using Innobackupex, Because it can back up the tables of the InnoDB and MyISAM engines at the same time. Note that my.cnf datadir This parameter is required to be specified, xtrabackup_55 is based on it to locate the location of the InnoDB data file. Use Help: Http://www.percona.com/docs/wiki/percona-xtrabackup:startinnobackupex syntax and parameter description innobackup[--sleep=ms][-- Compress[=level]][--include=regexp][--user=name][--password=word][--port=port][--socket=socket][--no-timestamp ][--ibbackup=ibbackup-binary][--slave-info][--stream=tar][--defaults-file=my. Cnf][--databases=list][--remote-host=hostname] http://www.linuxyw.comemail:[email protected]backup-root-dirinnobackup--apply-log[--use-memory= Mb][--uncompress][--defaults-file=my. Cnf][--ibbackup=ibbackup-binary]backup-dirinnobackup--copy-back[--defaults-file=my. Cnf]backup-dir parameter Description:--defaults-file Specifies the location of the MySQL configuration file my.cnf, such as--defaults-file=/etc/ MY.CNF If the parameter is not true, Xtrabackup will look for the configuration file/etc/my.cnf,/etc/mysql/my.cnf,/USR/LOCAL/ETC/MY.CNF, ~/.my.cnf from the following location, and read in the configuration file [ MYSQLD] and [xtrabackup] configuration segments. [mysqld] only need to specify DataDir, Innodb_data_home_dir, Innodb_data_file_path, Innodb_log_group_home_dir, innodb_log_files_in _group, Innodb_log_file_ Size6 parameters to allow the xtrabackup to work properly--apply-log--prepare parameters of Xtrabackup--copy-back to do data recovery when copying the backup data files to the MySQL server DataDir --remote-host=hostname the backup data to the process server via SSH, hostname is the remote IP address,--stream=[tar] Back up the file output format, Tar uses TAR4IBD, This file is available in the Xtarbackupbinary file. If you have specified--stream=tar at the time of backup, the directory where the Tar4ibd file is located must be in $path (because TAR4IBD is used to compress, This file is available in the binary package of xtrabackup). When using the parameter Stream=tar backup, your xtrabackup_logfile may be temporarily placed in the/tmp directory, if you back up the concurrent write large words xtrabackup_logfile may be very large (5g+),will probably fill up your/tmp directory, you can solve this problem by specifying the directory with the parameter--tmpdir. --tmpdir=directory when there is a specified--remote-hostor--stream, the transaction log is temporarily stored in a directory that defaults to the temporary directory specified in the MySQL configuration file Tmpdir--redo-only--apply-log Group , http://www.linuxyw.comemail:[email protected] Forces the backup log to only redo, skipping rollback. This is necessary when doing an incremental backup. --use-memory= #该参数在prepare的时候使用, the amount of memory used by the InnoDB instance when controlling prepare--throttle=ios xtrabackup parameter--throttle with--sleep= is used for Ibbackup, specify the 1M data per backup, the process to stop copying how many milliseconds, but also to minimize the impact on the normal business during backup, you can view the Ibbackup manual;--compress[=level] Import row compression on the backup data, Only support Ibbackup,xtrabackup has not been implemented,--include=regexp xtrabackup parameter--tables encapsulation, also support ibbackup. Back up the included library table, for example:--include= "test.*", which means that you want to back up all the tables in the test library. Omit this parameter if you need a full backup, or if you need to back up the 2 tables under test Library: Test1 and Test2, write:--include= "Test.test1|test.test2". You can also use wildcard characters, such as:--include= "test.test*". --databases=list lists the databases that need to be backed up, and if this parameter is not specified, all database containing MyISAM and INNODB tables will be backed up;--uncompress unzip the backed up data file, support Ibbackup, Xtrabackup has not yet implemented this function;--slave-info, backup from the library, plus--slave-info backup directory will generate more than one Xtrabackup_slave_info file, here will save the main log file and offset, the file content is similar to: Changemastertomaster_log_file= ', MASTER_LOG_POS=0--SOCKET=SOCKET specifies the location of the Mysql.sock so that the backup process logs on to MySQL. Operation: Database Full Library Backup: innobackupex--defaults-file=/etc/my.cnf--user=root--password= 111111--port=3306/tmp/Explanation:--defaults-file=/etc/my.cnf# Specifies MY.CNF location http://www.linuxyw.comemail:[email protected]--user=root# Specify MySQL account--password=111111# specify mysql password--port=3306# specify MySQL port/tmp/#指定备份好的文件存放目录, I put this on/ tmp/below, after the completion of execution, the following is a time-named directory, which is the backup file: [[email protected]tmp] #ls2013 -10-29_16-06-41[[email protected] TMP] #ls2013 -10-29_16-06-41/aaibdata1testxtrabackup_checkpointsbackup-my.cnfmysqlxtrabackup_binaryxtrabackup_ Logfiledrfdaiperformance_schemaxtrabackup_binlog_info Backup Drfdai Library: innobackupex--defaults-file=/etc/my.cnf--user= root--password=111111--port=3306--database=drfdai/tmp/Explanation:--database=drfdai# Specifies the name of the database that needs to be backed up [[email protected] 2013-10-29_16-23-53] #lsbackup-my.cnfibdata1xtrabackup_binlog_infoxtrabackup_logfiledrfdaixtrabackup_ Binaryxtrabackup_checkpoints backing up multiple libraries: innobackupex--defaults-file=/etc/my.cnf--user=root--password=111111--port= 3306--database= ' drfdaimysql '/tmp/explanation:--database= ' drfdaimysql ' #指定你需要备份的那几个库名, enclose these library names in single quotation marks, separated by spaces in the middle of each library. [[email protected]2013-10-29_16-33-09] #lsbackup-my.cnfibdata1xtrabackup_binaryxtrabackup_ Checkpointsdrfdaimysqlxtrabackup_binlog_infoxtrabackup_logfile http://www.linuxyw.comemail:[email protected] Backing up multiple tables: innobackupex--defaults-file=/etc/my.cnf--user=root--password=111111--port=3306--database = ' Drfdai.aa1drfdai.aa2 '/tmp/explanation:--database= ' Drfdai.aa1drfdai.aa2 ' # Specify the AA1 table and AA2 table restore operations under the Backup Drfdai Library: Stop the MySQL database and delete the database folder that you want to recover as my MySQL database folder is in/data/mysql/data, so I deleted this folder and rebuilt a/data/ Mysql/data/rm-rf/data/mysql/datamkdir/data/mysql/data Restore full backup: innobackupex--defaults-file=/etc/my.cnf--user= root--password=111111--port=3306--apply-log/tmp/2013-10-29_17-17-47/here--apply-log indicates that the log is applied to the data file, Restore the data from the backup file to the database when you are finished innobackupex--defaults-file=/etc/my.cnf--user=root--password=111111--port=3306--copy-back/ tmp/2013-10-29_17-17-47/The-copy-back here indicates that data recovery is performed. After the data recovery is complete, you need to modify the permissions of the relevant files MySQL database to start normally. Chown-rmysql:mysql/data/mysql/data Incremental Backup: First complete: innobackupex--defaults-file=/etc/my.cnf--user=root--password=111111--port=3306/tmp/when the backup is finished, the latest directory is generated, named: 2013-10-29_ 17-17-47 for the first incremental backup: innobackupex--defaults-file=/etc/my.cnf--user=root--password=111111--port=3306-- After the incremental--incremental-basedir=/tmp/2013-10-29_17-17-47/tmp backup is complete, the most recent incremental backup is generated, named: 2013-10-29_ 17-57-21 Explanation:--incremental# is specified for this incremental backup http://www.linuxyw.comemail:[email protected]-- incremental-basedir=/tmp/2013-10-29_17-17-47# Specifies the backup directory from which to make an incremental backup of the reference point for the second incremental backup: innobackupex--defaults-file=/etc/ my.cnf--user=root--password=111111--port=3306--incremental--incremental-basedir=/tmp/2013-10-29_17-57-21// TMP and so on, third, fourth ... Incremental backup Recovery: The steps for incremental backup recovery are basically the same as the steps for full backup recovery, except that the process of applying the logs is slightly different. When an incremental backup is restored, all incremental backups are first applied to the full backup data file, and then the data from the full backup is restored to the database. The command is as follows: Apply the first incremental backup innobackupex--user=root--password=mysqlpassword--defaults-file=/etc/my.cnf--apply-log/ mysqlbackup/full/2011-08-09_14-50-20/--incremental-dir=/mysqlbackup/trn/2011-08-09_15-12-43/ Apply a second incremental backup innobackupex--user=root--password=mysqlpassword--defaults-file=/etc/my.cnf--apply-log/mysqlbackup/full/2011-08-09_14-50-20/--incremental-dir=/mysqlbackup/trn/2011-08-05_15-15-47/ Restore the data from the full backup to the database. innobackupex--user=root--password=mysqlpassword--defaults-file=/etc/my.cnf--copy-back/mysqlbackup/full/ 2011-08-05_14-50-20/where--incremental-dir specifies the location of the incremental backup to restore. More Information:http://blog.chinaunix.net/uid-20682026-id-3319204.htmlhttp://database.51cto.com/art/201108/283254.htm
Innobackupex xtrabackup Backup to restore MySQL data
