Enterprise Backup
================
- MySQL Enterprise Backup provides enterprise-grade backup and recovery for MySQL. It delivers hot, online, non-blocking backups on multiple platforms including Linux, Windows, Mac & Solaris.
- MySQL Enterprise Backup delivers:
* “Hot” Online Backups – Backups take place entirely online, without interrupting MySQL transactions
* High Performance – Save time with faster backup and recovery
* Incremental Backup – Backup only data that has changed since the last backup
* Partial Backup – Target particular tables or tablespaces
* Compression – Cut costs by reducing storage requirements up to 90%
* Backup to Tape – Stream backup to tape or other media management solutions
* Fast Recovery – Get servers back online and create replicated servers
* Point-in-Time Recovery (PITR) – Recover to a specific transaction
* Partial restore – Recover targeted tables or tablespaces
* Restore to a separate location – Rapidly create clones for fast replication setup
- mysqlbackup --user=root --password --backup-dir=/u02/$DB_NAME backup-and-apply-log
- If wanted to have a unique folder for this backup, can use the –with-timestamp. The –with-timestamp option places the backup in a subdirectory created under the directory you specified above. The name of the backup subdirectory is formed from the date and the clock time of the backup run.
- If don’t use the backup-and-apply-log option you will need to read this: Immediately after the backup job completes, the backup files might not be in a consistent state, because data could be inserted, updated, or deleted while the backup is running. These initial backup files are known as the raw backup.
- You must update the backup files so that they reflect the state of the database corresponding to a specific InnoDB log sequence number. (The same kind of operation as crash recovery.) When this step is complete, these final files are known as the prepared backup.
- On the new server (where I will restore the data), I shutdown the mysqld process (mysqladmin -uroot -p shutdown), copied the my.cnf file to the proper directory, and now I can restore the database to the new server, using the copy-back option. The copy-back option requires the database server to be already shut down, then copies the data files, logs, and other backed-up files from the backup directory back to their original locations, and performs any required postprocessing on them.
- /usr/local/meb/bin/mysqlbackup --defaults-file=/etc/my.cnf --backup-dir=/Users/tonydarnell/hotbackups/2015-05-19_11-49-48 copy-back-and-apply-log
- An easy way to check to see if the databases match can use one of the MySQL Utilities – mysqldbcompare.
- The mysqldbcompare utility “compares the objects and data from two databases to find differences. It identifies objects having different definitions in the two databases and presents them in a diff-style format of choice. Differences in the data are shown using a similar diff-style format. Changed or missing rows are shown in a standard format of GRID, CSV, TAB, or VERTICAL.”
References:
https://www.mysql.com/products/enterprise/backup.html
http://www.oracle.com/us/products/mysql/mysql-enterprise-backup-362550.pdf
================
- MySQL Enterprise Backup provides enterprise-grade backup and recovery for MySQL. It delivers hot, online, non-blocking backups on multiple platforms including Linux, Windows, Mac & Solaris.
- MySQL Enterprise Backup delivers:
* “Hot” Online Backups – Backups take place entirely online, without interrupting MySQL transactions
* High Performance – Save time with faster backup and recovery
* Incremental Backup – Backup only data that has changed since the last backup
* Partial Backup – Target particular tables or tablespaces
* Compression – Cut costs by reducing storage requirements up to 90%
* Backup to Tape – Stream backup to tape or other media management solutions
* Fast Recovery – Get servers back online and create replicated servers
* Point-in-Time Recovery (PITR) – Recover to a specific transaction
* Partial restore – Recover targeted tables or tablespaces
* Restore to a separate location – Rapidly create clones for fast replication setup
- mysqlbackup --user=root --password --backup-dir=/u02/$DB_NAME backup-and-apply-log
- If wanted to have a unique folder for this backup, can use the –with-timestamp. The –with-timestamp option places the backup in a subdirectory created under the directory you specified above. The name of the backup subdirectory is formed from the date and the clock time of the backup run.
- If don’t use the backup-and-apply-log option you will need to read this: Immediately after the backup job completes, the backup files might not be in a consistent state, because data could be inserted, updated, or deleted while the backup is running. These initial backup files are known as the raw backup.
- You must update the backup files so that they reflect the state of the database corresponding to a specific InnoDB log sequence number. (The same kind of operation as crash recovery.) When this step is complete, these final files are known as the prepared backup.
- On the new server (where I will restore the data), I shutdown the mysqld process (mysqladmin -uroot -p shutdown), copied the my.cnf file to the proper directory, and now I can restore the database to the new server, using the copy-back option. The copy-back option requires the database server to be already shut down, then copies the data files, logs, and other backed-up files from the backup directory back to their original locations, and performs any required postprocessing on them.
- /usr/local/meb/bin/mysqlbackup --defaults-file=/etc/my.cnf --backup-dir=/Users/tonydarnell/hotbackups/2015-05-19_11-49-48 copy-back-and-apply-log
- An easy way to check to see if the databases match can use one of the MySQL Utilities – mysqldbcompare.
- The mysqldbcompare utility “compares the objects and data from two databases to find differences. It identifies objects having different definitions in the two databases and presents them in a diff-style format of choice. Differences in the data are shown using a similar diff-style format. Changed or missing rows are shown in a standard format of GRID, CSV, TAB, or VERTICAL.”
References:
https://www.mysql.com/products/enterprise/backup.html
http://www.oracle.com/us/products/mysql/mysql-enterprise-backup-362550.pdf
Thanks for sharing this information, this is useful to me.
ReplyDeleteLearn Mysq Online