MySQL | 您所在的位置:网站首页 › shell脚本连接mysql导出表数据 › MySQL |
哈喽,大家好,我是贾斯汀,欢迎进来学习啦~
【学习背景】 在日常工作和学习MySQL时,经常涉及到MySQL数据的导入和导出,分享几种常用又方便的方式: (1)MySQL命令行source命令 (3)语法into outfile和load data infile (3)MySQL目录bin下的mysqldump工具 本文将会介绍以及测试这几种MySQL导入导出数据的方式及使用注意事项,参数可能会比较多,大家可以学习最常用的就好,这里分享出来,希望能帮助到有需要的小伙伴~ 进入正文~ 学习目录 测试数据一、命令source实现2.1 导入数据2.2 导出数据 二、 into oufile和load data infile实现2.1 into outfile2.1.1 简单导出数据2.1.2 带格式导出数据2.1.3 导出注意事项 2.2 load data infile2.2.1 简单导入数据2.2.2 带格式导入数据 三、工具mysqldump实现3.1 导出3.1.1 数据库3.1.2 数据表 3.2 导入数据 测试数据本文以Windows下操作为例,Linux也是一样的方法,区别在于路径语法不同而已~ 创建一个MySQL数据库test和数据表demo_info,方便进行测试~ create database if not exists test default character set utf8 collate utf8_general_ci; use test; -- 创建测试表 create table test.demo_info( id int(7) primary key not null auto_increment, name varchar(255) not null, sex char(1) not null, age int(3) )ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; alter table test.demo_info comment '测试表'; alter table test.demo_info modify column id int(7) not null auto_increment comment 'ID'; alter table test.demo_info modify column name varchar(255) not null comment '姓名'; alter table test.demo_info modify column sex char(1) not null comment '性别:1-男,0-女'; alter table test.demo_info modify column age int(3) comment '年龄'; 一、命令source实现 2.1 导入数据(1)准备insert.sql内容如下: use test; insert into test.demo_info(name,sex,age) values('张一','1',21); insert into test.demo_info(name,sex,age) values('张二','0',22); insert into test.demo_info(name,sex,age) values('张三','1',23);存放路径:C:/Users/Administrator/Desktop/insert.sql (2)先登录到MySQL命令行 打开cmd命令窗口,登录到MySQL命令行: $ cd C:\Program Files\MySQL\MySQL Server 5.7\bin $ mysql -hlocalhost -uroot -p --default-character-set=utf8 输入密码: mysql > (3)执行source命令导入数据: mysql> use test; mysql> show tables; mysql> select * from demo_info; mysql> source C:/Users/Administrator/Desktop/insert.sql; 注意如果你数据库没有设置字符集为utf8,并且在连接时也没有指定--default-character-set=utf8连接,那么会导致插入中文数据时乱码,提示如下: 乱码原因是,默认客户端连接编码为GBK mysql> use test; mysql> show variables like '%character%'; 中文乱码情况的解决方案,如果不想在连接时指定字符集为utf8,可以修改mysql的配置my.ini(my.cnf)指定字符集为utf8,重启mysql服务生效~ [client] default-character-set=utf8 [mysql] character-set-server=utf8 [mysqld] default-character-set=utf8 2.2 导出数据命令source导出数据主要是通过执行导出数据的SQL语句,本质还是使用into outfile语法来实现,这里先简单直接使用下~ (1)准备select.sql内容如下: use test; select * from test.demo_info into outfile 'C:/Users/Administrator/Desktop/demo_info.txt';select.sql存放路径:C:/Users/Administrator/Desktop/select.sql (2)执行source命令导出数据: mysql> source C:/Users/Administrator/Desktop/select.sql; 不过,别高兴太早,一般都会报错的,提示如下: ERROR 1290 (HY000): The MySQL server is running with the –secure-file-priv option so it cannot execute this statement 原因是--secure-file-priv安全路径问题,具体往下进入到into outfile章节了解~ 二、 into oufile和load data infile实现 2.1 into outfile 2.1.1 简单导出数据导出数据通过into outfile语法实现,导入数据通过load data infile语法实现~ (1)前提条件说明 授权用户file权限: mysql > select * from mysql.user where user='root' \G; mysql > update mysql.user set File_priv='Y' where user='root'; mysql > select * from mysql.user where user='root' \G; mysql > flush privileges; 如果没有授予用户的File_priv权限为Y,into outfile导出文件时会报错: ERROR 1 (HY000): Can’t create/write to file ‘C:\Users\Administrator\Desktop\demo_info.txt’ (Errcode: 13 - Permission denied) 配置安全路径: MySQL使用into outfile语法导出数据时,只能导出数据文件到secure-file-priv指定的安全路径下~ 查看安全路径命令mysql> show variables like '%secure%'; 可以看到参数secure_file_priv对应的路径即为MySQL安全路径: 但是Windows下路径问题,有一个小坑,容易误导人,就是这里show显示的路径是单反斜杠\,但实际用的时候要么变成双反斜杠\\,要么改成单斜杠/,才能使用into outfile语法正常导出,否则会报错~ 如果指定导出文件路径不是安全路径下的,则会报错: ERROR 1290 (HY000): The MySQL server is running with the –secure-file-priv option so it cannot execute this statement 简单导出测试下(非安全路径,如桌面): select * from test.demo_info into outfile 'C:/Users/Administrator/Desktop/demo_info.txt';报错提示如下: 简单导出测试下(安全路径) select * from test.demo_info into outfile 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/demo_info.txt';正常导出demo_info.txt数据文件(注意Windows下路径不要用单反斜杠\) (2)配置安全路径 如果不想用默认安全路径,可以修改参数--secure-file-priv为自定义路径,修改MySQL配置文件,一般默认的配置文件路径为: Windows:C:\ProgramData\MySQL\MySQL Server 5.7\my.ini Linux:/etc/my.cnf 安全路径在[mysqld]组下找到参数secure_file_priv进行配置即可~ 这里我修改为空字符串"": secure-file-priv=""空字符串""表示不限制导出路径,不过需要是mysql用户有读写权限的目录,例如Linux下,你不能直接导出到/root/目录下,肯定是没权限创建数据文件的~~ (3)导出数据 简单导出测试: select * from test.demo_info into outfile 'C:/Users/Administrator/Desktop/demo_info.txt';发现导出到桌面居然不成功,其他MySQL安装目录和D盘都可以,C盘下都不行~ 解决方案是按快捷键:Win 快速搜索:服务关键字,找到mysql服务,右键查看属性~ 切换账户为本地系统账户并勾选允许服务与桌面交互~ 应用并重启mysql服务生效~ 重新简单导出测试,导出到桌面成功: select * from test.demo_info into outfile 'C:/Users/Administrator/Desktop/demo_info.txt'; 2.1.2 带格式导出数据通过前面简单导出数据得到数据文件demo_info.txt,可以看到导出的数据占用的空间比较大 7 张一 1 21 8 张二 0 22 9 张三 1 23如果字段的数据比较长,数据量比较大,会很浪费空间,因此需要对into outfile导出的数据文件进行格式化: (1)MySQL命令行> select * from demo_info into outfile 'C:/Users/Administrator/Desktop/demo_info.del' character set utf8 fields terminated by 0x0f;导出的数据空间完全紧密,不浪费任何空间,实际使用这种方式的非常多: (2)终端命令行: mysql -hlocalhost -uroot -p test -e "select * from test.demo_info into outfile 'C:/Users/Administrator/Desktop/demo_info.del' character set utf8 fields terminated by 0x0f"into outfile参数说明: 参数说明character set utf8字符集utf8,防止中文乱码,需要放在fields前面,否则报错fields域,后面常用字段有terminated/optionally/escapedterminated by 'string'设置字段数据之间的分隔符,如最常用的分隔符0x0foptionally enclosed by 'char'设置字段非数值的数据,使用什么符号引起,如英文双引号"escaped by 'char'字段数据存在特殊符号使用的转移符,默认是反斜杠\,如还可以指定为双引号"lines设置每条记录的开头starting和结尾字符terminatedlines starting by 'char'设置每条记录的开头字符,默认空字符串''lines terminated by 'char'设置每条记录的结尾字符默认换行符'\n'使用enclosed by参数示例: select * from demo_info into outfile 'C:/Users/Administrator/Desktop/demo_info2.del' character set utf8 fields terminated by 0x0f optionally enclosed by '"';使用escaped by参数示例: 例如,把张三的名字后面加个特殊符号换行符\n update test.demo_info set name='张一\n' where id=7;再执行导出命令: select * from test.demo_info into outfile 'C:/Users/Administrator/Desktop/demo_info3.del' character set utf8 fields terminated by 0x0f optionally enclosed by '"' escaped by '"';使用lines参数示例: update test.demo_info set name='张一' where id=7; select * from test.demo_info into outfile 'C:/Users/Administrator/Desktop/demo_info4.del' character set utf8 fields terminated by 0x0f optionally enclosed by '"' escaped by '"' lines terminated by 'end\n' starting by 'start ';观察每条记录的首尾数据格式: 2.1.3 导出注意事项(1)存在问题: Linux环境下,由于使用MySQL语法into outfile导出的数据文件时,数据文件只能保存在MySQL数据库服务端,那么会导致在集群模式下,当应用和数据库分别部署在两台不同的服务器时,会存在应用无法读取到数据文件的问题~ MySQL服务器M:/batchfile/mysql/data/test/demo_info.del; 应用服务器A: 批量程序,可能会通过shell脚本想要加载demo_info.del数据文件~ 应用服务器B: 批量程序,可能会通过shell脚本想要加载demo_info.del数据文件~ (2)解决方案: 可以通过mount挂在指定目录/batchfile/为共享盘目录,实现服务器A、B、M都能拥有该目录下的数据文件的读写访问权限~ 具体mount命令的使用方式,可以查询百度学习下~ 2.2 load data infile 2.2.1 简单导入数据(1)数据文件 前面通过into outfile简单导出得到demo_info.txt: 7 张一 1 21 8 张二 0 22 9 张三 1 23(2)导入数据 load data infile 'C:/Users/Administrator/Desktop/demo_info.txt' into table demo_info character set utf8; 2.2.2 带格式导入数据导入del数据文件(加载服务端文件): 命令行mysql> load data infile 'C:/Users/Administrator/Desktop/demo_info.del' into table demo_info character set utf8 fields terminated by 0x0f;load data infile参数说明: 参数说明character set utf8字符集utf8,防止中文乱码,需要放在fields前面,否则报错fields域,后面常用字段有terminated/optionally/escapedterminated by 'string'设置字段数据之间的分隔符,如最常用的分隔符0x0foptionally enclosed by 'char'设置字段非数值的数据,使用什么符号引起,如英文双引号"escaped by 'char'字段数据存在特殊符号使用的转移符,默认是反斜杠\,如还可以指定为双引号"lines设置每条记录的开头starting和结尾字符terminatedlines starting by 'char'设置每条记录的开头字符,默认空字符串''lines terminated by 'char'设置每条记录的结尾字符默认换行符'\n'(字段1,字段2,字段3)指定字段导入数据,注意放在整个语句最后,放前面,会报错其实除了指定字段的参数,其他参数大多只需要跟into outfile导出参数一样,导出时有的参数,load data infile导入时该有的参数也加上就好~ 比如into outfile导出最复杂的情况如下(分隔符为0x0f、非数值双引号"扩起、特殊转义符使用双引号"转义、每条记录开头是start及结尾是end\n)得到数据文件demo_info_complex_data.del update test.demo_info set name='张一\n' where id=7; select * from test.demo_info into outfile 'C:/Users/Administrator/Desktop/demo_info_complex_data.del' character set utf8 fields terminated by 0x0f optionally enclosed by '"' escaped by '"' lines terminated by 'end\n' starting by 'start ';可以看到demo_info_complex_data.del内容如下: 那么要导入demo_info_complex_data.del对应的load data infile语法完整SQL语句为: load data infile 'C:/Users/Administrator/Desktop/demo_info_complex_data.del' into table demo_info character set utf8 fields terminated by 0x0f optionally enclosed by '"' escaped by '"' lines terminated by 'end\n' starting by 'start ';其实很简单,把into outfile导出数据时character后面的参数直接copy过来就行~ Linux终端命令: mkdir -p /batchfile/mysql/data/test/ mysql -hlocalhost -uroot -p test -e "load data infile '/batchfile/mysql/data/test/demo_info.del' into table demo_info character set utf8 fields terminated by 0x0f"导入del数据文件(加载客户端本地LOCAL文件): 命令行mysql> load data LOCAL infile 'C:/Users/Administrator/Desktop/demo_info.del' into table demo_info character set utf8 fields terminated by 0x0f;Linux终端命令: mysql -hlocalhost -uroot -p test -e "load data LOCAL infile '/batchfile/mysql/data/test/demo_info.del' into table demo_info character set utf8 fields terminated by 0x0f"注意:如果MySQL服务端在Linux,load data infile默认是加载服务端路径的数据文件,指定LOCAL表示加载的是客户端的本地数据文件~ 三、工具mysqldump实现MySQL 自带mysqldump 工具,工具文件在bin目录下,不仅可以导出和导入表数据,还可以选择性的导出库表(整库、多库、单库、多表、单表)结构,是数据库备份的方途径之一~ 同样本文以Windows下为例,Linux区别在于路径不同~ 操作本地:mysqldump -u数据库用户 -p xxx 操作远程:mysqldump -hIP地址 -P端口号 -p xxx 3.1 导出 3.1.1 数据库打开cmd命令窗,进入到bin目录下: cd C:\Program Files\MySQL\MySQL Server 5.7\bin(1)导出所有数据库(结构+数据) mysqldump -uroot -p --all-databases > C:/Users/Administrator/Desktop/all_databases.sql(2)导出指定数据库(结构+数据) mysqldump -uroot -p --databases test > test.sql也可以指定多个数据库(结构+数据) mysqldump -u root -p --databases test test2 > test_test2.sql 3.1.2 数据表(1)导出指定数据表(结构+数据) mysqldump -u root -p --set-gtid-purged=OFF test demo_info > demo_info.sql注意:这里设置--set-gtid-purged参数设置为OFF表示mysqldump备份时会记录MySQL的binlog日志,如果不加,则不会记录binlog日志,binlog日志这里不再做具体介绍,简单说明就是MySQL数据库备份、主从复制的核心日志文件~ 那要不要记录binlog日志,取决于你的MySQL设置主从复制的时候用到了gtid: mysql> show variables like '%gtid%'; 参数gtid_mode为ON表明用到gtid了,当然我这里是单库没有主从因此为OFF~ 所以如果是MySQL主从数据库,并且在主库使用mysqldump备份时,需要加--set-gtid-purged=OFF,以便主库记录binlog日志,否则主库没有了binlog日志,当你想在主库恢复备份的数据时,数据并不会被同步到从库~ (2)导出指定数据表(仅结构) mysqldump -u root -p --set-gtid-purged=OFF -d test demo_info > demo_info.sql–参数说明 参数说明-d等价于--no-data,表示不包含数据,仅导出表结构(2)导出指定数据表(仅数据) mysqldump -u root -p --set-gtid-purged=OFF -t test demo_info > demo_info.sql等价于: mysqldump -u root -p --set-gtid-purged=OFF --no-create-info test demo_info > demo_info.sql–参数说明 参数说明-t等价于--no-create-info,表示仅导出数据,不导出CREATE TABLE的表结构(3)导出指定数据表(仅数据 + where条件) mysqldump -u root -p --set-gtid-purged=OFF --no-create-info test demo_info --where "name = '张三'" > demo_info.sql 3.2 导入数据使用工具mysqldump本质是得到SQL语句文件,本文主要目的是介绍导入和导出表数据~ (1)导出表数据 通过前面导出的介绍,可以使用以下命令仅导出demo_info表的数据即可~ 终端命令$ : mysqldump -hlocalhost -P3306 -uroot -p --set-gtid-purged=OFF --no-create-info test demo_info > C:/Users/Administrator/Desktop/demo_info.sql得到demo_info数据表的SQL数据文件demo_info.sql~ (2)导入表数据 先清空表,再导入数据~ 终端命令$ : mysqldump -hlocalhost -P3306 -uroot -p source C:/Users/Administrator/Desktop/demo_info.sql; 或 通过终端命令$: mysql -hlocalhost -P3306 -uroot -pabc@123456 test -e"select * from test.demo_info;" mysql -hlocalhost -P3306 -uroot -pabc@123456 test -e"delete from test.demo_info;" mysql -hlocalhost -P3306 -uroot -pabc@123456 test -e"source C:/Users/Administrator/Desktop/demo_info.sql;"导入数据成功!!! 文章完结~~ 原创不易,觉得有用的小伙伴来个一键三连(点赞+收藏+评论 )+关注支持一下,非常感谢~ |
CopyRight 2018-2019 实验室设备网 版权所有 |