MySQL backup and recovery in a detailed

MySQL Data backup

Inside MySQL, there are logical backups and physical backups. The biggest advantage of logical backups is that the same method can be used to back up the various storage engines. While physical backups are different, different storage engines have different backup methods.

Logical Backup and Recovery backup

In MySQL, the logical backup is to use mysqldump to back up the data in the database as a text file, and the backed up files can be viewed and edited. Depending on the scope of the backup, you can divide the backup into the following three types of backups.

• Backs up a specified database or some tables in the database
  mysqldump [Options] Database name [table name] > Data.sql
• Backing up a specified number of databases
  mysqldump [options]–database > Data.sql Database 1 Database 2 database 3 ...
• Back up all the databases
  mysqldump [Options]–all-database > Data.sql
  Parse: [Options] Backup time, required permissions information, etc. There are many options for mysqldump, which can be viewed through mysqldump–help. In order to ensure data backup consistency, MySQL storage engine in the north, the need to add the-l parameter, indicating that all tables are read-lock, during the backup, all tables will be read only, not write. But for the InnoDB engine, –single-transaction can be used. Data.sql backed-up data files
  Parameters:
  L: Delegates lock all tables
  F: Represents the generation of a new day file

The instance backs up all tables in the XXPT database to the Dequan.sql table. The command is as follows:
Mysqldump-uroot-p XXPT >dequan.sql

Because I did not specify a backup path above, by default it is backed up to the current path, so it is backed up to the D:\wamp\bin\mysql\mysql5.6.17\bin path.

Restore Full recovery

The recovery of the mysqldump is also simple, and the backup is performed as input. The results are as follows:
Mysql-uroot-p dbname

Not fully recovered

Incomplete recovery includes point-in-time recovery and location-based recovery. The point-in-time and position are relative to the time and location of the binary log (Binlog log).

Based on point in time

will be set 4:00 to 5:00 before the data room error, recovery time, need to skip. First, let's take a look at the Binlog log. If more than 4 o'clock in the afternoon update data is wrong, restore time need to skip, 5 o'clock in the afternoon more delete data is correct, need to keep.

1. by Mysql-uroot-p dbname

Location-based Recovery

When using location recovery, we need to review the Binlog log file, determine the location number, and then use the following command to recover:
Mysqlbinlog d:\wamp\bin\mysql\mysql5.6.17\data\mybinlog.000012–stop-position=716406|mysql-uroot-p
Restore operations after 5 points
D:\wamp\bin\mysql\mysql5.6.17\data\mybinlog.000012–start-position=723613|mysql-uroot-p

Physical Backup and recovery

Physical backups are divided into cold and hot backups. Compared to logical backups, it has the greatest advantage of faster backup and recovery. Because the principle of physical backup is file-based CP.

Cold Backup and Recovery

Cold backup is actually the method that will stop the database service and copy the data file. This method is suitable for both MyISAM and InnoDB.
Restore: First stop the MySQL service, restore the MySQL data file at the operating system level, then restart the MySQL service and use the Mysqlbinlog tool to restore all binlog since the backup.

Hot backup

Different storage engines are not the same in MySQL when they are hot backed up.

MyISAM Storage Engine

The MyISAM storage Engine backup principle is the table read lock that will be backed up, and then the CP data file to the backup directory. Common methods

• Method One: Use Mysqlhotcop
  MYSQLHOTCOP db_name [Catalogue]
• Method Two: Manual lock table Copy
  First, read the lock on all tables in the database and then the CP data.
  Lock all tables flush tables with read lock;
  

InnoDB Storage Engine

Learning ...

Import and export of tables

• Use SELECT ... Into OUTFILE ... +[options] Command implementation
  About the options parameters are as follows
  
  The default path is the path that corresponds to the data:
  
  
• Using mysqldump
  Mysqldump-u username-t TargetDir dbname Tablename[options]
  Like what:
  Mysqldump-uroot-p-T D:/WAMP/BIN/MYSQL/MYSQL5.6.17/XXPT T1
  Two files were generated, such as:
  
  T1.txt The data information in the T1.sql file, the contents of the files are as follows

--MySQL dump 10.13 distrib 5.6.17, for Win32 (x86)----Host:localhost database:xxpt-- --------------------------------------------------------Server version 5.6.17-log/*!40101 SET @[email protected] @CHARACTER_SET_CLIENT * /;/*!40101 SET @[email protected] @CHARACTER_SET_RESULTS * /;/*!40101 SET @[email protected] @COLLATION_CONNECTION * /;/*!40101 SET NAMES UTF8 * /;/*!40103 SET @[email protected] @TIME_ZONE * /;/*!40103 SET time_zone= ' +00:00 ' * /;/*!40101 SET @[email protected] @SQL_MODE, sql_mode= "* *;/*!40111 SET @[email protected] @SQL_NOTES, sql_notes=0 * /;----Table structure for table ' T1 '-- DROP TABLE IF EXISTS ' t1 ';/*!40101 SET @saved_cs_client = @ @character_set_client * /;/*!40101 SET character_set_client = UTF8 * /; CREATE TABLE ' t1 ' ( ' id1 ' int(One-  by-one) not NULL  DEFAULT ' 0 ', ' id2 ' int(3) unsigned zerofill  not NULL DEFAULT ' $ ') Engine=innodb DEFAULT Charset=utf8;/*!40101 SET character_set_client = @saved_cs_client * /;/*!40103 SET [email protected]_time_zone * /;/*!40101 SET [email protected]_sql_mode * /;/*!40101 SET [email protected]_character_set_client * /;/*!40101 SET [email protected]_character_set_results * /;/*!40101 SET [email protected]_collation_connection * /;/*!40111 SET [email protected]_sql_notes * /;--Dump completed on 2016-04-18 19:30:51

Import

Method One:
Load Data infile
eg
Load data infile ' d:/wamp/bin/mysql/mysql5.6.17/t1.txt ' into table T1;

Method Two: Use Mysqlinport

MySQL backup and recovery in a detailed