MySQL数据迁移之表级别的数据导出

1. 使用mysqldump命令导出

mysqldump -uroot -T path -p -S/xxxx/mysql.sock {$database} {$table}\
  --single-transaction \
  --default-character-set=utf8mb4 \
  --set-gtid-purged=off \
  --fields-terminated-by='|' \ 
  --replace \
  --no-create-info

参数说明：

• --fields-terminated-by=value:  设置字段之间的分隔符可以为单个或多个字符，默认情况下为制表符“\t”。

• --fields-enclosed-by=value:   设置字段的包围字符。

• --fields-optionally-enclosed-by=value：设置字段的包围字符，只能为单个字符，包括  CHAR  和  VARCHAR  等字符数据字段。

• --fields-escaped-by=value：控制如何写入或读取特殊字符，只能为单个字符，即设置转义字符，默认值为“\”。

• --lines-terminated-by=value：设置每行数据结尾的字符，可以为单个或多个字符，默认值为“\n”。

• -T 指定导出位置。

• -t  仅导出数据。

注意：支持导出数据（逻辑文件）在客户端主机存放。

2. secure-file-priv没有设置的导出异常处理

处理：

1）查看secure-file-priv设置

>show variables like '%secure%';

2）修改数据库配置文件my.cnf，添加如下设置

secure-file-priv=""

3）重启数据库

4）查看secure-file-priv最新设置

备注：

• secure_file_prive=null  ––限制mysqld 不允许导入导出；

• secure_file_priv=/path/ – --限制mysqld的导入导出只能发生在默认的/path/目录下；

• secure_file_priv=’’   – --不对mysqld 的导入 导出做限制。

3. 执行mysqldump数据导出

4. 检查导出的数据文件

1. 使用select into outfile命令导出

参考命令：

mysql> SELECT * FROM table INTO OUTFILE '/tmp/dump.txt' 
FIELDS TERMINATED BY '|' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';

参数说明：

• 1）FIELDS  TERMINATED  BY    'value'  设置字段之间的分隔符可以为单个或多个字符，默认情况下为制表符“\t”。

• 2）FIELDS   [OPTIONALLY]  ENCLOSED   BY   'value'   设置字段的包围字符，只能为单个字符，如果使用了OPTIONALLY  则只包括  CHAR  和  VARCHAR  等字符数据字段。

• 3）FIELDS   ESCAPED   BY   'value'   设置如何写入或读取特殊字符，只能为单个字符，即设置转义字符，默认值为“\”。

• 4）LINES   STARTING   BY   'value'    设置每行数据开头的字符，可以为单个或多个字符，默认情况下不使用任何字符。

• 5）LINES   TERMINATED   BY   'value'   设置每行数据结尾的字符，可以为单个或多个字符，默认值为“\n”。

注意：导出的数据存放服务器端。

2. 登陆数据库执行导出命令

3. 查看导出的数据文件

1. 使用mysql命令自定义导出数据的shell脚本

参考命令：

#!/bin/bash

source ~/.bash_profile

mysql -uroot -p'password' -h127.0.0.1 -P3306 -N --default-character-set=gbk -e "SELECT * FROM test a WHERE a.BUSI_ID IN('191000000011');" >/tmp/exp/test3.txt

mysql -uroot -p'password' -h127.0.0.1 -P3306 --default-character-set=gbk -e "SELECT * FROM test a limit 1;">/tmp/exp/title.txt

sed -i '2,$d' title.txt

cd /tmp/exp/

cat *test*.txt>> title.txt

#tar zcvfP all.tgz title.txt --remove-files

参数说明：

• 1）参数 -N 不带标题；

• 2）参数 -D 指定数据库名；

• 3）参数 --html  导出为html格式。

注意：该导出方式支持远程导出，导出的文件在客户端主机。

2. 通过shell脚本执行数据导出

1. 创建空文件test4.xls，以.xls表格方式保存

把test4.xls以文本方式打开，然后另存为，在编码选择ansi编码，保存，参考如下命令导出至test4.xls表格。

echo "select id,name from db.tablename where 1 order by id 
desc ;"| usr/local/mysql/bin/mysql -h127.0.0.1-uroot -p123456 > /tmp/extp/test4.xls

2. 执行导出命令

3. 查看导出的数据文件

1. 通过客户端工具（如navicat、SQLyog等）进行数据导出

2. 以SQLyog的数据导出操作为例

3. 选择需要导出的文件存储类型

导出的数据文件如果不做进一步处理的话，就可能会在查看报个时出现错误，例如操作方法4导出的文件以excel打开时，就会出现与真实数据不一致的情况：

以导出的数据文件导入表格的需求为例，列举2个简单的操作方法：

方法1：使用excel自带数据导入功能

处理过的数据文件，数据显示为正常：

方法2：通过CSV to Excel工具

如果出现中文乱码时可以选择UTF-8字符集。

处理过的数据文件，数据显示为正常：

END