[Release] MySQL DB backup v1.1.

[RC_Robio]

Go to your Regional and Language Options in your windows contol panel and set it to Enlgish (United States) and try your .bat again.

[ReDBaroN]

:lol:   ....sanity reigns....It worked!

 

Thanks guys, really appreciate your hard work and help :)

[RimBlock]

Ok, so it is the difference between UK date format and US date format.

 

We should be able to account for that in the bat file itself without needing to change the time format on the server although changing the regional settings to US time format will resolve the issue as a short term fix.

 

Let me take a look at the code again. 

[ReDBaroN]

can't believe that I'm the only UK dedicated that's using back ups...  :unsure:

 

That would be great though Rimblock if it compensated for that before other folks run into probs when you get dished out with 1052. Congrats on that btw ... another Rimblock mod promoted to the core :)

[RC_Robio]

Glad it worked out. Until RimBlock 

 

For UK

%DATE:~10,4%%DATE:~7,2%%DATE:~4,2%

[RimBlock]

Thanks.

 

Ok, the code below should sort you out regardless of datetime format.  I am not a batch file programmer by any means but after a quick google and play this seems to work.  Give it a test and let me know.

@REM *** PARAMETERS/VARIABLES ***
SET BackupDir="[Where you want to save the backups]"
SET mysqldir="C:\Program Files\MySQL\MySQL Server 5.6\bin"
SET mysqlschema=[Your DB schema name]
SET mysqlpassword=[your DB password]
SET mysqluser=[your DB user login]
SET housekeepafter=5

for /F "usebackq tokens=1,2 delims==" %%i in (`wmic os get LocalDateTime /VALUE 2^>NUL`) do if '.%%i.'=='.LocalDateTime.' SET ldt=%%j
set datestamp=%ldt:~0,4%-%ldt:~4,2%-%ldt:~6,2%.%ldt:~8,2%-%ldt:~10,2%-%ldt:~12,2%

@REM *** EXECUTION ***@REM Change to mysqldir
c:
cd %mysqldir%

@REM dump/backup ALL database, this is all in one line
mysqldump -u %mysqluser% -p%mysqlpassword% --databases %mysqlschema% --routines --events --triggers --quick >%BackupDir%\%mysqlschema%_backup.%datestamp%.sql

@REM - Housekeeping
forfiles -p %BackupDir% -s -m *.sql -d -%housekeepafter% -c "cmd /c del @path"

The forum code block did a good attempt at messing up all the lines but I think I have got them all straightened out.

 

If both of you have a chance to sest and it all goes well then I will push it to the Epoch core files.

 

Thanks.

[RC_Robio]

Confirmed working for both UK and US time formats. 

 

EDit: Noticed that it keeps overwriting the same .sql file

[ReDBaroN]

Confirmed working for both UK and US time formats. 

 

EDit: Noticed that it keeps overwriting the same .sql file

Well, this isn't working for me...well, not as expected anyway...

 

Using both regional settings it now creates the file OK but doesn't put the date in the filename, just .. So, then it overwrites itself...

 

EDIT: Sorry Rc_Robio, just seen your edit too... :)

 

So, yes can confirm the same result

[RimBlock]

Try adding a read at the end and pasting up the result of the run.

At work so cannot test (well on a firedrill). Seemed ok on a desktop but there may be more restrictions on a server.

[ReDBaroN]

sure, here is the output:

 

C:\Windows\system32>SET BackupDir="D:\Backup_DB"


C:\Windows\system32>SET mysqldir="C:\xampp\mysql\bin"


C:\Windows\system32>SET mysqlschema=Epoch_Database


C:\Windows\system32>SET mysqlpassword=xxxxxx


C:\Windows\system32>SET mysqluser=db_backup


C:\Windows\system32>SET housekeepafter=5


C:\Windows\system32>for /F "usebackq tokens=1,2 delims==" %i in (`wmic os get Lo
calDateTime /VALUE 2>NUL`) do if '.%i.' == '.LocalDateTime.' SET ldt=%jset


.' == '.LocalDateTime.' SET ldt=set


.' == '.LocalDateTime.' SET ldt=set


C:\Windows\system32>if '.LocalDateTime.' == '.LocalDateTime.' SET ldt=2014102702
set  051000+060


.' == '.LocalDateTime.' SET ldt=set


.' == '.LocalDateTime.' SET ldt=set


.' == '.LocalDateTime.' SET ldt=set


C:\Windows\system32>datestamp=2014-10-27.02-12-00
'datestamp' is not recognized as an internal or external command,
operable program or batch file.


C:\Windows\system32>c:


C:\Windows\system32>cd "C:\xampp\mysql\bin"


