RAILS项目中MYSQL数据库同步到ORACLE数据库

方法一：直接在mysql中查找到数据然后遍历保存到oracle中

 #load wifi_units data
  def load_wifi_units_data(now)
    wifi_units = Wifi::MobileUnit.find(:all,:conditions=>"length(sta_mac) > 15")
    puts "total wifi_units:  #{wifi_units.length}"
    wifi_infos = Array.new
      wifi_units.each do |wifi_unit|
        wifi_info = WlanWifiUnits.new(:sampletime => now)
        wifi_info.ap_dn          = wifi_unit.ap_dn
        wifi_info.mac            = wifi_unit.mac
        wifi_info.first_seen     = wifi_unit.first_seen
        wifi_info.last_seen      = wifi_unit.last_seen
        wifi_info.duration       = wifi_unit.duration
        wifi_info.sta_mac        = wifi_unit.sta_mac
        wifi_info.sta_ip         = wifi_unit.sta_ip
        wifi_info.sta_power      = wifi_unit.sta_power
        wifi_info.sta_rssi       = wifi_unit.sta_rssi
        wifi_info.sta_channel    = wifi_unit.sta_channel
        wifi_info.sta_vlan       = wifi_unit.sta_vlan
        wifi_info.sta_ssid       = wifi_unit.sta_ssid
        wifi_info.sta_rxframe    = wifi_unit.sta_rxframe
        wifi_info.sta_txframe    = wifi_unit.sta_txframe
        wifi_info.sta_dropframe  = wifi_unit.sta_dropframe
        wifi_info.sta_rxbytes    = wifi_unit.sta_rxbytes
        wifi_info.sta_txbytes    = wifi_unit.sta_txbytes
        wifi_info.sta_dropbytes  = wifi_unit.sta_dropbytes
        wifi_info.sta_noise      = wifi_unit.sta_noise
        wifi_info.sta_noise_rate = wifi_unit.sta_noise_rate

        wifi_infos << wifi_info
      end

      WlanWifiUnits.transaction do
        wifi_infos.each do |wifi_info|
          p "importing: #{wifi_info.ap_dn}"
          wifi_info.save
        end
      end

    Report.deal_wifi_perf(now)
    p "import wifi_units infos over!"
  end

方法二：
在需要大量数据同步的情况下采用了以下方法技术

  一：在mysql库中读取数据写入到ctl文件中
  二：如果mysql和oracle不在同一台服务器，需要配置解决夸服务器复制文件的问题
  三：将ctl文件拿过来用sqlldr工具写入到对应oracle数据库中。

首先可以定义一个ctl文件头：
例如：

OPTIONS(ERRORS=100000)
Load DATA
INFILE *
TRUNCATE INTO TABLE MIT_SSIDS
Fields terminated by '|'
TRAILING NULLCOLS
(id,cn,dn,ap_id,ssidIndex,objectclass,text,radio_no,ssidEnabled,ssidHidden,
staIsolate,dot11Auth,security,authenMode,securityCiphers,vlanId,maxSimultUsers)
BEGINDATA

