【MySQL学习】数据备份与恢复

数据备份（SQL格式）

mysqldump [arguments] > file_name

• --all-databases或-A 备份所有数据库

mysqldump --all-databases > dump.sql

• --databases 或-B 备份单一数据库

mysqldump --databases db1 db2 db3 > dump.sql

--databases参数后面的名字都是数据库名，如果没有这个参数，那么mysqldump会认为第一个是数据库名，剩下的是表名。

mysqldump --all-databases --no-data --routines --events > dump-defs.sql

导出单个数据库，其中–databases可以省略，--databases参数省略与不省略的区别是，如果没有该参数，输出的备份文件中没有CREATE DATABASE or USE 语句，下面是几个建议：

• 当你恢复备份文件时，你必须指定一个默认的数据库名以便服务器知道你想把数据恢复到那个数据库
• 你可以指定新的数据库名不同于以往，这样数据就能在另外一个数据库中存放。
• 如果待恢复的数据库不存在，那么你就需要创建它。
• 因为输出包含CREATE DATABASE语句， --add-drop-database 选项就不起作用了，如果你还是加上该参数，输出的文件中也不会有DROP DATABASE语句。

mysqldump --databases test > dump.sql

• 导出多个表，多个表名跟在数据库名后面。

mysqldump test t1 t3 t7 > dump.sql

• –user=user_name,-u user_name 用户名
• --password[=password], -p[password] 密码
• --port=port_num, -P port_num 端口号
• --host=host_name, -h host_name 主机
• --no-data, -d 只导出表结构信息，不导出数据
• --no-create-info, -t只导出数据，不包含表结构
• --where=“where_condition“, -w ”where_condition“双引号
• –default-character-set=charset_name 设置字符集，默认utf8

shell> mysqldump --no-data test > dump-defs.sql
shell> mysqldump --no-create-info test > dump-data.sql

数据条件备份

mysqldump -uroot -p123 school score -t -w"id in(2,8)">c:\fp\score.sql

更多参数看官方文档

• –tab=dir_name 表结构语句与数据文件分离，但是必须由file权限。--secure-file-priv

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

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

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

使用了–tab参数，输出的数据文件.txt默认使用制表符在列值之间，列值周围没有引号，换行使用换行符；这和select...into outfile具有相似之处。

而数据文件格式可以使用下面的参数定制：

• [--fields-terminated-by=str]

  列与列分隔符，默认制表符tab

• [--fields-enclosed-by=char]

  将列值括起来的字符（默认值：无字符）。

• [--fields-optionally-enclosed-by=char]

  将非数值列值括起来的字符（默认值：无字符）。

• [--fields-escaped-by=char]

  用于转义特殊字符的字符（默认值：不转义）。

• [--lines-terminated-by=str]

  行终止字符串（默认：换行）。

实例：shell> mysqldump --tab=/tmp --fields-terminated-by=,
         --fields-enclosed-by='"' --lines-terminated-by=0x0d0a db1

数据恢复

• 如果mysqldump使用了–all-databases或者–databases参数，那么输出的sql文件包含了create database和use 语句，也就没有必要指定默认的数据库来恢复数据。

shell> mysql < dump.sql

​ 如果已经登陆了mysql控制台，也可以使用source命令恢复数据库
mysql -uroot --default-character-set=utf8 -ppassword 指定字符集

mysql> source dump.sql

• 如果mysqldump输出的备份文件是单一数据库，并且没有create database和use 语句那么就需要先创建数据库

shell> mysqladmin create db1

​ 然后指定恢复的数据库

shell> mysql db1 < dump.sql

​ 如果已经登陆了mysql控制台，也可以使用source命令恢复数据库

mysql> CREATE DATABASE IF NOT EXISTS db1;
mysql> USE db1;
mysql> source dump.sql

官方文档

https://dev.mysql.com/doc/refman/8.0/en/mysqldump-sql-format.html