mysql每幾個小時匯入幾條資料到線上

標籤：

 1 由於公司需要，需要將線下的資料庫每幾個小時導幾條資料到線上資料庫中 2   3 [[email protected] bbs]# rpm -qa | grep "expect" 4 expect-5.44.1.15-5.el6_4.x86_64 5 如果沒有 yum -y install expect 6   7 1,ssh映射  8 ssh_bbs.sh 9 #!/usr/bin/expect10 set timeout 20 11 spawn ssh -C -f -N -g -i id_dsa(密鑰)  -L 本機ip:20222（映射到原生連接埠）:線下內網dbhosts:3306 [email protected]外網ip  -p2212 expect "*passphrase*"13 send "wdzjdsakey\r"14 expect "suspend"15  16 2，ps_ssh.sh17 ps -ef | grep "192.168.11.45:20222" | grep -v grep18 if [ $? != 0 ]19 then20      ./ssh_bbs.sh21 fi22  23 3,mysql_export.py24 #!/usr/bin/python25 import MySQLdb26 import time27 import commands,sys28 import subprocess29 30 times=time.strftime(‘%Y-%m-%d-%H:%M:%S‘)31 32 def cmd(mysql_cmd):33      (status,output)=commands.getstatusoutput(mysql_cmd)34      if status == 0:35           print mysql_cmd+‘...is ok‘36      elif status == 256:37           print ‘yes‘38           print mysql_cmd+‘...is ok‘39      else:40           sys.exit(1)41 42 conn=MySQLdb.connect(‘192.168.11.23‘,‘root‘,‘root‘,‘test‘,charset=‘utf8‘)43 cur=conn.cursor()44 cur.execute("select id from wenda_question order by id  desc")45 #cur.execute("select id from wenda_question")46 mem=047 for i in  cur.fetchall():48      mem+=149      if mem>20:50           mem=051           time.sleep(3600)52           cmd("kill -9 `ps -ef | grep ‘192.168.11.45:20222‘| grep -v grep|awk ‘{print $2}‘|xargs`")53           time.sleep(30)54           subprocess.Popen("sh ps_ssh.sh",subprocess.PIPE,shell = True)55           time.sleep(20)56           cmd(‘ps -ef | grep "192.168.11.45:20222" | grep -v grep‘)57           print times58      question=‘mysqldump -uroot -proot -h192.168.11.23 --skip-add-drop-table test wenda_question -w "id=‘+str(i[0])+‘">wenda_question.sql‘59      answer=‘mysqldump -uroot -proot -h192.168.11.23 --skip-add-drop-table test wenda_answer -w "qid=‘+str(i[0])+‘">wenda_answer.sql‘60      question_import=‘mysql -uwdzj -pwdzj_2014_bbsnew -h192.168.11.45 -P 20222 -f wdzj_wenda  < wenda_answer.sql‘61      answer_import=‘mysql -uwdzj -pwdzj_2014_bbsnew -h192.168.11.45 -P 20222 -f wdzj_wenda  < wenda_question.sql‘62      cmd(question)63      cmd(answer)64      cmd(question_import)65      cmd(answer_import)

 

mysql每幾個小時匯入幾條資料到線上