Detailed explanation of MySQL backup and recovery

MySQL data backup

In mySQL, there are logical backups and physical backups. The biggest advantage of logical backup is that the same method can be used for backup of various storage engines. Physical backup is different, and different storage engines have different backup methods.

Logical backup and recovery

Backup

In MySQL, logical backup uses mysqldump to back up the data in the database as a text file. The backed up file can be viewed and edited. . According to the backup scope, backup can be divided into the following three types of backup.

• Back up a specified database or some tables in the database
  mysqldump [options] database name [table name] > data.sql

• Back up multiple specified databases
  mysqldump [options] –database > data.sql database 1 database 2 database 3...

• Back up all Database
  mysqldump [options] –all-database > data.sql
  Analysis: [options] During backup, required permission information, etc. There are many options for mysqldump, which can be viewed through mysqldump –help. In order to ensure the consistency of data backup, when the MySQL storage engine is in the north, you need to add the -l parameter, which means to add read locks to all tables. During the backup period, all tables will only be read, not written. But for the InnoDB engine, –single-transaction can be used. Data file backed up by data.sql
  Parameters:
  l: represents locking all tables
  f: represents generating a new daily file

The instance will be xxpt All tables in the database are backed up to the dequan.sql table. The command is as follows:
mysqldump -uroot -p xxpt >dequan.sql

Because I did not specify the backup path above, by default, the backup will be to the current path, so the backup will be to D:\ The wamp\bin\mysql\mysql5.6.17\bin path is under.

Recovery

Full recovery

Mysqldump recovery is also very simple, just execute the backup as input. The results are as follows:
mysql -uroot -p dbname

Incomplete recovery

Incomplete recovery includes point-in-time-based recovery and location-based recovery. The time point and position correspond to the time point and position in the binary log (binlog log).

Based on time point

If the data room is set to be wrong between 4:00 pm and before 5:00 pm, it needs to be skipped during recovery. First let's take a look at the binlog log. For example, updating data after 4 pm is wrong and needs to be skipped during recovery. Deleting data after 5 pm is correct and needs to be retained.

1. Through mysql -uroot -p dbname

Location-based recovery

When using location recovery, we need to first check the binlog log file to determine the location number , and then use the following command to restore:
mysqlbinlog D:\wamp\bin\mysql\mysql5.6.17\data\mybinlog.000012 –stop-position=716406|mysql -uroot -p
Restore the data after 5 points Operation
D:\wamp\bin\mysql\mysql5.6.17\data\mybinlog.000012 –start-position=723613|mysql -uroot -p

Physical backup and recovery

Physical backup is divided into cold backup and hot backup. Compared with logical backup, its biggest advantage is the fast backup and recovery speed. Because the principle of physical backup is based on file cp.

Cold backup and recovery

Cold backup is actually a method of stopping the database service and copying the data files. This method is suitable for both MyISAM and InnoDB.
Recovery: First stop the Mysql service, restore the MySQL data files at the operating system level, then restart the Mysql service, and use the Mysqlbinlog tool to restore all binlogs since the backup.

Hot backup

The hot backup of different storage engines in mysql is different.

MyISAM Storage Engine

The backup principle of MyISAM storage engine is to add a read lock to the table to be backed up, and then cp the data file to the backup directory. Commonly used methods

• Method 1: Use mysqlhotcop
  mysqlhotcop db_name [Directory]

• Method 2: Manually lock the table copy
  First Add read locks to all tables in the database, and then cp the data.
  Lock all tables flush tables with read lock;
  

InnoDB storage engine

Learning···

表的导入与导出

导出

• 使用SELECT …INTO OUTFILE …+[options]命令实现
  关于options参数如下
  
  默认路径为该数据对应的路径下：
  
  

• 使用mysqldump
  mysqldump -u username -T targetDir dbname tableName[options]
  比如：
  mysqldump -uroot -p -T  D:/wamp/bin/mysql/mysql5.6.17/  xxpt t1  
  生成了两个文件，如下图：
  
  t1.txt中保存中数据信息，t1.sql文件内容如下

-- MySQL dump 10.13  Distrib 5.6.17, for Win32 (x86)
---- Host: localhost    Database: xxpt
-- ------------------------------------------------------
-- Server version   5.6.17-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;
/*!40111 SET @OLD_SQL_NOTES=@@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(11) NOT NULL DEFAULT '0',
`id2` int(3) unsigned zerofill NOT NULL DEFAULT '000') ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2016-04-18 19:30:51

导入

方法一：
load data infile
eg：
load data infile ‘D:/wamp/bin/mysql/mysql5.6.17/t1.txt’ into table t1;

方法二：使用mysqlinport

 以上就是MySQL的备份与恢复详解的内容，更多相关内容请关注PHP中文网（www.php.cn）！