MySql逻辑备份恢复方法简单总结

MySql逻辑备份恢复方法简单总结

一、 Mysql 逻辑备份

 

1. 使用 mysqldump 命令生成 INSERT 语句备份

此方法类似于 oracle 的 expdp\exp 工具

语法如下:

mysqldump [arguments] > file_name.sql

 

使用帮助:

[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

 

备份实例:

备份所有数据库

# mysqldump -uroot -proot –all-database > /tmp/dumpback/alldb.sql

 

备份某些数据库

# mysqldump -uroot -proot –database sqoop hive > /tmp/dumpback/sqoop_hive.sql

 

备份某数据库中的表

# mysqldump -uroot -proot sqoop tb1 > /tmp/dumpback/sqoop_tb1.sql

 

查看备份内容:

[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 `tb1` (

`table_schema` 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_schema’,’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

 

注意事项:

如何保证数据备份的一致性?

要想保证数据的一致性可以通过以下两种方法做到:

第一 、同一时刻取出所有数据

对于事务支持的存储引擎,如 Innodb 或者 BDB 等,可以通过控制将整个备份过程在同一个事务中,使用“ –single-transaction ”选项。

例如:

# mysqldump –single-transaction test > test_backup.sql

 

第二 、数据库中的数据处于静止状态

通过锁表参数

–lock-tables 每次锁定一个数据库的表,此参数是默认为 true( 见上面备份内容实例 );

–lock-all-tables 一次锁定所有的表,适用于 dump 的表分别处于各个不同的数据库中的情况

 

 

2. 生成特定格式的纯文本文件备份

1) 通过 SELECT … TO OUTFILE FROM … 命令

通过 Query 将特定数据以指定方式输出到文本文件中,类似于 oracle 中的 spool 功能

参数说明:

FIELDS ESCAPED BY [‘name’] 将 SQL 语句中需要转义的字符;

FIELDS TERMINATED BY 设定每两个字段之间的分隔符;

FIELDS [OPTIONALLY] ENCLOSED BY ‘name’ 包装,有 optionally 数字类型不被包装,否则全包装;

LINES TERMINATED BY ‘name’ 行分隔符,即每记录结束时添加的字符;

 

备份实例:

mysql> select * into outfile ‘/tmp/tb1.txt’

-> fields terminated by ‘,’

-> optionally enclosed by ‘”‘

-> lines terminated by ‘\n’ — 默认

-> from tb1 limit 50;

Query OK, 50 rows affected (0.00 sec)

 

[root@gc tmp]# more tb1.txt

“information_schema”,”CHARACTER_SETS”,”SYSTEM VIEW”

“information_schema”,”COLLATIONS”,”SYSTEM VIEW”

……

 

2) 通过 mysqldump 工具命令导出文本

用此方法可以生成一个文本数据和一个对应的数据库结构创建脚本

主要重要参数:

-T, –tab=name      Create tab-separated textfile for each table to given

path. (Create .sql and .txt files.) NOTE: This only works

if mysqldump is run on the same machine as the mysqld

server.

 

备份实例:

导出 sqoop 库的 tb1 表

# mysqldump -uroot -proot -T /tmp sqoop tb1 –fields-enclosed-by=\” –fields-terminated-by=,

[root@gc tmp]# ls

tb1.sql  tb1.txt

 

 

二、逻辑备份恢复

1. INSERT 语句文件的恢复

1) 使用 mysql 命令直接恢复

把 sqoop 库的 tb1 表恢复到 test 库

# mysql -uroot -proot -D test < /tmp/dumpback/sqoop_tb1.sql

 

2) 连接上 MySql 在命令行中执行恢复

上面的例子同样可以使用下面的方法

[root@gc ~]# mysql -uroot -proot -D test

mysql> select database();

+————+

| database() |

+————+

| test       |

+————+

1 row in set (0.00 sec)

 

mysql> source /tmp/dumpback/sqoop_tb1.sql

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

……

 

或是

 

mysql> \. /tmp/dumpback/sqoop_tb1.sql

 

2. 纯文本文件的恢复

1) 使用 LOAD DATA INFILE 命令

此命令是 SELECT … TO OUTFILE FROM 反操作,类似于 oracle 的 sqlldr 工具

语法:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE ‘file_name.txt’

[REPLACE | IGNORE]

INTO TABLE tbl_name

[FIELDS

[TERMINATED BY ‘string’]

[[OPTIONALLY] ENCLOSED BY ‘char’]

[ESCAPED BY ‘char’ ]

]

 

[LINES

[STARTING BY ‘string’]

[TERMINATED BY ‘string’]

]

 

[IGNORE number LINES]

[(col_name_or_user_var,…)]

[SET col_name = expr,…]]

 

恢复实例:

mysql> use sqoop;

Database changed

 

mysql> load data infile ‘/tmp/tb1.txt’ into table tb1

-> fields terminated by ‘,’

-> optionally enclosed by ‘”‘

-> lines terminated by ‘\n’;

Query OK, 50 rows affected (0.01 sec)

Records: 50  Deleted: 0  Skipped: 0  Warnings: 0

 

2) 使用 mysqlimport 工具恢复

用此工具用于可用于恢复上面 mysqldump 生成 txt 和 sql 两文件,所以要保证 txt 文件对应的数据库中的表存在。

 

恢复实例:

— 首先恢复表结构

[root@gc ~]# mysql -uroot -proot -D test < /tmp/tb1.sql

— 恢复数据

[root@gc ~]# mysqlimport -uroot -proot test –fields-enclosed-by=\” –fields-terminated-by=, /tmp/tb1.txt

test.tb1: Records: 93  Deleted: 0  Skipped: 0  Warnings: 0

发表评论

电子邮件地址不会被公开。 必填项已用*标注