MYSQL——mysqldump备份与恢复

最新推荐文章于 2023-08-15 19:44:12 发布

YuH2019

最新推荐文章于 2023-08-15 19:44:12 发布

阅读量290

点赞数

分类专栏：数据库

数据库专栏收录该内容

11 篇文章

订阅专栏

原文MYSQL备份
冷备份：停止服务进行备份，即停止数据库的写入

热备份：不停止服务进行备份（在线）

l mysql的MyIsam引擎只支持冷备份，InnoDB支持热备份，原因：

InnoDB引擎是事务性存储引擎，每一条语句都会写日志，并且每一条语句在日志里面都有时间点，那么在备份的时候，mysql可以根据这个日志来进行redo和undo，将备份的时候没有提交的事务进行回滚，已经提交了的进行重做。但是MyIsam不行，MyIsam是没有日志的，为了保证一致性，只能停机或者锁表进行备份。

l InnoDB不支持直接复制整个数据库目录和使用mysqlhotcopy工具进行物理备份：

因为MYSQL表保存为文件方式，所以可以直接复制MYSQL数据库的存储目录以及文件进行备份。MYSQL的数据库目录位置不一定相同，在Windows平台下，MYSQL5.6存放数据库的目录通常默认为~\MySQL\MYSQL Server 5.6\data，或其他用户自定义的目录。这种方法对INNODB存储引擎的表不适用。使用这种方法备份的数据最好还原到相同版本的服务器中，不同的版本可能不兼容。在恢复的时候，可以直接复制备份文件到MYSQL数据目录下实现还原。通过这种方式还原时，必须保证备份数据的数据库和待还原的数据库服务器的主版本号相同。而且这种方式只对MYISAM引擎有效，对于InnoDB引擎的表不可用。执行还原以前关闭mysql服务，将备份的文件或目录覆盖mysql的data目录，启动mysql服务。

2．使用mysqlhotcopy工具快速备份

mysqlhotcopy是一个perl脚本，最初由Tim Bunce编写并提供。他使用LOCK TABLES 、FLUSH TABLES和cp或scp来快速备份数据库。他是备份数据库或单个表的最快途径，但他只能运行在数据库目录所在机器上，并且只能备份myisam类型的表。

mysqldump备份简述
mysqldump可产生两种类型的输出文件，取决于是否选用- -tab=dir_name选项。

l 不使用- -tab=dir_name选项，mysqldump产生的数据文件是纯文本的SQL文件，又CREATE（数据库、表、存储路径等）语句和INSERT（记录）语句组成。输出结果以一个文件保存，可以用mysql命令去恢复备份文件。

l 使用- -tab=dir_name选项，mysqldump对于每一个需备份的数据表产生两个输出文件：一个是带分隔符的文本文件，备份的数据表中的每行存储为文本中的一行，以“表名.txt”保存；另一个输出文件为数据表的CREATE TABLE语句，以“表名.sql”保存。

mysqldump语法和选项
【命令】shell> mysqldump -help

-all-databases表示备份系统中所有数据库，使用- -databases参数之后，必须指定至少一个数据库的名称，多个数据库名称之间用空格隔开

【常用的选项】

```
   - -add-drop-table
```

这个选项将会在每一个表的前面加上DROP TABLE IF EXISTS语句，这样可以保证导回MySQL数据库的时候不会出错，因为每次导回的时候，都会首先检查表是否存在，存在就删除

```
   - -add-locks
```

这个选项会在INSERT语句中捆上一个LOCK TABLE和UNLOCK TABLE语句。这就防止在这些记录被再次导入数据库时其他用户对表进行的操作

```
   - -tab
```

这个选项将会创建两个文件，一个是带分隔符的文本文件，备份的数据表中的每行存储为文本中的一行，以“表名.txt”保存；另一个输出文件为数据表的CREATE TABLE语句，以“表名.sql”保存。

```
   --quick或者—opt
```

)如果你未使用–quick或者–opt选项，那么mysqldump将在转储结果之前把全部内容载入到内存中。这在你转储大数据量的数据库时将会有些问题。该选项默认是打开的，但可以使用–skip-opt来关闭它。

```
   --skip-comments
```

使用–skip-comments可以去掉导出文件中的注释语句

```
   –compact
```

使用–compact选项可以只输出最重要的语句，而不输出注释及删除表语句等等

以SQL格式备份数据
如果备份文件名.sql没有指定所放置的路径，则默认放在~\MySQL\MySQL Server 5.6\bin目录下。但可以通过以下方式指定其备份文件的路径：

mysqldump –h 主机名 –u 用户名 –p - -all-databases > C:\备份文件名.sql

l 调用mysqldump带有- -all-databases选项备份所有的数据库

【命令】mysqldump –h 主机名 –u 用户名 –p - -all-databases > 备份文件名.sql

【例子】以’test’@’%’用户为例，查看其数据库：

用mysqldump带有- -all-databases选项备份所有的数据库（test，test1）：

l 调用mysqldump带有- -databases选项备份指定的数据库

【命令】mysqldump –u 用户名 –p - -databases db1 db2 db3 … > 备份文件名.sql

【例子】用mysqldump带有- -databases选项备份指定的数据库（如test，test1）

l 调用mysqldump备份一个指定的数据库：

【命令1】mysqldump –u 用户名 –p - -databases db > 备份文件名.sql

【例子1】用mysqldump带有- -databases选项备份指定的一个数据库（如test）

或【命令2】 mysqldump –u 用户名 –p db > 备份文件名.sql

【例子2】用mysqldump不带有- -databases选项备份指定的一个数据库（如test）

注意生成的备份文件中是没有CREATE DATABASE和USE语句的：

【注意】当对一个数据库进行备份时- -databases允许省略（【命令2】），但是省略后导致的是备份文件名.sql中没有CREATE DATABASE 和USE语句，那么恢复备份文件时，必须指定一个默认的数据库名，由此服务器才知道备份文件恢复到哪个数据库中；由此可以导致你可以使用一个和原始数据库名称不同的数据库名。

l 调用mysqldump备份某个数据库中的某几张表：

【命令】mysqldump –u用户名 –p 数据库名表名1 表名2 表名3… > 备份文件名.sql

【例子】test数据库中的表：

用mysqldump备份数据库test中的course表和student表：

scdump.sql文件中只有CREATE TABLE，INSERT course，student的信息。

恢复SQL格式的备份文件
通过mysqldump备份的文件，如果用了- -all-databases或- -databases选项，则在备份文件中包含CREATE DATABASE和USE语句，故并不需要指定一个数据库名去恢复备份文件。

在Shell命令下：

shell> mysql –u 用户名 –p < 备份文件.sql

在mysql命令下，用source命令导入备份文件：

mysql> source备份文件.sql; //已登录mysql，用source命令

如果通过mysqldump备份的是单个数据库，且没有使用- -databases选项，则备份文件中不包含CREATE DATABASE和USE语句，那么在恢复的时候必须先创建数据库。

在shell命令下：

          shell>  mysqladmin –u 用户名 –p create 数据库名     //创建数据库

          shell>  mysql –u 用户名 –p数据库名 < 备份文件.sql

在mysql命令下：

         mysql>  CREATE DATABASE IF NOT EXIST 数据库名;

mysql> USE 数据库名;

mysql> source备份文件.sql;

注意：只能在cmd界面下执行source命令，不能在mysql工具里面执行source命令，会报错，因为cmd是直接调用mysql.exe来执行命令的。