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?
Best Answer
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 BackupAug 30, 2010
: StackOverflow : Python subprocess, mysqldump and pipesThe first one looks like the easiest way. The code from it says
Although I am no expert in Python, if you trust your Python skills and instincts, then ...
Give it a Try !!!