Mysqlbackup backup details (MySQL official Backup tool)

A.1 Full Library Backup.

Command:

Mysqlbackup--defaults-file=/home/mysql-server/mysql3/my.cnf--user=root--password=root--databases= "MySQL total2" --with-timestamp--backup-dir=/home/mysql-server/backup Backup

Parameter description:

The path to the--defaults-file my.cnf file is used primarily for multiple MySQL services on a single server. The default location is/ETC/MY.CNF

--user user name, this user must be in the MySQL library to create table and query, insert permissions. During the backup process. Mysqlbackup will build backup_history under the MySQL library, Backup_ Progress table. The user retains the historical information for the backup and the underlying information for the backup.

--password Password

--database need to back up the database, to back up multiple databases need to "" included, each database separated by a space

--with-timestamp the user creates a folder under the current time in the backup directory, if there is no such parameter, when multiple backups are made, the same directory is created, overwriting the last backup file.

--backup-dir the backup directory

Backup indicates that this is a back-up operation

A.2 Full-Library restore.

First step: Detecting transaction logs

Mysqlbackup--defaults-file=/home/mysql-server/mysql2/my.cnf--backup-dir=/home/mysql-server/backup/2012-03-21_ 14-01-34/apply-log

Parameter description:

Apply-log: Because it is online at the time of the backup, if there is a newly inserted SQL statement, the newly added LSN point is recorded, and then the newly modified page is placed in the file (Ibbackup_logfile) and also in the table space. When the restore uses this parameter, Mysqlbackup detects the LSN points of the ibbackup_logfile and tablespace, and then compares the difference between the Ibbackup_logfile file Tablespace LSN and puts this value in the transaction log logs. (If the transaction log fills up, it goes into the table space.)

Step two: Copy physical files

Mysqlbackup--defaults-file=/home/mysql-server/mysql2/my.cnf--backup-dir=/home/mysql-server/backup/2012-03-21_ 14-01-34/--innodb_log_files_in_group=2 Copy-back

Note: In the my.cnf file, you need to have datadir parameters when restoring.

B.1 Compressed full-Library backup

Mysqlbackup--defaults-file=/home/mysql-server/mysql3/my.cnf--user=root--password=root--compress-level=1-- databases= "MySQL total2"--with-timestamp--backup-dir=/home/mysql-server/backup Backup

Parameter description:

--compress-level=1

1 for fast compression. Total 9 Levels

B.2 Compress Restore

The first step: detecting transaction logs and extracting

Mysqlbackup--defaults-file=/home/mysql-server/mysql2/my.cnf--uncompress--backup-dir=/home/mysql-server/backup/ 2012-03-21_14-08-33/apply-log

Uncompress unzip the compressed file.

Step two: Copy physical files

Mysqlbackup--defaults-file=/home/mysql-server/mysql2/my.cnf--innodb_log_files_in_group=2--backup-dir=/home/ Mysql-server/backup/2012-03-21_14-08-33/copy-back

Note: In the my.cnf file, you need to have datadir parameters when restoring.

Incremental backups do not support compression. Backup-and-apply-log parameter cannot be used with--compress-level

C.1 The transaction log at the same time as the backup

Backup

Mysqlbackup--defaults-file=/home/mysql-server/mysql3/my.cnf--user=root--password=root--databases= "MySQL total2" --with-timestamp--backup-dir=/home/mysql-server/backup Backup-and-apply-log

Parameter description:

Backup-and-apply-log This parameter, at the time of the backup, the function of transaction log detection is completed, and the Ibbackup_logfile and table space difference of the LSN content into the transaction log, so at the time of the restore, only need to back up the physical files on the line

Restores

Mysqlbackup--defaults-file=/home/mysql-server/mysql2/my.cnf--innodb_log_files_in_group=2--backup-dir=/home/ Mysql-server/backup/2012-03-21_19-25-09/copy-back

D.1 Incremental Backup

Incremental backup for the first time:

Mysqlbackup--defaults-file=/home/mysql-server/mysql3/my.cnf--user=root--password=root--with-timestamp

--databases= "MySQL total2"--incremental--incremental-backup-dir=/home/mysql-server/in/--incremental-base=dir:/ Home/mysql-server/backup/2012-03-22_11-10-28 Backup

