MySQL数据备份与还原

备份数据库的方法网上有很多。园子里我见有一个园友写的就挺好。传送门https://www.cnblogs.com/kissdodog/p/4174421.html

我就在他的基础上补充点，他没写的。

1、备份数据库中的某个表

语法格式：

mysqldump -u user -h host -p dbname [tbname,[tbname..]] > filename.sql

实例：

mysqldump -uroot -p food shop_order >G:\shop_order.sql

dbname 表示数据库中的表名，多个表名之间用空格打开。
备份数据可以与备份表的不同之处在与，如果要备份数据库，把表名去掉即可。

2、数据库还原

2.1第一种还原办法：

source filename

实例：

1、登录mysql服务器
2、use food
3、source G:\shop_order.sql

2.2、第二种还原办法

mysql -uroot -p <test.sql

3、直接复制到数据库目录

　　注：保存备份的数据库和待还原的数据库服务器的主版本号相同。而且这种方式只针对MyISAM引擎的表有效，对于InnoDB引擎的表不可用。

执行还原前关闭mysql服务，将备份的文件或目录覆盖MySQL的data目录，启动mysql服务。LINUX需要将文件的用户和组更改为mysql运行的用户和组。

4、数据库迁移

　　直接复制，此方法只使用MyISAM引擎的镖。

　　1、相同版本的mysql数据库之间的迁移

mysqldump -h www.abc.com -uroot -ppassword food1 |  mysql -h www.efd.com -uroot -ppassword

mysqldump导入的数据之间通过管道符"|"，传给mysql命令导入到主机www.efd.com数据库中。food1为需要迁移的数据库名称。迁移全部的数据库，可使用参数 --all-databases。

　　2、不同版本的mysql数据库之间的迁移

　　需要注意的的新旧数据库版本如果使用不同的默认字符集，在导入带有中文数据的时候，需要对默认字符集进行修改。不然可能无法正常显示结果。MyISAM可以直接复制或者mysqlhotcopy工具,mysqldump导入到目标服务器。InnoDB，一般使用mysqldump导入。新版本向旧版本迁移时要小心，最好使用mysqldump命令导出，然后导入目标数据库。

　　3、不同数据之间的迁移

　　不同数据库。他们之间关键字有可能会不同。所以迁移的时候可以使用工具。如：navcat还有mysql官方提供的工具 MySQL Migration Toolkit。

5、导出文本文件

  语法：

SELECT columnlist FROM table WHERE condition INTO OUTFILE 'filename' [OPTIONS]

  --OPTIONS 选项

• FIELDS TERMINATED BY 'value'：设置字段直接的间隔字符，可以为单个或多个字符，默认情况下为制表符 "\t".
• FIELDS [OPTIONALLY] ENCLOSED BY 'value'：设置字段的包围字符串，只能为单个字符，如果使用了optionally，则只有char和varchar等字符数据字段包被括。
• FIELDS ESCAPED BY 'value'：设置如何写或读取特殊字符，只能为单个字符，即设置转义字符，默认值为"\"
• LINES STARTING BY ‘value’：设置每行数据开头的字符，可以为单个或多个字符，默认情况下不使用任何字符
• LINES TERMINATED BY ‘value’：设置每行数据结尾的字符，可以为单个或多个字符，默认值为"\n".
• FIELDS和LINES两个字句都是自选的，单是如果两个都被指定了。FIELDS必须位于LINES的前面

  select .. info outfile可以非常快速地把一个表转储到服务器上。如果在服务器主机之外的部分主机上创建结果文件。则使用mysql -e "select ...">file_name 这样的命令。

    实例：

select * from tbl_shop_order into outfile 'g:\1.txt'
FIELDS
TERMINATED BY ','
ENCLOSED BY '\"'
ESCAPED BY '\''
LINES
TERMINATED BY '\r\n';

该语法导出之后只有数据，没有结构

6、mysql命令导出文本文件

语法:

mysql -uroot -p --execute="SELECT语句" dbname >filename.txt

--execute表示执行选项后面的语句并退出。dbname指数据库名称；语句必须用双引号；导出的文件中不同列直接使用制表符分割，第1行包含了各个字段的名称。

实例：

mysql -uroot -p --execute="select * from tbl_shop_log" test >G:\tbl_shop_log.txt

--execute前面添加--vertical可以将每条记录分为多行显示；添加 --html 可以将查询结果导出到html文件中;添加--xml可以将查询结果导出到xml文件中

mysql -uroot -p --html --execute="select * from tbl_shop_log;" test >G:\tbl_shop_log.html

7、mysqldump导出文本文件

实例：

mysqldump -T G:\  test1 -uroot -p

G:\后面不加文件夹名称 表示导入根目录，否则则是导入根目录 ；如果导入根目录需要加空格   test1代表数据库名称

执行成功之后，将会导出.txt和.sql两个文件；

.txt文件只包含数据

.sql包含表结构

7、使用load data infile 方式导入文本文件

语法：

LOAD DATA INFILE 'filename' INTO TABLE tablename  [OPTIONS]
[IGNORE number LINES]

OPTIONS参数与导出文本文件参数一致。

ignore number lines表示忽略文件开始处的行数，number表示忽略的行数。执行load data语句需要file权限

实例：

load data infile 'G:\tbl_shop_order.txt' into table liuhesan.tbl_shop_order
FIELDS
TERMINATED BY ','
ENCLOSED BY '\"'
ESCAPED BY '\''
LINES
TERMINATED BY '\r\n';

 

8、使用mysqlimport导入文本文件

语法：

mysqlimport -u root-o dbname filename.txt [OPTIONS]

  --OPTIONS 选项

• fields-ferminated=value：设置字段直接的封字符，可以为单个或多个字符，默认情况下为制表符 "\t".
• fields-enclosed-by=value：设置字段的包围字符
• fields-optionally-enclosed-by=value：设置字段的包围字符串，只能为单个字符，包括char和varchar等字符数据字段。
• fields-escaped-by=value：控制如何写入或读取特殊字符，只能为单个字符，即设置转义字符，默认值为"\"
• lines-terminated-by=value：设置每行数据结尾的字符，可以为单个或多个字符，默认值为"\n".
• ignore-lines=n：忽视数据文件的前n行
• ....

dbane为数据库名称。mysqlimport不指定导入数据库的表名称，数据库表的名称由导入文件名称确定，即文件名作为表名，导入数据之前该表必须存在。

实例：

mysqlimport -uroot -p test G:\1.txt --fields-terminated-by=,
--fields-optionall-enclosed-by=\" --fields-escaped-by=? --lines-terminated-by=\r\n