MySQL Backup and restore simple introduction

Why would you want to back up?
Because of the use of some of their own mistakes in MySQL data loss or write a program to delete the data that should not be deleted, we know that the enterprise data is the most important, then for the security of the data, we must advance the data to the backup, in case of problems, it is good to save back.

    In fact, there are many reasons for the data disaster, some basic examples: hardware failure, software failure, natural disaster, hacker attacks, misoperation, etc. will lead to data loss, then in the production environment we back up the data becomes an essential link.
    generally there are three kinds of environment in the company, such as production environment, test environment and development environment. It's important that when we save the data from the disaster, don't assume that everything is going to be all right, a necessary part of it is that you need to test whether the number of saved back is complete, in case your recovery is missing any steps and omit the data to be recovered, so the test must be carried out. Of course, it's not just that the recovered data needs to be backed up, and the data we're backing up also has to be randomly pulled out to test whether the data we backed up is intact.
    
    Here are a few important points to keep in mind:
      tolerable loss of data;
  How long the      recovery needs to be done;
      What needs to be restored;
    
    What are the types of backups that follow?
         Full backup, partial backup:
             Partial backup: Backs up only one or more of the tables;
    
         full backup, incremental backup, differential backup

Full backup: Back up all data for the specified library
Incremental backup: Backs up only the portions of data that have changed since the last full or incremental backup;
Differential backup: Backs up only the portions of data that have changed since the last full backup;

Hot backup, warm backup and cold backup: Based on whether the server is online classified
Hot backup: Online backup, read and write operation is not affected;
Warm backup: Online backup, read operation can continue, but the write operation is not allowed;
Cold backup: Offline backup, database server offline, during backup can not provide read and write services for the business;
MyISAM: Support Win Bei
InnoDB: Hot Standby

From the backup, is to implement data directly import export or directly copy data files for backup, divided into:
Physical and logical backups:
Physical backup: Direct copy of the data file for backup; Physical backups are not cross-platform in some scenarios (backups performed under Linux may not be restored to Windows);
Logical backup: A backup of the "Export" data saved from the database, and a way to support network connection recovery and backup

Factors to consider when backing up a rule:
The length of the holding lock
Backup process Duration
Backup load
Recovery process Duration

Backup what?
Data, additional data (transaction logs for binary logs and InnoDB), code (stored procedures and stored functions, triggers, event schedulers, etc.), server configuration files

Designing a backup scenario is usually a full backup plus incremental backup

A full backup once a week, incremental backups every day

Backup tool:
Mysqldump: Logical Backup tool for all storage engines, Win Bei, full backup, partial backup, hot standby for InnoDB storage engine, (incremental backup only requires backup of binary log from full backup)
CP, tar and other file system Tools: Physical Backup tool for all storage engines, cold standby, full backup, partial backup;
LVM2 snapshot: Almost hot standby, with the help of file system tools to achieve physical backup;
Mysqlhotcopy: almost cold; only applicable to MyISAM storage engine;

Backup tool selection for back up scenarios:
Mysqldump+binlog (mainly used for point-in-time restore): mysqldump: Full backup, incremental backup via backup binary log;
LVM2 snapshot +binlog: Almost hot standby, physical backup
Xtrabackup:
For InnoDB: Hot standby, support for full and incremental backups (not implemented by backup binary logs, which themselves implement incremental backups), are physical backups
For MyISAM engine: Win Bei, only full backup is supported

This article is from the "Happy is good" blog, please be sure to keep this source http://wdllife.blog.51cto.com/6615958/1637833

MySQL Backup and restore simple introduction