Overview of logical and physical backup for MySQL Database Backup

I. Preface

Logical backup and physical backup have their own advantages and disadvantages. Generally, physical Backup recovery is fast, occupies a large amount of space, and logical backup is slow and occupies a small amount of space. The recovery cost of logical backup is high.

Ii. Logical backup

Logical backup is a backup SQL statement. When restoring, execute the backup SQL statement to reproduce the database data.

1) mysqldump

Mysqldump adopts an SQL-level backup mechanism. It imports data tables into SQL script files and is the most common logical backup method.

Iii. Physical backup

Physical backup is used to back up data files. The more visual point is the data files under cp, but the actual backup is not as simple as cp.

1) Use the xtrabackup Tool

Is an open-source tool used to back up MySQL databases.

Main features:

<1>. Online hot backup. Innodb and myisam can be backed up. Innodb mainly applies the recovery principle. Myisam directly Copies files.

<2>. Supports stream backup. You can back up data to disks, tape, and reomot hosts. -Stream = tar./| ssh user @ remotehost cat ">"/backup/dir/

<3>. Incremental backup is supported. You can use the lsn and basic Backup Directory for Incremental backup.

<4>. The master log and master position information on slave can be recorded.

<5>. Support for simultaneous hot backup of multiple processes, and the stability of xtrabackup is quite good.

2) LVM

Features: Hot Backup, support for all local disk-based storage engines, fast backup, low overhead, easy to maintain integrity, and fast recovery.

3) cp + tar

You can directly copy database files for packaging and backup. Note the following steps: Lock tables, backup, and untable.

Recovery is also very simple, just copy it to the storage directory of the previous database files.

Note: For Innodb Engine tables, you also need to back up the log file, that is, the ib_logfile * file. Because when the Innodb table is corrupted, these log files can be recovered.

4) mysqlhotcopy

Mysqlhotcopy is a perl program that uses lock tables, flush tables, cp, or scp to quickly back up databases.

It is the fastest way to back up a database or a single table, but it can only run on the machine where the database files (including data table files, data files, and index files) are located.

Mysqlhotcopy can only be used to back up MyISAM.

5) use mysql master-slave Replication

Mysql replication transfers the DDL and DML operations of the primary database to the slave server through a binary file (bin-log, then, the slave server performs a re-execution on these logs to synchronize data between the slave server and the master server.