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)
