mysql每几个小时导入几条数据到线上 

 1 由于公司需要，需要将线下的数据库每几个小时导几条数据到线上数据库中
 2  
 3 [root@www 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/expect
10 set timeout 20 
11 spawn ssh -C -f -N -g -i id_dsa(密钥)  -L 本机ip:20222（映射到本机的端口）:线下内网dbhosts:3306 zenghui@外网ip  -p22
12 expect "*passphrase*"
13 send "wdzjdsakey\r"
14 expect "suspend"
15  
16 2，ps_ssh.sh
17 ps -ef | grep "192.168.11.45:20222" | grep -v grep
18 if [ $? != 0 ]
19 then
20      ./ssh_bbs.sh
21 fi
22  
23 3,mysql_export.py
24 #!/usr/bin/python
25 import MySQLdb
26 import time
27 import commands,sys
28 import subprocess
29 
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=0
47 for i in  cur.fetchall():
48      mem+=1
49      if mem>20:
50           mem=0
51           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 times
58      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)

 

posted @ 2015-01-16 09:33  xiaohui163  阅读(376)  评论(0)    收藏  举报