MYSQL数据导出备份、导入的几种方式

第一种 mysqldump:

这是mysql自带的备份命令,提供了很多扩展参数可供选择,工作中常用此方式

常用语法:

mysqldump[options] database [tables,多个表用空格隔开]

--databases [options] BD1 [DB2..多个库用空格隔开]

--all-databases [options]

导出(备份): 导出库 > SQL文件(本地导入导出不需要-P3306 -h 192.168.1.25)(-q –e写法可以合并)

mysqldump -P 3306 -h 192.168.1.25 -uroot -p -q -e base_push > C:\Users\thinkive\Desktop\base_push20170921.sql

-P 3306 -h 192.168.1.25是远程mysql地址和端口

-uroot是远程用户名为root

base_push 是远程库名

以上是导入导出数据的语句，该方法15分钟导出1.6亿条记录，导出的文件中平均7070条记录拼成一个insert语句mysql备份，通过source进行批量插入，导入1.6亿条数据耗时将近5小时。平均速度：3200W条/h。后来尝试加上--single-transaction参数，结果影响不大。另外，若在导出时增加-w参数，表示对导出数据进行筛选，那么导入导出的速度基本不变，筛选出的数据量越大，时间越慢而已。对于其中的参数这里进行说明：

[options]参数

含义

-q , --quick

在导出大表时很有用，它强制 mysqldump 从服务器查询取得记录直接输出而不是取得所有记录后将它们缓存到内存中

-c

是在insert中增加具体的字段名。这样对目的表结构不同原表，情况下更有用

-e ,

--extended-insert

用具有多个VALUES列的INSERT语法。这样使导出文件更小，并加速导入时的速度。默认为打开状态，使用--skip-extended-insert取消选项

-t

仅导出表数据，不导出表结构

--opt –d

仅导出表结构

-R , --routines

导出存储过程以及自定义函数。

--triggers

导出触发器,默认启用

-E , --events

导出事件

--single-transaction

该选项在导出数据之前提交一个BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于多版本存储引擎，仅InnoDB。本选项和--lock-tables 选项是互斥的，因为LOCK TABLES 会使任何挂起的事务隐含提交。要想导出大表的话，应结合使用--quick 选项。在本例子中没有起到加快速度的作用

mysqldump -uroot -p --host=localhost --all-databases --single-transaction

--master-data=[1/2]

如果值等于1，就会添加一个CHANGE MASTER语句(二进制文件的名称和位置)

如果值等于2，就会在CHANGE MASTER语句前添加注释

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;

如果加入此配置,mysql说Binlogging on server not active,解决方案如下:

Linux:需要在/etc/my.cnf里的[mysqlid]下方加入: log-bin=mysql-bin 再重启

Windows:mysql安装目录下my.ini文件,如上操作

--all-databases,-A

导出全部数据库

--add-drop-database

每个数据库创建之前添加drop数据库语句。

--add-drop-table

每个数据表创建之前添加drop数据表语句。(默认为打开状态，使用--skip-add-drop-table取消选项)

-w , --where

过滤条件,只支持单表数据条件导出

mysqldump –ubackup –p –master-data=2 –where "id>10 and id order.sql

如果想要看更多的扩展参数可以看官网介绍

[all options]

导入: 库 < SQL文件(本地导入导出不需要-P 3306 -h 192.168.1.25)

mysql -P 3306 -h 127.0.0.1 -uroot -p base_push < C:\Users\thinkive\Desktop\base_push.sql

远程端口 远程ip 用户名 库名 导入文件路径

第二种 mysqlpump:

与mysqldump相比:

支持基于表的并行导出功能(参数--default-parallelism，默认为2，参数--parallel-schemas，控制并行导出的库)

导出的时候带有进度条（参数--watch-progress，默认开启）

支持直接压缩导出导入（压缩算法参数--compress-output=zlib或lz4 (生成同样后缀名文件,zlib速度较lz4快,体积也较小,解压缩:zlib_decompress input_file(如intput.zlib) output_file(如output.sql) lz4类似(为mysql5.7.10命令) 不推荐使用,解压缩后数据不完全)

注:

mysqldump导出的文件没有库的限制(以及建库语句等),所以可以迁入与牵出库名不同.

mysqlpump导出的库建表有库名的限制(默认也含建库语句),所以迁入库不需要建立(但需要与迁出库名相同)

对比测试：

1.mysqlpump(支持多进程)压缩备份test数据库(21G)三个并发线程备份，消耗时间：2m42.515s，gzip压缩率要比LZ4大

mysqlpump -uroot -p -h127.0.0.1 --single-transaction--default-character-set=utf8 --compress-output=LZ4 --default-parallelism=3 -Btest | gzip > d:\temp\mysqlpump.test.gz

2.mysqldump(单进程)压缩备份test数据库(21G)，消耗时间：28m9.930s，gzip压缩率要比LZ4大(gzip命令需要在linux环境使用)

mysqldump -uroot -p -h127.0.0.1 --default-character-set=utf8 -P3306 --skip-opt--add-drop-table --create-options --quick --extended-insert--single-transaction -B test | gzip >/data/mysql/mysql3306/data/test_db.sql.gz

3.mydumper压缩备份test数据库(21G)，开三个并发线程，消耗时间：10m10.207s

mydumper -u test -h 127.0.0.1 -p safe2016 -P 3306 -t 3 -c -B test -o /data/mysql/mysql3306/data/

4.mydumper压缩备份test数据库(21G)，三个并发线程备份，并且开启对一张表多个线程以chunk的方式批量导出，消耗时间：10m9.518s

