1

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\
    > 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?

  • Why would you not use the canonical backup utility for MySQL, which is mysqldump? Using a different library for your backups seems like an unnecessary reinvention of the wheel at best, and a risk of your data's integrity at worst. – Michael - sqlbot Dec 26 '14 at 22:39
  • Your update is actually a completely separate question. Please post the update as its own question. – RolandoMySQLDBA Dec 27 '14 at 18:31
1

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

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 !!!

| improve this answer | |
  • thanks! can you please have a look on my question once again? it just got an update! – user706838 Dec 27 '14 at 17:34
  • Did my post answer your original question ??? – RolandoMySQLDBA Dec 27 '14 at 18:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.