MySQL backup and Restoration

Outline

I. MySQL Backup Type
2. What are MySQL backups?
Iii. MySQL backup tools
Iv. MySQL backup policy
V. Backup preparation
Vi. demo of backup policy
Note: The system version CentOS6.4 X86_64 and MySQL version 5.5.32

Implementation of MySQL backup and recovery

Summary of three methods for MySQL backup and recovery

MySQL backup and restoration (views and stored procedures)

I. MySQL Backup Type

1. Hot Backup, warm backup, and cold backup (based on the server status)

• Hot Backup: Read and Write are not affected;

• Warm backup: only read operations can be performed;

• Cold backup: offline backup; read and write operations are suspended;

2. Physical backup and logical backup (from objects)

• Physical backup: Copy data files;

• Logical backup: export data to a text file;

3. Full backup, Incremental backup, and differential backup (from data collection)

• Full backup: Back up all data;

• Incremental Backup: only the data changed after the last full backup or Incremental backup is backed up;

• Differential backup: only the data changed since the last full backup is backed up;

4. Advantages of logical backup:

• The backup speed depends on different storage engines.

• Physical backup can be restored quickly. However, the minimum physical backup can only be performed on tables.

• The Structure Stored in logical backup is usually pure ASCII, so we can use a text processing tool to process

• Logical backup has strong compatibility, while physical backup has high version requirements.

• Logical backup also ensures data security.

5. disadvantages of logical backup:

• Logical backup requires additional pressure on RDBMS, while bare backup does not.

• The logical backup result may be larger than the source file. Therefore, many people compress the backup content.

• The precision of floating point numbers may be lost in logical backup.

6. Differences between Incremental backup and differential backup

Note: Differential backup takes more space than Incremental backup, but it is easier to recover! But we generally use Incremental backup!

2. What are MySQL backups?

We usually back up the following parts:

1. Data Files

2. log files (such as transaction logs and binary logs)

3. Stored Procedures, stored functions, and triggers

4. configuration files (very important. All configuration files must be backed up)

5. scripts used for database backup, Croutab for database cleaning, etc ......

Iii. MySQL backup tools

For example,

For the comparison of all the above backup tools, let's take a look at the commonly used backup tools,

1. Mysql built-in backup tools

• The mysqldump logical backup tool supports all engines. The MyISAM engine is a warm backup, and the InnoDB engine is a hot backup. The backup speed is medium and the restoration speed is very slow. However, when restoring data, there is a lot of room for operation. Good elasticity.

• Mysqlhotcopy physical backup tool, but only supports the MyISAM engine, basically belongs to the category of cold backup, physical backup, fast.

2. File System Backup tools

• Cp cold backup supports all engines and copy commands, which can only be used for cold backup and physical backup. Archive tools and cp commands are used to back up the data. The backup speed is fast and the restoration speed is almost the fastest. However, the flexibility is very low, and the data can be cross-system, but the cross-platform capability is poor.

   

• Lvm is almost a hot backup. It supports all engines and physical backup Based on snapshots (LVM, ZFS). It is very fast and almost always hot backup. It takes only a few seconds for the data to be affected. However, the snapshot creation process affects online database usage. Therefore, the backup speed is faster and the recovery speed is faster. There is no elastic space, and the LVM restrictions are as follows: multiple logical volumes cannot be backed up at the same time. Therefore, data files, transaction logs, and other files must be stored on the same LVM. ZFS can be backed up between multiple logical volumes.

3. Other Tools

• Ibbackup commercial tool MyISAM is a warm backup, InnoDB is a hot backup, and the backup and restoration speed are very fast. This software has an Authorized Version of $5000 per server.

• Xtrabackup open-source tool MyISAM is a warm backup, InnoDB is a hot backup, and is an alternative tool for ibbackup commercial tools.

1 2 3 4 Next Page