Mysql 数据库备份和恢复

  一、为何要数据库备份
         （1） 保证重要数据不丢失
         （2） 数据转移

   二、 MySQL数据库备份方法
               mysqldump备份工具作用
                 1)转储数据库
                 2)搜集数据库进行备份
                 3)将数据转移到另一个SQL服务器(不一定是MySQL服务器)

**：mysqldump和mysql同级的命令

mysqldump --help|more  帮助你查看mysqldump的可选参数

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

• 常用的选项

      

三、 以SQL格式备份数据

     如果备份文件名.sql没有指定所放置的路径，则默认放在~\MySQL Server 5.7\bin目录下。

指定数据备份路径:
mysqldump –h 主机名 –u 用户名 –p - -all-databases > d:\备份文件名.sql

调用mysqldump带有- -all-databases选项备份所有的数据库
mysqldump –h 主机名 –u 用户名 –p - -all-databases > d:\备份文件名.sql

调用mysqldump带有- -databases选项备份指定的数据库
mysqldump –u 用户名 –p - -databases db1 db2 db3 … > d:\备份文件名.sql

调用mysqldump备份一个指定的数据库：
mysqldump –u 用户名 –p - -databases db > 备份文件名.sql

用mysqldump不带有- -databases选项备份指定的一个数据库，生成的备份文件中是没有CREATE DATABASE和USE语句的：
mysqldump –u 用户名 –p db > 备份文件名.sql

调用mysqldump备份某个数据库中的某几张表：
mysqldump –u用户名 –p 数据库名 表名1 表名2 表名3… > 备份文件名.sql

 操作如下：

              1） --skip-add-drop-table 跳过导出sql脚本会加上 DROP TABLE IF EXISTS 语句
                mysqldump -uroot -proot --skip-add-drop-table work2_20 >d:/work.sql
     
              2) -t   不创建每个转储表的CREATE TABLE语句
               mysqldump -uroot -proot -t work2_20 >d:/work2.sql

              3) -c   insert时有表的列名字段
               mysqldump -uroot -proot -c work2_20 >d:/work3.sql

              4) -d   不添加数据
               mysqldump -uroot -proot -d work2_20 >d:/work4.sql
             
             案例1：备份MySchool数据库的subject课程表，保存为subject.sql脚本文件
              要求 :在每个INERT语句的列上加上字段名
              mysqldump -uroot -proot -c myschool subject >d:/subject.sql

              -- 多张时，不能写“，”，只能写空格
              mysqldump -uroot -proot -c myschool subject result >d:/subject2.sql

 

四、恢复SQL格式的备份文件

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

在dos命令下：
c:/> mysql –u 用户名 –p < 备份文件.sql

在mysql命令下，用source命令导入备份文件：
mysql> source备份文件.sql; //已登录mysql，用source命令

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

在dos命令下：
c:/> mysqladmin –u 用户名 –p create 数据库名 //创建数据库
c:/> mysql –u 用户名 –p数据库名 < 备份文件.sql

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

在mysql命令下：可以使用原本的数据库，也可以自己创建
mysql> CREATE DATABASE IF NOT EXIST 数据库名;
mysql> USE 数据库名;
mysql> source 备份文件.sql;

用 SOURCE恢复数据：  
       /path/是一个绝对路径，并且必须是mysql 运行用户有权限读取的文件
      SOURCE 在MySQL命令行里执行:   SOURCE  /path/db_name.sql;

 步骤： 
        1>dos命令进入mysql登录：c:/>mysql -uroot -pxxx;    
        2>使用哪一个数据库： mysql>use student;
        3>使用source命令导入数据:  mysql> source d:/subject2.sql;

五、用mysql命令将查询的中间结果导出

• 将查询结果导入到文本文件中

   mysql是一个功能丰富的工具命令，使用mysql还可以在命令行模式下执行SQL指令，将查询结果导入到文本文件中。
相比mysqldump，mysql工具导出的结果可读性更强。如果mysql服务器是单独的机器，
用户是在一个client上进行操作，用户要把数据结果导入到client机器上，可以使用mysql -e语句。

c:/> mysql -u root -p --execute="SELECT 语句" dbname > filename.txt

     该命令使用–execute 选项，表示执行该选项后面的语句并退出，后面的语句必须用双引号括起来

     dbname为要导出的数据库名称，导出的文件中不同列之间使用制表符分隔，第一行包含了字段名称

 【案例1】使用mysql命令，导出myemployees库的locations表记录到文本文件：

 c:/>mysql -uroot -proot  --execute="SELECT * FROM locations;" myemployees > d:\locations.txt

   可以看到，person3.txt文件中包含了每个字段的名称和各条记录，如果某行记录字段很多，可能一行不能完全显示，可以使用 - -vertical参数，将每条记录分为多行显示 

【案例2】使用mysql命令，导出myemployees库的locations表使用–vertical参数显示：

c:/>mysql -uroot -proot --vertical --execute="SELECT * FROM locations;" myemployees > d:\locations2.txt

       

【案例3】使用mysql命令，导出myemployees库的locations表将查询结果导入到html文件中 

c:/>mysql -uroot -proot --html --execute="SELECT * FROM locations;" myemployees > d:\locations3.html

 【案例4】使用mysql命令，导出myemployees库的locations表将查询结果导入到xml文件中 

c:/>mysql -uroot -proot --xml --execute="SELECT * FROM locations;" myemployees > d:\locations4.xml