MySQL backup
MySQL backup
1. MySQL backup process:
MyISAM
Complete:
a. Lock table-LOCK TABLE-lock-all-tables
b. Synchronization table-FLUSH TABLES
c. Refresh binary log-FLUSH LOGS-flush-logs
d. Record binary log location-SHOW MASTER STATUS-master-data=2
e. Backup data-mysqldump-databases
f. Release the lock-UNLOCK TABLES
g. Delete the old binary log-PURGE BINARY LOGS TO
Summary command:
Mysqldump-databases-lock-all-tables-master-data=2-flush-logs DB_NAME > db_name-datetime.sql
Incremental backup:
a. Refresh binary log-FLUSH LOGS
b. Just copy the old binary log
InnoDB
Complete:
a. Refresh the binary log
b. Record the current binary log location
c. Delete old binary log
* relevant locking tables and other operations are performed automatically in the database, which can be completed by the parameter-single-transaction.
Summary command:
Mysqldump-databases-master-data=2-flush-logs-single-transaction DB_NAME > db_name-datetime.sql
Incremental backup:
a. Refresh log-FLUSH LOGS
b. Just copy the old binary log
Backup script
[root@ibm01 ~] # cat sqlbackup.sh
#! / bin/bash
DB_Name= "$1"
DB_Egine= "$2"
Backup_Type= "$3"
Backup_Time=date +% Y%M%d%H%M%S
Backup_File=$DB_Name-$Backup_Time.sql
Data_Dir= "/ data/mysql/logs/"
Function FullBackup () {
If [$DB_Egine = = "MyISAM"]
Then
Mysqldump-databases-lock-all-tables-flush-logs-master-data=2 $DB_Name > $Backup_File
Elif [$DB_Egine = = "InnoDB"]
Then
Mysqldump-databases-single-transaction-flush-logs-master-data=2 $DB_Name > $Backup_File
Fi
Current_BinLog_File=egrep-o "MASTER_LOG_FILE=.*," $Backup_File | cut-d "="-f2 | tr-d ","
Mysql-e "PURGE BINARY LOGS TO $Current_BinLog_File;"
}
Function IncrBackup () {
BinLog_Files=mysql-N-e "SHOW BINARY LOGS;" | cut-F1 | tr "\ n"
Mysql-e "FLUSH LOGS;"
Mkdir / root/binlogs/$Backup_Time
Cd $Data_Dir
Cp $BinLog_Files / root/binlogs/$Backup_Time
}
If [$Backup_Type = = "Full"]
Then
FullBackup
Elif [$Backup_Type = = "Incr"]
Then
IncrBackup
Fi
Xtrabackup backup:
1. Incremental backup can be implemented for innodb, but only fully available for myISAM
2. Innobackupex / backuppath (backup)
3. Innobackupex-- apply-log / backuppath/backuptime (application transaction log)
4. Innobackupex-- copy-back / backuppath/backuptime (restore)
Xtrabackup incremental backup:
Backup:
1. Innobackupex-- incremental--incremental-basedir=/ Last backup path / backup path
The recovery of an incremental backup requires three steps:
1. Restore a full backup
2. Restore incremental backup to full backup (add-- redo-only parameter for the first incremental backup, and remove-- redo-only for the last incremental backup)
3. Restore the overall full backup and roll back the uncommitted data
Recovery process:
# # prepare a complete # #
[root@centos6 pxb] # innobackupex-- apply-log-- redo-only / data/pxb/2017-04-2411 02-46-11 /
Xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2628145
InnoDB: Number of pools: 1
01:33:52 completed OK!
# # apply increment 1 to full backup # #
[root@centos6 pxb] # innobackupex-- apply-log-- redo-only / data/pxb/2017-04-249-02-46-11 /-incremental-dir=/data/pxb/inc/2017-04-28-01-09-40 /
# # apply the increment 2 to the full backup, do not add the-redo-only parameter # #
[root@centos6 pxb] # innobackupex-- apply-log / data/pxb/2017-04-240236-11 /-- incremental-dir=/data/pxb/inc/2017-04-280127-46 /
# # perform an apply operation on all the combined full backups and roll back the uncommitted data # #
[root@centos6 pxb] # innobackupex-- apply-log / data/pxb/2017-04-240224-46-11 /
Xtrabackup: Log applied to lsn 2628145
Xtrabackup: The intended lsn is 2643563
