Summary of MySQL logical Backup Recovery Methods

I. Mysql logical Backup 1. Use the mysqldump command to generate an INSERT statement backup. This method is similar to the Oracle expdpexp tool Syntax: mysqldump [argum

I. Mysql logical Backup 1. Use the mysqldump command to generate an INSERT statement backup. This method is similar to the Oracle expdp \ exp tool Syntax: mysqldump [argum

I. Mysql logical backup



1. Use the mysqldump command to generate an INSERT statement backup

This method is similar to the expdp \ exp tool of Oracle.

Syntax:

Mysqldump [arguments]> file_name. SQL



Help:

[Root @ gc ~] # Mysqldump

Usage: mysqldump [OPTIONS] database [tables]

OR mysqldump [OPTIONS] -- databases [OPTIONS] DB1 [DB2 DB3. ..]

OR mysqldump [OPTIONS] -- all-databases [OPTIONS]

For more options, use mysqldump -- help



Backup instance:

Back up all databases

# Mysqldump-uroot-proot -- all-database>/tmp/dumpback/alldb. SQL



Back up some databases

# Mysqldump-uroot-proot -- database sqoop hive>/tmp/dumpback/sqoop_hive. SQL

Back up tables in a database

# Mysqldump-uroot-proot sqoop tb1>/tmp/dumpback/sqoop_tb1. SQL



View the backup content:

[Root @ gc dumpback] # more sqoop_tb1. SQL

-- MySQL dump 10.13 Distrib 5.5.24, for Linux (x86_64)

--

-- Host: localhost Database: sqoop

--------------------------------------------------------

-- Server version 5.5.24



/*! 40101 SET @ OLD_CHARACTER_SET_CLIENT = @ CHARACTER_SET_CLIENT */;

......

--

-- 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 'tab1 '(

'Table _ scheme' varchar (64) character set utf8 not null default '',

'Table _ name' varchar (64) character set utf8 not null default '',

'Table _ type' varchar (64) character set utf8 not null default''

) 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 ('information _ scheme', 'character _ sets', 'System view ')

......



/*! 40000 alter table 'tb1 'enable keys */;

Unlock tables;

/*! 40103 SET TIME_ZONE = @ OLD_TIME_ZONE */;

.....



-- Dump completed on 2013-03-25 18:26:53



Note:

How can we ensure data backup consistency?

To ensure data consistency, you can use the following two methods:

1. Retrieve all data at the same time

For storage engines supported by transactions, such as Innodb or BDB, you can use the "-- single-transaction" option to control the entire backup process in the same transaction.

For example:

# Mysqldump -- single-transaction test> test_backup. SQL

2. The data in the database is in the static state.

Lock table parameters

-- Lock-tables: each time a database table is locked, this parameter is set to true by default (see the backup content instance above );

-- Lock-all-tables: locks all tables at a time. It is applicable to dump tables in different databases.

,