如何在 Python 中使用 mysqldump 和 mysql 复制数据库? [英] How to copy a database with mysqldump and mysql in Python?

问题描述

我正在编写一个简单的 Python 脚本来复制 MySQL 数据库.我正在尝试根据以下 SO 问题及其答案复制数据库:复制/复制数据库而不使用mysqldump"、python 子进程和mysqldump"和Python 子进程、mysqldump 和管道".但是，由于某些原因，我的脚本无法运行，因为表格和数据没有出现在我的新数据库中.

I am writing a simple Python script to copy a MySQL database. I am attempting to copy the database based on the following SO questions and their answers: "Copy/duplicate database without using mysqldump", "python subprocess and mysqldump" and "Python subprocess, mysqldump and pipes". However, my script does not work for some reason I cannot see as the tables and the data do not appear in my new database.

我可以从我的输出中看到 mysqldump 工作正常(我在输出中看到Dump completed on...")，所以我认为 我的管道有问题.

I can see from my output that the mysqldump works correctly (I see a "Dump completed on..." in my output), so I think that something is wrong with my pipeline.

这是我的脚本:

#!/usr/bin/env python

import pymysql
from subprocess import Popen, PIPE, STDOUT

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='mydb')
cur = conn.cursor()

print("Attempting to create new database...")
try:
    cur.execute("CREATE DATABASE mydb2")
    print("Creating new database")
except Exception:
    print("Database already exists")
print()

# close connection just to be sure
cur.close()
conn.close()

print("Trying to copy old database to new database...")

args1 = ["mysqldump", "-h", "localhost", "-P", "3306", "-u", "root", "-p", "mydb"]
args2 = ["mysql", "-h", "localhost", "-P", "3306", "-u", "root", "-p", "mydb2"]

p1 = Popen(args1, stdout=PIPE, stderr=STDOUT)
p2 = Popen(args1, stdin=p1.stdout, stdout=PIPE, stderr=STDOUT)
output = p2.communicate()

print("output:")
print(output)
print()

如您所见，我从 this answer 获取了复制数据库管道.起初我遇到了错误 mysqldump: Couldn't find table: "|" 就像 其他问题.所以现在我按照建议使用两个 subprocess.Popen 调用，解决了该错误消息.

As you can see I took the copy database pipeline from this answer. And at first I had the error mysqldump: Couldn't find table: "|" just as in that other question. So now I use two subprocess.Popen calls as suggested, which solved that error message.

输出变量显示执行了 mysqldump，但我没有看到关于 mysql 命令的任何内容.

The output variable shows that a mysqldump is performed, but I see nothing being mentioned about the mysql command.

我尝试使用 p2.wait() 和 p1.wait() 而不是 p2.communicate() 在一个答案中建议，但这只会让我的 Python 脚本变得无响应.

I have tried to use p2.wait() and p1.wait() instead of p2.communicate() as suggested in one answer, but that just makes my Python script become unresponsive.

我还尝试了以下方法:

output1 = p1.communicate()
output2 = p2.communicate()

但是 output1 和 output2 都显示了相同的 mysqldump 输出.所以我猜这只是一件愚蠢的事情..

But then both output1 and output2 show the same mysqldump output. So that was just a silly thing to do I guess..

我也尝试过使用 subprocess.call 而不是 subprocess.Popen，但这也会使我的脚本变得无响应.

I have also tried to use subprocess.call instead of subprocess.Popen, but that also makes my script become unresponsive.

在 Popen 或 call 中也包含 shell=True 也会导致脚本无响应.

Also including shell=True in either Popen or call also results in the script being just unresponsive.

但是，在命令提示符(我使用Windows 8.1)中键入命令确实有效，如下所示:

However, it does work to type in the command in the command prompt (I use Windows 8.1) as follows:

mysqldump -h localhost -P 3306 -u root -p mydb |mysql -h localhost -P 3306 -u root -p mydb2

它在不到三秒的时间内复制了我的小型测试数据库.

It copies my small test database in less than three seconds.

我希望我也可以让它在 Python 中工作.

I wish I could also get it to work in Python.

推荐答案

我不知道你想复制的pure Python程度，但是你可以委托整个管道对 shell 的操作.

I don't know the degree of pure Python you want to use for the copy, but you can just delegate the entire pipe operation to the shell.

subprocess.Popen('mysqldump -h localhost -P 3306 -u -root mydb | mysql -h localhost -P 3306 -u root mydb2', shell=True)

这应该与您在 shell 上运行时的工作方式相同.

This should work the same way it works when you run it on the shell.

这篇关于如何在 Python 中使用 mysqldump 和 mysql 复制数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！