MySQL backup recovery based on mysqldump and Lvmsnapshot

First, the Backup object

Data

Configuration file

Code: Stored procedures, stored functions, triggers

Configuration related to Replication

Binary log files

Second, backup tools

• Mysqldump: Logical Backup tool

InnoDB Hot Spares, MyISAM Win Bei, Aria Win Bei

Slow backup and recovery process

Mysqldumper: Multi-threaded mysqldump

Difficult to implement differential or incremental backups

• Lvm-snapshot:

Tools close to hot spare: because you want to request a global lock, then create a snapshot, and then release the global lock after the snapshot is created

Physical backup using tools such as copy, TAR, etc.

Faster backup and Recovery

It is difficult to implement incremental backups and request global locks to wait a while, especially on busy servers

• Innobase: Commercial Backup tool, Innobackup

Xtrabackup: Open Source Backup tool provided by Percona

InnoDB hot standby, incremental backup

MyISAM Win Bei, incremental backup not supported

Physical backup, fast speed

• Mysqlhotcopy: Almost cold prepared

Third, mysqldump (applicable data volume 5G or less)

Back up a single library, if the target library does not exist when recovering, you need to manually create

mysqldump [OPTIONS] database [tables]

Backing up a specified number of libraries

mysqldump [Options]--databases [options] DB1 [DB2 DB3 ...]

Back Up all libraries

mysqldump [Options]--all-databases [options]

Note: Lock before backup (the following two do not use at the same time)

--lock-all-tables: Request lock All tables before backup, Win Bei for MyISAM, InnoDB, Aria

--single-transaction: Enables hot provisioning of INNODB storage engines

Backup code:

--events: Backing up event Scheduler code

--routines: Backing up stored procedures and storage functions

--triggers: Backup Trigger

Scroll logs when backing up:

--flush-logs: Rolling logs before backup, request to lock

Synchronization location Tag when copying:

--MASTER-DATA=[0|1|2]

0: Do not record

1: Record as change master statement

2: Change master statement recorded as comment

Using mysqldump Backup:

Request Lock:--lock-all-tables or using--single-transaction for InnoDB hot standby

Scrolling log:--flush-logs

Select the library to back up:--databases

Record binary files and be in location:--master-data=2

Mysqldump--databases Fansik--lock-all-tables--flush-logs > Fansik.sql

Or

Mysqldump--databases Fansik--single-transaction--flush-logs > Fansik.sql

Specific operation:

Recovery:

Recommendation: Turn off binary logging and close other user connections

Backup strategy: Based on mysqldump

Backup: mysqldump+ binary log file

Sunday make a full backup: Rolling logs at the same time as backups

Monday to Saturday: Backing up binary logs

Recovery:

Full backup + events from each binary log file to the moment

The MySQL configuration file and the MySQL-related configuration file should be backed up after each modification

Iv. lvm-snapshot: LVM Snapshot-based backup

1, the transaction log and the data file file must be on the same volume;

2. To request the global lock of MySQL before the snapshot is created, release the lock after the snapshot creation is complete;

3, the request global lock completed, do a log scrolling; binary log files and location tags (manual);

Five, lvm-snapshot backup steps

1, request global lock, and scroll log

Mysql> FLUSH TABLES with READ LOCK;

Mysql> FLUSH LOGS;

2, do binary log file and location tag (manual);

# mysql-e ' Show Master status ' >/tmp/fansik

3. Create a Snapshot volume

# lvcreate-l 100m-s-N fansik-p r/path/fansik_lv

4. Release the global lock

Mysql> UNLOCK TABLES;

5. Mount the snapshot and back up

Cp

6. Delete the snapshot volume after the backup is complete (uninstall)

Vi. lvm-snapshot Recovery Steps

1. The binary log is saved so that all events after the backup are extracted into a SQL script

2, restore data, modify permissions and the main array, etc., and start MySQL

3, block instant point Restore

MySQL backup recovery based on mysqldump and Lvmsnapshot