Jump to content

Recommended Posts

MySQL DB backup v1.1.

 

Note: This script is included in the DayZEpoch 1.0.6 distribution (when released).

 

What is it.

 

This is a Windows batch file (.bat) which will backup your MYSql database(s) for you.  If you add it to Windows scheduler it can also run automatically at a frequency you define (every 15 minutes for example).

 

Features.

 

- Backup tables, triggers and events.

- Housekeeps old backups after user defined number of days.

- Resulting .sql files can be loaded in to a SQL client and run against a MYSQL database to recreate the structure and data from the backed up database.

- Server DateTime format independant (works for MM/DD/YYYY or DD/MM/YYYY date formats).

- Can be scheduled with Windows scheduler.

 

How to use it.

 

Create a .bat file (call it something like DB_Backup.bat) and paste the following in to it.

@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"
 
Configuration
 
Input your DB connection details and desired backup save location.  All details that need changing are in []. Make the changes without the []. 
 
e.g.
SET mysqlschema=[Your DB schema name]
may change to 
SET mysqlschema=EpochDB
 
SET BackupDir="[Where you want to save the backups]"
May change to 
SET BackupDir="c:\DB-Backups"
 
Housekeeping will clear any .sql files that are over housekeepafter number of days old.
 
You may need to confirm the MySQL path for the mysqldump exe as it may depend on your provider.
 
Add "read" (without "") at the end of the file to require an input for debugging.  This allows you to check the test backup is ok and troubleshoot any issues.  Remove it when automating or the .bat file will never close.  Automation can be done via Windows task scheduler.  Create a basic task and then edit the tasks parameters after creating if you want to backup more than once a day.  The option is only available after the task is created.

 

Revision History.

 

Current version: v1.1

 

Changes:

 - Amended code to cope with US & UK date formats when calculating the resulting .sql files datetimestamp included in the filename.

 

Previous versions:

v1.0: Initial version.

 

Possible future improvements.

 

I may add an option to compress (zip) the files if there is enough interest.

 

Share this post


Link to post
Share on other sites

Most Rented (non dedi) server have database backup feature from thier host.

 

This method only applies for dedicated Machines. It backs up db whenever you start up your server.

Share this post


Link to post
Share on other sites

It will backup a MySQL DB when it is run.  You can use MS Task Scheduler to have it backup automatically whenever you wish.

 

It is for people with dedicated servers without any hosting provider supplied backup.

Share this post


Link to post
Share on other sites

Try use this.

 

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

 

change 

%mysqldumpexe% --user=%dbuser% --password=%dbpass% --databases --routines --log-error=%errorLogPath% %%F > "%backupfldr%%%F.%backuptime%.sql"

to

%mysqldumpexe% --no-defaults --login-path=local --routines --events --triggers --quick --add-drop-database --log-error=%errorLogPath% --databases %%F > "%backupfldr%%%F.%backuptime%.sql"

Share this post


Link to post
Share on other sites

Can't seem to get this to work... It gives me this error

 

ERROR: No files found with the specified search criteria.

My .bat file looks like this:

 

@REM *** PARAMETERS/VARIABLES ***
SET BackupDir="C:\xampp\mysql\bin"
SET mysqldir="C:\xampp\mysql\data"
SET mysqlschema=epoch_database
SET mysqlpassword=PASSWORD WAS SET HERE
SET mysqluser=USERNAME WAS SET EHRE
SET housekeepafter=5
SET datestamp=%date:~-10,2%-%date:~-7,2%-%date:~-4,4%_%time:~0,2%.%time:~3,2%.%time:~6,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% >%BackupDir%\%mysqlschema%_backup.%datestamp%.sql
 
@REM - Housekeeping
forfiles -p %BackupDir% -s -m *.sql -d -%housekeepafter% -c "cmd /c del @path"
 
What did I do wrong?
I am using PHPMyAdmin with MySQL via XAMPP.

Share this post


Link to post
Share on other sites

It is probably working fine.  The housekeeping side is looking to clear out files that are older than "housekeepafter" and none yet exist so it reports no files found.  Just make sure there are files being created and that they have sql commands inside (ther will be the sql to completely recreated the DB Instance you have requested backup of).

Share this post


Link to post
Share on other sites

Hello, when I double click the backup bat, it saved a an sql back file but its empty!!

