MySQL logical backup recovery _ MySQL

MySQL logical backup recovery 1. 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 SETTIME_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.

-------------------------------------- Split line --------------------------------------

MySQL management using XtraBackup for hot standby http://www.linuxidc.com/Linux/2014-04/99671.htm

MySQL open source backup tool Xtrabackup backup deployment http://www.linuxidc.com/Linux/2013-06/85627.htm

MySQL Xtrabackup backup and recovery http://www.linuxidc.com/Linux/2011-12/50275.htm

Using XtraBackup to achieve MySQL master-slave replication and rapid deployment [master table lock] http://www.linuxidc.com/Linux/2012-10/71919p2.htm

Install and use Xtrabackup from Percona to back up MySQLhttp: // www.linuxidc.com/Linux/2011-10/44451.htm

-------------------------------------- Split line --------------------------------------

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.

For more details, refer to the highlights on the next page.: Http://www.linuxidc.com/Linux/2014-05/101626p2.htm