189 8069 5689
本篇内容主要讲解“python怎么让数据库的表迁移到另一台服务器”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“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 " +" -h292.168.0.** " +"-d " +db2 +" "+table +" > " + "/backup/" + db2 +"_"+table+ ".sql"
os.system(dumpcmd)
sql1="/home/mysql/bin/mysql "+"-uroot "+"-pg****in@123 "+"-h292.168.0.220 "+db2 +"
os.system(sql1)
print "data translate completed"
到此，相信大家对“python怎么让数据库的表迁移到另一台服务器”有了更深的了解，不妨来实际操作一番吧！这里是创新互联网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！