@REM *** PARAMETERS/VARIABLES ***
SET BackupDir="C:\Users\Administrator\Documents\backups\backup_saves"
SET mysqldir="C:\xampp\mysql\data"
SET mysqlschema=Epoch_Database
SET mysqlpassword=loulou123
SET mysqluser=Epoch_User
SET housekeepafter=5
SET datestamp=%date:~-10,2%-%date:~-7,2%-%date:~-4,4%_%time:~0,2%.%time:~3,2%.%time:~6,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% >%BackupDir%\%mysqlschema%_backup.%datestamp%.sql

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

this is my bat file

 

Cheers

 

 

Robbie

Share this post


Link to post
Share on other sites

add "read" (without the "") at the end of the file then run it manually.  It should pause at the end so you can see any error messages.  Press any key to close it.

 

That should give you a better idea of what is going wrong. 

 

COuld be a permissions issue fo the account you are using or a number of other things.

Share this post


Link to post
Share on other sites

hey Rim,

 

     I have been trying to get this to work for about an hour now and I can't seem to figure out whats wrong. My server and SQL DB are both on my "B" drive, but for the sake of space I have the backups saved on the "C" drive. When I run the batch everything appears to be ok, the files are created where they are supposed to be but they are completely empty. When i add "Pause" to the bottom of the batch to read what it says all I see is

 

"mysqldump: unknown option '--no-beep'"

 

and then the error about no files found with search criteria which I know is normal when the backups are still new.

Share this post


Link to post
Share on other sites

Does this require any special permissions as get this error?

 

"Access Denied for user when using Lock tables"

 

Thanks

 

 

Doesn't seem to work creats a file with no .sql at the end plus the file ony has a few entries for each table

 

If the chosen user does not have access to lock tables then it will not be able to lock the tables in order to dump the contents without it being changed whilst the dump is in progress.  Give the backup user lock permissions.  I use a specific backup user profile (backupadmin) for the account that does the dumps.  It is pre-defined in MySQL Workbench.

 

hey Rim,

 

     I have been trying to get this to work for about an hour now and I can't seem to figure out whats wrong. My server and SQL DB are both on my "B" drive, but for the sake of space I have the backups saved on the "C" drive. When I run the batch everything appears to be ok, the files are created where they are supposed to be but they are completely empty. When i add "Pause" to the bottom of the batch to read what it says all I see is

 

"mysqldump: unknown option '--no-beep'"

 

and then the error about no files found with search criteria which I know is normal when the backups are still new.

 

What is the mysqldump line you are running in the .bat file.  My code above does not include a -no-beep option.

 

Yep, you are correct about the no files found message.  Just means there are no files to be house kept as none are old enough yet.

Share this post


Link to post
Share on other sites

 

Try use this.

 

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

 

change 

%mysqldumpexe% --user=%dbuser% --password=%dbpass% --databases --routines --log-error=%errorLogPath% %%F > "%backupfldr%%%F.%backuptime%.sql"

to

%mysqldumpexe% --no-defaults --login-path=local --routines --events --triggers --quick --add-drop-database --log-error=%errorLogPath% --databases %%F > "%backupfldr%%%F.%backuptime%.sql"

 

Thanks, apologies for missing these extra options.

 

--events : Dumps out DB events (db Epoch housekeeping events for example).

--triggers : Dumps out any triggers which are fired id a row in a table changes depending ont he trigger definition (non used for vanilla Epoch).

--quick : Dumps out a row at a time rather than pooling in the MySQL server ram and then writing in one go.

 

One other option that may be of use to some is 

 

 --single-transaction : Does the dump in a single transaction (sets a begin tran) negating the need to lock tables (no user lock privilege required for DB backup user).

 

The one I have deliberately left out is --add-drop-database as this will cause the original table to be dropped from the DB (if it already exists) when the backup sql file is reloaded in to the DB.  The reason I have left it out is that it is usually better to load the backup in to a staging DB so the data can be checked before moving it over to the prod DB.  If anyone would prefer just to be able to load straight over the top of the existing data in their DB then you can add this option.

 

Original code amended on my Git and a pull request has been raised for the Epoch base code to absorb.

Share this post


Link to post
Share on other sites

 

What is the mysqldump line you are running in the .bat file.  My code above does not include a -no-beep option.

 

Yep, you are correct about the no files found message.  Just means there are no files to be house kept as none are old enough yet.

