Operating-system-level file copying is not a failsafe backup method because files exist in various places and database implementations change from version to version. Commercial backup software is generally reliable. If your budget does not allow this, use mysqldump.
The next question is, how often should you backup? The more important the data, the more frequent backups must be. A common, reasonable approach is to save a mysqldump once per working day to another network computer, and to save those backup files offsite once a week or so. The question that needs an answer is, how much data can you afford to lose? When the answer is zero, you need offsite replication; otherwise run backups and save them offsite. An approach to *Nix backups is described at http://stackoverflow.com/questions/19664893/linux-shell-script-for-database-backup. When that approach is applied under Windows, you get a batch file like this ...
for /f "tokens=1-3 delims=/- " %%a in ('date /t') do set XDate=%%a%%b%%c
echo MySQL backup for %XDate%:
if exist %1\mysqlbak%XDate%.sql del %1\mysqlbak%XDate%.sql
"PATH_TO_MYSQL\bin\mysqldump" -uUSR -pPWD -A --add-drop-database -K -E -R >%1\mysqlbak%XDate%.sql
... where PATH_TO_MYSQL is the path to the mysql installation, USR and PWD are your MySQL username and password, and %1 is where the backup is to be written, passed to the batch file as a param.
Last updated 16 Aug 2024
