Although MySQL is normally thought of as the relational database management system that many websites are built on, I’ve found it to be pretty useful for many non-web tasks. Some advantages to using MySQL is that it’s cross platform so I can use it on my Linux and Windows computers and the availability of the gui tool phpMyAdmin which is also cross-platform.
If you use phpMyAdmin, or you’re a linux geek, you know that backing up your MySQL database to a .sql file is easy. There is a point-and-click interface for phpMyAdmin and you can use mysqldump in a terminal on linux. Turns out this also works on windows with a small tweak and can be called from a Python script as well if you are using that language for development….and if you’re not you should really consider it.
Steps to backup a MySQL database running on windows from Python
My default install of WAMP2 for Windows 7 didn’t set an environment variable for the MySQL folder so I added it by adding the text “;C:\wamp\bin\mysql\MySQL 5.1.36\bin” without quotes to the windows PATH by right clicking “My Computer” > Properties > Advanced settings > Environment variables.
Now you should be able to run mysql from the command line from any folder. Try it by opening a command window (win + r > cmd > OK) and typing: “mysql -u username”. Pretty cool.
Now you’re set to run MySQL commands from Python (or PHP, etc. as well I imagine). The following few lines of code will backup an entire database to a temporary directory in your root directory. Good luck!
import os target_dir = 'C:\\TEMP\\' os.system("mysqldump --add-drop-table -c -u username -ppassword database > "+target_dir+"database.bak.sql")