记一次MySql数据导入、导出

最新推荐文章于 2024-05-31 19:45:50 发布

罗小爬EX

最新推荐文章于 2024-05-31 19:45:50 发布

阅读量2.1k

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/luo15242208310/article/details/106054192

版权

mysql 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

最近进行了一次Sonar（SonarCube，代码检测工具）数据库的迁移，库大小在1.4G左右，单表（live_measures）最多记录280万行左右，单表(file_sources)最大数据量600Mb左右，单表（live_measures）最大索引容量400Mb左右；

sonar库数据量
在这里插入图片描述
sonar各表数据量（共63个表，下图只依次截取数据量较大的表）

方式1 Navicat

使用Navicat导出(转储SQL文件 -> 结构和数据 -> sonar.sql)、导入(运行SQL文件)
在这里插入图片描述

导出的文件为snoar-new.sql大小为1.98G，普通的文本编辑器（记事本、notepad++）等都无法加载，故选择了emEditor编辑器进行sql文件的预览，在打开文件时选择使用UTF8编码进行查看，否则中文可能出现乱码，

在这里插入图片描述
这种导入方式非常耗时，遥遥无期，在历经了将近4个小时，数据终于导入成功了。

方式2 mysqldump+source

使用mysqldump命令，导出时间不超过5分钟，导入时间不超过20分钟，但是需要注意character编码问题，否则可能出现导入的数据出现中文乱码问题。