#往sql文件里写数据select..into outfile
def self.build_wifi_into_outfile(src_file)
    connection.execute("select now() sampletime,ap_dn,mac,first_seen,last_seen,duration,
      sta_mac,sta_ip,sta_power,sta_rssi,sta_channel,sta_vlan,sta_ssid,sta_rxframe,
      sta_txframe,sta_dropframe,sta_rxbytes,sta_txbytes,sta_dropbytes,sta_noise,
      sta_noise_rate from wifi_mobile_units into outfile '#{src_file}'
      fields escaped by '' terminated by '|' lines terminated by '%^&'")
  end

desc 'report to load wifi_units data.'
namespace :wifi_units do
  task :import => :environment do
    puts "usage: rake wifi_units:import is_local=0 src_path='/var/lib/mysql/' imp_path='/opt/mobile/webport/tmp/hourly_import'"
    is_local = ENV['is_local']
    is_local ||= 0
    src_path = ENV['src_path']
    src_path ||= "/var/lib/mysql/"
    imp_path = ENV['imp_path']
    imp_path ||= "/opt/mobile/webport/tmp/hourly_import"
    start_time = Time.now
    puts "===================================="
    puts "start_time:  #{start_time}          "
    puts "===================================="
      now = Time.now
      now1 = Time.now
      puts "(#{now1}):begin to import wifi_units data..."
      load_wifi_units_data2(is_local,src_path,imp_path,now)
      puts "import wifi_units data successfully."
    end_time = Time.now
    puts "===================================="
    puts "end_time:  #{end_time}            "
    puts "total_time :  #{end_time - start_time}"
    puts "===================================="
  end

 
  #load wifi_units data2
  def load_wifi_units_data2(is_local,src_path,imp_path,now)
  time_start = Time.now
  src_file = src_path+"/wifi_data_import_#{now.strftime("%H")}.sql" 
  p "starting output file #{src_file}..."
  src_config = Rails.configuration.database_configuration["development"]
  src_ip = src_config["host"]
  rm_com = "rm -rf #{imp_path}/wifi_data_import_#{now.strftime("%H")}.sql"
  p rm_com
  system rm_com#生成一个空SQL文件
  if is_local == '0'   #两服务器不在一起（采用scp）
    remove_com = "ssh root@#{src_ip}  \"rm #{src_file}\""
    p remove_com
    system remove_com #将文件拷贝到相关目录下
    Wifi::MobileUnit.build_wifi_into_outfile(src_file)
    copy_com = "scp root@#{src_ip}:#{src_file} #{imp_path}/wifi_data_import_#{now.strftime("%H")}.sql"
    p copy_com
    system copy_com
  else   #两服务器在一起（采用cp）
    remove_com = "rm -rf #{src_file}"
    p remove_com
    system remove_com
    Wifi::MobileUnit.build_wifi_into_outfile(src_file)
    copy_com = "cp #{src_file} #{imp_path}/wifi_data_import_#{now.strftime("%H")}.sql"
    p copy_com
    system copy_com  #将文件拷贝到相关目录下
  end
  head_file = "#{RAILS_ROOT}/config/wifi_data_head.ctl" #获取定义好的文件头
  data_file = "#{imp_path}/wifi_data_import_#{now.strftime("%H")}.sql" #获取拷贝过来的数据文件
  format_com = "sed -i ':a;N;$ s/\\\n//g;ba' #{data_file}"
  p format_com
  system format_com
  format_com2 = "sed -i -e 's/NULL//g' -e 's/\\\\//g' -e 's/%^&/\\\n/g' #{data_file}"
  p format_com2
  system format_com2  #对数据文件过滤
  sqlldr_file = "#{imp_path}/wifi_data_import_#{now.strftime("%H")}.ctl" #生成一个新文件
  cat_com = "cat #{head_file} #{data_file} > #{sqlldr_file}"  #组装文件
  p cat_com
  system cat_com   #组装文件运行
  time_end = Time.now
  puts "total_time for build file & copy to ./tmp/hourly_import :#{time_end - time_start}"

  time_start = Time.now
  oracle_config = Rails.configuration.database_configuration["wifi_rms"]
  username = oracle_config["username"]
  password = oracle_config["password"]
  service = oracle_config["service"]
  sqlldr_com = "sqlldr userid=#{username}/#{password}@#{service},control=#{sqlldr_file},log=#{RAILS_ROOT}/log/wifi_data_import#{now.strftime("%H")}.log"
  p sqlldr_com
  system sqlldr_com #sqlldr读取导入ORACLE数据库
  time_end = Time.now
  puts "total_time for sqlldr import wifi infos :#{time_end - time_start}"

  if now.min >=0 and now.min < 59
    p "calling deal_wifi procedure"
    time_start = Time.now
    Report.deal_wifi_perf(now)
    p "import wifi_units infos over!"
    time_end = Time.now
    puts "total_time for deal wifi infos :#{time_end - time_start}"
  end
end


end

方法三：
采用ETL工具很好用。