For Windows Servers, you can use a batch file like so:
set year=%DATE:~10,4%
set day=%DATE:~7,2%
set mnt=%DATE:~4,2%
set hr=%TIME:~0,2%
set min=%TIME:~3,2%
IF %day% LSS 10 SET day=0%day:~1,1%
IF %mnt% LSS 10 SET mnt=0%mnt:~1,1%
IF %hr% LSS 10 SET hr=0%hr:~1,1%
IF %min% LSS 10 SET min=0%min:~1,1%
set backuptime=%year%-%mnt%-%day%-%hr%-%min%
set backupfldr=C:\inetpub\wwwroot\backupfiles\
set datafldr="C:\Program Files\MySQL\MySQL Server 5.5\data"
set zipper="C:\inetpub\wwwroot\backupfiles\zip\7za.exe"
set retaindays=21
:: Switch to the data directory to enumerate the folders
pushd %datafldr%
:: Get all table names and save them in a temp file
mysql --skip-column-names --user=root --password=mypassword mydatabasename -e "show tables" > tables.txt
:: Loop through all tables in temp file so that we can save one backup file per table
for /f "skip=3 delims=|" %%i in (tables.txt) do (
set tablename = %%i
mysqldump --user=root --password=mypassword mydatabasename %%i > "%backupfldr%mydatabasename.%backuptime%.%%i.sql"
)
del tables.txt
:: Zip all files ending in .sql in the folder
%zipper% a -tzip "%backupfldr%backup.mydatabasename.%backuptime%.zip" "%backupfldr%*.sql"
echo "Deleting all the files ending in .sql only"
del "%backupfldr%*.sql"
echo "Deleting zip files older than 21 days now"
Forfiles /p %backupfldr% /m *.zip /d -%retaindays% /c "cmd /c del /q @path"
Then schedule it using Windows Task Scheduler.
Also, if you want to exclude certain tables in your backup, note that you can use a where clause on the "show tables" statement, but the column name depends on your database name.
So for example, if your database name is "blah" then your column name in the "show tables" result set will be "tables_in_blah". Which means you could add a where clause something similar to this:
show tables where tables_in_blah <> 'badtable'
or
show tables where tables_in_blah like '%goodtable%'