How To Copy A Database With Mysqldump And Mysql In Python?

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 w

Solution 1:

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)

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

Solution 2:

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)

Solution 3:

I keep coming back to this post as I try to carry out the same task, and it occurs to me that the reason for the unresponsiveness here is the "-p" switch in your mysql and mysqldump commands. "-p" by itself means "prompt for password," so the subprocesses are unresponsive because they're waiting for a password input.

Just in case anybody else comes across this ancient thread and tries to make it work for themselves, this was a trip-up for me.

Solution 4:

Here's how you could run mysqldump .. | mysql pipeline without the 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?

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.