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