使用navicat等客户端软件功能进行同步使用mysqldump进行导出导入同步使用mysq命令的into file,load file进行同步命令
数据量不大可以用navicat,mysqldump进行同步,navicat可视化操作方便。但当数据量达到一定级别3000w,1亿以上,实际使用中发现整个导入过程变得漫长
在测试中navacat同步1亿4000w数据 在整个同步过程中,mysql 配置innodb引擎,内存最大innodb_buffer_pool_size=20G ,Windows的c盘虚拟内存配置最大10g,数据盘300G
随着同步进程运行,mysql内存使用率增长到最大限制20g左右,c盘虚拟内存文件pagefile.sys不断增大,由于c盘剩余空间不多,导致最后c盘和内存使用率都几乎100%。Navicat开始报错。 如果同步时间成本不在考虑范围内是,同步时候要考虑硬件配置,当不足时同步一半会报错失败的。
总的方案就是小表数据少的用navicat可视化客户端同步,大的表用可视化可能最有由于硬件资源不足导致失败,或者由于效率低下导入时间持续过久。
1、使用navicat软件数据同步功能
数据传输可以同步结构和数据
数据同步相同表结构两个表只同部数据
结构同步只同步的数据库结构
连接源库和目标库,选择需要同步表和函数过程
有特殊同步需求可以点击高级配置
选择完成后,开始即可开始同步
2、使用mysqldump命令导出数据,source或者客户端导入
导出命令:
mysqldump.exe -h**.mysql. --default-character-set=utf8mb4 --single-transaction --set-gtid-purged=OFF-uabc-p123456 databasetablename>D:/mysql-8.0.15-winx64/Uploads/tablename.sql
-h 数据库域名或IP地址
--default-character-set导出默认使用编码:utf8,utf8mb4等数据存储的编码
--single-transaction不锁表进行导出(可省略)
--set-gtid-purged=OFF不导出正在执行的事务gpid,在阿里云rdsmysql不添加会导出事务信息,自建库没发现需要加
-u 用户名
-p 密码,后面可直接跟密码也可留空,执行时会提示输入密码
database 替换为要导出的数据库名
Tablename 替换为要导出的表名
>路径文件 重定向要导出到的路径和文件名,windows和linux注意路径的书写格式不同
导入命令:
方法1: 使用可视化客户端执行sql,sql中没有创建数据库命令可以用客户端或者命令创建
方法2:使用命令行
使用有权限用户登入数据库,没要导入的数据库可以命令创建,后source 导出的脚本执行导入
#mysql -hip -uuser -ppassword
#Mysql> CREATE DATABASE IF NOT EXISTS testdb DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_0900_ai_ci;
一些关键字简写方式:create database testdb default charset utf8mb4 COLLATE utf8mb4_0900_ai_ci ;
#Mysql> use testdb;
#Mysql> source D:/mysql-8.0.15-winx64/Uploads/tablename.sql;
也可以不登录进去直接重定向导入,确保存在导入的库或者sql中有建库命令
mysql --default-character-set=utf8mb4 -hip -uuser -ppassword testdb < /sql/tablename.sql
windows系统注意:mysql为mysql.exe ,路径类似windows路径D:/sql/tablename.sql; 不要用windows默认的反斜杠
3、使用 into outfile,load导出导入文本文件,这样导出的只有数据,没有表结构。然后创建好表结构后使用文本文件导入数据
SELECT[列名]FROMtable[WHERE 语句]INTOOUTFILE'目标文件'[OPTION];
这个语句只能在mysql服务器上执行,必须拥有写入权限,目标文件不能是已存在的防止覆盖
“OPTION”参数为可选参数选项:
FIELDS TERMINATED BY '字符串':设置字符串为字段之间的分隔符,可以为单个或多个字符。默认值是“\t”。
FIELDS ENCLOSED BY '字符':设置字符来括住字段的值,只能为单个字符。默认情况下不使用任何符号。
FIELDS OPTIONALLY ENCLOSED BY '字符':设置字符来括住CHAR、VARCHAR和TEXT等字符型字段。默认情况下不使用任何符号。
FIELDS ESCAPED BY '字符':设置转义字符,只能为单个字符。默认值为“\”。
LINES STARTING BY '字符串':设置每行数据开头的字符,可以为单个或多个字符。默认情况下不使用任何字符。
LINES TERMINATED BY '字符串':设置每行数据结尾的字符,可以为单个或多个字符。默认值是“\n”。
FIELDS和LINES两个子句都是可选选的,如果两个子句都用到了,FIELDS必须位于LINES的前面。
由于没有权限连接mysql服务器本机,所以into outfile不能使用,折中直接在linux或windows服务器使用命令行客户端连接mysql服务器重定向查询内容
mysql -hxxx.mysql. -uuser-ppassworddatabasename -N-e "select * from tablename limit 10000000">/tmp/sqlbak/输出文件名1;
mysql -hxxx.mysql. -uuser-ppassworddatabasename -N-e "select * from tablename limit 10000000,20000000">/tmp/sqlbak/输出文件名2;
* -N 可以去除表头,只保留数据
* -e 后面跟可执行的sql
* 如果数据库内容过多,使用如上命令导出会不断增大内存的消耗,实测16G内存导出2000w左右数据就会爆满。所以使用limit分段查找。 不过到6000w以上这条语句效率不够分页也仍会占据大量内存,进程会被linux 强行杀掉。增加了swap文件大小提后内存缓存也只是缓解一点。没有测试pagefile 方式分页查询效率
* 倒叙显示所有数据库中表的条数,通过查询小表用户客户端同步大表通过查询手动处理
select TABLE_NAME,table_rows from information_schema.tables where TABLE_SCHEMA='数据库名' and table_rows>0 order by table_rows desc;
字典不是实时更新可能跟实际不符,以下命令显示更新时间间隔默认为24小时
SHOW GLOBAL VARIABLES LIKE 'information_schema_stats_expiry';
可以修改更新时间间隔提高刷新率,下面两个命令都可以修改默认值为0
SET GLOBAL information_schema_stats_expiry=0;
SET @@GLOBAL.information_schema_stats_expiry=0;
sed -i 's/NULL/\\N/g' 输出文件名
* 使用sed效率高,vim或者windows文本编辑器较大文件可能无法打开,编辑效率也很低
* 使用重定向导出的数据如果是空值默认字符串为NULL,load导入mysql不能识别说不能导入NULL值,所以替换为\N ,这样使用load就可以导入了
* 使用navicat客户端,into file导出的空值默认处理为\N 就不用特别处理了。
LOAD DATA INFILE 'filename.txt' INTO TABLE tablename [OPTIONS] [IGNORE number LINES]
--OPTIONS 选项
FIELDS TERMINATED BY 'value' /*设置字段之间分隔符,单个或多个字符,默认为'\t'*/
FIELDS [OPTIONALLY] ENCLOSEED BY 'value' /*设置字段包围分隔符,单个字符*/
FIELDS ESCAPED BY 'value'/*如何写入或读取特殊字符,单个字符*/
LINES STARTING BY 'value'/*每行数据开头的字符,单个或多个*/
LINES TERMINATED BY 'value' /*每行数据结尾的字符,单个或多个*/
load data infile 'D:/mysql-8.0.15-winx64/Uploads/per_post' into table per_post lines terminated by '\r\n';
* 如上图因为是windows导出可以看到结尾为CRLF windows的回车换行符,如果是linux换行则只有\n.
使用文本编辑器notepad++如下设置可以显示特殊的字符
* 当个load的导入效率实测随着导入文件的增大导入效率并没有很高,为了提高效率可以导出500w,1000w数据生成一个文件,在mysql用多文件同时load,多并发同时导入来提高导入效率
如果觉得《mysql大数据量迁移方案》对你有帮助,请点赞、收藏,并留下你的观点哦!