Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.