Setting Up A Scheduled MySQL Backup on Windows

April 16th, 2009: My first day on Earth as a MySQL DBA.

I think I need five tasks to run every night:

1. Backup!
2. CHECK TABLES
3. REPAIR TABLES
4. ANALYZE TABLES
5. OPTIMIZE TABLES

CHECK and ANALYZE seem redundant but the docs are not clear to me about this, and there's no harm in redundancy. The docs do seem to suggest that REPAIR and OPTIMIZE are more for adhoc usage, after encountering problems. But I will run them nightly anyway because I want MySQL to leave me alone and just run smoothly and quietly in the background.

Requirements (in order of priority):

1. Minimize data loss from crashes, mistakes, or any other possible reasons.
2. Minimize downtime from database problems.
3. Receive advance warning of potential database problems.

Approach:

• Nightly: BACKUP, REPAIR, OPTIMIZE. In that order.
• Also enable MySQL's 'binary-log' feature to try and recover as close to a data-loss incident as possible.

Philosophy:

• Pretend MyISAM and InnoDB don't exist. This forces our hand on backup tool:  mysqldump.
• Nonetheless, let's not mix MyISAM tables with InnoDB tables. We'll either go 100% MyISAM or 100% InnoDB for a given database.

Solution In Three Files:

# I append these 5 lines to 'my.ini' file.  Only the 'log-bin' and 'expire_logs_days' are really needed
# for the incremental data recovery.  But I'm such a MySQL newb, I want to log everything so that I have
# some hope if I encounter problems.
log
log-slow-queries
log-slow-admin-statements

# It's very important to not log to the network.  Must be local disk.  A separate process
# will move these incremental backup logs to network/backup/tape/etc.
log-bin=C:\Backups\MySQL-50\[computer-name]-bin
expire_logs_days=7

@ECHO OFF
SET MYSQL_BIN="C:\Program Files\MySQL\MySQL Server 5.0\bin"
SET MYSQL_BACKUP="C:\Backups\MySQL-50"
SET MYSQL_USER=[mysql-user]
SET MYSQL_PW=[mysql-password]


:: Date/time DOS BATCH logic comes from http://www.ericphelps.com/batch/samples/ntdate.bat.txt
echo.|date|find "current" >t#e.bat
echo set date=%%5> the.bat
call t#e.bat
del t?e.bat > nul
for /f "tokens=1-4 delims=/ " %%a in ('date /t') do (set weekday=%%a& set day=%%c& set month=%%b& set year=%%d)
for /f "tokens=1-3 delims=: " %%a in ('time /t') do (set hour=%%a& set minute=%%b& set ampm=%%c)
set T=%year%-%month%-%day%.%hour%%minute%%ampm%

:: Print version to stdout, flush tables, and flush logs.
%MYSQL_BIN%\mysqladmin -u %MYSQL_USER% -p%MYSQL_PW% version
%MYSQL_BIN%\mysqladmin -u %MYSQL_USER% -p%MYSQL_PW% refresh


:: Backup our DB #1!
%MYSQL_BIN%\mysqldump -u %MYSQL_USER% -p%MYSQL_PW% -f -x --opt --order-by-primary -R -F --result-file=%MYSQL_BACKUP%\db1-%T%.sql  db1

:: Backup our DB #2!
%MYSQL_BIN%\mysqldump -u %MYSQL_USER% -p%MYSQL_PW% -f -x --opt --order-by-primary -R -F --result-file=%MYSQL_BACKUP%\db2-%T%.sql  db2

:: Backup our DB #3!
%MYSQL_BIN%\mysqldump -u %MYSQL_USER% -p%MYSQL_PW% -f -x --opt --order-by-primary -R -F --result-file=%MYSQL_BACKUP%\db3-%T%.sql  db3


:: check/analyze/repair/optimize
@echo.
@echo Check Tables
%MYSQL_BIN%\mysqlcheck -u %MYSQL_USER% -p%MYSQL_PW% -v -1 -A -c
@echo.
@echo Repair Tables (medium)
%MYSQL_BIN%\mysqlcheck -u %MYSQL_USER% -p%MYSQL_PW% -v -1 -A -r -m
@echo.
@echo Analyze Tables
%MYSQL_BIN%\mysqlcheck -u %MYSQL_USER% -p%MYSQL_PW% -v -1 -A -a
@echo.
@echo Optimize Tables
%MYSQL_BIN%\mysqlcheck -u %MYSQL_USER% -p%MYSQL_PW% -v -1 -A -o

:: Final refresh (flush tables and logs)
%MYSQL_BIN%\mysqladmin -u %MYSQL_USER% -p%MYSQL_PW% refresh

@echo.
@echo.
date /T
time /T
@echo.

@ECHO OFF
:: Date/time DOS BATCH logic comes from http://www.ericphelps.com/batch/samples/ntdate.bat.txt
echo.|date|find "current" >t#e.bat
echo set date=%%5> the.bat
call t#e.bat
del t?e.bat > nul
for /f "tokens=1-4 delims=/ " %%a in ('date /t') do (set weekday=%%a& set day=%%c& set month=%%b& set year=%%d)
for /f "tokens=1-3 delims=: " %%a in ('time /t') do (set hour=%%a& set minute=%%b& set ampm=%%c)
set T=%year%-%month%-%day%.%hour%%minute%%ampm%

.\nightly-backup-impl.bat > C:\Backups\MySQL-50\stdout-%T%.log