Yeah I know it's a weird error, that's why I thought I would ask. I haven't modified the code you provided at all (excluding the paths and login info).

Share this post


Link to post
Share on other sites

Well here is the code as a whole:

 

@REM *** PARAMETERS/VARIABLES ***
SET BackupDir="C:\DSTC\DB Backups"
SET mysqldir="B:\MySQL\MySQL Server 5.6\bin"
SET mysqlschema=[******]
SET mysqlpassword=[****]
SET mysqluser=[****]
SET housekeepafter=5
SET datestamp=%date:~-10,2%-%date:~-7,2%-%date:~-4,4%_%time:~0,2%.%time:~3,2%.%time:~6,2%
@REM *** EXECUTION ***
@REM Change to mysqldir
B:
cd %mysqldir%

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

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

Share this post


Link to post
Share on other sites

Clearly no -no-beep option in the msqldump line.

 

Are you using a hosting provider as they may be running a backup for you are well and you may be picking up their error.  GIve a check to your Windows scheduler to make sure you do not have another copy running from the distant past or something.  The bat file you posted will not be the cause.

Share this post


Link to post
Share on other sites

Clearly no -no-beep option in the msqldump line.

 

Are you using a hosting provider as they may be running a backup for you are well and you may be picking up their error.  GIve a check to your Windows scheduler to make sure you do not have another copy running from the distant past or something.  The bat file you posted will not be the cause.

 

Damn, thanks man. I am not using any host it's all here on my home box. I did notice you're original post seems to have changed a bit, is an update reccomended?

 

 

####UPDATE#####

SO I did some extensive googling and although I never found a specific case of my error I did find many similar which led me to look in "MySQL Server\my.ini", lo and behold right there was no-beep right in the first line after the [client] class.

 

Weird Huh?

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

  • Similar Content

    • By FG_Trap
      Made this in my free time awhile ago thought id make it public.
      Right Alt is the key to open the menu.
      Install instructions are inside the download.
      https://github.com/NWDZ-Trapxxgods/traps-menu
       
       
      here is a picture of the server menu
       
    • By lordacus
      so i have been making a server between trying to get my provider to update their mods section. after getting this don i found the server with epoch and infistar were conflicting each other such as: when joining i can use infistar but when i press f9 or i to enter my inventory or f10 for the mobile pad it then conflicts (i think) infistar and i can no longer use it. any ideas on how to stop this
       
    • By vbawol
      Open world survival mod set just two years after the mass extinction of billions of people. Those that remain are left with remnants of a once technological society. Try to survive, build, or explore your way through the harsh dynamic environment.
      Features
      Powerful persistence framework for Arma 3 with native Windows and Linux dedicated server support. Fully configurable script based security checks. Base Building re-envisioned for Arma 3 PhysX. Persistent secure and insecure storage devices. Upgradeable locking doors. Barter based economy and trading systems backed a single currency ¤ called "Krypto". Hostile Environment with several different types of antagonists, Air and Water temperature extremes, and Earthquakes. Hunting, Fishing, Tame Dogs, Explore & Loot: Wrecked ships, Abandoned vehicles and buildings, Perform task based missions and more! Open source APL-SA Epoch Survival MP gamemode. Open source APL-SA Modders resource configs and Class List. Assets can be used as a mod dependency on Steam workshop.
    • By Achef
      Greetings!
      So I recently started up in a new host and haven't gotten to make the server run, found that a ca/modules bis_fnc issue is recurring but doesn't affect players when running coop missions like the default arma 2 oa ones, but when trying with Epoch hive won't connect/start despite being configured.
      Hive error
      Meanwhile at line 23...
       
      I'm still bad with Pastebin, sorry.
       
      This error has been chasing me for 2 days now
    • By WeepiestLem0n
      So I've been wanting to create a private ArmA 2 DayZ Epoch server for a bunch of my mates to play on, however I dont have a dedicated machine and want to run it on my PC. So I looked at the server download files on the GitHub page and followed the ReadMe, however I absolutely cannot figure out how to get the MySQL server and DB set up. I have downloaded MySQL Server on my PC and created the admin role, root passwords and ran it as a windows service. I now have no clue how to create the database and am wondering if anyone can help me set this up or let me know if I've done anything wrong. 

      I can add on Steam or Discord if need be for convenience.
  • Advertisement
  • Supporters
  • Discord

×
×
  • Create New...