Mysql Full Backup: -
https://dev.mysql.com/doc/mysql-enterprise-backup/3.12/en/mysqlbackup.full.html
Parameters on the command line or in the configuration file?
For clarity, the examples in this guide often show some of the command line options that are used with mysqlbackup commands. For convenience and consistency, you can enable those options that remain unchanged for most backup jobs in the [mysqlbackup] section of the MySQL configuration file that you supply to mysqlbackup. mysqlbackup also selects parameters from the [mysqld] section, if there is one. Entering parameters into the configuration file can simplify the administration of the backup for you: for example, entering a port of information in the configuration file, you can avoid the need to edit your backup scripts every time the database instance switches to a different port. See Chapter 14, “Configuration Files and Settings, for information on using configuration files.
Output in subdirectories with a single directory or wildcard?
For convenience, the --with-timestamp option creates an unambiguously named subdirectory under the backup directory to store the output from each backup job. Temporary subdirectories make it easy to set retention periods, making it easy to take and archive backup data that has passed a certain age.
If you use one backup directory (that is, if you omit --with-timestamp), either specify a new unique directory name for each backup job, or specify the -force option to overwrite existing backup files.
For incremental backups that use the --incremental-base option to specify the directory containing the previous backup, to make directory names predictable, you can not use --with-timestamp and generate a sequence of directory names with your backup script.
Always full backup or full backup plus incremental backup?
If your InnoDB data volume is small or your database is so busy that a large percentage of the data changes between backups, you may want to run a full backup each time. However, you can usually save time and storage by running periodic full backups and then doing several incremental backups between them, as described in Section 4.3.2, “Creating a Differential or Incremental Backup”.
Use compression or not?
Creating a compressed backup can save you significant storage space and significantly reduce I / O usage. And with the LZ4 compression method (introduced since release 3.10), the processing overhead of compression is pretty low. In cases where the database is backed up from a faster disk system, where active database files are possible, slower storage, compression will often be significantly lower than the total backup time. This can lead to shorter recovery times. Well. In general, we recommend LZ4 compression without compression for most users, since LZ4-based backups often end in shorter periods. However, test MySQL Enterprise Backup in yours to determine the most efficient approach.
The incremental backup feature is primarily intended for InnoDB tables or non-InnoDB tables that are read-only or rarely updated. For files other than InnoDB, the entire file is included in an incremental backup if that file has changed since the previous backup.
You cannot perform incremental backups with the --compress option.
Incremental backups detect page-level changes in InnoDB data files, as opposed to table rows; Each modified page will be copied. Thus, space and time savings are not exactly proportional to the percentage of modified InnoDB rows or columns.
When the InnoDB table is discarded and you perform the subsequent incremental backup, the Apply-log step removes the corresponding .ibd file from the full backup directory. Since the backup program cannot have the same idea of ​​the purpose of files other than InnoDB when a non-InnoDB file is deleted between the time of the full backup and the subsequent incremental backup, the application step does not delete this file from the full backup directory. Therefore, restoring a backup may cause the deleted file to reappear.
Create incremental backups using only the redo log
"-incremental-with-redo-log-only may offer some of the benefits of the -incremental option to create incremental backups:
Changes to InnoDB tables are determined based on the contents of the InnoDB change log. Since the repetition log files are fixed in size, you know in advance, it may require less I / O to read the changes than to scan the InnoDB tablespace files to find the modified pages, depending on the size of your database, the number of DML operations, and file size repeat log.
Because redo log files act like a circular buffer, with entries older changes are overwritten when new DML operations occur, you must accept new incremental backups in a predictable graph dictated by the size of the log files and the amount of re-generated data for your workload. Otherwise, the redo log may not go far enough to record all changes from the previous incremental backup, in which case mysqlbackup will quickly determine that it cannot continue and will return an error. Your backup script should catch this error and then do an incremental backup with - instead an alternative is used.
For instance:
To calculate the size of the redo log, enter the command SHOW VARIABLES LIKE 'innodb_log_file%' and, depending on the result, multiply the parameter innodb_log_file_size by the value innodb_log_files_in_group. To calculate the size of the retry log to the physical layer, look in the datadir directory of the MySQL instance and summarize the sizes of the files matching the ib_logfile * pattern.
InnoDB LSN is the number of bytes written to the redo log. To check the LSN at some point in time, run the SHOW ENGINE INNODB STATUS command and see the LOG header. While planning your backup strategy, periodically record LSN values ​​and subtract the previous value from the current one to calculate how much Duplicate data is generated every hour, day, etc.
Prior to MySQL 5.5, it was decided to keep retry logs small enough to avoid a long startup time when the MySQL server was killed and not closed normally. With MySQL 5.5 and above, disaster recovery performance has been greatly improved, as described in Optimizing InnoDB Configuration Variables, so that you can make your redo log files larger if that helps your backup strategy and your database load.
This type of incremental backup does not forgive too low a level of --start-lsn as a standard non-incremental option. For example, you cannot make a full backup, and then make a series --incremental-with-redo-log-only back up everything using the same value --start-lsn. Be sure to specify the exact end of the LSN of the previous backup as the initial LSN of the next incremental backup; do not use arbitrary values.
Note. To ensure that LSNs match exactly between successive incremental backups, it is recommended that you always use -incremental-base if you use the -incremental-with-redo-log-only option.
To judge if this type of incremental backup is practical for a particular instance of MySQL:
Measure how quickly data changes in InnoDB reuse log files. Check your LSN periodically to determine how many retries are accumulating over a number of hours or days.
Compare the speed of log re-accumulation with the size of the log file replay Use this ratio to see how often incremental backups are taken to avoid the likelihood of backup failing because historical data is not available in the redo log. For example, if you produce 1 GB of redo log data per day, and the total size of your redo log files is 7 GB, you plan to back up more often than once a week. You can perform incremental backups every day or two to avoid a potential problem when there is a sudden flurry of updates made more than usual.
Monitor incremental backup times using both --incremental and --incremental-with-redo-log-only to verify that the redo log is backed up with less overhead than the traditional incremental backup method. The result may depend on the size of your data, the number of DML operations, and the size of your repeat log files. Test on a server with realistic volume data and realistic workload. For example, if you have a huge repetition of log files, reading them during an incremental backup, you can as long as reading InnoDB data files using the traditional incremental technique. Conversely, if the amount of data is large, reading all the data files to find a few modified pages may be less efficient than processing much smaller log files.
Other considerations for incremental backups
The incremental backup feature is primarily intended for InnoDB tables or non-InnoDB tables that are read-only or rarely updated. Incremental backups detect page-level changes in InnoDB data files, as opposed to table rows; each changed page is backed up. Thus, saving space and time is not entirely proportional to the percentage of modified InnoDB rows or columns.
For files other than InnoDB, the entire file is included in the incremental file if this file has changed since the previous backup, which means that saving backup resources is less significant when compared with InnoDB tables.
You cannot perform incremental backups with the --compress option.
When creating an incremental backup based on the backup (full or incremental) created using the -no-locking option, use --skip-binlog to skip the binary log backup, since the binary log information will not be available for mysqlbackup in this situation .
Incremental Backup Examples
This example uses mysqlbackup to create an incremental backup of a MySQL server, including all databases and tables. We show two alternatives: one with the -incremental-base option, and the other with the -start-lsn option.
With the --incremental-base option, you do not need to track LSN values ​​between one backup and the next. Instead, you can simply specify the directory of the previous backup (full or incremental), and mysqlbackup determines the starting point for this backup based on the metadata of the previous one. Since you need a well-known set of directory names, you can use hard-coded names or generate a sequence of names in your own backup script, rather than using the --with-timestamp parameter.
$ mysqlbackup --defaults-file=/home/pekka/.my.cnf --incremental \ --incremental-base=dir:/incr-backup/wednesday \ --incremental-backup-dir=/incr-backup/thursday \ backup
... many lines of output ... mysqlbackup: The backup created in the '/ incr-backup / thursday' directory mysqlbackup: start_lsn: 2654255717 mysqlbackup: incremental_base_lsn: 2666733462 mysqlbackup: end_lsn: 2666736714z
101208 17:14:58 mysqlbackup: mysqlbackup completed OK! Please note that if your last backup was a single file instead of a directory backup, you can still use -incremental-base by specifying for the dir: directory_path directory the location of the temporary directory that you provided with the -backup-dir option during full backup copy.
As an alternative to specifying -incremental-base = dir: directory_path, you can tell mysqlbackup to request the end_lsn value from the last deleted backup recorded in the backup_history table on the server using -incremental-base = history: last_backup (this required the last backup was done with a connected mysqlbackup server).
You can also use the -start-lsn option to specify where the incremental backup should start. You must write the LSN of the previous backup that mysqlbackup reports at the end of the backup:
mysqlbackup: managed to parse the log to lsn 2654255716 The number is also written to the meta / backup_variables.txt file in the folder specified by --backup-dir during backup. Then put this number in mysqlbackup using the -start-lsn option. An incremental backup then includes all changes that have occurred after the specified LSN. Since the location of the previous backup is not very important, you can use --with-timestamp to create named subdirectories automatically.
$ mysqlbackup --defaults-file=/home/pekka/.my.cnf --incremental \ --start-lsn=2654255716 \ --with-timestamp \ --incremental-backup-dir=/incr-backup \ backup
... many lines of output ... mysqlbackup: backup created in the directory '/ incr-backup / 2010-12-08_17-14-48' mysqlbackup: start_lsn: 2654255717 mysqlbackup: incremental_base_lsn: 2666733462 mysqlbackup: end_lsn: 2666736714
101208 17:14:58 mysqlbackup: mysqlbackup completed OK! To create an incremental backup image, use the following command, specifying with --incremental-backup-dir a temporary directory to store metadata for backup and some temporary files:
$ mysqlbackup --defaults-file=/home/pekka/.my.cnf --incremental \ --start-lsn=2654255716 \ --with-timestamp \ --incremental-backup-dir=/incr-tmp \ --backup-image=/incr-backup/incremental_image.bi backup-to-image
In the following example, though, since the -backup image does not provide the full path to the image file to be created, an incremental backup image is created in the folder specified as -incremental-backup-dir:
$ mysqlbackup --defaults-file=/home/pekka/.my.cnf --incremental \ --start-lsn=2654255716 \ --with-timestamp \ --incremental-backup-dir=/incr-images \ --backup-image=incremental_image1.bi backup-to-image
https://dev.mysql.com/doc/mysql-enterprise-backup/3.7/en/mysqlbackup.incremental.html