从MySQL同步数据到oracle的小脚本
export ORACLE_BASE=/home/app/oracle
export ORACLE_HOME=/home/app/oracle/product/11.2.0/db_1
export ORACLE_SID=tfsxindaidb
export PATH=/home/app/oracle/product/11.2.0/db_1/bin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin:/home/oracle/bin
export LANG=zh_CN.UTF-8
export NLS_LANG='Simplified Chinese_china.AL32UTF8'
#####获取当前oracle数据库中最新的数据更新时间
cnt=$(
sqlplus -S tfsfund/tfsaabbccd<< EOF
set heading off feedback off
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select max(update_time) from TFSFUND.FUND_ACCOUNT;
EOF
)
echo "1"$cnt
#####从MySQL中抓取数据存入到本地的文件中
mysql -h172.166.230.229 -uread -p'tbc$123read' -Ne "select concat('delete from fund.fund_account where user_id = ''',user_id,''';') from offline.fund_account where update_time > '${cnt}';" > /tmp/2.sql
echo "2"$cnt
mysql -h172.166.230.229 -uread -p'tbc$123read' -Ne "select concat('insert into tfsfund.fund_account values(''',user_id,''',',available_amount,',to_date(''',update_time,''',''yyyy-mm-dd hh24:mi:ss''));') from offline.fund_account where update_time > '${cnt}';" >> /tmp/2.sql
echo "3"$cnt
#####
sqlplus tfsfund/tfsaabbccd << EOF
set heading off feedback off
@/tmp/2.sql;
commit;
EOF
export ORACLE_HOME=/home/app/oracle/product/11.2.0/db_1
export ORACLE_SID=tfsxindaidb
export PATH=/home/app/oracle/product/11.2.0/db_1/bin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin:/home/oracle/bin
export LANG=zh_CN.UTF-8
export NLS_LANG='Simplified Chinese_china.AL32UTF8'
#####获取当前oracle数据库中最新的数据更新时间
cnt=$(
sqlplus -S tfsfund/tfsaabbccd<< EOF
set heading off feedback off
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select max(update_time) from TFSFUND.FUND_ACCOUNT;
EOF
)
echo "1"$cnt
#####从MySQL中抓取数据存入到本地的文件中
mysql -h172.166.230.229 -uread -p'tbc$123read' -Ne "select concat('delete from fund.fund_account where user_id = ''',user_id,''';') from offline.fund_account where update_time > '${cnt}';" > /tmp/2.sql
echo "2"$cnt
mysql -h172.166.230.229 -uread -p'tbc$123read' -Ne "select concat('insert into tfsfund.fund_account values(''',user_id,''',',available_amount,',to_date(''',update_time,''',''yyyy-mm-dd hh24:mi:ss''));') from offline.fund_account where update_time > '${cnt}';" >> /tmp/2.sql
echo "3"$cnt
#####
sqlplus tfsfund/tfsaabbccd << EOF
set heading off feedback off
@/tmp/2.sql;
commit;
EOF
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30623428/viewspace-1870442/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
mysql 备份的的一个小脚本
下一篇：
mysqldump的基本用法
请登录后发表评论
登录
全部评论