mydumper -u test -h 127.0.0.1 -p safe2016 -P 3306 -t 3 -r 300000 -c -B test -o/data/mysql/mysql3306/data/

从上面看出，mysqlpump的备份效率是最快的，mydumper次之，mysqldump最差。所以在IO允许的情况下，能用多线程就别用单线程备份。并且mysqlpump还支持多数据库的并行备份，而mydumper要么备份一个库，要么就备份所有库。

由于实际情况不同，测试给出的速度提升只是参考。到底开启多少个并行备份的线程，这个看磁盘IO的承受能力，若该服务器只进行备份任务，可以最大限制的来利用磁盘.

mysqlpump的并行导出功能的架构为：队列+线程，允许有多个队列，每个队列下有多个线程，而一个队列可以绑定1个或者多个数据库。但是，对于每张表的导出只能是单个线程的

mydumper支持一张表多个线程以chunk的方式批量导出，这在主键是随机的情况下，导出速度还能有提升

MySQL 5.7.11版本解决了一致性备份问题，推荐线上环境使用

mysql压缩成gz方式导入与导出(linux环境)一定数据:导出成sql格式:63MB, gz格式:2.78MB

mysqlpump [options] –B database_name | gzip> /opt/database_name.sql.gz

gunzip < backupfile.sql.gz | mysql -u用户名 -p密码（也可不输入）数据库名

命令示例(--compress-output=zlib压缩参数必须相应生成压缩文件,linux环境`>`前可加`| gzip`生成相应gz文件,不然为sql):

mysqlpump –P 3306 -h192.168.1.189 -uroot -p--single-transaction --default-character-set=utf8 --default-parallelism=3

-B thinkive_base_push > D:\Administrator\Desktop\thinkive.sql

[all options]

选项

Description

介绍

在每个CREATE DATABASE语句之前添加DROP DATABASE语句

在每个CREATE TABLE语句之前添加DROP TABLE语句

在每个CREATE USER语句之前添加DROP USER语句

用LOCK TABLES和UNLOCK TABLES语句环绕每个表转储

转储所有数据库

使用指定的网络接口连接到MySQL服务器

安装字符集的目录

使用包含列名称的完整INSERT语句

压缩客户端和服务器之间发送的所有信息

输出压缩算法,zlib或lz4(生成同样文件,zlib速度较lz4快,体积也较小), 默认不压缩,生成sql文件; 如使用此参数,须指定生成的压缩文件扩展名

/ -B

导出指定多个库,如mysqlpump--databases db_name1 db_name2 ...

导出指定库的多个表,如mysqlpump db_name tbl_name1 tbl_name2

编写调试日志

程序退出时打印调试信息

打印程序退出时的调试信息，内存和CPU统计信息

身份验证插件使用

指定默认字符集

并行处理的默认线程数(较mysqldump新增)

除了通常的选项文件外，还要读取已命名的选项

只读取命名的选项文件

选项组后缀值

对于重新加载，将索引创建推迟到加载表行之后

从转储的数据库转储事件

要从转储中排除的数据库

要从转储中排除的事件

从转储中排除的例程

要从转储中排除的表

触发器从转储中排除

用户从转储中排除

使用多行INSERT语法

显示帮助信息并退出

使用十六进制符号转储二进制列

主机连接到（IP地址或主机名）

要包含在转储中的数据库

包含在转储中的事件

包含在转储中的例程

要包含在转储中的表

触发器包含在转储中

用户包含在转储中

写INSERT IGNORE而不是INSERT语句

将警告和错误附加到指定的文件

阅读.mylogin.cnf中的登录路径选项

发送到服务器或从服务器接收的最大数据包长度

TCP / IP和套接字通信的缓冲区大小

不要写CREATE DATABASE语句(如果迁出库名与原库名不一致),默认包含建库

不要编写重新创建每个转储表的CREATE TABLE语句

读取任何选项文件

指定模式处理并行性

连接到服务器时使用的密码

安装插件的目录

用于连接的TCP / IP端口号

打印默认选项

使用连接协议

编写REPLACE语句而不是INSERT语句

直接输出到给定的文件

从转储的数据库转储存储的例程（过程和函数）

不要以旧（4.1之前）格式发送密码到服务器

添加SET NAMES default_character_set来输出

是否添加SET @@ GLOBAL.GTID_PURGED输出

5.7.18

在单个事务中转储表(5.7.9后--default-parallelism多线程才能与其合用,且其与--add-locks互斥)

从视图和存储的程序CREATE语句中删除DEFINER和SQL SECURITY子句

不要转储表行

用于连接到localhost，要使用的Unix套接字文件

启用加密连接

包含受信任的SSL证书颁发机构列表的文件

包含可信SSL证书颁发机构证书文件的目录

包含X509证书的文件

连接加密允许的密码列表

包含证书吊销列表的文件

包含证书撤销列表文件的目录

包含X509密钥的文件

服务器连接的安全状态

5.7.11

根据服务器证书通用名称身份验证主机名称

允许加密连接的协议

5.7.10

每个转储表的转储触发器

添加SET TIME_ZONE ='+ 00:00'转储文件

连接到服务器时使用的MySQL用户名

转储用户帐户

显示版本信息并退出

5.7.9

显示进度指示器

第三种 通过data数据文件夹内容进行备份、还原:

直接复制data数据文件夹下相应库和ibdata1文件迁移到新库中(新库最好是空白的,以免数据覆盖),笔者在迁移新电脑时 装完mysql,就懒得把原机器mysql中的各个库像前列方法那样拷贝迁移,所以索性直接复制原机器的data下数据文件替换到新电脑的mysql相应路径,经测试,没有问题.

（编辑：财气旺网 - 财气网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!