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语句,通过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<20" orderdb order > order.sql
如果想要看更多的扩展参数可以看官网介绍
[all options] https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html
导入: 库 < 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] https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html
选项
Description
介绍
--add-drop-database
在每个CREATE DATABASE语句之前添加DROP DATABASE语句
--add-drop-table
在每个CREATE TABLE语句之前添加DROP TABLE语句
--add-drop-user
在每个CREATE USER语句之前添加DROP USER语句
--add-locks
用LOCK TABLES和UNLOCK TABLES语句环绕每个表转储
--all-databases
转储所有数据库
--bind-address
使用指定的网络接口连接到MySQL服务器
--character-sets-dir
安装字符集的目录
--complete-insert
使用包含列名称的完整INSERT语句
--compress
压缩客户端和服务器之间发送的所有信息
--compress-output
输出压缩算法,zlib或lz4(生成同样文件,zlib速度较lz4快,体积也较小), 默认不压缩,生成sql文件; 如使用此参数,须指定生成的压缩文件扩展名
--databases / -B
导出指定多个库,如mysqlpump--databases db_name1 db_name2 ...
导出指定库的多个表,如mysqlpump db_name tbl_name1 tbl_name2
--debug
编写调试日志
--debug-check
程序退出时打印调试信息
--debug-info
打印程序退出时的调试信息,内存和CPU统计信息
--default-auth
身份验证插件使用
--default-character-set
指定默认字符集
--default-parallelism
并行处理的默认线程数(较mysqldump新增)
--defaults-extra-file
除了通常的选项文件外,还要读取已命名的选项
--defaults-file
只读取命名的选项文件
--defaults-group-suffix
选项组后缀值
--defer-table-indexes
对于重新加载,将索引创建推迟到加载表行之后
--events
从转储的数据库转储事件
--exclude-databases
要从转储中排除的数据库
--exclude-events
要从转储中排除的事件
--exclude-routines
从转储中排除的例程
--exclude-tables
要从转储中排除的表
--exclude-triggers
触发器从转储中排除
--exclude-users
用户从转储中排除
--extended-insert
使用多行INSERT语法
--help
显示帮助信息并退出
--hex-blob
使用十六进制符号转储二进制列
--host
主机连接到(IP地址或主机名)
--include-databases
要包含在转储中的数据库
--include-events
包含在转储中的事件
--include-routines
包含在转储中的例程
--include-tables
要包含在转储中的表
--include-triggers
触发器包含在转储中
--include-users
用户包含在转储中
--insert-ignore
写INSERT IGNORE而不是INSERT语句
--log-error-file
将警告和错误附加到指定的文件
--login-path
阅读.mylogin.cnf中的登录路径选项
--max-allowed-packet
发送到服务器或从服务器接收的最大数据包长度
--net-buffer-length
TCP / IP和套接字通信的缓冲区大小
--no-create-db
不要写CREATE DATABASE语句(如果迁出库名与原库名不一致),默认包含建库
--no-create-info
不要编写重新创建每个转储表的CREATE TABLE语句
--no-defaults
读取任何选项文件
--parallel-schemas
指定模式处理并行性
--password
连接到服务器时使用的密码
--plugin-dir
安装插件的目录
--port
用于连接的TCP / IP端口号
--print-defaults
打印默认选项
--protocol
使用连接协议
--replace
编写REPLACE语句而不是INSERT语句
--result-file
直接输出到给定的文件
--routines
从转储的数据库转储存储的例程(过程和函数)
--secure-auth
不要以旧(4.1之前)格式发送密码到服务器
--set-charset
添加SET NAMES default_character_set来输出
--set-gtid-purged
是否添加SET @@ GLOBAL.GTID_PURGED输出
5.7.18
--single-transaction
在单个事务中转储表(5.7.9后--default-parallelism多线程才能与其合用,且其与--add-locks互斥)
--skip-definer
从视图和存储的程序CREATE语句中删除DEFINER和SQL SECURITY子句
--skip-dump-rows
不要转储表行
--socket
用于连接到localhost,要使用的Unix套接字文件
--ssl
启用加密连接
--ssl-ca
包含受信任的SSL证书颁发机构列表的文件
--ssl-capath
包含可信SSL证书颁发机构证书文件的目录
--ssl-cert
包含X509证书的文件
--ssl-cipher
连接加密允许的密码列表
--ssl-crl
包含证书吊销列表的文件
--ssl-crlpath
包含证书撤销列表文件的目录
--ssl-key
包含X509密钥的文件
--ssl-mode
服务器连接的安全状态
5.7.11
--ssl-verify-server-cert
根据服务器证书通用名称身份验证主机名称
--tls-version
允许加密连接的协议
5.7.10
--triggers
每个转储表的转储触发器
--tz-utc
添加SET TIME_ZONE ='+ 00:00'转储文件
--user
连接到服务器时使用的MySQL用户名
--users
转储用户帐户
--version
显示版本信息并退出
5.7.9
--watch-progress
显示进度指示器
第三种 通过data数据文件夹内容进行备份、还原:
直接复制data数据文件夹下相应库和ibdata1文件迁移到新库中(新库最好是空白的,以免数据覆盖),笔者在迁移新电脑时 装完mysql,就懒得把原机器mysql中的各个库像前列方法那样拷贝迁移,所以索性直接复制原机器的data下数据文件替换到新电脑的mysql相应路径,经测试,没有问题.
————————————————
版权声明:本文为CSDN博主「没有永恒」的原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/qq_18406563/article/details/80215751