Python subprocess, mysqldump and pipes

Question

I've got a problem trying to build a easy backup/upgrade database script.

The error is in the mysqldump call using subprocess:

cmdL = ["mysqldump", "--user=" + db_user, "--password=" + db_pass, domaindb + "|", "gzip", ">", databases_path + "/" + domaindb + ".sql.gz"]
print "%s: backup database %s \n\t[%s]" % (domain, domaindb, ' '.join(cmdL))
total_log.write("%s: backup database %s \n\t[%s] \n" % (domain, domaindb, ' '.join(cmdL)))
p = subprocess.Popen(cmdL, stdout=subprocess.PIPE, stderr=subprocess.STDOUT)

Before that cose, i redirect sys.stdout and sys.stderr to files, in order to have a log system.

In those log, i find the error:

[mysqldump --user=xxxxxx --password=yyyyyyyy database_name | gzip > /home/drush-backup/2010-08-30.15.37/db/database_name.sql] [Error]: mysqldump: Couldn't find table: "|"

Seem that the | character is seen as an mysqldump arguments, instead as a pipe.

Looking into the python subprocess documentation, this is normal, but how can i obtain what i need (call the command mysqldump --user=xxxxxx --password=yyyyyyyy database_name | gzip > /home/drush-backup/2010-08-30.15.37/db/database_name.sql)?

EDIT I just see this example on python docs:

output=`dmesg | grep hda`
==>
p1 = Popen(["dmesg"], stdout=PIPE)
p2 = Popen(["grep", "hda"], stdin=p1.stdout, stdout=PIPE)
output = p2.communicate()[0]

and i've edit my script:

command = ["mysqldump", "--user=" + db_user, "--password=" + db_pass, domaindb, "|", "gzip", ">", databases_path + "/" + domaindb + ".sql.gz"]
cmdL1 = ["mysqldump", "--user=" + db_user, "--password=" + db_pass, domaindb]
cmdL2 = ["gzip", ">", databases_path + "/" + domaindb + ".sql.gz"]

print "%s: backup database %s \n\t[%s]" % (domain, domaindb, ' '.join(command))
total_log.write("%s: backup database %s \n\t[%s] \n" % (domain, domaindb, ' '.join(command)))

p1 = subprocess.Popen(cmdL1, stdout=subprocess.PIPE, stderr=subprocess.STDOUT)
p2 = subprocess.Popen(cmdL2, stdin=p1.stdout, stdout=subprocess.PIPE, stderr=subprocess.STDOUT)
cmdError, cmdData = p2.communicate()

now the command variable is just used for convenience in logs.

This go a step next but it stops in the > stream, with this error:

[Error]: gzip: >: No such file or directory
gzip: /path/to/backups/dir/natabase_name.sql.gz: No such file or directory

Obviously, if i try the command in a terminal it works.

The commas add a space, but the plus sign does not. domaindb and the pipe are joined with a plus. Maybe that is the problem? Not sure why you are joining strings with the commas rather than just using spaces and keeping them within the same quotation marks. — xnine, Aug 30 '10 at 13:57
Becose im newbie in python ;) anyway, I think I have to use the +, becose there must be no space between `--user=` and the db_user.. the correct form should be `--user=foo`, or am i mistaken? — Strae, Aug 30 '10 at 14:12

Remi · Answer 1 · 2012-06-22T09:21:49.063

With path, user, pswd and dbname given, the following works like a charm:

import gzip
from subprocess import Popen, PIPE

cmd = "mysqldump --user={user} --password={pswd} {dbname}".format(**locals())        
p = Popen(cmd, shell=True, stdout=PIPE, stderr=PIPE)
with gzip.open(path, "wb") as f:
    f.writelines(p.stdout)

using f as stdout argument in subprocess.Popen() also works but does not compress the data. Before Python 2.7, the with statement does not work, so use f=gzip.open(..) and f.close(). Errors can be read with p.stderr.read(), so if this is not an empty string, you better raise an exception

To restore the backup you can do the following:

cmd = "mysql --user={user} --password={pswd} {dbname}".format(**locals())
p = Popen(cmd, shell=True, stdin=PIPE, stdout=PIPE, stderr=PIPE)
with gzip.open(path, "rb") as f:
    p.stdin.write(f.read())
    p.communicate()[0]
    p.stdin.close()
    p_err = p.stderr.read()
if p_err:
    raise Exception('Error restoring database:\n{0}'.format(p_err))

Jeremy Brown · Accepted Answer · 2010-08-30T15:03:02.953

I'm not sure of how the pipe will get interpreted. If that's a problem, you can programatically create a pipelilne.

from: http://docs.python.org/library/subprocess.html#replacing-shell-pipeline

p1 = Popen(["dmesg"], stdout=PIPE)
p2 = Popen(["grep", "hda"], stdin=p1.stdout, stdout=PIPE)
output = p2.communicate()[0]

edit

As for file redirection, you can direct stdout to a file..

stdin, stdout and stderr specify the executed programs’ standard input, standard output and standard error file handles, respectively. Valid values are PIPE, an existing file descriptor (a positive integer), an existing file object, and None.

Example:

out_file = open(out_filename, "wb")
gzip_proc = subprocess.Popen("gzip", stdout=out_file)
gzip_proc.communicate()

or if you take Alex's advice and use Python's standard library gzip module, you can do something like this:

import gzip
import subprocess

...
#out_filename = path to gzip file

cmdL1 = ["mysqldump", "--user=" + db_user, "--password=" + db_pass, domaindb]
p1 = subprocess.Popen(cmdL1, stdout=subprocess.PIPE, stderr=subprocess.STDOUT)
dump_output = p1.communicate()[0]

f = gzip.open(out_filename, "wb")
f.write(dump_output)
f.close()

i just did and edited my question; now it broke with the `>` step — Strae, Aug 30 '10 at 14:17

score 2 · Answer 3 · answered Aug 30 '10 at 14:13

Try subprocess.Popen(' '.join(cmdL), shell=True).

Pipelines (and redirections) are recognized as such and scheduled by the shell, and, by default (on Unix), subprocess avoids using a shell (it's slower and gives you less control) -- you need to explicitly force a shell to be in control, if a pipeline or redirection is what you absolutely must have.

Normally one tries to avoid pipelines (and therefore avoid shell=True and the attendand issues) by doing as much of that as possible in Python (e.g., in your case, with the gzip module of Python's standard library). Of course for this one must carefully separate stdout (which is to be further processed) from stderr, as two separate pipes.

Python subprocess, mysqldump and pipes

3 Answers3

Linked