:: Auto MySQL Backup For Windows Servers
\n:: By CONSULTING, INC.<\/p>\n\n\n\n
:: \u2014\u2014\u2014\u2014\u2014\u2014 FILE HISTORY \u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2013
\n:: UPDATE 03.10.2017 v 1.0 <\/p>\n\n\n\n
:: If the time is less than two digits insert a zero so there is no space to break the filename<\/p>\n\n\n\n
set year=%DATE:~10,4%
\nset day=%DATE:~7,2%
\nset mnt=%DATE:~4,2%
\nset hr=%TIME:~0,2%
\nset min=%TIME:~3,2%<\/p>\n\n\n\n
IF %day% LSS 10 SET day=0%day:~1,1%
\nIF %mnt% LSS 10 SET mnt=0%mnt:~1,1%
\nIF %hr% LSS 10 SET hr=0%hr:~1,1%
\nIF %min% LSS 10 SET min=0%min:~1,1%<\/p>\n\n\n\n
set backuptime=%year%-%mnt%-%day%-%hr%-%min%
\necho %backuptime%<\/p>\n\n\n\n
:: SETTINGS AND PATHS
\n:: Note: Do not put spaces before the equal signs or variables will fail<\/p>\n\n\n\n
:: Defaults file path \u2013 no hardcoding of mysql password in the script
\nset defaultsFilePath=\u201dC:\\Users\\home\\mysql_inspectionapp.cnf\u201d<\/p>\n\n\n\n
:: Error log path \u2013 Important in debugging your issues
\nset errorLogPath=\u201d\\\\naster\\inspectionBK\\backupfiles\\dumperrors.log\u201d<\/p>\n\n\n\n
:: MySQL EXE Path
\nset mysqldumpexe=\u201dC:\\Program Files\\MySQL\\MySQL Server 5.5\\bin\\mysqldump.exe\u201d<\/p>\n\n\n\n
:: Error log path
\nset backupfldr=\\\\naster\\inspectionBK\\backupfiles\\<\/p>\n\n\n\n
:: Path to data folder which may differ from install dir
\nset datafldr=\u201dC:\\ProgramData\\MySQL\\MySQL Server 5.5\\data\u201d<\/p>\n\n\n\n
:: Path to zip executable
\nset zipper=\u201d\\\\naster\\inspectionBK\\zip\\7za.exe\u201d<\/p>\n\n\n\n
:: Number of days to retain .zip backup files
\nset retaindays=5<\/p>\n\n\n\n
:: DONE WITH SETTINGS<\/p>\n\n\n\n
:: GO FORTH AND BACKUP EVERYTHING!<\/p>\n\n\n\n
:: Switch to the data directory to enumerate the folders
\npushd %datafldr%<\/p>\n\n\n\n
echo \u201cPass each name to mysqldump.exe and output an individual .sql file for each\u201d<\/p>\n\n\n\n
:: turn on if you are debugging
\n@echo off<\/p>\n\n\n\n
FOR \/D %%F IN (*) DO (<\/p>\n\n\n\n
IF NOT [%%F]==[performance_schema] (
\nSET %%F=!%%F:@002d=-!
\n%mysqldumpexe% \u2013defaults-file=%defaultsFilePath% \u2013max_allowed_packet=256M \u2013single-transaction \u2013master-data=2 \u2013quick \u2013databases \u2013routines \u2013events \u2013log-error=%errorLogPath% %%F > \u201c%backupfldr%%%F.%backuptime%.sql\u201d
\n) ELSE (
\necho Skipping DB backup for performance_schema
\n)
\n)<\/p>\n\n\n\n
echo \u201cZipping all files ending in .sql in the folder\u201d<\/p>\n\n\n\n
:: .zip option clean but not as compressed
\n%zipper% a -tzip \u201c%backupfldr%FullBackup.%backuptime%.zip\u201d \u201c%backupfldr%*.sql\u201d<\/p>\n\n\n\n
echo \u201cDeleting all the files ending in .sql only\u201d<\/p>\n\n\n\n
del \u201c%backupfldr%*.sql\u201d<\/p>\n\n\n\n
echo \u201cDeleting zip files older than %retaindays% days now\u201d
\nForfiles -p %backupfldr% -s -m *.* -d -%retaindays% -c \u201ccmd \/c del \/q @path\u201d<\/p>\n\n\n\n
:: IF WHO WISH TO FTP BACKUP FILES UNCOMMENT THESE LINES AND UPDATE <\/p>\n\n\n\n
::cd\\[path to directory where your file is saved]
\n::@echo off
\n::echo user [here comes your ftp username]>ftpup.dat
\n::echo [here comes ftp password]>>ftpup.dat
\n::echo [optional line; you can put “cd” command to navigate through the folders on the ftp server; eg. cd\\folder1\\folder2]>>ftpup.dat
\n::echo binary>>ftpup.dat
\n::echo put [file name comes here; eg. FullBackup.%backuptime%.zip]>>ftpup.dat
\n::echo quit>>ftpup.dat
\n::ftp -n -s:ftpup.dat [insert ftp server here; eg. myserver.com]
\n::del ftpup.dat<\/p>\n\n\n\n
echo \u201cdone\u201d<\/p>\n\n\n\n
::return to the main script dir on end
\npopd<\/p>\n","protected":false},"excerpt":{"rendered":"
:: Auto MySQL Backup For Windows Servers :: By CONSULTING, INC. :: \u2014\u2014\u2014\u2014\u2014\u2014 FILE HISTORY \u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2013 :: UPDATE 03.10.2017 v 1.0 :: If the time is less than two digits insert a zero so there is no space to break … Continue reading