DB-Mysql-数据备份

原理

数据库管理软件按照最初设计的逻辑关系，以数据库的逻辑结构对象为单位，将数据中的数据按照预定义的逻辑关联格式一条一条生成相关的文本文件，达到备份的目的。

逻辑

1、将数据生成为可以完全重现当时数据库中数据的INSERT语句；
2、通过软件，将数据库表的数据以特定的分隔符进行分割后记录在文本文件中。

生产insert语句

存在的问题：
mysqldump不停备份时，可能有数据变更的请求，这样可能造成mysqldump备份出来的数据不一致，在对一致性和完整性要求比较严格的系统中，该备份是无效的备份。

解决方法：
1、将整个备份过程控制在一个事务中；
mysqldump通过 -single-transcation选项支持此功能而不影响数据库的服务。
2、通过锁表：–lock-tables 每次锁定一个数据库的表；（–lock-all-tables一次锁定所有的表，适用于dump的表分别处于各个不同的数据库中的情况。
）

生成特定格式的纯文本备份数据文件：

优点：使用存储空间小，数据格式清晰，编辑方便；
缺点：每个备份文件只能备份一个表，没有数据结构的重建命令（不过可以通过其他方式生成）；

  （1）select ... from... into outfile [fields terminated by] 
        [lines terminated by]
  （2）通过mysqldump，即 –T选项：结构与数据分别导出备份；
例如：mysqldump -u root -p -T /tmp/test backname

###数据库备份程序
mysqldump
mysqldump客户端可用来转储数据库或搜集数据库进行备份或将数据转移到另一个SQL服务器(不一定是一个MySQL服务器)。转储包含创建表和/或装载表的SQL语句。

MysqlDump与MysqlHotCopy的区别是什么？
mysqlhotcopy 只能用于备份 MyISAM，并且只能运行在 类Unix 和 NetWare 系统上。
目前 MySQL 支持的免费备份工具有：mysqldump、mysqlhotcopy，还可以用 SQL 语法进行备份：BACKUP TABLE 或者 SELECT INTO OUTFILE，又或者备份二进制日志（binlog），还可以是直接拷贝数据文件和相关的配置文件。MyISAM 表是保存成文件的形式，因此相对比较容易备份，上面提到的几种方法都可以使用。Innodb 所有的表都保存在同一个数据文件 ibdata1 中（也可能是多个文件，或者是独立的表空间文件），相对来说比较不好备份，免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump。

使用语法：

shell> mysqldump [options] db_name [tables]
shell> mysqldump [options] --database DB1 [DB2 DB3...]
shell> mysqldump [options] --all-database
#如果没有指定任何表或使用了--database或--all-database选项，则转储整个数据库。

mysqldump重要参数

Options(参数)        说明
--add-drop-database 在每个CREATE DATABASE语句前添加DROP DATABASE语句
--add-drop-tables   在每个CREATE TABLE语句前添加DROP TABLE语句
--all-database      转储所有数据库中的所有表，与--database相同
--allow-keywords    允许创建关键字列名
--comments[={0|1}]  如果设置为 0，禁止转储文件中的其它信息。 默认值为1，即包括额外信息。
--default-character-set=charset 使用charsetas默认字符集。
--database，-B       转储几个数据库
--quick             该选项用于转储大的表。它强制mysqldump从服务器一次一行地检索表中的行而不是检索所有行并在输出前将它缓存到内存中。
--add-locks         在插入数据开始的时候加上LOCK TABLES
-R, --routines      导出函数和存储过程
--triggers          导出触发器
-n, --no-create-db  不提供建库语句
-t, --no-create-info    不写重新创建每个转储表的CREATE TABLE语句
-d, --no-data       不提供导出数据库数据记录
-T, --tab=name      分别导出数据库结构和数据，生成.sql和.txt文件
--tables            用表格式显示输出

PS：选项中“-”和“–”的区别：“—选项”后可以用=赋予值，而“-选项”不可以

mysqldump重要选项

--Options(--选项)       设置选项但无=时的默认值    未设置选项时的默认值
--add-drop--database    FALSE                   不启用
--add-drop-tables       TRUE                    启用
--all--database         FALSE   
--allow-keywords        FALSE   
--comments[={0|1}]      TRUE                    1
--default-character-set utf8                    UTF-8
--database              FALSE                   不启用
--quick                 TRUE    
--add-locks             TRUE                    启用
--triggers              TRUE                    启用
--no-create-db          FALSE                   启用
--no-create-info        FALSE                   不启用
--no-data               FALSE                   不启用
--tab=name              No default value        不启用

---

执行命令：mysqldump –uroot –p > test.sql 结果是什么？
输出如下提示信息：

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 –p zoo > zoo_test.sql
more zoo_test.sql

结果是怎么样的？

-- MySQL dump 10.11
--
-- Host: localhost    Database: zoo
-- ------------------------------------------------------
-- Server version   5.0.89-community
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `pet`
--

DROP TABLE IF EXISTS `pet`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;  
/*定义表结构*/
CREATE TABLE `pet` (
  `name` varchar(20) default NULL,
  `owner` varchar(20) default NULL,
  `species` varchar(20) default NULL,
  `sex` char(1) default NULL,
  `birth` date default NULL,
  `death` date default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `pet`
--
/*导出表结构*/
LOCK TABLES `pet` WRITE;
/*!40000 ALTER TABLE `pet` DISABLE KEYS */;
INSERT INTO `pet` VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL),('Bower','Benny','dog','m','1989-08-31','1995-07-29'),('Fluffy','Harold','cat','f','1993-02-04',NULL),('Claws','Gwen','bird','m','1994-03-17',NULL),('rold','cat','f','1','0000-00-00',NULL),('Claws','Gwen','cat','m','1994-03-17','0000-00-00'),('Buffy','Harold','dog','f','1989-05-13','0000-00-00'),('Fang','Benny','dog','m','1990-08-27','0000-00-00'),('Bowser','Diane','dog','m','1979-08-31','1995-07-29'),('Chirpy','Gwen','bird','f','1998-09-11','0000-00-00'),('Slim','Benny','snake','m','1996-04-29',NULL),('',NULL,NULL,NULL,NULL,NULL);
/*!40000 ALTER TABLE `pet` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2010-01-18  0:55:11

mysqldump

mysqldump最常用于备份一个整个的数据库：
shell> mysqldump –opt db_name > backup-file.sql
可以这样将转储文件读回到服务器：
shell> mysql db_name < backup-file.sql

---

mysqldump也可用于从一个MySQL服务器向另一个服务器复制数据时装载数据库：
shell> mysqldump –opt db_name | mysql –host=remote_host -C db_name
其中，-C表示压缩在客户端和服务器之间发送的所有信息
–compress，-C
压缩在客户端和服务器之间发送的所有信息（如果二者均支持压缩）。

---

可以用一个命令转储几个数据库：
shell > mysqldump —database db_name1 [db_name2 …] > my_databases.sql
如果想要转储所有数据库，使用–all–database选项：
shell > mysqldump –all-databases > all_databases.sql

---

纯数据文本备份的恢复，利用load命令、
Shell > mysql -u root -p 数据库名< 备份文件目录
例如： mysqldump -uroot -p –tables zoo pet < /root/est1/pet_dump.sql