scheduled backups

[Lanmanfm]

I thought I had seen the SQL code to add a scheduled backup to the database, but can't seem to find it for the life of me...

 

So, what is the SQL to create a scheduled event for backing up the db?

 

Actually, what I really want to do is use the BEC scheduler to back the db up and copy/clear all the log files every reboot, but I would be happy to just get the db backup automated for now.

[Gimmic]

This is more of a mySQL question than an epoch one. You will want to use mysqldump to create a dump of the DB.

Google example: http://www.noupe.com/how-tos/10-ways-to-automatically-manually-backup-mysql-database.html

[axeman]

Create a file called mySQLBackup.bat and add this with notepad++:

"D:\MySQL\MySQL Server 5.1\bin\mysqldump.exe" --user=[username] --pass=[password] -A > D:\bak\fullmysqldump.sql

Adjust the path to your mysql server's bin directory and backup output directory. That will create a backup of all schemas. If you want to backup just one specific database use:

"D:\MySQL\MySQL Server 5.1\bin\mysqldump.exe" --user=[username] --pass=[password] [database/schema name eg. dayz_epoch] > D:\bak\epochmysqldump.sql

Add that batch file to windows scheduled tasks (every hour shouldn't hurt - probably worth monitoring the load when backing up and adjust accordingly).

 

Place hands behind head, sit back and relax  ;)

[Gimmic]

I would really suggest implementing rolling backups. With the above, you're going to lose the previous backup each time it runs. This means when something bad happens, if you don't catch it within the hour you lose your backup.

[axeman]

Just add a date/time variable to the file name..

[Swash]

Here's some overkill for you.

 

Add a bat file I call mine dayzdbdump.bat

@echo off
REM MySQL Dump / Backup Script for Windows NT Systems.
REM
REM This Script will dump all tables from your MySQL Instance to a 7zip archive; 
REM it will Also take care of starting and stopping the MySQL Service on the machine.
REM
REM @author Jonny Reeves - http://www.jonnyreeves.co.uk/
REM Modified by UnboundGaming


setlocal
set mysql_username="dayz"
set mysql_password="PASSWORDGOESHERE"
set mysql_service="MySQL56"
set mysql_path="C:\Program Files\MySQL\MySQL Server 5.6\bin"
set output_path="C:\DayZDB"




REM Start of Script.
IF NOT EXIST %output_path% (mkdir %output_path%)


REM Check to see if the MySQL Service is running
for /f "tokens=*" %%a IN ('sc query "%mysql_service%" ^| find "RUNNING"') do set servicerunning=%%a
if "X%servicerunning%%" == "X" (goto service_stopped) ELSE (goto service_running)


:service_stopped
    echo Starting MySQL Service: %mysql_service%
    net start %mysql_service%
    call :dump_and_zip
    echo Stopping MySQL Service: %mysql_service%
    net stop %mysql_service%
    goto end


:service_running
    echo MySQL Service is already running.
    call :dump_and_zip
    goto end
     
:dump_and_zip:
    REM Dump out the MySQL Database to a timestamped .sql file
    for /f "tokens=1,2,3 delims=/ " %%a in ('DATE /T') do set date=%%c-%%b-%%a
    for /f "tokens=1,2 delims=:" %%a in ('TIME /T') do set time=%%a-%%b
    %mysql_path%\mysqldump.exe --user %mysql_username% --password=%mysql_password% --all-databases --opt > "%output_path%\%date%_%time%.sql"


    REM Check for Errors.
    if %ERRORLEVEL% NEQ 0 (goto error)
         
:error
    echo An error occured.
    EXIT /B 42
     
:end
    endlocal

This will date and timestamp each db dump. (example: 20-09-Fri_05-00 PM.sql)

I set this to run every 30 minutes via task scheduler.

 

Next is cleanup. 

Make a new bat file dbdumpcleanup.bat

forfiles -p "C:\DayZDB" -s -m *.* /D -2 /C "cmd /c del @path"

This will delete all dumps older than 2 days.

You can set it longer or shorter at the /D -2 part

You can set this to run once a day via task scheduler, or incorporate it into a server restart.

 

This set up will give you backups every 30 minutes over a 2 day period, or about 96 incremental backups.

[Kind-Sir]

I wrote a couple a while back for both Windows and Linux.

**These have been tested to work, but have not been extensively tested. Anything that happens IS NOT MY RESPONSIBILITY.

 

For Windows

========================

• Create a new file in any directory and name it autobackup.bat
• Put the following script in:

  @echo off
  set hr=%time:~0,2%
  if "%hr:~0,1%" equ " " set hr=0%hr:~1,1%
  SET backupTime=%date:~-4,4%-%date:~-10,2%-%date:~-7,2%_%hr%-%time:~3,2%
  cd "C:\path\to\mysql\bin"
  mysqldump.exe --user=MYSQLUSER --password=PASSWORDHERE --compact --replace --skip-lock-tables DATABASE character_data object_data player_data > "C:\path\to\backup\folder\%backupTime%.sql"

• Edit the script accordingly (directories, user, password, database)
• Set up a Windows service to run however many times you want per hour (5 minutes is ample) with the autobackup.sh to run

For Linux

========================