mysqldump -h localhost -P 3306 -u <username> -p <dbname> > /path/to/***.sql
mysqldump -u <username> -p <dbname> > /path/to/***.sql
source /path/to/***.sql
# 导出全部表结构和数据（-B参数可指定多个database，同时添加create database、use database语句）
mysqldump -uroot -p -B db_mx_car_chat > /db_mx_car_chat.sql
# 导出多个库（-B参数且多个database用空格分隔）
mysqldump -uroot -p -B db_mx_car_chat db_mx_test > /db_back.sql
# 导出单个库的多张表
mysqldump -uroot -p db_mx_car_chat message conversation > /db_back.sql
# 只导出表结构（-d参数指定）
mysqldump -uroot -p -d db_mx_car_chat > /db_mx_car_chat.sql
# 只导出数据（-t参数指定）
mysqldump -uroot -p -t db_mx_car_chat > /db_mx_car_chat.sql

在导入时，可以使用source进行导入，在导入之前一定要注意character编码问题，如下图可使用show variables like ‘character%’;来查看mysql相关编码设置，若与mysqldump出的sql文件格式不一致，需在执行source前手动设置编码set names utf8mb4;，具体编码根据导出文件编码进行设置，例如utf8, utf8mb4等；
在这里插入图片描述
关于character的具体说明，可参考：10.4 Connection Character Sets and Collations，而在set names时会同时改变character_set_client, character_set_connection, character_set_results;

character_set_client：mysql client发送给mysqld的语句使用的编码字符集
character_set_connection：msyql server在收到客户端sql语句后的解码字符集
character_set_results：mysqld在返回查询结果集或者错误信息到client时，使用的编码字符集
character_set_database：数据库默认编码字符集
character_set_server：server级默认编码字符集
character_set_system: 系统元数据(字段名等)存储时使用的编码字符集，该字段和具体存储的数据无关。总是固定不变的——utf8

在这里插入图片描述
注：
在使用navicat、mysqldump进行导出时，blob类型的字段值会自动被转换为十六进制格式，以0x开头，并且在导入时也没有问题，blob导出示例数据如下：

0x4C5A34426C6F636B26530100009C010000E6CD6C06F3800A62080112263C3F786D6C2076657273696F6E3D22312E302220656E636F64696E673D227574662D38223F3E28CCBAD9E9D32D7A2C302C352C6B3B362C31332C633B31342C31392C733B32302C32382C633B32392C33362C733B33362C33382C6BA801000A6E080212423C736861706520786D6C6E733A616E64726F69643D22687474703A2F2F736368656D61732E1800D32E636F6D2F61706B2F7265732

同时在使用msyqldump导出的文件会比navicat导出的文件小很多，本示例中小了将近1G。查看具体导出文件会发现使用mysqldump导出的文件中数据插入使用形式：insert int xx values(…), (…), (…),… ,(…)；，即同一个insert语句插入多行记录；而使用navicat导出的数据插入使用形式：insert into xxx values(…)；insert into xxx values(…)；，即每一个insert语句仅插入一行数据，不难看出mysqldump会比navicat导出的文件小（避免多个insert）、导入速度更快（批量插入）的部分原因。

方式3 outfile+load data

使用load data方式还是有诸多限制，但是效率更高；

select * into outfile 'out_file_name' from your_table_name;
load data infile 'file_name' into table your_table_name;

首先导出、导入的文件位置必须符合secure_file_priv的设置，即若指定secure_file_priv目录位置时，则导出、导入的文件必须只能存放在该目录。如下图，导出文件的位置必须位于/var/lib/mysql-files目录下，否则执行select into outfile报错：[Code: 1290, SQL State: HY000] The MySQL server is running with the –secure-file-priv option so it cannot execute this statement
在这里插入图片描述

secure_file_priv=null 不允许文件的导入导出
secure_file_priv=xxx 文件导入导出到某路径
secure_file_priv= 文件可导入到任意路径

其次load data是不支持blob类型字段的，在进行load blob类型的字段时，会提示：Error Code: 1300. Invalid utf8 character string: ‘xxx’，通过查看outfile文件时，会发现blob类型的字段值会出现乱码（并未如之前msyqldump似的采用十六进制进行编码），如下图：
在这里插入图片描述
注：
关于blob的处理，可以参考：stackoverflow - load-data-infile-with-blob-field，即在导出时使用HEX转换列，并在load data导入时通过用户变量UNHEX还原列；

通用导出脚本
导出outfile目录需符合secure-file-priv定义，本示例中为/var/lib/mysql-files，且目录结构如下：
在这里插入图片描述

#!/bin/bash
username='root'
password='xxx'
dbname='db_mx_car_chat'
# SHOW VARIABLES LIKE 'secure_file_priv';
securefilepriv='/var/lib/mysql-files/'
# 数据库备份目录
dbstoredir="${securefilepriv}${dbname}"
# 数据表outfile目录
tablestoredir="${dbstoredir}/outfiledata"
# 创建目录并chmod 777，否则报Can't create/write to file 'xxx' (Errcode: 13 - Permission denied)
mkdir -m 777 -p ${dbstoredir}
mkdir -m 777 -p ${tablestoredir}
echo -e "\033[32m $(date "+%Y-%m-%d %H:%M:%S") create dbstore directory: ${dbstoredir} \033[0m"
echo -e "\033[32m $(date "+%Y-%m-%d %H:%M:%S") create tablestore directory: ${tablestoredir} \033[0m"
echo -e "\033[32m $(date "+%Y-%m-%d %H:%M:%S") connect to database: ${dbname} \033[0m"
# 备份DB定义
echo -e "\033[32m $(date "+%Y-%m-%d %H:%M:%S") mysqldump database ${dbname} definition to ${dbstoredir}/${dbname}.sql \033[0m"
mysqldump -u${username}  -p${password} -B -d ${dbname} > ${dbstoredir}/${dbname}.sql
# 备份table数据到outfile
for tablename in ` mysql -u${username} -p${password} -e "use ${dbname};show tables;" | grep -Evi "Tables_in_"`
do
    # echo -e "\033[32m mysqldump table ${tablename} to /${dbname}/${tablename}_bak.sql \033[0m"
    # mysqldump -u${username} -p${password}  ${dbname} $tablename > /${dbname}/${tablename}_bak.sql
    echo -e "\033[32m $(date "+%Y-%m-%d %H:%M:%S") mysql select ${tablename} into outfile: ${tablestoredir}/${tablename} \033[0m"
    mysql -u${username} -p${password} -e "use ${dbname}; set names utf8mb4; select * into outfile '${tablestoredir}/${tablename} ' from ${tablename};"
done
echo -e "\033[32m $(date "+%Y-%m-%d %H:%M:%S") mysql database ${dbname} export finish \033[0m"

通用导入脚本
将上面导出的文件夹拷贝到待导入数据库主机的secure-file-priv目录下，并在dbname对应的目录下执行如下导入脚本。

#!/bin/bash
username='root'
password='xxx'
dbname='db_mx_car_chat'
curdir=$(pwd)
# SHOW VARIABLES LIKE 'secure_file_priv';
# securefilepriv='/var/lib/mysql-files/' 
# 数据库备份目录
dbstoredir="${curdir}"
# 数据表outfile目录
tablestoredir="${curdir}/outfiledata"
# 创建目录并chmod 777，否则报Can't create/write to file 'xxx' (Errcode: 13 - Permission denied)
mkdir -m 777 -p ${dbstoredir}
mkdir -m 777 -p ${tablestoredir}
echo -e "\033[32m $(date "+%Y-%m-%d %H:%M:%S") current dbstore directory: ${dbstoredir} \033[0m"
echo -e "\033[32m $(date "+%Y-%m-%d %H:%M:%S") current tablestore directory: ${tablestoredir} \033[0m"
echo -e "\033[32m $(date "+%Y-%m-%d %H:%M:%S") connect to database: ${dbname} \033[0m"
# 导入DB定义
echo -e "\033[32m $(date "+%Y-%m-%d %H:%M:%S") mysql source database definition sql: ${dbstoredir}/${dbname}.sql \033[0m"
mysql -u${username}  -p${password} -e "source ${dbstoredir}/${dbname}.sql"
# 从outfile中导入table数据
cd ${tablestoredir}
echo -e "\033[32m $(date "+%Y-%m-%d %H:%M:%S") cd outfiledata dir: ${tablestoredir} \033[0m"
for tablename in `ls`
do
    echo -e "\033[32m $(date "+%Y-%m-%d %H:%M:%S") load data ${tablename} infile: ${tablestoredir}/${tablename} \033[0m"
    mysql -u${username} -p${password} -e "use ${dbname}; set names utf8mb4; load data infile '${tablestoredir}/${tablename}' into table ${tablename};"
done
echo -e "\033[32m $(date "+%Y-%m-%d %H:%M:%S") mysql database ${dbname} import finish \033[0m"

定制blob导出脚本

#!/bin/bash
username='root'
password='xxx'
dbname='sonar-luo2'
# SHOW VARIABLES LIKE 'secure_file_priv';
securefilepriv='/var/lib/mysql-files/' 
# 数据库备份目录
dbstoredir="${securefilepriv}${dbname}"
# 数据表outfile目录
tablestoredir="${dbstoredir}/outfiledata"
# 创建目录并chmod 777，否则报Can't create/write to file 'xxx' (Errcode: 13 - Permission denied)
mkdir -m 777 -p ${dbstoredir}
mkdir -m 777 -p ${tablestoredir}
echo -e "\033[32m $(date "+%Y-%m-%d %H:%M:%S") create dbstore directory: ${dbstoredir} \033[0m"
echo -e "\033[32m $(date "+%Y-%m-%d %H:%M:%S") create tablestore directory: ${tablestoredir} \033[0m"
echo -e "\033[32m $(date "+%Y-%m-%d %H:%M:%S") connect to database: ${dbname} \033[0m"
# 备份DB定义
echo -e "\033[32m $(date "+%Y-%m-%d %H:%M:%S") mysqldump database ${dbname} definition to ${dbstoredir}/${dbname}.sql \033[0m"
mysqldump -u${username}  -p${password} -B -d ${dbname} > ${dbstoredir}/${dbname}.sql
# 备份table数据到outfile
for tablename in ` mysql -u${username} -p${password} -e "use ${dbname};show tables;" | grep -Evi "Tables_in_"`
do
    echo -e "\033[32m $(date "+%Y-%m-%d %H:%M:%S") mysql select ${tablename} into outfile: ${tablestoredir}/${tablename} \033[0m"
    # 特殊处理table中含有blob类型的字段(导出时使用HEX，导入时使用UNHEX)
    # -eq为数字比较，所使用-eq则报[: Illegal number: xxx，此处使用=直接比较字符串
    if [ "$tablename" = "issues" ];then
        echo " issues ===> $tablename"
	    mysql -u${username} -p${password} -e "use ${dbname};set names utf8; select id, kee, rule_id, severity, manual_severity, message, line, gap, status, resolution, checksum, reporter, assignee, author_login, action_plan_key, issue_attributes, effort, created_at, updated_at, issue_creation_date, issue_update_date, issue_close_date, tags, component_uuid, project_uuid, HEX(locations), issue_type, from_hotspot from issues into outfile '${tablestoredir}/${tablename}';"
    elif [ "$tablename" = "file_sources" ];then
	echo "file_sources ===> $tablename"
        mysql -u${username} -p${password} -e "use ${dbname};set names utf8; select id, project_uuid, file_uuid, line_hashes, data_hash, created_at, updated_at, src_hash, HEX(binary_data), revision, line_hashes_version, line_count from file_sources into outfile '${tablestoredir}/${tablename}';"
    else
        # echo "tables ===>  $tablename"
        mysql -u${username} -p${password} -e "use ${dbname};set names utf8; select * into outfile '${tablestoredir}/${tablename}' from ${tablename};"
    fi
done
echo -e "\033[32m $(date "+%Y-%m-%d %H:%M:%S") mysql database ${dbname} export finish \033[0m"

定制blob导入脚本

#!/bin/bash
username='root'
password='xxx'
dbname='sonar-luo3'
curdir=$(pwd)
# SHOW VARIABLES LIKE 'secure_file_priv';
# securefilepriv='/var/lib/mysql-files/' 
# 数据库备份目录
dbstoredir="${curdir}"
# 数据表outfile目录
tablestoredir="${curdir}/outfiledata"
# 创建目录并chmod 777，否则报Can't create/write to file 'xxx' (Errcode: 13 - Permission denied)
mkdir -m 777 -p ${dbstoredir}
mkdir -m 777 -p ${tablestoredir}
echo -e "\033[32m $(date "+%Y-%m-%d %H:%M:%S") current dbstore directory: ${dbstoredir} \033[0m"
echo -e "\033[32m $(date "+%Y-%m-%d %H:%M:%S") current tablestore directory: ${tablestoredir} \033[0m"
echo -e "\033[32m $(date "+%Y-%m-%d %H:%M:%S") connect to database: ${dbname} \033[0m"
# 导入DB定义
echo -e "\033[32m $(date "+%Y-%m-%d %H:%M:%S") mysql source database definition sql: ${dbstoredir}/${dbname}.sql \033[0m"
mysql -u${username}  -p${password} -e "source ${dbstoredir}/${dbname}.sql"
# 从outfile中导入table数据
cd ${tablestoredir}
echo -e "\033[32m $(date "+%Y-%m-%d %H:%M:%S") cd outfiledata dir: ${tablestoredir} \033[0m"
for tablename in `ls`
do
    echo -e "\033[32m $(date "+%Y-%m-%d %H:%M:%S") load data ${tablename} infile: ${tablestoredir}/${tablename} \033[0m"
    # 特殊处理blog类型的自动（导出时使用HEX，导入时使用UNHEX）
    # -eq比较数字，=直接比较字符串
    if [ "$tablename" =  "issues" ];then
	   echo "issues ===> $tablename"
	   mysql -u${username} -p${password} -e "use ${dbname}; set names utf8; load data infile '${tablestoredir}/${tablename}' into table issues(id, kee, rule_id, severity, manual_severity, message, line, gap, status, resolution, checksum, reporter, assignee, author_login, action_plan_key, issue_attributes, effort, created_at, updated_at, issue_creation_date, issue_update_date, issue_close_date, tags, component_uuid, project_uuid, @hexlocations, issue_type, from_hotspot) SET locations=UNHEX(@hexlocations);"
    elif [ "$tablename" = "file_sources" ];then
        echo "file_sources ===> $tablename"
	    mysql -u${username} -p${password} -e "use ${dbname}; set names utf8; load data infile '${tablestoredir}/${tablename}' into table file_sources(id, project_uuid, file_uuid, line_hashes, data_hash, created_at, updated_at, src_hash, @hexBinaryDdata, revision, line_hashes_version, line_count) SET binary_data=UNHEX(@hexBinaryDdata);"
    else
    	mysql -u${username} -p${password} -e "use ${dbname}; set names utf8; load data infile '${tablestoredir}/${tablename}' into table ${tablename};"
    fi 
done
echo -e "\033[32m $(date "+%Y-%m-%d %H:%M:%S") mysql database ${dbname} import finish \033[0m"

使用load data方式耗时3分13秒（共计193秒）

2020-05-13 09:54:23 current dbstore directory: /var/lib/mysql-files/sonar-luo2 
2020-05-13 09:54:23 current tablestore directory: /var/lib/mysql-files/sonar-luo2/outfiledata 
2020-05-13 09:54:23 connect to database: sonar-luo3 
2020-05-13 09:54:23 mysql source database definition sql: /var/lib/mysql-files/sonar-luo2/sonar-luo3.sql 
2020-05-13 09:54:25 cd outfiledata dir: /var/lib/mysql-files/sonar-luo2/outfiledata 
2020-05-13 09:54:25 load data active_rule_parameters infile: /var/lib/mysql-files/sonar-luo2/outfiledata/active_rule_parameters 
2020-05-13 09:54:25 load data active_rules infile: /var/lib/mysql-files/sonar-luo2/outfiledata/active_rules 
2020-05-13 09:54:25 load data alm_app_installs infile: /var/lib/mysql-files/sonar-luo2/outfiledata/alm_app_installs 
2020-05-13 09:54:25 load data analysis_properties infile: /var/lib/mysql-files/sonar-luo2/outfiledata/analysis_properties 
2020-05-13 09:54:25 load data ce_activity infile: /var/lib/mysql-files/sonar-luo2/outfiledata/ce_activity 
2020-05-13 09:54:26 load data ce_queue infile: /var/lib/mysql-files/sonar-luo2/outfiledata/ce_queue 
2020-05-13 09:54:26 load data ce_scanner_context infile: /var/lib/mysql-files/sonar-luo2/outfiledata/ce_scanner_context 
2020-05-13 09:54:27 load data ce_task_characteristics infile: /var/lib/mysql-files/sonar-luo2/outfiledata/ce_task_characteristics 
2020-05-13 09:54:27 load data ce_task_input infile: /var/lib/mysql-files/sonar-luo2/outfiledata/ce_task_input 
2020-05-13 09:54:27 load data ce_task_message infile: /var/lib/mysql-files/sonar-luo2/outfiledata/ce_task_message 
2020-05-13 09:54:28 load data default_qprofiles infile: /var/lib/mysql-files/sonar-luo2/outfiledata/default_qprofiles 
2020-05-13 09:54:28 load data deprecated_rule_keys infile: /var/lib/mysql-files/sonar-luo2/outfiledata/deprecated_rule_keys 
2020-05-13 09:54:28 load data duplications_index infile: /var/lib/mysql-files/sonar-luo2/outfiledata/duplications_index 
2020-05-13 09:54:28 load data es_queue infile: /var/lib/mysql-files/sonar-luo2/outfiledata/es_queue 
2020-05-13 09:54:28 load data event_component_changes infile: /var/lib/mysql-files/sonar-luo2/outfiledata/event_component_changes 
2020-05-13 09:54:28 load data events infile: /var/lib/mysql-files/sonar-luo2/outfiledata/events 
2020-05-13 09:54:28 load data file_sources infile: /var/lib/mysql-files/sonar-luo2/outfiledata/file_sources 
file_sources ===> file_sources
2020-05-13 09:54:58 load data group_roles infile: /var/lib/mysql-files/sonar-luo2/outfiledata/group_roles 
2020-05-13 09:54:58 load data groups infile: /var/lib/mysql-files/sonar-luo2/outfiledata/groups 
2020-05-13 09:54:58 load data groups_users infile: /var/lib/mysql-files/sonar-luo2/outfiledata/groups_users 
2020-05-13 09:54:58 load data internal_properties infile: /var/lib/mysql-files/sonar-luo2/outfiledata/internal_properties 
2020-05-13 09:54:58 load data issue_changes infile: /var/lib/mysql-files/sonar-luo2/outfiledata/issue_changes 
2020-05-13 09:55:20 load data issues infile: /var/lib/mysql-files/sonar-luo2/outfiledata/issues 
issues ===> issues
2020-05-13 09:55:52 load data live_measures infile: /var/lib/mysql-files/sonar-luo2/outfiledata/live_measures 
2020-05-13 09:57:18 load data manual_measures infile: /var/lib/mysql-files/sonar-luo2/outfiledata/manual_measures 
2020-05-13 09:57:18 load data metrics infile: /var/lib/mysql-files/sonar-luo2/outfiledata/metrics 
2020-05-13 09:57:18 load data notifications infile: /var/lib/mysql-files/sonar-luo2/outfiledata/notifications 
2020-05-13 09:57:18 load data org_qprofiles infile: /var/lib/mysql-files/sonar-luo2/outfiledata/org_qprofiles 
2020-05-13 09:57:18 load data org_quality_gates infile: /var/lib/mysql-files/sonar-luo2/outfiledata/org_quality_gates 
2020-05-13 09:57:18 load data organization_alm_bindings infile: /var/lib/mysql-files/sonar-luo2/outfiledata/organization_alm_bindings 
2020-05-13 09:57:18 load data organization_members infile: /var/lib/mysql-files/sonar-luo2/outfiledata/organization_members 
2020-05-13 09:57:18 load data organizations infile: /var/lib/mysql-files/sonar-luo2/outfiledata/organizations 
2020-05-13 09:57:18 load data perm_templates_groups infile: /var/lib/mysql-files/sonar-luo2/outfiledata/perm_templates_groups 
2020-05-13 09:57:18 load data perm_templates_users infile: /var/lib/mysql-files/sonar-luo2/outfiledata/perm_templates_users 
2020-05-13 09:57:18 load data perm_tpl_characteristics infile: /var/lib/mysql-files/sonar-luo2/outfiledata/perm_tpl_characteristics 
2020-05-13 09:57:18 load data permission_templates infile: /var/lib/mysql-files/sonar-luo2/outfiledata/permission_templates 
2020-05-13 09:57:18 load data plugins infile: /var/lib/mysql-files/sonar-luo2/outfiledata/plugins 
2020-05-13 09:57:18 load data project_alm_bindings infile: /var/lib/mysql-files/sonar-luo2/outfiledata/project_alm_bindings 
2020-05-13 09:57:18 load data project_branches infile: /var/lib/mysql-files/sonar-luo2/outfiledata/project_branches 
2020-05-13 09:57:18 load data project_links infile: /var/lib/mysql-files/sonar-luo2/outfiledata/project_links 
2020-05-13 09:57:18 load data project_mappings infile: /var/lib/mysql-files/sonar-luo2/outfiledata/project_mappings 
2020-05-13 09:57:18 load data project_measures infile: /var/lib/mysql-files/sonar-luo2/outfiledata/project_measures 
2020-05-13 09:57:20 load data project_qprofiles infile: /var/lib/mysql-files/sonar-luo2/outfiledata/project_qprofiles 
2020-05-13 09:57:20 load data projects infile: /var/lib/mysql-files/sonar-luo2/outfiledata/projects 
2020-05-13 09:57:29 load data properties infile: /var/lib/mysql-files/sonar-luo2/outfiledata/properties 
2020-05-13 09:57:30 load data qprofile_changes infile: /var/lib/mysql-files/sonar-luo2/outfiledata/qprofile_changes 
2020-05-13 09:57:31 load data qprofile_edit_groups infile: /var/lib/mysql-files/sonar-luo2/outfiledata/qprofile_edit_groups 
2020-05-13 09:57:31 load data qprofile_edit_users infile: /var/lib/mysql-files/sonar-luo2/outfiledata/qprofile_edit_users 
2020-05-13 09:57:31 load data quality_gate_conditions infile: /var/lib/mysql-files/sonar-luo2/outfiledata/quality_gate_conditions 
2020-05-13 09:57:31 load data quality_gates infile: /var/lib/mysql-files/sonar-luo2/outfiledata/quality_gates 
2020-05-13 09:57:31 load data rule_repositories infile: /var/lib/mysql-files/sonar-luo2/outfiledata/rule_repositories 
2020-05-13 09:57:31 load data rules infile: /var/lib/mysql-files/sonar-luo2/outfiledata/rules 
2020-05-13 09:57:32 load data rules_metadata infile: /var/lib/mysql-files/sonar-luo2/outfiledata/rules_metadata 
2020-05-13 09:57:32 load data rules_parameters infile: /var/lib/mysql-files/sonar-luo2/outfiledata/rules_parameters 
2020-05-13 09:57:32 load data rules_profiles infile: /var/lib/mysql-files/sonar-luo2/outfiledata/rules_profiles 
2020-05-13 09:57:32 load data schema_migrations infile: /var/lib/mysql-files/sonar-luo2/outfiledata/schema_migrations 
2020-05-13 09:57:32 load data snapshots infile: /var/lib/mysql-files/sonar-luo2/outfiledata/snapshots 
2020-05-13 09:57:33 load data user_properties infile: /var/lib/mysql-files/sonar-luo2/outfiledata/user_properties 
2020-05-13 09:57:33 load data user_roles infile: /var/lib/mysql-files/sonar-luo2/outfiledata/user_roles 
2020-05-13 09:57:33 load data user_tokens infile: /var/lib/mysql-files/sonar-luo2/outfiledata/user_tokens 
2020-05-13 09:57:33 load data users infile: /var/lib/mysql-files/sonar-luo2/outfiledata/users 
2020-05-13 09:57:33 load data webhook_deliveries infile: /var/lib/mysql-files/sonar-luo2/outfiledata/webhook_deliveries 
2020-05-13 09:57:33 load data webhooks infile: /var/lib/mysql-files/sonar-luo2/outfiledata/webhooks 
2020-05-13 09:57:33 mysql database sonar-luo3 import finish

总结

日常开发时，可以直接使用Navicat进行导入导出，非常方便；
日常运维时，推荐mysqldump，操作简单、兼容性好且效率比较高效（需要注意set names character问题）；
极端情况下，如单表超过500万或千万级数据，推荐使用load data方式更为快速，但是需要单独对table编写sql语句（into outfile、load data）且需要处理blob类型字段；

罗小爬EX

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
打赏
0
评论
记一次MySql数据导入、导出

最近进行了一次Sonar（SonarCube，代码检测工具）数据库的迁移方式1 Navicat方式2 mysqldump+source方式3 into outfile + load data...
复制链接

扫一扫