MySQL logical Backup Recovery

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.

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

MySQL management-using XtraBackup for Hot Backup

MySQL open-source backup tool Xtrabackup backup deployment

MySQL Xtrabackup backup and recovery

Use XtraBackup to implement MySQL master-slave replication and quick deployment [master-slave table lock-free]

Install and use Xtrabackup from Percona to back up MySQL

-------------------------------------- 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, please continue to read the highlights on the next page:

1 2 Next Page