C:\xampp\mysql\bin>mysqldump -u db_backup -pxxxxxx --databases Epoch_Database --
routines --events --triggers --quick  1>"D:\Backup_DB"\Epoch_Database_backup..sq
l
Warning: Using a password on the command line interface can be insecure.


C:\xampp\mysql\bin>forfiles -p "D:\Backup_DB" -s -m *.sql -d -5 -c "cmd /c del @
path"
ERROR: No files found with the specified search criteria.


C:\xampp\mysql\bin>pause
Press any key to continue . . .

[RC_Robio]

Forgot the Set in front of datestamp?

 

This works. for both time/date formats.

 

for /F "usebackq tokens=1,2 delims==" %%i in (`wmic os get LocalDateTime /VALUE 2^>NUL`) do if '.%%i.'=='.LocalDateTime.' SET ldt=%%jset 

set datestamp=%ldt:~0,4%-%ldt:~4,2%-%ldt:~6,2%.%ldt:~8,2%-%ldt:~10,2%-%ldt:~12,2%

 

@REM *** EXECUTION ***@REM Change to mysqldir

c:

cd %mysqldir%

 

@REM dump/backup ALL database, this is all in one line

mysqldump -u %mysqluser% -p%mysqlpassword% --databases %mysqlschema% --routines --events --triggers --quick >%BackupDir%\%mysqlschema%_backup.%datestamp%.sql

 

@REM - Housekeeping

forfiles -p %BackupDir% -s -m *.sql -d -%housekeepafter% -c "cmd /c del @path" read

 

@REM - Housekeeping

forfiles -p %BackupDir% -s -m *.sql -d -%housekeepafter% -c "cmd /c del @path"

[RimBlock]

Ok,

 

An issue with copying it to the forums code box.

 

change

for /F "usebackq tokens=1,2 delims==" %%i in (`wmic os get LocalDateTime /VALUE 2^>NUL`) do if '.%%i.'=='.LocalDateTime.' SET ldt=%%jset
datestamp=%ldt:~0,4%-%ldt:~4,2%-%ldt:~6,2%.%ldt:~8,2%-%ldt:~10,2%-%ldt:~12,2%
 

to

for /F "usebackq tokens=1,2 delims==" %%i in (`wmic os get LocalDateTime /VALUE 2^>NUL`) do if '.%%i.'=='.LocalDateTime.' SET ldt=%%j
set datestamp=%ldt:~0,4%-%ldt:~4,2%-%ldt:~6,2%.%ldt:~8,2%-%ldt:~10,2%-%ldt:~12,2%
 

Edit: have updated it in post 56 above as well.

[Jey]

Hello using as well a simple bat script with windows automated task to make a backup every 15 minuteS.

 

However I wanted to know if someone does know how to add extra lines to compress through a zip or rar compression the sql file.

 

Would lower the disk usage.

[ReDBaroN]

That's great guys and working perfectly.. :D

 

Just a quick n00b question if you don't mind before we sign this off.....am I right in thinking that, when needed, I would just drag and drop the sql file into the query box in heidi or workbench, hit go and it restores on it's own from there..? Or, is there more to it when restoring? 

 

Sorry, but new to this non managed/dedicated world

[RimBlock]

The resulting .sql file will rebuild all the DB tables, triggers and events for the databases (schemas) you have chosen to backup.

 

If you open the file in your SQL editor and change the name of the DB at the top (use [DBNAME] line) then you could remake a copy fo your live DB in a new DB for comparing data and checking things without the need to overwrite the live data.

 

I suspect (but would need to check) that the resulting .sql backup file does not drop tables etc if they already exist.  Have a quick check to see if there are any "if exists then drop table [tablename]" type lines.  I know I was thinking of deliberately leaving this option out as it is very easy to accidently load a sql dump over the top of your prod database by mistake (i.e. someone forgot to change the 'use [dbname] line' as I have before - luckily only on a test system).

 

To add in the drop database command you can add the "--add-drop-database" parameter to the mysqldump line (after "--quick " should work) in the .bat file.  This will drop the whole DB and recreate it when you run the resulting SQL in the backup SQL file (the actual backup will only dump out the contents of the DB so no risk there).

[RC_Robio]

Yep, just dump the file in the query and run it.

[RC_Robio]

DROP TABLE IF EXISTS `character_data`; <-- It in the sql.

 

I just restored a backup from a dump and it created the database correctly

 

Edit. I actually have the same database under a different name and they or both identical after the restore.

[ReDBaroN]

Nice :D

 

Probably save that test to later today now as 3.30am for me.... :/

 

Thanks again for your help guys

[RC_Robio]

Another helpful tip is to save a backup to a dropbox or other online storage to have easy access on your net connected devices. I hate having to RDP!!

[RimBlock]