• Create a new file in any directory and name it autobackup.sh
• Put the following script in:

  #!/bin/bash
  timeout=300
  location="/media/games/backups/arma"
  cd $location;
  while true
  do
    file=$(date +"%H-%M")
    directory=$(date +"%m-%d-%Y")
    if [ ! -d "$directory" ]; then
      mkdir /home/arma/backups/$directory
    fi
    mysqldump --user=USERHERE --password=PASSWORDHERE --compact --replace --skip-lock-tables DATABASE instance_deployable instance_vehicle profile survivor > "$directory/$file.sql"
    echo "Backup Created at $location/$directory/$file.sql"
    sleep $timeout
  done

• Edit the script accordingly (directories, user, password, database)
• chmod +x autobackup.sh
• Set up a screen process or just run the script via command line, I'm sure if you're using Linux you wont need more detail

Screen

=======

• Install the package Screen (yum install screen, apt-get install screen, etc)
• Create dayzautobackup in your /usr/bin directory
• chmod +x /usr/bin/dayzautobackup
• Copypasta the following (and edit accordingly):

  #!/bin/sh
  
  file="/media/games/backups/arma/autobackup.sh"
  screen="dayzautobackup"
  
  #Determine whether or not screen is already running
  RUNNING=`screen -ls | grep $screen`
  
  case "$1" in
  'start')
      cd $LOCATION
      RUNNING=`screen -ls | grep $screen`
      if [ "$RUNNING" = "" ]
      then
          screen -dmS $screen $file
      fi
      ;;
  'stop')
      screen -x $screen -X quit
      ;;
  
  'restart')
      screen -x $screen -X quit
      RUNNING=`screen -ls | grep $screen`
      cd $LOCATION
      until [ "$RUNNING" = "" ]
      do
          RUNNING=`screen -ls | grep $screen`
      done
      screen -dmS $screen
      ;;
  
  'view')
      screen -x $screen
      ;;
  
  'sv')
      cd $LOCATION
      if [ "$RUNNING" = "" ]
      then
          screen -dmS $screen $file
      fi
      sleep 1
      screen -x $screen
      ;;  
  
  *)
      echo "Usage: $0 { start | stop | restart | view | sv (start & view) }"
      ;;
  esac
  exit 0

• Then simply run dayzautobackup from via command line and it will pop up with a usage dialog

[Radiix]

I'd just use the Windows Task Sheduler.

 

First of all: Create a .bat called "mysqlbackup.bat"

 

C&P this code:

@echo off
echo MySQL Backup wird gestartet...
cd C:\Program Files\MySQL\MySQL Server 5.6\bin
FOR /F "tokens=1,2,3 delims=/: " %%a in ('Time/T') do set CTIME=%%a%%b%%c
set ZEIT=%CTIME%
set DATUM=%date%
mysqldump -uroot -pYOURMYSQLPASSWORD YOUREPOCHDB_NAME > c:\mysqlbackup\dayzserver_%DATUM%_%ZEIT%.sql
echo Backup durchgeführt!

save it.

 

Go to C:\, create a folder called "mysqlbackup". This is the folder where all backed up databases will be stored, sorted by date and time that they have been created.

 

Now go to the windows task sheduler and create a new schedule. You can adjust WHEN the script will be started and HOW OFTEN. I've set up mine to run every 15 minutes, which is a good basic use. :)

 

I hope i could help you some way. :)

[BetterDeadThanZed]

Is there a solution for those of us renting a server from a host, since we can't set up batch files to run MySQL backups?

[fr1nk]

Is there a solution for those of us renting a server from a host, since we can't set up batch files to run MySQL backups?

 

Personally, I use Navicat for MySQL. The built-in scheduling feature is very simple and reliable. Backups can be restored with a click, or exported to a SQL file if you want. I'm guessing there's plenty of SQL clients out there capable of this, just depends on what you're able to get your hands on.

[BetterDeadThanZed]

Personally, I use Navicat for MySQL. The built-in scheduling feature is very simple and reliable. Backups can be restored with a click, or exported to a SQL file if you want. I'm guessing there's plenty of SQL clients out there capable of this, just depends on what you're able to get your hands on.

 

Keeping a program running, connected to my database, to do scheduled back up isn't exactly what I'm looking for. :(

[fr1nk]

Keeping a program running, connected to my database, to do scheduled back up isn't exactly what I'm looking for. :(

 

Yeah, I agree that it's not ideal. Until I (eventually) get my dedi box set up, this is what I'll need to go with. As a sidenote, my host does offer a scheduled SQL backup service, but there's a 50 file limit before older files get overwritten...that's actually plentiful for my needs, but I've found the service itself to be unreliable at time (it stops working at random times).

[haku]

http://dayzepoch.com...ackups/?p=11523

[BetterDeadThanZed]

http://dayzepoch.com...ackups/?p=11523

 

Doesn't that program cost like $200?

[fr1nk]

Non-commercial Navicat premium is $250...but unless you need connections to different databases like Oracle, Navicat for MySQL would be enough...it's cheaper but still a small investment (~$90)

[Kind-Sir]

I just use HeidiSQL for Windows and for Linux I usually access it via command line.

[PetuniaEpoch]

I just use HeidiSQL for Windows and for Linux I usually access it via command line.

 

Hey Kind-Sir... I love HeidiSQL, but don't think it's capable of scheduled backups, unless there's a plugin for it or something? - Please tell me if I'm wrong, as I'd love to use Heidi for schedules backups :D