Rolling Backups

[Mateo]

I am looking for the best way to keep a rolling backup. I don't have any real experience doing database administration so writing my own SQL code is pretty much beyond my scope. Does anyone have an adaptable system that I can use for my DB - something that I can restore from rather easily. How can I setup an every 15 minute rolling backup that overwrites itself after two hours or so. A self contained sql file that I can import using workbench would be dandy. Can anyone assist?

 

[kikyou2]

I write how I solved this issue.

I use this batch script:

http://www.redolivedesign.com/utah-web-designers-blog/2011/05/23/automated-mysql-backup-for-windows/

You can choose when the files should be deleted and it zips the files themselves. Also you can use FTP upload if you want so.

Then I used the Windows Task Planer to schedule running this script every x time (my script ist backuping every minute for example and deletes every 48 hours old files). Because it was annoying that the batch script opened always I use a vbs script which opens the batch file (simple targeted the .vbs script at the Windows Task Planer).

VBS Script:
Set WshShell = WScript.CreateObject("WScript.
Prog = "Directory of Batch File"
WshShell.Run """" & Prog & """",7

Hope it helps.

Regards kikyou2

Edit: I also use Dropbox to automate uploading the backup, that it is backuped twice

 

[joikd]

Thanks for that link.

 

[Quarterbreed]

I write how I solved this issue.

I use this batch script:

http://www.redolivedesign.com/utah-web-designers-blog/2011/05/23/automated-mysql-backup-for-windows/

You can choose when the files should be deleted and it zips the files themselves. Also you can use FTP upload if you want so.

Then I used the Windows Task Planer to schedule running this script every x time (my script ist backuping every minute for example and deletes every 48 hours old files). Because it was annoying that the batch script opened always I use a vbs script which opens the batch file (simple targeted the .vbs script at the Windows Task Planer).

VBS Script:

[SIZE=13px][FONT=arial][COLOR=#222222]Set WshShell = WScript.CreateObject("WScript.[/COLOR][/FONT][/SIZE][SIZE=13px][FONT=arial][COLOR=#222222]Shell")[/COLOR][/FONT][/SIZE]
[SIZE=13px][FONT=arial][COLOR=#222222]Prog = "Directory of Batch File[/COLOR][/FONT][/SIZE][SIZE=13px][FONT=arial][COLOR=#222222]"[/COLOR][/FONT][/SIZE]
[SIZE=13px][FONT=arial][COLOR=#222222]WshShell.Run """" & Prog & """",7[/COLOR][/FONT][/SIZE]

Hope it helps.

Regards kikyou2

Edit: I also use Dropbox to automate uploading the backup, that it is backuped twice

What happens if a hacker teles all vehicles to a spot does it still back it up?

 

[Hatsjoe]

What happens if a hacker teles all vehicles to a spot does it still back it up?

When a hacker messes around on your server, i.e. killing everyone, teleporting everyone, destroying/teleporting vehicles, that will be updated in the database (players state, dead or alive, locations, vehicles states) and thus it will be backed up. But since you should not override your previous backup (NEVER override previous backups with new ones) you can always restore the state the server was in before the hacker came.

Personally I'd backup like every 15 or 30 minutes or so, keep those backups for 48 hours (if you got enough backup space) and keep 1 backup of every day for 30 days, just in case something went unnoticed initially. That way, you have a backup of every 15 or 30 minutes for the past 48 hours, and before that you have 1 backup a day for the past 30 days.

 

[thevisad]

I use this batch file to backup on a hourly basis to an external backup location. It used windows task scheduler to handle the timer. Requirement for it is WinRar.

@echo off
 
REM "Create a time signature for the backup"
SET backupTime=%date:~7,2%%date:~4,2%%date:~10,4%_%time:~0,2%%time:~3,2%%time:~6,2%
 
REM "Dump the database to the file."
"C:\Program Files (x86)\MySQL\MySQL Server 5.5\bin\mysqldump" --user=whatever --password=Whatever celle > "Z:\dayz\celle_%backupTime%.sql"
"C:\Program Files (x86)\MySQL\MySQL Server 5.5\bin\mysqldump" --user=whatever --password=Whatever dayz > "Z:\dayz\dayz_%backupTime%.sql"
"C:\Program Files (x86)\MySQL\MySQL Server 5.5\bin\mysqldump" --user=whatever --password=Whatever dayzlingor > "Z:\dayz\dayzlingor_%backupTime%.sql"
"C:\Program Files (x86)\MySQL\MySQL Server 5.5\bin\mysqldump" --user=whatever --password=Whatever fallujah > "Z:\dayz\fallujah_%backupTime%.sql"
"C:\Program Files (x86)\MySQL\MySQL Server 5.5\bin\mysqldump" --user=whatever --password=Whatever namalsk > "Z:\dayz\namalsk_%backupTime%.sql"
"C:\Program Files (x86)\MySQL\MySQL Server 5.5\bin\mysqldump" --user=whatever --password=Whatever panthera > "Z:\dayz\panthera_%backupTime%.sql"
"C:\Program Files (x86)\MySQL\MySQL Server 5.5\bin\mysqldump" --user=whatever --password=Whatever takistan > "Z:\dayz\takistan_%backupTime%.sql"
"C:\Program Files (x86)\MySQL\MySQL Server 5.5\bin\mysqldump" --user=whatever --password=Whatever tavi > "Z:\dayz\tavi_%backupTime%.sql"
"C:\Program Files (x86)\MySQL\MySQL Server 5.5\bin\mysqldump" --user=whatever --password=Whatever utes > "Z:\dayz\utes_%backupTime%.sql"
"C:\Program Files (x86)\MySQL\MySQL Server 5.5\bin\mysqldump" --user=whatever --password=Whatever zargabad > "Z:\dayz\zargabad_%backupTime%.sql"
 
 
"C:\Program Files\WinRAR\rar" a "Z:\dayz\sql_backup_%backupTime%.rar" Z:\dayz\*.sql
erase /f Z:\dayz\*.sql

 

[zedar]

i have this shit and will improve with the rar like thevisad now im seeing it (my db has 30mb now)

scheduler (must be started with the server) makes a backup every 60minutes

echo OFF
:mysql_backup
cls
echo db BACKUP CADA 60MINUTOS - no cerrar - no cerrar -  no cerrar !!!!
set SAVESTAMP=%DATE:/=-%@%TIME::=-%
set SAVESTAMP=%SAVESTAMP: =%.sql
server_setup\xampp\mysql\bin\mysqldump.exe -u dayz -mypass --port 3306 --databases dayz > C:\backups\bd\dayz_cron_%SAVESTAMP%
PING -n 3600 127.0.0.1>nul
goto mysql_backup

 

[thevisad]

i have this shit and will improve with the rar like thevisad now im seeing it (my db has 30mb now)

scheduler (must be started with the server) makes a backup every 60minutes

echo OFF
:mysql_backup
cls
echo db BACKUP CADA 60MINUTOS - no cerrar - no cerrar -  no cerrar !!!!
set SAVESTAMP=%DATE:/=-%@%TIME::=-%
set SAVESTAMP=%SAVESTAMP: =%.sql
server_setup\xampp\mysql\bin\mysqldump.exe -u dayz -mypass --port 3306 --databases dayz > C:\backups\bd\dayz_cron_%SAVESTAMP%
PING -n 3600 127.0.0.1>nul
goto mysql_backup

If you use task scheduler that is built into windows you can have it start and repeat every 1 hour. Then you are not wasting CPU cycles on the ping command. It starts the script automatically every 1 hour and then exits.

 

[zedar]

good catch thevisad , thanks

 

[Doc]

I scheduled a cron job on my web server which executes a php script that connects remotely to my game server, creates a dump file and archives it on the web server. It executes every 30 minutes.

 

[linkinx]

I use Chive to explore the DB, and to back it up I use mysqldumper, both are web based tools.

 

[Shibdib]

I use MySQL Administrator to save the backups. (I'm on a managed server so my DB is remote, and this works like a charm, has a backup scheduler feature)

And then this .bat scheduled on windows to run nightly to delete whatever ones are older than 3 days.

forfiles -p "D:\Dayz Backups" -s -m *.* -d 3 -c "cmd /c del @path"
//
exit

 

[dave_beastttt]

I use Navicat full version which has in built schedules and backs up each of my 3 DayZ server db's every 10 minutes, rollback is as easy as selecting the backup you want and click it.