How to take backups of MySQL databases, explicitly based on PyMySQL I am looking for a purely Python solution to take backups of every MySQL database on localhost. I already use PyMySQL to run queries on these databases. Any help is highly appreciated.

EDIT

After some search, I ended up with the following non-python-based solution.

import os

x =\
    "../../../mysql/bin/mysqldump\
    --host=localhost\
    --port=3306\
    --databases ****\
    --user=****\
    --password=****\
    --default-character-set=utf8\
    --add-drop-database\
    --add-drop-table\
    --add-locks\
    --complete-insert\
    --extended-insert\
    --lock-all-tables\
    --create-options\
    --disable-keys\
    --quick\
    --order-by-primary\
    --set-charset\
    --tz-utc\
    &gt; dump/test.sql;\
    cd dump;\
    tar -zcf test.sql.tar.gz test.sql;\
    rm test.sql;"

os.system(x)


So far so good but I have some questions regarding the parameters as well as the execution.

Regarding my first concern, I just want to make sure that all of them are required and do not cause any conflicts when used together. Ultimately, I would like to make a very robust and consistent dump file, with millions of records, that creates tables, databases, and inserts data. The good thing is that making the database not-available for a while is not a problem for me. My only goal is to make a robust and consistent dump file.

Regarding my second concert, I would like to know if a) the os.system(x) python function is the recommended way to execute Unix commands through Python (as opposed to subprocess.Popen() that most people use), and b) how to get informed when one of these commands goes wrong and if so throw an exception.

Any ideas?
Google searching turns up a few good posts on Python script wrappers for mysqldump


Oct 08, 2014 : StackOverflow : How to copy a database with mysqldump and mysql in Python?
May 05, 2014 : GitHub Gist : A configurable python script that wraps around mysqldump with some additional configuration.
Dec 03, 2013 : TecAdmin.net : Python Script for MySQL Database Backup
Aug 30, 2010 : StackOverflow : Python subprocess, mysqldump and pipes


The first one looks like the easiest way. The code from it says

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


Although I am no expert in Python, if you trust your Python skills and instincts, then ...

Give it a Try !!!