mysql 导入脚本

需求：53数据上线需求

需求说明：

登录Windows服务器172.16.11.53，桌面有data.deal.txt
mysqldump -uroot -p147258 CH_MedicareData MI_Medicare_2018_INCR  >MI_Medicare_2018_INCR_2018xxxxx.sql
打开WinSCP，将备份文件上传到192.168.17.20.
登录linux服务器192.168.17.20，use CH_MedicareData 
Rename table MI_Medicare_2018_INCR to MI_Medicare_2018_INCR_bak_20180xxx;
Mysql –uroot  –p  CH_MedicareData < MI_Medicare_2018_INCR_2018xxxxx.sql
登录到数据库：
INSERT INTO MI_Medicare_2018
(`INSName`, `BillNumber`, `TenBitBillNumber`, `HospitalCode`, `TherapyType`, `MZEnterDate`, `MZOutDate`, `MZDays`, `ZYEnterDate`, 
`ZYOutDate`, `ZYDays`, `DiseaseDescription`, `BillAmount`, `OwnAmount`, `SubOwnAmount`, `PreAmount`, `ItemA31`, `ItemA32`, 
`ItemA33`, `ItemA34`, `BBD001`, `BBD002`, `BBD003`, `BBD004`, `BBD005`, `BBD006`, `BBD007`, `BBD008`,`BBD009`,`BBD010`,`IDCardHashCode`,`CreateDate`) 
SELECT `INSName`, `BillNumber`, `TenBitBillNumber`, `HospitalCode`, `TherapyType`, `MZEnterDate`, `MZOutDate`, `MZDays`, `ZYEnterDate`, 
`ZYOutDate`, `ZYDays`, `DiseaseDescription`, `BillAmount`, `OwnAmount`, `SubOwnAmount`, `PreAmount`, `ItemA31`, `ItemA32`, 
`ItemA33`, `ItemA34`, `BBD001`, `BBD002`, `BBD003`, `BBD004`, `BBD005`, `BBD006`, `BBD007`, `BBD008`,`BBD009`,`BBD010`,`IDCardHashCode`,`CreateDate`
FROM MI_Medicare_2018_INCR;

shell脚本：

[root@master shell]# cat test_sql.sh 
#!/bin/bash
Date=`date +"%Y%m%d"`
# log the alter table
Alter_log=/home/shell/a.log
#log the import data
Import_log=/home/shell/output.log
#the option of the alter table
echo $Date >>$Alter_log
SQL="alter  table MI_Medicare_2018_INCR rename  MI_Medicare_2018_INCR_${Date}"
Rename=$(mysql -uroot -p123456 CH_MedicareData -s -e "${SQL}")
Tablename=$(mysql -uroot -p123456 CH_MedicareData -s -e "desc MI_Medicare_2018_INCR_${Date}")
if [ ! -n "$Tablename" ]; then
 echo "the MI_Medicare_2018_INCR_${Date}  is not existed" >>$Alter_log
else
 echo "the name of the MI_Medicare_2018_INCR_${Date} has been changed" >>$Alter_log
fi
#option of import data
if [ -d  "/home/backup/MI_Medicare_2018_INCR_${Date}.sql" ]; then
/usr/local/mysql/bin/mysql -uroot -p123456 CH_MedicareData<MI_Medicare_2018_INCR_${Date}.sql
 echo "the following is the count of source data" >>$Alter_log
/usr/local/mysql/bin/mysql -uroot -p123456 CH_MedicareData -e "select Max(Id),CreateDate from MI_Medicare_2018"
else
 echo "the file is not existed" >>$Alter_log
fi
echo "the option is the FROM MI_Medicare_2018_INCR to  MI_Medicare_2018"
/usr/local/mysql/bin/mysql -uroot -p123456 << EOF
tee  /home/shell/output.log
show databases;
use CH_MedicareData;
select Max(Id),CreateDate from MI_Medicare_2018;
INSERT INTO MI_Medicare_2018
(INSName, BillNumber, TenBitBillNumber,HospitalCode,TherapyType,MZEnterDate,MZOutDate, MZDays,ZYEnterDate, 
ZYOutDate,ZYDays,DiseaseDescription,BillAmount,OwnAmount, SubOwnAmount,PreAmount,ItemA31, ItemA32, 
ItemA33,ItemA34,BBD001,BBD002,BBD003,BBD004,BBD005,BBD006,BBD007,BBD008,BBD009,BBD010,IDCardHashCode,CreateDate) 
SELECT INSName,BillNumber,TenBitBillNumber,HospitalCode,TherapyType, MZEnterDate, MZOutDate,MZDays,ZYEnterDate, 
ZYOutDate,ZYDays,DiseaseDescription,BillAmount,OwnAmount,SubOwnAmount,PreAmount,ItemA31,ItemA32, 
ItemA33,ItemA34,BBD001,BBD002,BBD003,BBD004,BBD005,BBD006,BBD007,BBD008,BBD009,BBD010,IDCardHashCode,CreateDate
FROM MI_Medicare_2018_INCR;
select Max(Id),CreateDate from MI_Medicare_2018 a,MI_Medicare_2018_INCR b where a.ID=b.Id and a.CreateDate>b.CreateDate;

notee
EOF
exit