Shulou(Shulou.com)06/01 Report--

This article mainly explains "how to migrate the tables of the database to another server by python". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how python migrates the tables of the database to another server.

Install MySQL-python

To enable python to operate mysql, MySQL-python driver is needed, which is an indispensable module for python to operate mysql.

Download address: https://pypi.python.org/pypi/MySQL-python/

Download the MySQL-python-1.2.5.zip file and extract it directly. Enter the MySQL-python-1.2.5 directory:

> > python setup.py install

Error report:

[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

Solution: install mysql_python manually

Find mysql_config.path=/xxx/xxx in setup_posix.py (mysql installation path / bin/mysql_config)

As follows:

[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

After that:

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

Running install

.

.

.

.

.

In the file containing from _ mysql.c:44:

/ home/mysql/include/my_config.h:442:1: warning: "HAVE_WCSCOLL" redefined

In the file containing from / usr/local/include/python2.7/Python.h:8

From _ mysql.c:29:

/ usr/local/include/python2.7/pyconfig.h:902:1: warning: this is the previously defined location

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

So far, the MySQL-python driver has been installed successfully.

Put what mysql finds out in a list:

[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= []

# get all the results

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') )]

The above result is that there is another meta-ancestor in a list, which is not what we want, so loop the list and then loop the meta-ancestor first, so that we can actually store the contents found in the database in a list. The red part is as follows.

[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= []

# get all the results

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)

The following is a python script written to complete the migration from one mysql server to another mysql server, table by table, which is used to build a master-slave or test environment. My future is easy to understand and a little redundant. In fact, there is no need to put the extracted library and the following table name into a list of these two steps.

[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= []

# get all the results

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.The *", "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.table+ *" + "- 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"

At this point, I believe you have a deeper understanding of "how python migrates the tables of the database to another server". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.