Innobackupex options trading
This page documents all of the command-line options for the innobackupex. Also, create new transaction logs. The InnoDB configuration is read from the file backup-my. InnoDB configuration in this context means server variables that affect data format, i.
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. Do not keep files opened. This option is passed directly to innobackupex options trading. However, if the number of tablespaces is really huge and can not fit into any limit, there is an option to innobackupex options trading file handles once they are no longer accessed.
Percona XtraBackup can produce inconsistent backups with this option enabled. Use at your own risk. Create a compact backup with all secondary index pages omitted. See the xtrabackup documentation for details. This option instructs xtrabackup to compress backup copies of InnoDB data files.
It is passed directly to the xtrabackup child process. This option specifies the number of worker threads that will be used for parallel compression.
This option specifies the size of the internal working buffer for each compression innobackupex options trading, measured in bytes. The default value is 64K. Copy all the files in a previously made backup from the backup directory to their original locations. Percona XtraBackup innobackupex --copy-back option will not copy over existing files unless innobackupex options trading --force-non-empty-directories option is specified.
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. Please make sure that —databases contains all of the InnoDB databases and tables, so that all of the innodb.
In innobackupex options trading 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. Decompresses all files with the. In order to decompress, the qpress utility MUST be installed and accessible within the path. Decrypts all files with the.
This option accepts a string argument that specifies what file to read the default MySQL options from. Must be given as the first option on the command-line. This option specifies what extra file to read the default MySQL options from before the standard defaults-file. This option accepts a string argument that specifies the group which should be read from the configuration file.
This can also be used to indicate groups other than mysqld and xtrabackup. See the xtrabackup documentation for more details.
This option specifies the number of worker threads that will be used for parallel encryption. Innobackupex options trading option specifies the size of the internal working buffer for each encryption thread, measured in bytes. This option is passed directly to xtrabackup --export option. It enables exporting individual tables for import into another server. When specified, it makes innobackupex --copy-back option or innobackupex --move-back option transfer files to non-empty directories.
No existing files will be overwritten. If —copy-back or —move-back has to copy a file from the backup directory which already exists in the destination directory, it will still fail with an error. Has no effect when backup locks are used to create the backup.
An optional history series name may be specified that will be placed with the history record for the current backup being taken. It is passed to the mysql child process without innobackupex options trading. See mysql --help for details.
This option specifies which xtrabackup binary should be used. The option accepts a string argument. The option can be useful if the xtrabackup binary is not in your search path or working directory. If this option is not specified, innobackupex attempts to determine the binary to use automatically. This option is a regular expression to be matched against table names in databasename.
This option tells xtrabackup to create an incremental backup, rather than innobackupex options trading full one. It is passed innobackupex options trading 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 innobackupex options trading first timestamped backup directory in the backup base directory. This option accepts a string argument that specifies the directory containing the full backup that is the base dataset for the incremental backup. It is used with the --incremental option. This option accepts a string argument that specifies the directory where innobackupex options trading incremental backup will be combined with the full backup to make a new full backup.
This will be mutually exclusive with innobackupex --incremental-history-uuid ,: 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 innobackupex --incremental option. If no valid lsn can be found no success record innobackupex options trading that uuid xtrabackup will return with an error. This option accepts a string argument that specifies innobackupex options trading log sequence number LSN to use for the incremental backup.
Innobackupex options trading is used instead of specifying --incremental-basedir. For databases created in 5. Default is 0 seconds, which means innobackupex will not attempt to kill any queries. Where innobackupex options trading Percona Server 5. This option specifies which types of queries should be killed to unblock the global lock. If there are still such queries when the timeout expires, innobackupex terminates with an error.
This option specifies the query run time threshold which is used by innobackupex to detect long-running queries with a non-zero value of innobackupex --ftwrl-wait-timeout. This option has no effect if —ftwrl-wait-timeout is 0. Default value is 60 seconds. This option specifies which types of queries are allowed to complete before innobackupex will issue the global lock. Move all the files in a previously made backup from the backup directory to their original locations.
As innobackupex options trading option removes backup files, it must be used with caution. 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. This option accepts an integer argument that specifies the number of threads the xtrabackup child process should use to back up files concurrently.
Note that this option works on file level, that is, if you have several. If your tables are stored together in a single tablespace file, it will have no effect. In order to decompress, the qpress utility MUST be installed and accessable within the path.
This option accepts a string argument specifying the password to use when connecting to the database. It is passed to the mysql child process. 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 innobackupex options trading used to prepare compact backups. See the xtrabackup documentation for more information. This option only has effect when used together with the —apply-log and —rebuild-indexes option and is passed directly to xtrabackup.
When used, xtrabackup processes innobackupex options trading in parallel with the specified number innobackupex options trading threads when rebuilding indexes.
This option should be used when preparing the base full backup and when merging all incrementals except the last one. This is necessary if the backup will innobackupex options trading incremental changes applied to it later. 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. 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 slave SQL thread will be restarted when the backup finishes. Defaults to seconds.
This option accepts a string argument that specifies the command line options to pass to scp when the option --remost-host is specified. If the option is not innobackupex options trading, the default options are -Cp -c arcfour. This option is useful when backing up a replication slave server.
It prints the binary log position and name of the master server. This option accepts a string argument that specifies the socket to use when connecting to the local database server with a UNIX domain socket.
No one can deny that one of the most important and daily tasks for DBAs is performing backup and restore operations, we're not required to perform backup and restore operations only when we want to add new replication slave, when we want to implement disaster recovery procedures or when we want to prepare testing or staging server for the running production system, but even if we're going to make any changes to the database schema in order to enhance the database performance, it's recommended to have fresh backup copy before making any live changes, so if backup and restore operations cannot be handled smoothly, we're going to face many troubles in our daily work.
If we're going to talk about backup and restore operations, Xtrabackup tool will innobackupex options trading strongly appeared. Xtrabackup tool is a free open source tool developed by Percona to perform physical backup and restore operations which is much faster than performing logical backup and restore using the MySQL utilities mysqldump and mysqland many other advantages.
Xtrabackup tool has many options and features which are very useful, but in innobackupex options trading article, I'll go through only on how to use this tool to perform simple full, incremental and partial backups and restores, advantages and disadvantages of each method and some important tips.
For more information about Xtrabackup tool, you can browse the innobackupex options trading document from here. As compared to the full logical database backup using mysqldump and mysql utilities very long time to backup and more than the doubled time to restoretaking a full physical backup using Xtrabackup tool will make your life much easier.
A timestamped folder for ex. Xtrabackup tool now created the backup files under the folder "full-backup" plus some extra files like "xtrabackup-checkpoints" file which contains some information useful in the incremental backups like:.
The backed up files are not ready at the moment to be restored, we must prepare the backup files first as follows:. Innobackupex options trading get the full backup restored, innobackupex options trading MySQL instance should be stopped first and then one of the following two procedures should be done:. Xtrabackup tool - in this method - will copy all files under the backup folder full-backup to the MySQL datadir which must be indicated in the my.
If you don't want to keep the backup files on your local system you have another copy in an external innobackupex options tradingthe move command will be very fast to get your backup restored:.
GTID is supported in Xtrabackup starting from version 2. However, the innobackupex options trading steps should be done:. When you have a very large database system, you will need large enough storage to store your database backups in, and if you want to perform a daily innobackupex options trading then your mission will be more difficult. In such cases, the incremental database backup method innobackupex options trading be very useful.
It allows you innobackupex options trading have only the database changes innobackupex options trading - after the physical full backup — with the minimum storage space required in a fast way, and hence, you can perform the daily backup operations to your innobackupex options trading system without the need to having large storage available. The following steps describe a simple way to perform your physical incremental database backup using XtraBackup tool:.
To perform an incremental backup, we should first perform a full backup - the same like we did in the previous section - to be the base backup of the upcoming incremental backups.
We informed the Xtrabackup tool to perform an incremental backup by adding the command "--incremental", and by specifying the full-backup path as the basedir, we informed it from which backup it should start tracking the database changes. We informed the Xtrabackup tool to perform an incremental backup by adding the command "--incremental", and by specifying the 1st incremental backup path as the basedir, we informed it to start tracking the database changes innobackupex options trading the last incremental not the full backup.
We can create as many incremental backups as we want by using the same procedure above. Innobackupex options trading mentioned earlier in the article, the preparation process consists of two steps replaying the committed transactions and rolling back innobackupex options trading uncommitted transactions and using the --apply-log option only will do both of them like we did in the full backup but in innobackupex options trading incremental backups, we MUST do them separately as follows:.
The full backup folder will be the only folder to be restored innobackupex options trading no need to the incremental backup folders as it contains all data after appending the changes from all incremental backup. We can restore it the same way we did in the full backup restore. We can use the incremental backup strategy in order to perform differential backups, but we should consider the following points:.
Having differnetial backups in the middle of incremental backups will be useful for many reasons, such as:. The same like the other backup methods, the backed up files are not ready until we get them prepared by adding the "--export" option as follows:.
Some errors regarding those not included InnoDB tables from the backup innobackupex options trading be appeared, but that's fine. Also there will be a notification of creating the ". The restore process of the partial backup is quite different than innobackupex options trading full and incremental backups. Now, you can use the Xtrabackup tool to perform full, incremental and partial database backups, you can decide which method s of them are suitable for you according to the advantages and disadvantages of each one, and by considering the important hints for each method you can perform your backup efficiently.
About us Services Tools Resources Download. Prerequisites MySQL server installed. Download the xtrabackup tool. Install it as explained in the manual document. Below is the needed steps to make a full physical database backup using XtraBackup tool: Create Backup A simple Xtrabackup command to backup the full databases should be something like: Xtrabackup tool now created the backup files under the folder "full-backup" plus some extra files like innobackupex options trading file which contains some information useful in the incremental backups like: Prepare Full Backup The backed up files are not ready at the moment to be restored, we must prepare the backup files first as innobackupex options trading Now, the full backup is ready to be restored Restore Full Backup To get the full backup restored, the MySQL instance should be stopped first and then one of the following two procedures should be done: Using the copy back option: Using the operating system copy or move commands: If you don't want to keep the backup files on your local system you have another copy in an external serverthe move command will be very fast to get your backup restored: Prepare slave from full backup Preparing a slave using Xtrabackup is pretty easy and a straight forward process: Restore the full backup as explained above.
Check the binary logs information of the backup: However, the following steps should be done: Restore the full backup normally as explained above. Check the GTID value of the backup: Fast, simple and easy way to get your full database backed up and restored. All Xtrabackup tool features like streaming: Simple way to introduce a new slave to the master. We can't extract one single database or single table from the whole backup Unless it's MyISAM tablewhich means that you have to take it all or leave it all.
Important Hints The message innobackupex: The preparation process innobackupex options trading of two steps, replaying the committed transactions and rolling back the uncommitted transactions, using the --apply-log option only in the preparation command will do both steps for you.
Incremental Backup When you have a very large database system, you innobackupex options trading need innobackupex options trading enough storage to store your database backups in, and if you want to perform a daily backup then your mission will be more difficult.
The following steps describe a simple way to perform your physical incremental database backup using XtraBackup tool: Create Incremental Backup To perform an incremental backup, we should first perform a full backup - the same like we did in the previous section - to be the base backup of the upcoming incremental backups. Creating the full backup Base Backup: The "xtrabackup-checkpoints" file contents will be something like: Creating the first incremental backup: Creating the second incremental innobackupex options trading Prepare Incremental Backup As mentioned earlier in the article, the preparation process consists of two steps replaying the committed transactions and rolling back the uncommitted transactions and using the --apply-log option only will do both of them like we did in the full backup but in the incremental backups, we MUST do them separately as follows: Replay the committed transactions on the base backup by adding the option "--redo-only": Replay the committed transactions on the 1st incremental backup: Replay the committed transactions on the 2nd incremental backup: Finally, innobackupex options trading back all uncommitted transactions: Innobackupex options trading, the incremental backup is ready to be restored Restore Incremental Backup The full backup folder will be the only folder to be restored there's no need to the incremental backup folders as it contains all data after appending the changes from all incremental backup.
Less storage resources needed. Faster than the full backup. In addition to the disadvantages of the full backup, there are other ones: Complicate and hard process to implement as compared innobackupex options trading the full backup. If we have many incremental backups, appending all of them might consume time and might be confusing as well. If one of the incremental backups become corrupted or not available for any reason, we will not be able to add all incremental backups after that to the full backup.
If the "--redo-only" option was not be used in any of the preparation steps except the final stepall up coming incremental backups will be useless as we won't be able to add them to innobackupex options trading base backup anymore. Replaying the committed transactions steps bring all incremental data and append it to the full backup, so that, the rolling back of the uncommitted transactions step should be execute only on the full backup as it contains already the whole data.
In the incremental backups, Xtrabackup generates two files for every table ". The preparation time of the individual incremental backup will depend on how much data changed there since the last incremental. The preparation time for the full backup - in most cases - is really small as compared to the incremental ones because full backups apply the redo logs only while the incremental backups apply the deltas plus the redo logs. So if the delta files are big, the preparation process will take longer.
Full backups is recommended against Incremental backups if there are many changes applied on the DB, while the incremental backups are recommended when there are few changes applied on the DB. Differential backup We can use the incremental backup strategy in order to perform differential backups, but we should consider the following points: We always specify the full backup folder as the base backup in the incremental we specify the previous incremental folder as a base backup All incremental backups between differential and full backups MUST BE ignored when preparing the backup files because the differential backup contains already all changes since the last full backup.
In the backup preparation process, we should consider the last differential backup as the first incremental backup and all incremental backups after that could be applied normally.
Having differnetial backups in the middle of incremental backups will be useful for many reasons, such as: Differential backups reduce the chances of loosing the incremental backups if we have corrupted incremental backup in the middle, because in this case, differential backup will act as a backup of the previous incremental backups.
The following steps describing how to perform partial database backup using XtraBackup tool: Create Partial Backup A simple Xtrabackup command to backup some databases or tables should be something like: Prepare Partial Backup The same like the other backup methods, the backed up files are not ready until we get them prepared by adding the "--export" option as follows: Now, the partial backup is ready to be restored Restore Partial Backup The restore process of the partial backup is quite different than the full and incremental backups.
To restore a partial backup, the following steps should be made: Unlike the other methods Full and Incremental backupsMySQL instance on the destination server shouldn't be stopped because we will have to execute some SQL commands.
On the destination server, we should create new tables as many as we have in the partial backup or as we will restore with the same structure like the one in the partial backup and innobackupex options trading discard its table space: The streaming feature is not available in the partial backup. Beside the "--databases" option, other two alternative options to perform the same needs could be used but we must provide each table with the fully qualified naming format: