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 +"