1.mysql服务器连接本地数据库报错
[bdp@host7 smart-test]$ mysql -uroot -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
添加mysql.sock文件路径连接
[bdp@host7 smart-test]$ mysql -uroot -proot -S/mysql/mysql5.6/data/mysql.sock
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2411652
Server version: 5.6.13-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
比较完整的命令：
mysql -h10.45.47.7 -Dsmart_prod -uroot -proot -P3306 -S/mysql/mysql5.6/data/mysql.sock
2.查看mysql --help命令，常用参数命令
3.以指定字符串竖线分割导出txt文本
用的是select into outfile的方法
#!/bin/bash
#服务器域名
hostname="10.45.47.18";
#数据库名
dbname="smart";
#用户名
user="root";
#密码
password="root";
#端口号
prot="3306";
#scok文件所在路径,可以在安装目录的 /mysql/mysql5.6/my.cnf 查看
mysqlSock="/mysql/mysql5.6/data/mysql.sock";
#生成文件所在目录
fileDir="/home/weblogic/smart/testMysqlExport/";
#导出标签生成文件名，注意date后面空格不省略
fileName="injection_label_`date +%Y%m%d%H%M`.txt";
labelValueFileName="injection_label_value_`date +%Y%m%d%H%M`.txt";
#标签全路径=生成文件所在目录+文件名
fullPathFileName="${fileDir}${fileName}";
#标签值全路径=生成文件所在目录+文件名
labelValueFullPathFileName="${fileDir}${labelValueFileName}";
#表名
tableName="injection_label";
#目标主机信息
ftpIp="10.45.47.66";
ftpUser="bdp";
ftpPassword="bdp123";
ftpDir="/home/bdp/smarttest/injectionLabel/";
#若文件存在，则删除文件
if [ -f ${fullPathFileName} ]; then
rm ${fullPathFileName}
fi
#创建文件
TIME1=$(date "+%Y-%m-%d %H:%M:%S")
echo -e "\n\n========================"${TIME1}" start handle!\n"
echo -e '-h'${hostname} '-P'${prot} '-u'${user} '-p'${password} '-D'${dbname} '-e"'${sqlstr}'"',
#从数据库promotion中读取cateWhiteList表信息--skip-column-names:去掉表头信息
#mysql -h${hostname} -P${prot} -u${user} -p${password} -S /mysql/mysql5.6/data/mysql.sock << EOF
mysql -h${hostname} -D${dbname} -u${user} -p${password} -P${prot} -S${mysqlSock} << EOF
use ${dbname};
SELECT
'injectionLabelId',
'injectionLabelCode',
'injectionLabelName',
'injectionLabelDesc',
'labelManageType',
'labelType',
'labelValueType',
'labelDataType',
'relevantFlag',
'statusCd',
'createStaff',
'updateStaff',
'createDate',
'statusDate',
'updateDate',
'remark',
'actionType',
"tableCode",
"orderId"
UNION
SELECT
a.INJECTION_LABEL_ID,
a.INJECTION_LABEL_CODE,
a.INJECTION_LABEL_NAME,
a.INJECTION_LABEL_DESC,
NULL,
a.LABEL_TYPE,
a.LABEL_VALUE_TYPE,
a. LABEL_DATA_TYPE,
NULL,
a.STATUS_CD,
a.CREATE_STAFF,
a.UPDATE_STAFF,
a.CREATE_DATE,
a.STATUS_DATE,
a.UPDATE_DATE,
a.REMARK,
b.CHANGE_TYPE,
b.table_code,
null
FROM
injection_label a
join data_change_log b on a.INJECTION_LABEL_ID = b.DATA_ID
where b.is_syn_bss =0
and b.table_code='INJECTION_LABEL'
into outfile "${fullPathFileName}" fields terminated by '|' lines terminated by '\\n';
SELECT
"labelValueId",
"injectionLabelId",
"labelValue",
"valueName",
"valueDesc",
"statusCd",
"createStaff",
"updateStaff",
"createDate",
"statusDate",
"updateDate",
"remark",
"actionType"
UNION
SELECT
a.LABEL_VALUE_ID,
a.INJECTION_LABEL_ID,
a.LABEL_VALUE,
a.VALUE_NAME,
a.VALUE_DESC,
a.STATUS_CD,
a.CREATE_STAFF,
a.UPDATE_STAFF,
a.CREATE_DATE,
a.STATUS_DATE,
a.UPDATE_DATE,
a.REMARK,
c.CHANGE_TYPE
FROM
injection_label_value a
join injection_label b on a.INJECTION_LABEL_ID = b.INJECTION_LABEL_ID
join data_change_log c on b.INJECTION_LABEL_ID=c.DATA_ID
where c.is_syn_bss =0
into outfile "${labelValueFullPathFileName}" fields terminated by '|' lines terminated by '\\n';
UPDATE data_change_log
set is_syn_bss=1
where is_syn_bss=0 and TABLE_CODE='INJECTION_LABEL';
EOF
if [ $? -ne 0 ]; then
echo "读取表失败"
exit 1
fi
#推送文件
ftp -v -n<<!
open $ftpIp
user $ftpUser $ftpPassword
binary
cd $ftpDir
lcd ${fileDir}
prompt
mput ${fileName} ${labelValueFileName}
bye
!
echo "推送标签增量文件成功"
rm ${fullPathFileName} ${labelValueFullPathFileName}
##echo "清空本地文件成功"3.远程主机导出数据并入库
注意：乱码设置，还有如果是查询导出默认行数为1000，可以通过select_limit设置行数据，default-character-set设置编码。
#!/bin/bash
#引入服务器编码为utf-8
#export NLS_LANG="AMERICAN_AMERICA.UTF8"
#服务器域名
hostname="10.45.47.7";
#数据库名
dbname="smart_prod";
#用户名
user="root";
#密码
password="root";
#端口号
prot="3306";
#scok文件所在路径
mysqlSock="/mysql/mysql5.6/data/mysql.sock";
#生成文件所在目录
fileDir="/home/weblogic/smart/testMysqlExport/";
#服务器域名
bssHostname="10.45.46.20";
#数据库名
bssDbname="marking";
#用户名
bssUser="bss30";
#密码
bssPassword="1qaz@wsX ";
#端口号
bssProt="8890";
#创建文件
TIME1=$(date "+%Y-%m-%d %H:%M:%S")
echo -e "\n\n========================"${TIME1}" 开始导出数据!\n"
mysql -h${bssHostname} -P${bssProt} -u${bssUser} -p${bssPassword} -D${bssDbname} --default-character-set=utf8 --select_limit=99999 -N -e"SELECT MKT_CAMPAIGN_ID,TIGGER_TYPE,MKT_CAMPAIGN_NAME,PLAN_BEGIN_TIME,PLAN_END_TIME,BEGIN_TIME,END_TIME,MKT_CAMPAIGN_TYPE,MKT_ACTIVITY_NBR,MKT_ACTIVITY_TARGET,MKT_CAMPAIGN_DESC,STATUS_CD,STATUS_DATE,CREATE_STAFF,CREATE_DATE,UPDATE_STAFF,UPDATE_DATE,REMARK,LAN_ID,MKT_CAM_CATE_ID,MKT_CAM_LEVEL,IS_PARENT_CAM,MANAGE_TYPE,EXEC_TYPE,EXEC_INVL,EXEC_NUM,MKT_CAM_EXEC_LEVEL,SERVICE_TYPE,EXT_MKT_CAMPAIGN_ID,REGION_ID FROM mkt_campaign" > ${fileDir}mkt_campaign.txt;
mysql -h${bssHostname} -P${bssProt} -u${bssUser} -p${bssPassword} -D${bssDbname} --default-character-set=utf8 --select_limit=99999 -N -e"SELECT EVT_RECOM_CALC_REL_ID,MKT_CAMPAIGN_ID,ALGO_ID,ALGORITHMS_RUL_ID,PRIORITY,STATUS_CD,CREATE_STAFF,UPDATE_STAFF,CREATE_DATE,UPDATE_DATE,STATUS_DATE,REMARK,STRATEGY_ID FROM mkt_cam_recom_calc_rel" > ${fileDir}mkt_cam_recom_calc_rel.txt;
mysql -h${bssHostname} -P${bssProt} -u${bssUser} -p${bssPassword} -D${bssDbname} --default-character-set=utf8 --select_limit=99999 -N -e"SELECT ALGO_ID,ALGO_CODE,ALGO_NAME,HANDLE_CLASS,ALGO_DESC,STATUS_CD,STATUS_DATE,CREATE_STAFF,CREATE_DATE,UPDATE_STAFF,UPDATE_DATE,REMARK,EXT_ALGO_ID FROM mkt_algorithms limit 999999" > /home/weblogic/smart/testMysqlExport/mkt_algorithms.txt;
mysql -h${bssHostname} -P${bssProt} -u${bssUser} -p${bssPassword} -D${bssDbname} --default-character-set=utf8 --select_limit=99999 -N -e"SELECT ALG_RUL_RELA_ID,MKT_CAMPAIGN_ID,ALGORITHMS_RUL_ID,STATUS_CD,CREATE_STAFF,UPDATE_STAFF,CREATE_DATE,STATUS_DATE,UPDATE_DATE,REMARK FROM cpc_alg_rul_detail_rela" > ${fileDir}cpc_alg_rul_detail_rela.txt;
mysql -h${bssHostname} -P${bssProt} -u${bssUser} -p${bssPassword} -D${bssDbname} --default-character-set=utf8 --select_limit=99999 -N -e"SELECT ALGORITHMS_RUL_ID,TAR_GRP_ID,ITEM_ID,CONTACT_CHL_ID,PRIORITY,STATUS_CD,CREATE_STAFF,UPDATE_STAFF,CREATE_DATE,STATUS_DATE,UPDATE_DATE,REMARK,STRATEGY_ID FROM cpc_alg_rul_detail" > ${fileDir}cpc_alg_rul_detail.txt;
mysql -h${bssHostname} -P${bssProt} -u${bssUser} -p${bssPassword} -D${bssDbname} --default-character-set=utf8 --select_limit=99999 -N -e"SELECT EVENT_ID,INTERFACE_CFG_ID,EVENT_NBR,EVENT_NAME,EVT_MAPPED_ADDR,EVT_MAPPED_IP,EVT_PROCOTOL_TYPE,EVT_MAPPED_FUN_NAME,EVENT_DESC,EVT_TYPE_ID,EVENT_TRIG_TYPE,STATUS_CD,CREATE_STAFF,UPDATE_STAFF,CREATE_DATE,STATUS_DATE,UPDATE_DATE,REMARK,CONTACT_EVT_TYPE,IS_ORIG_EVENT,LOGO_URL,EXT_EVENT_ID,TRIG_RUN_PERIOD,TRIG_CYCLE_VALUE,TRIG_RUN_TIME,NEXT_RUN_TIME FROM event" > ${fileDir}event.txt;
mysql -h${bssHostname} -P${bssProt} -u${bssUser} -p${bssPassword} -D${bssDbname} --default-character-set=utf8 --select_limit=99999 -N -e"SELECT MKT_CAMP_EVT_REL_ID,MKT_CAMPAIGN_ID,EVENT_ID,STATUS_CD,STATUS_DATE,CREATE_STAFF,CREATE_DATE,UPDATE_STAFF,UPDATE_DATE,REMARK,LAN_ID,EXT_MKT_CAMP_EVT_REL_ID FROM mkt_cam_evt_rel" > ${fileDir}mkt_cam_evt_rel.txt;
#把数据导入到本地
mysql -h${hostname} -D${dbname} -u${user} -p${password} -P${prot} -S${mysqlSock} << EOF
use ${dbname};
truncate table mkt_campaign;
truncate table mkt_cam_recom_calc_rel;
truncate table mkt_algorithms;
truncate table cpc_alg_rul_detail_rela;
truncate table cpc_alg_rul_detail;
truncate table event;
truncate table mkt_cam_evt_rel;
load data infile '${fileDir}mkt_campaign.txt' into table mkt_campaign(MKT_CAMPAIGN_ID,TIGGER_TYPE,MKT_CAMPAIGN_NAME,PLAN_BEGIN_TIME,PLAN_END_TIME,BEGIN_TIME,END_TIME,MKT_CAMPAIGN_TYPE,MKT_ACTIVITY_NBR,MKT_ACTIVITY_TARGET,MKT_CAMPAIGN_DESC,STATUS_CD,STATUS_DATE,CREATE_STAFF,CREATE_DATE,UPDATE_STAFF,UPDATE_DATE,REMARK,LAN_ID,MKT_CAM_CATE_ID,MKT_CAM_LEVEL,IS_PARENT_CAM,MANAGE_TYPE,EXEC_TYPE,EXEC_INVL,EXEC_NUM,MKT_CAM_EXEC_LEVEL,SERVICE_TYPE,EXT_MKT_CAMPAIGN_ID,REGION_ID);
load data infile '${fileDir}mkt_cam_recom_calc_rel.txt' into table mkt_cam_recom_calc_rel(EVT_RECOM_CALC_REL_ID,MKT_CAMPAIGN_ID,ALGO_ID,ALGORITHMS_RUL_ID,PRIORITY,STATUS_CD,CREATE_STAFF,UPDATE_STAFF,CREATE_DATE,UPDATE_DATE,STATUS_DATE,REMARK,STRATEGY_ID);
load data infile '${fileDir}mkt_algorithms.txt' into table mkt_algorithms(ALGO_ID,ALGO_CODE,ALGO_NAME,HANDLE_CLASS,ALGO_DESC,STATUS_CD,STATUS_DATE,CREATE_STAFF,CREATE_DATE,UPDATE_STAFF,UPDATE_DATE,REMARK,EXT_ALGO_ID);
load data infile '${fileDir}cpc_alg_rul_detail_rela.txt' into table cpc_alg_rul_detail_rela(ALG_RUL_RELA_ID,MKT_CAMPAIGN_ID,ALGORITHMS_RUL_ID,STATUS_CD,CREATE_STAFF,UPDATE_STAFF,CREATE_DATE,STATUS_DATE,UPDATE_DATE,REMARK);
load data infile '${fileDir}cpc_alg_rul_detail.txt' into table cpc_alg_rul_detail(ALGORITHMS_RUL_ID,TAR_GRP_ID,ITEM_ID,CONTACT_CHL_ID,PRIORITY,STATUS_CD,CREATE_STAFF,UPDATE_STAFF,CREATE_DATE,STATUS_DATE,UPDATE_DATE,REMARK,STRATEGY_ID);
load data infile '${fileDir}event.txt' into table event(EVENT_ID,INTERFACE_CFG_ID,EVENT_NBR,EVENT_NAME,EVT_MAPPED_ADDR,EVT_MAPPED_IP,EVT_PROCOTOL_TYPE,EVT_MAPPED_FUN_NAME,EVENT_DESC,EVT_TYPE_ID,EVENT_TRIG_TYPE,STATUS_CD,CREATE_STAFF,UPDATE_STAFF,CREATE_DATE,STATUS_DATE,UPDATE_DATE,REMARK,CONTACT_EVT_TYPE,IS_ORIG_EVENT,LOGO_URL,EXT_EVENT_ID,TRIG_RUN_PERIOD,TRIG_CYCLE_VALUE,TRIG_RUN_TIME,NEXT_RUN_TIME);
load data infile '${fileDir}mkt_cam_evt_rel.txt' into table mkt_cam_evt_rel(MKT_CAMP_EVT_REL_ID,MKT_CAMPAIGN_ID,EVENT_ID,STATUS_CD,STATUS_DATE,CREATE_STAFF,CREATE_DATE,UPDATE_STAFF,UPDATE_DATE,REMARK,LAN_ID,EXT_MKT_CAMP_EVT_REL_ID);
EOF
#占位查询
#mysql -h${hostname} -P${prot} -u${user} -p${password} -D${dbname} -e"${sqlstr}"
if [ $? -ne 0 ]; then
echo "数据导入失败"
exit 1
fi
echo "导入cpc数据成功";
#rm ${fullPathFileName} ${labelValueFullPathFileName}
##echo "清空本地文件成功"
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
