mysql导入导出问题

1sql: select .. into outfile

1select 'itemid' union select itemid from iteminfo  into outfile  '/tmp/test1.csv' FIELDS TERMINATED BY '\t'

下面错误，导出路径不能任意指定，必须在指定的路径下才可以

The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

2使用sqlyog工具

将一个环境下的库表数据信息复制到另外一个环境：

  使用sqlyog的导出 sql文件，然后将此文件不做任何增删修改在另外一个环境上执行导入

我第一次操作时，只是把其中相关操作的语句在另一个环境执行，导致中文信息乱码。后来才发现，再导出时，对name有做utf8设置操作，而自己只是取其中的create ,insert语句，所以乱码了

3mysql命令行

-- 直接在当前位置，执行mysql命令，连接sql


mysql -uroot -p -h192.168.1.100 -e "select * from b" > /tmp/2.sql

-- 有一次上面写法导不出来，改为下面写法

 echo 'select * from b;'|mysql -uroot -p -h192.168.1.100 -e "select * from b" > /tmp/2.sql

4如何通过mysql命令在导出时更改字段分隔符  ，mysql命令参数没办法实现，在写入文件之前是linux的sed命令替换的方式实现

[lsx@tcbj3-mgm01 ~]$ mysql -ulsx -p -h10.10.16.20 -e "SELECT a.itemid AS cms_itemid,a.umcp AS cms_umcp,d.terminal AS cc_terminal FROM masterdb.iteminfo a INNER  JOIN  central_content.trackinfo b ON b.cmsitemid=a.itemid LEFT  JOIN  central_content.trackcpinfo c ON c.trackid=b.trackid  LEFT  JOIN  central_content.copyright d ON d.copyrightid=c.copyrightid LEFT JOIN masterdb.contentprovidergroupinfo e ON e.ContentProviderGroupID=a.spid WHERE  a.statusid=404 AND a.ItemCode!=d.terminal limit 10"|sed 's/\t/::**::/g'
Enter password:
cms_itemid::**::cms_umcp::**::cc_terminal
178::**::1::**::1,2,3,4,5,6,7,8,9,10,11
179::**::1::**::1,2,3,4,5,6,7,8,9,10,11
180::**::1::**::1,2,3,4,5,6,7,8,9,10,11
182::**::1::**::1,2,3,4,5,6,7,8,9,10,11
197::**::1::**::1,2,3,4,5,6,7,8,9,10,11
198::**::1::**::1,2,3,4,5,6,7,8,9,10,11
200::**::1::**::1,2,3,4,5,6,7,8,9,10,11
203::**::1::**::1,2,3,4,5,6,7,8,9,10,11
213::**::1::**::1,2,3,4,5,6,7,8,9,10,11
222::**::1::**::1,2,3,4,5,6,7,8,9,10,11

5如果直接在mysql客户端或者命令行里执行mysql导出时是可以直接指定分隔符的

SELECT 'CC_Company Type','CC_Trackid','CC_company_spid','CC_company_spname',
'CC_availablestatus','CC_umcp','CC_stop_using','CC_pay_mode','re_warrant','is_exclusive',
'copyright_id(合同号ID)','can_auth(合同信息-是否可转授)'
,'CMS_itemid','CMS_Company Type','CMS_Status','CMS_Sub Status'
UNION
SELECT a.companytype,a.trackid,a.companyid,a.companyname,
b.availablestatus,b.umcp,b.stopusing,b.paymode,d.rewarrant,d.isexclusive,
d.copyrightid,e.canauth，
c.itemid,c.company_type,c.StatusID,c.sub_status
FROM test.id1 a
LEFT JOIN 
central_content.trackcontrol b ON a.trackid=b.trackid
LEFT JOIN central_content.trackcpinfo d ON d.trackid=a.trackid
LEFT JOIN central_content.copyright e ON e.copyrightid=d.copyrightid
LEFT JOIN masterdb.iteminfo c ON c.itemid=a.cmsitemid
INTO OUTFILE '/atmd/data/i0610_1.csv' FIELDS TERMINATED BY '\t'

6如何导入数据 sql脚本  注意source在命令行使用，在mysql客户端不可用

 mysql -ulsx111 -pxxx  -h 10.66.11.11 -e "source C:\\Users\\dell\\Desktop\\aa3.sql"

方式二，通过客户端

SQLyog---数据库--导入--SQL...

mysqldump

导出表结构和insert语句

mysqldump --lock-tables=false -h ip -P port -uroot -p123456  -d database_name > dump.sql