Parameter description:

--incremental: Represents an incremental backup.

--incremental-backup-dir: Which path the incremental backup is stored in

--incremental-base: Incremental backup of the underlying backup or incremental backup of the file

Second Incremental backup:

Mysqlbackup--defaults-file=/home/mysql-server/mysql3/my.cnf--user=root--password=root--with-timestamp

--databases= "MySQL total2"--incremental--incremental-backup-dir=/home/mysql-server/in/--incremental-base=dir:/ Home/mysql-server/backup/2012-03-22_11-15-28 Backup

Parameter description:

--incremental-base: The backup here is the path to the last incremental backup

Incremental backup principle: Incremental backup is based on the complete last LSN point after the first full backup is backed up. When the second incremental backup, Backups are based on the basis of the LSN point of the previous incremental backup. So--incremental-base this parameter, when incremental, refers to the LSN point of a full or incremental backup.

D.2 Incremental Restore

1. Full standby detection matching release transaction log

Mysqlbackup--defaults-file=/home/mysql-server/mysql2/my.cnf--backup-dir=/home/mysql-server/backup/2012-03-21_ 17-59-54/apply-log

2. Detect the incremental backup for the first time matching release

Mysqlbackup--backup-dir=/home/mysql-server/backup/2012-03-21_17-59-54/--incremental-backup-dir=/home/ Mysql-server/in/2012-03-22_11-47-47/apply-incremental-backup

3. Detect the incremental backup that matches the release second time

Mysqlbackup--backup-dir=/home/mysql-server/backup/2012-03-21_17-59-54/--incremental-backup-dir=/home/ Mysql-server/in/2012-03-22_11-50-47/apply-incremental-backup

4. Final physical file copy

Mysqlbackup--defaults-file=/home/mysql-server/mysql2/my.cnf--backup-dir=/home/mysql-server/backup/2012-03-21_ 17-59-54/copy-back

Incremental Restore principle:

1. First detect the matching release fully prepared transaction log file (of course, if the backup is used Backup-and-apply-log, in the backup, the match has been detected, there is no need for this step)

2. The first incremental backup file is released into the full file. (The transaction log is first entered, then the tablespace), so--backup-dir points to the fully-prepared directory (incremented based on LSN points)

3. The second incremental backup file is also released into the full file. (The transaction log is first entered, then the tablespace), since the first incremental backup has already had the first LSN point in the whole library, so the two restore points also point to the full file inside to increase the LSN point outside

4. Because incremental pages have all entered the transaction log or table space, this time, you can back up the physical files directly.

But in the end still remember ...

# =======================================================================

Put back the system library MV to the original place

Modify the permissions of the data directory (if you are setting up a MySQL user access)

Chown-r Mysql.mysql Data

# =========================================================================

When you start MySQL server:

Mysqlbackup Working principle

1. Mysqlbackup physically replicates the InnoDB tablespace, but it records the LSN point, and during the backup process, the newly added input is written directly to the ibbackup_logfile of the backup file. Record the last LSN point at the same time

2. Mysqlbackup to MyISAM is the lock table full. Even if it is an incremental backup, it is still fully prepared.

3. When restoring, detect the difference between the Ibbackup_logfile file and the table space, so that the data inside the Ibbackup_logfile into the transaction log or table space

4. Some information about the backup is recorded in the Meta/backup_variables.txt file in the backup file

[Backup_variables]

start_lsn=1602048 #开始备份的LSN点

end_lsn=687810168 # End LSN Point

Apply_log_done=1 # Whether to release the instrumented Ibbackup_logfile file (0 means no, 1 means has been freed)

Is_incremental=0 #是否是增量为增量备份文件 (0: No, 1: YES)

Is_incremental_with_redo_log_only=0 # Only the redo log is configured, and when the input data size is redo log size, there will be a throw

Is_partial=1 # is server backup or partial backup (0 for full server backup, 1 for partial backup)

Is_compressed=0 #是否压缩 (0 means no compression, 1 means compression)

binlog_position=mysql-bin.000001:107 # binary File size

Is_onlyinnodb=0 #是否只备份了innodb的表

Mysqlbackup backup details (MySQL official Backup tool)