Mysqldump Xtrabackup logical Backup and physical backup

Logical backup

A logical backup is a backup SQL statement that performs a backup of the SQL statement to reproduce the database data at the time of recovery.

Tool: Mysqldump

Characteristics:

1. Strong portability

2. Backup and recovery takes a long time and does not apply to large business systems

Physical backup

Physical backup is the backup data file, the comparison image Point is the CP under the data file, but the real backup time is not naturally the CP so simple.

Tool: Xtrabackup

Characteristics:

1. No portability, backup environment and recovery environment must be identical

2. Faster backup and recovery times for large business systems

Mysqldump

Backup principle: Connect to the MySQL database via protocol, query the data that needs to be backed up, convert the queried data into the corresponding INSERT statement, and when we need to restore the data, we can restore the corresponding data as long as the INSERT statement is executed.

Advantages:

The corresponding backup data can be processed directly using the Text Processing tool because the backup data has been mysqldump converted to the corresponding INSERT statement, so we can use the Text Processing tool in the file system to directly process the backup data.

Disadvantages:

1, unable to back up the index and other information (because it simply will need to back up the data to query out, restore the need to rebuild the index)

2, when the data is floating-point type, there will be loss of precision

3, the backup process is a logical backup, backup speed, recovery speed and physical backup tool is relatively slow, and mysqldump backup process is serialized, not parallel to the backup, if you want to back up in parallel, you can use Mydumper, but here we do not consider these, Only consider mysqldump, when the amount of data is large, usually do not use mysqldump for backup, because the efficiency is low.

Characteristics:

Mysqldump supports hot standby for the InnoDB storage Engine, InnoDB supports transactions, and we can heat-prepare the database based on transactions through Mysqldump.

Mysqldump only supports Win Bei for the MyISAM storage engine, and when backing up tables using the MyISAM storage engine, a maximum of Win Bei can be achieved by mysqldump, because the backup table requests the lock when it is completed and the lock is freed when the backup is complete.

Use:

[[email protected]]/var/lib/mysql$ mysqldump-uroot test-p
Enter Password:
--MySQL dump 10.13 distrib 5.7.21, for Linux (x86_64)
--
--Host:localhost Database:test
-- ------------------------------------------------------
--Server Version5.7.21-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 ' */;
/*!40014 SET @[email protected] @UNIQUE_CHECKS, unique_checks=0 */;
/*!40014 SET @[email protected] @FOREIGN_KEY_CHECKS, foreign_key_checks=0 */;
/*!40101 SET @[email protected] @SQL_MODE, sql_mode= ' no_auto_value_on_zero ' */;
/*!40111 SET @[email protected] @SQL_NOTES, sql_notes=0 */;

--
--Table structure for table ' TB1 '
--

DROP TABLE IF EXISTS ' tb1 ';
/*!40101 SET @saved_cs_client = @ @character_set_client */;
/*!40101 SET character_set_client = UTF8 */;
CREATE TABLE ' tb1 ' (
' Name ' varchar DEFAULT NULL,
' Age ' int (one) DEFAULT NULL
) Engine=innodb DEFAULT charset=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
--Dumping data for table ' TB1 '
--

LOCK TABLES ' tb1 ' WRITE;
/*!40000 ALTER TABLE ' tb1 ' DISABLE KEYS * *;
INSERT into ' tb1 ' VALUES (' Hanli ', +), (' Dongpo ', "), (' Heiqi ', '), (' yeyi ', '), (' Xueji ', 24);
/*!40000 ALTER TABLE ' tb1 ' ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET [Email protected]_time_zone */;

/*!40101 SET [Email protected]_sql_mode */;
/*!40014 SET [Email protected]_foreign_key_checks */;
/*!40014 SET [Email protected]_unique_checks */;
/*!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 2018-03-17 4:50:16

Xtrabackup

Mysqldump Xtrabackup logical Backup and physical backup