Hello using as well a simple bat script with windows automated task to make a backup every 15 minuteS.

 

However I wanted to know if someone does know how to add extra lines to compress through a zip or rar compression the sql file.

 

Would lower the disk usage.

 

If you are getting large files then you may want to sort it out by putting some robust housekeeping on the DB tables.  Cleaning out character_data and other character / player tables can make a big difference.  Trimming down the object_data table will also have a big impact if you are running with a large number of objects.

 

To compress the resulting .sql file you could try the zip command which should be available on Windows servers (probably ;) ).

 

You could try changing

mysqldump -u %mysqluser% -p%mysqlpassword% --databases %mysqlschema% --routines --events --triggers --quick >%BackupDir%\%mysqlschema%_backup.%datestamp%.sql

 

to

mysqldump -u %mysqluser% -p%mysqlpassword% --databases %mysqlschema% --routines --events --triggers --quick | zip >%BackupDir%\%mysqlschema%_backup.%datestamp%.sql.zip

 

This has not been tested by me so please test before putting live.

 

To extract the files you sould be able to do

zip e [filename]

[ReDBaroN]

Another helpful tip is to save a backup to a dropbox or other online storage to have easy access on your net connected devices. I hate having to RDP!!

So you can then use phpMyadmin locally....ok, will test that out too.

 

Cheers :D

[RimBlock]

Reformatted and updated the first post (inc most recent code change from testing above).

[Shadowking74]

I have my own custom backup.bat which runs fine if ran manually(not through task scheduler) or through BEC(but BEC lately has not been starting like normal), when the task scheduler run the backup.bat it makes a sql file but its empty, if I run the .bat file myself its fine

 

Any ideas?

[js2k6]

@echo off

for /f "tokens=1-7 delims=:/-, " %%i in ('echo exit^|cmd /q /k"prompt $d $t"') do (
   for /f "tokens=2-4 delims=/-,() skip=1" %%a in ('echo.^|date') do (
      set dow=%%i
      set %%a=%%j
      set %%b=%%k
      set %%c=%%l
      set hh=%%m
      set min=%%n
      set ss=%%o
   )
)

set dumppath="C:\DayZDB"

echo %dow% %yy%-%mm%-%dd% @ %hh%:%min%:%ss%

cd /d %dumppath%
start "" "7za" a -mx9 %yy%-%mm%-%dd%-OverpochDB.7z *.sql

timeout 180
del *.sql

timeout 30
cls
@exit

further to an sql dump script. this is a batch file i run once every day. (just run it using windows scheduler)

it requires 7zip, but it compresses all the sql files from the day into a datestamped.7z archive.

then it deletes all the sql files.

 

http://www.dotnetperls.com/7-zip-examples

basically it just compresses roughly 70mb of data (4 servers, each sql dump every 30 mins) into a 1-2mb 7z file..

 

after that is complete. i have a seperate batch file which then moves the files from C:\dayzdb to my google drive folder so it then syncs on my home pc, my dedi box and of course my google drive

 

i've been the victim of hard drive failure on the server box once. if i'm that unlucky again.. i'll be prepared next time

[ReDBaroN]

@echo off

for /f "tokens=1-7 delims=:/-, " %%i in ('echo exit^|cmd /q /k"prompt $d $t"') do (
   for /f "tokens=2-4 delims=/-,() skip=1" %%a in ('echo.^|date') do (
      set dow=%%i
      set %%a=%%j
      set %%b=%%k
      set %%c=%%l
      set hh=%%m
      set min=%%n
      set ss=%%o
   )
)

set dumppath="C:\DayZDB"

echo %dow% %yy%-%mm%-%dd% @ %hh%:%min%:%ss%

cd /d %dumppath%
start "" "7za" a -mx9 %yy%-%mm%-%dd%-OverpochDB.7z *.sql

timeout 180
del *.sql

timeout 30
cls
@exit

further to an sql dump script. this is a batch file i run once every day. (just run it using windows scheduler)

it requires 7zip, but it compresses all the sql files from the day into a datestamped.7z archive.

then it deletes all the sql files.

 

http://www.dotnetperls.com/7-zip-examples

basically it just compresses roughly 70mb of data (4 servers, each sql dump every 30 mins) into a 1-2mb 7z file..

 

after that is complete. i have a seperate batch file which then moves the files from C:\dayzdb to my google drive folder so it then syncs on my home pc, my dedi box and of course my google drive

 

i've been the victim of hard drive failure on the server box once. if i'm that unlucky again.. i'll be prepared next time

 

Hey, thanks for this :)

 

Having a slight problem again though with my regional settings. When I run this above I get this as the 7zip folder name:

22-2015-01

 

Can you tell me what to change to get it to reflect the right date with UK regional settings?

 

Thanks