DB backups
==========
1> OS level
a) :: Shutdown the slave. Make the copy of whole system. Move the tar file and untar it on new system.
2> Percona backup/Recvoery (if using Community version)
:: backup - innobackupex --defaults-file=${MY_CNF} --user=${DB_USER} --password=${DB_PASS} --no-lock --parallel=4 --port=${DB_PORT} ${DEST_DIR} --no-timestamp
:: recovery -
a) Check space availability and Stop DB instance
b) copy and untar backup file
c) Remove data/log directories
d) innobackupex --defaults-file=/u01/app/mysql/${DB_NAME}/etc/my.cnf --apply-log $BACKUP_DIR/$BACKUP_DIREC
e) innobackupex --defaults-file=/u01/app/mysql/${DB_NAME}/etc/my.cnf --copy-back $BACKUP_DIR/$BACKUP_DIREC
f) Copy back configuration file
3> MySQL Enterprise backup/recovery (if using Enterprise edition)
:: backup - mysqlbackup --backup-image=/backups/sales.mbi --backup-dir=/backup-tmp backup-to-image backup-and-apply-log
backup-and-apply-log :: This option performs an extra stage after the initial backup, to bring all InnoDB tables up-to-date with any changes that occurred during the backup operation, so that the backup is immediately ready to be restored.
:: Restore - mysqlbackup --defaults-file=<backupDir>/backup-my.cnf -uroot --backup-image=<image_name> \
--backup-dir=<backupTmpDir> --datadir=<restoreDir> copy-back-and-apply-log
4> mysqldump - in case need to migrate 1 or 2 tables for some debugging.
:: --single-transaction - This option sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications. When using this option, you should keep in mind that only InnoDB tables are dumped in a consistent state. For example, any MyISAM or MEMORY tables dumped while using this option may still change state.
5> LVM Snapshots
==========
1> OS level
a) :: Shutdown the slave. Make the copy of whole system. Move the tar file and untar it on new system.
2> Percona backup/Recvoery (if using Community version)
:: backup - innobackupex --defaults-file=${MY_CNF} --user=${DB_USER} --password=${DB_PASS} --no-lock --parallel=4 --port=${DB_PORT} ${DEST_DIR} --no-timestamp
:: recovery -
a) Check space availability and Stop DB instance
b) copy and untar backup file
c) Remove data/log directories
d) innobackupex --defaults-file=/u01/app/mysql/${DB_NAME}/etc/my.cnf --apply-log $BACKUP_DIR/$BACKUP_DIREC
e) innobackupex --defaults-file=/u01/app/mysql/${DB_NAME}/etc/my.cnf --copy-back $BACKUP_DIR/$BACKUP_DIREC
f) Copy back configuration file
3> MySQL Enterprise backup/recovery (if using Enterprise edition)
:: backup - mysqlbackup --backup-image=/backups/sales.mbi --backup-dir=/backup-tmp backup-to-image backup-and-apply-log
backup-and-apply-log :: This option performs an extra stage after the initial backup, to bring all InnoDB tables up-to-date with any changes that occurred during the backup operation, so that the backup is immediately ready to be restored.
:: Restore - mysqlbackup --defaults-file=<backupDir>/backup-my.cnf -uroot --backup-image=<image_name> \
--backup-dir=<backupTmpDir> --datadir=<restoreDir> copy-back-and-apply-log
4> mysqldump - in case need to migrate 1 or 2 tables for some debugging.
:: --single-transaction - This option sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications. When using this option, you should keep in mind that only InnoDB tables are dumped in a consistent state. For example, any MyISAM or MEMORY tables dumped while using this option may still change state.
5> LVM Snapshots
No comments:
Post a Comment