如何在 Python 中使用 mysqldump 和 mysql 复制数据库？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26256248/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to copy a database with mysqldump and mysql in Python?
提问by PJvG
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.
我正在编写一个简单的 Python 脚本来复制 MySQL 数据库。我试图根据以下 SO 问题及其答案复制数据库：“不使用 mysqldump 复制/复制数据库”、“ python 子进程和 mysqldump”和“ Python 子进程、mysqldump 和管道”。但是，由于某些原因，我的脚本不起作用，因为表和数据没有出现在我的新数据库中。
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.
我可以从我的输出中看到 mysqldump 工作正常（我在输出中看到“Dump completed on...”），所以我认为我的管道有问题。
Here is my script:
这是我的脚本：
#!/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()
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.Popencalls as suggested, which solved that error message.
正如您所看到的，我从这个答案中获取了复制数据库管道。起初我遇到了错误mysqldump: Couldn't find table: "|"，就像在另一个问题中一样。所以现在我subprocess.Popen按照建议使用了两次调用，这解决了该错误消息。
The output variable shows that a mysqldump is performed, but I see nothing being mentioned about the mysql command.
输出变量显示执行了 mysqldump，但我看不到有关 mysql 命令的任何内容。
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.
我曾尝试使用p2.wait()andp1.wait()而不是p2.communicate()按照一个答案中的建议使用，但这只会使我的 Python 脚本变得无响应。
I have also tried the following:
我还尝试了以下方法：
output1 = p1.communicate()
output2 = p2.communicate()
But then both output1 and output2 show the same mysqldump output. So that was just a silly thing to do I guess..
但是随后 output1 和 output2 都显示相同的 mysqldump 输出。所以我猜这只是一件愚蠢的事情。
I have also tried to use subprocess.callinstead of subprocess.Popen, but that also makes my script become unresponsive.
我也尝试使用subprocess.call而不是subprocess.Popen，但这也使我的脚本变得无响应。
Also including shell=Truein either Popenor callalso results in the script being just unresponsive.
还包括shell=True要么Popen或call也导致脚本只是无响应。
However, it does work to type in the command in the command prompt(I use Windows 8.1) as follows:
但是，它确实可以在命令提示符（我使用 Windows 8.1）中键入命令，如下所示：
mysqldump -h localhost -P 3306 -u root -p mydb | mysql -h localhost -P 3306 -u root -p mydb2
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.
它在不到三秒的时间内复制了我的小型测试数据库。
I wish I could also get it to work in Python.
我希望我也可以让它在 Python 中工作。
采纳答案by dreyescat
I don't know the degree of pure Pythonyou want to use for the copy, but you can just delegate the entire pipe operation to the shell.
我不知道您想用于复制的纯 Python程度，但您可以将整个管道操作委托给 shell。
subprocess.Popen('mysqldump -h localhost -P 3306 -u -root mydb | mysql -h localhost -P 3306 -u root mydb2', shell=True)
This should work the same way it works when you run it on the shell.
这应该与在 shell 上运行时的工作方式相同。
回答by matiu
One problem that I saw is on this line:
我看到的一个问题是在这一行：
p2 = Popen(args1, stdin=p1.stdout, stdout=PIPE, stderr=STDOUT)
It should read:
它应该是：
p2 = Popen(args2, stdin=p1.stdout, stdout=PIPE, stderr=STDOUT)
(args1 were being passed to the second proc, so that the program did two dumps and zero restores)
（args1 被传递给第二个 proc，因此程序进行了两次转储和零恢复）
回答by jfs
Here's how you could run mysqldump .. | mysqlpipeline without the shell:
以下是在mysqldump .. | mysql没有 shell 的情况下运行管道的方法：
#!/usr/bin/env python
from subprocess import Popen, PIPE
mysql = Popen("mysql -h localhost -P 3306 -u root -p mydb2".split(),
stdin=PIPE, stdout=PIPE)
mysqldump = Popen("mysqldump -h localhost -P 3306 -u root -p mydb".split(),
stdout=mysql.stdin)
mysql_stdout = mysql.communicate()[0]
mysqldump.wait()
See How do I use subprocess.Popen to connect multiple processes by pipes?
请参阅如何使用 subprocess.Popen 通过管道连接多个进程？
If you don't need to pass command-line parameters that require complex (possibly non-portable) escaping, capture the exit statuses, stdout then it is simpler to use the shell here.
如果您不需要传递需要复杂（可能不可移植）转义的命令行参数，捕获退出状态，stdout，那么在这里使用 shell 会更简单。
