python之在 python 中通过 SSH 运行远程 mysqldump

您好，我正在尝试编写 Python 脚本以通过 SSH 连接到远程服务器并执行 mysqldump。到目前为止，我的方法是建立端口转发，然后运行我已经编写的备份脚本。我怀疑问题出在端口转发中，因为备份脚本非常简单。这是端口转发:

import getpass 
import os 
import socket 
import select 
import SocketServer 
import sys 
 
import paramiko 
 
username = 'myusername' 
remote_server = 'remote.servername' 
remote_port = 3306 
local_server = '127.0.0.1' 
local_port = 3307 
SSH_PORT = 22 
password = None 
keyfile = 'hosting.pem' 
 
g_verbose = True 
 
 
class ForwardServer (SocketServer.ThreadingTCPServer): 
    daemon_threads = True 
    allow_reuse_address = True 
 
 
class Handler (SocketServer.BaseRequestHandler): 
 
    def handle(self): 
        try: 
            chan = self.ssh_transport.open_channel('direct-tcpip', 
                                                   (self.chain_host, self.chain_port), 
                                                   self.request.getpeername()) 
        except Exception, e: 
            verbose('Incoming request to %s:%d failed: %s' % (self.chain_host, 
                                                              self.chain_port, 
                                                              repr(e))) 
            return 
        if chan is None: 
            verbose('Incoming request to %s:%d was rejected by the SSH server.' % 
                    (self.chain_host, self.chain_port)) 
            return 
 
        verbose('Connected!  Tunnel open %r -> %r -> %r' % (self.request.getpeername(), 
                                                            chan.getpeername(), (self.chain_host, self.chain_port))) 
        while True: 
            r, w, x = select.select([self.request, chan], [], []) 
            if self.request in r: 
                data = self.request.recv(1024) 
                if len(data) == 0: 
                    break 
                chan.send(data) 
            if chan in r: 
                data = chan.recv(1024) 
                if len(data) == 0: 
                    break 
                self.request.send(data) 
        chan.close() 
        self.request.close() 
        verbose('Tunnel closed from %r' % (self.request.getpeername(),)) 
 
 
def forward_tunnel(local_port, remote_host, remote_port, transport): 
    # this is a little convoluted, but lets me configure things for the Handler 
    # object.  (SocketServer doesn't give Handlers any way to access the outer 
    # server normally.) 
    class SubHander (Handler): 
        chain_host = remote_host 
        chain_port = remote_port 
        ssh_transport = transport 
    ForwardServer(('', local_port), SubHander).serve_forever() 
 
 
def verbose(s): 
    if g_verbose: 
        print s 
 
 
 
def pforward(): 
    paramiko.util.log_to_file('demo.log') 
    client = paramiko.SSHClient() 
    client.load_system_host_keys() 
    client.set_missing_host_key_policy(paramiko.WarningPolicy()) 
 
    try: 
        client.connect(remote_server, SSH_PORT, username, key_filename=keyfile, 
                       look_for_keys=True) 
    except Exception, e: 
        print '*** Failed to connect to %s:%d: %r' % (remote_server, SSH_PORT, e) 
        sys.exit(1) 
 
    verbose('Now forwarding port %d to %s:%d ...' % (local_port, remote_server, remote_port)) 
 
    try: 
        forward_tunnel(local_port, remote_server, remote_port, client.get_transport()) 
    except KeyboardInterrupt: 
        print 'C-c: Port forwarding stopped.' 
        sys.exit(0) 
pforward() 

然后一旦运行，这是我的 mysql 备份脚本:

import MySQLdb 
import os 
 
 
conn = MySQLdb.connect ( 
    host = "127.0.0.1", 
    user = "root", 
    passwd = "password" 
    port = 3307) 
 
cursor = conn.cursor() 
 
cursor.execute("SHOW DATABASES") 
 
results = cursor.fetchall() 
cursor.close() 
conn.close() 
print results 
for result in results: 
    backupfile=result[0]+".sql.gz" 
    cmd="echo 'Back up "+result[0]+" database to yourLocation/"+backupfile+"'" 
    os.system(cmd) 
    cmd="mysqldump -u "+user+" -h "+host+" -p"+passwd+" --opt --routines --flush-privileges --single-transaction --database "+result[0]+" | gzip -9 --rsyncable > yourlocation/"+backupfile 

这是我得到的错误:

_mysql_exceptions.OperationalError: (2013, "Lost connection to MySQL server at 'waiting for initial communication packet', system error: 0")

请您参考如下方法:

好吧 - 所以我想我想多了，不需要设置端口转发和所有那些乱七八糟的东西。这是解决方案>

# -*- coding: utf-8 -*- # <nbformat>3.0</nbformat> 
 
import paramiko 
import os 
 
 
savefile = 'dump.sql' 
mykey = paramiko.RSAKey.from_private_key_file("/users/me/my-host.pem") 
 
client = paramiko.SSHClient() 
client.load_system_host_keys() 
client.connect('hungry.turtles.com', username = "turtles", pkey = mykey) 
 
 
def ssh(cmd): 
    out = [] 
    msg = [stdin, stdout, stderr] = client.exec_command(cmd) 
    for item in msg: 
        try: 
            for line in item: 
                out.append(line.strip('\n')) 
        except: pass 
 
    return(list(out)) 
 
dump = ssh('mysqldump -u root -ppassword turtleturds') 
 
file = open(savefile, 'w') 
file.write(str(dump)) 
file.close() 
print 'The dump had '+ str(len(dump))+ ' lines and was saved to '+ str(os.path.realpath('dump.sql'))