MySQL Backup and recovery

In a real-world production environment, data is critical. The key to saving a company is how to implement secure redundancy of data and quickly recover data in the event of an accident or disaster. Then, backing up data on a regular basis is a basic work.

Type of Backup:

Depending on the amount of data backed up can be divided into:

Full backup: Backing up the entire data set

Partial backup: Backing up part of a data set

Incremental backup: Backs up data that has changed since the last full or incremental backup (if present)

Cons: Recovery is more cumbersome due to multiple increments possible

Differential backup: Backs up only data that has changed since the last full backup

Cons: A large amount of data, more wasted disk space

Depending on how you backup, you can divide it into:

Physical Backup: Copy data files directly for backup

Logical backup: A backup that is independent of the storage engine and that "export" data is saved from the database

Depending on the available state of the data during backup, it can be divided into:

Hot spare: Data is readable and writable when you back up

Win Bei: Data is readable only when backing up

Cold: Data is not readable and writable when backing up

Key points to consider when backing up:

① can tolerate the maximum amount of data lost

② The amount of time it takes to recover data

What data ③ need to recover

④ Choose the right tool based on the amount of data backed up

⑤mysql binary logs are critical and must be retained for point-in-time restore

The next step is to state data backup and recovery based on Mysqldump,xtrabackup.

　　First, mysqldump

MySQL comes with a logical backup tool for all storage engines, supports full and partial backups, supports Win Bei, and supports hot standby for InnoDB

mysqldump [Options] db_name [Tb1_name,...  --databases db_name ...  --all-databases

Common options:

-uusername//the user at the time of backup-ppassword//user's password when backing up-e,--events//backs up all event schedulers related to the specified database-r,--routines//backing up specified database-related stored procedures and storage functions--[skip-]triggers//(not) backing up related triggers--master-data[=#]1:the change MASTER to statement that is recorded as not being commented;2.Change MASTER to statement recorded as comment; (General 2)--Flush-logs//After the lock table is complete, execute the flush logs command;//Win Bei option, lock the backup library first, and then start the backup operation (MyISAM and InnoDB)--lock-all-tables//Lock all tables for all libraries (production environment is used with caution)--lock-tables//lock all tables in a single databaseHot -Standby options--single-transaction//start a huge transaction

　　Second, Xtrabackup

is a tool provided by Percona to support a hot standby (physical backup) of InnoDB for full backup, incremental backup.

Backup command: Xtrabackup

Recommended use: Innobackupex (Perl script for Xtrabackup two times), relatively simple and lightweight;

Create a backup recovery user (based on least privilege)

MariaDB [(None)]> CREATE USER ' backupuser ' @ ' 192.168.1.102 ' identified by ' BackupPass '; MariaDB [(None)]> GRANT reload,lock tables,replication CLIENT on * * to ' backupuser ' @ ' 192.168.1.102 '; MariaDB [(none)]FLUSH priviliges;

① Full Backup

Innobackupex--user=dbuse--password=dbusepass/path/to/backup-DIR/

Finishing operations based on a full backup before recovery:

Innobackupex--apply-log /path/to/backup-Dir(the directory in which the backup files are located)// collation Error Completed OK to perform a full backup recovery operation:Innobackupex--copy-back/path/to/backup-Dir(sorted backup file directory)  // Note: When executing the Innobackupex--copy-back command, you do not need to start the MySQL service and stop configuring the MySQL data directory. Do not forget to change the data file's owner group to MySQL after successful recovery

② Incremental Backup

Innobackupex--incremental/backup--incremental-basedir=basedir/* where basedir refers to the directory where the full backup resides.    at the end of this command execution, the Innobackupex command creates a new time-named directory in the/backup directory that has all the incremental backup data stored.    In addition, when performing an incremental backup again, its--incremental-basedir should point to the same directory as the last incremental backup note: Incremental backups can only be applied to InnoDB and XTRADB tables for the storage engine, and for MyISAM tables, Performing an incremental backup is actually a full backup * /

Grooming based on full backup and incremental backup before recovery:

/*Unlike a full backup recovery: (1) You need to "replay" a transaction that has already been committed on each backup, including full and individual incremental backups. After replay, all backup data is merged into a full backup. (2) "Roll Back" uncommitted transactions based on all backups. *///then, execute the order of the collation:Innobackupex--apply-Log--redo-only base-DIR//then executeInnobackupex--apply-Log--redo-only base-DIR--incremental-dir=incremental-DIR-1//And then the second increment:Innobackupex--apply-Log--redo-only base-DIR--incremental-dir=incremental-DIR-2//where Base-dir refers to the directory where the full backup is located, and Incremental-dir-1 refers to the first incremental backup of the directory, incremental-dir-2 refers to the second incremental backup of the directory, and so on, if there are multiple incremental backups, Each time you perform the action as above. After finishing, perform the recovery data operation:Innobackupex--Copy-back/path/to/backup-Dir(The sorted backup file directory)

　　　　

　　

MySQL Backup and recovery