python脚本完成数据库的一个表一个表的迁移到另一台服务器

安装MySQL-python

要想使python可以操作mysql 就需要MySQL-python驱动，它是python 操作mysql必不可少的模块。

下载地址： https://pypi.python.org/pypi/MySQL-python/

下载MySQL-python-1.2.5.zip 文件之后直接解压。进入MySQL-python-1.2.5目录:

>>python setup.py install

报错：

[root@S220 MySQL-python-1.2.5]# python setup.py install

sh: mysql_config: command not found

Traceback (most recent call last):

File "setup.py", line 17, in

metadata, options = get_config()

File "/MySQL-python-1.2.5/setup_posix.py", line 43, in get_config

libs = mysql_config("libs_r")

File "/MySQL-python-1.2.5/setup_posix.py", line 25, in mysql_config

raise EnvironmentError("%s not found" % (mysql_config.path,))

EnvironmentError: mysql_config not found

解决办法：手工安装mysql_python

在setup_posix.py内找到mysql_config.path=/xxx/xxx(mysql安装路径/bin/mysql_config)

如下：

[root@S220 MySQL-python-1.2.5]# vi setup_posix.py

import os, sys

from ConfigParser import SafeConfigParser

# This dequote() business is required for some older versions

# of mysql_config

def dequote(s):

if s[0] in "\"'" and s[0] == s[-1]:

s = s[1:-1]

return s

def compiler_flag(f):

return "-%s" % f

def mysql_config(what):

from os import popen

f = popen("%s --%s" % (mysql_config.path, what))

data = f.read().strip().split()

ret = f.close()

if ret:

if ret/256:

data = []

if ret/256 > 1:

raise EnvironmentError("%s not found" % (mysql_config.path,))

return data

mysql_config.path ='/home/mysql/bin/mysql_config'

def get_config():

from setup_common import get_metadata_and_options, enabled, create_release_file

之后：

[root@S220 MySQL-python-1.2.5]# python setup.py install

running install

。

。

。

。

。

在包含自 _mysql.c：44 的文件中:

/home/mysql/include/my_config.h:442:1: 警告：“HAVE_WCSCOLL”重定义

在包含自 /usr/local/include/python2.7/Python.h：8 的文件中，

从 _mysql.c：29:

/usr/local/include/python2.7/pyconfig.h:902:1: 警告：这是先前定义的位置

copying build/lib.linux-x86_64-2.7/MySQLdb/constants/__init__.py -> build/bdist.linux-x86_64/egg/MySQLdb/constants

Processing dependencies for MySQL-python==1.2.5

Finished processing dependencies for MySQL-python==1.2.5

至此MySQL-python驱动安装成功。。。。。。

把mysql查出的内容放到一个列表中：

[root@S220 ~]# cat mysqldump.py

#!/usr/bin/python

#-*_ coding:UTF-8 _*_

import MySQLdb

conn= MySQLdb.connect(

host='192.168.0.144',

port = 3306,

user='root',

passwd='admin',

db ='jf',

)

cur = conn.cursor()

dbs=cur.execute('show databases')

mail_list=[]

#获取所有结果

databases = cur.fetchall()

result=list(databases)

for r in result:

mail_list.append(r)

print mail_list

[root@S220 ~]# python mysqldump.py

[('ad',), ('agency',), ('beifen',), ('chinabidding',), ('crm',), ('crm2',), ('data',), ('dingyue',), ('experience',), ('fxb2016',), ('hdzq',), ('history',), ('info',), ('info_addition',), ('info_back',), ('information_schema',), ('infoservice',), ('jf',), ('jf2016',), ('liuwenhe',), ('log',), ('mailer',), ('mysql',), ('performance_schema',), ('resin_session',), ('sbiao',), ('sbw2',), ('stat',), ('test',), ('web',), ('weblog',), ('winfo',)]

上面的结果中是一个列表中又有一个元祖，这不是我们想要的结果，所以先循环列表再循环元祖，这样可以真正做到把数据库查出的内容存到一个列表中。如下红色部分。

[root@S220 ~]# cat mysqldump.py

#!/usr/bin/python

#-*_ coding:UTF-8 _*_

import MySQLdb

conn= MySQLdb.connect(

host='192.168.0.144',

port = 3306,

user='root',

passwd='admin',

db ='jf',

)

cur = conn.cursor()

dbs=cur.execute('show databases')

mail_list=[]

#获取所有结果

databases = cur.fetchall()

result=list(databases)

for r in result:

mail_list.append(r)

dbs_list=[]

for db in mail_list:

for db1 in db:

dbs_list.append(db1)

print(dbs_list)

下面是写的一个python脚本，用来完成从一台mysql服务器迁移到另一台mysql服务器，一个表一个表的迁移，这试用于搭建主从，或者搭建测试环境的场景。我这个未来便于理解，写的有点多余，其实不需要把取出来的库和下面的表名字，放到一个列表中这2步骤。

[root@S220 ~]# cat mysqldump.py

#!/usr/bin/python

#-*_ coding:UTF-8 _*_

import MySQLdb

import os

conn= MySQLdb.connect(

host='192.168.0.144',

port = 3306,

user='root',

passwd='***',

db ='jf',

)

cur = conn.cursor()

dbs=cur.execute('show databases')

mail_list=[]

#获取所有结果

databases = cur.fetchall()

result=list(databases)

for r in result:

mail_list.append(r)

dbs_list=[]

for db in mail_list:

for db1 in db:

dbs_list.append(db1)

conn.close()

cur.close()

tables_list=[]

for db2 in dbs_list:

if db2=='liuwenhe':

conn_name={"host":"192.168.0.**","user":"root","passwd":"ad****min","db":db2}

conn1= MySQLdb.connect(**conn_name)

cur_new = conn1.cursor()

tbs=cur_new.execute('show tables')

tbs_list=[]

tables = cur_new.fetchall()

result=list(tables)

for t in result:

tbs_list.append(t)

for tb1 in tbs_list:

for tb2 in tb1:

tables_list.append(tb2)

conn1.close()

cur_new.close()

for table in tables_list:

dumpcmd = "/home/mysql/bin/mysqldump -uroot " + " --opt " " -padmin " +" -h192.168.0.** " +"-d " +db2 +" "+table +" > " + "/backup/" + db2 +"_"+table+ ".sql"

os.system(dumpcmd)

sql1="/home/mysql/bin/mysql "+"-uroot "+"-pg****in@123 "+"-h192.168.0.220 "+db2 +"

os.system(sql1)

print "data translate completed"

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29654823/viewspace-2146753/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29654823/viewspace-2146753/