[Release] MySQL DB backup v1.1.

[RimBlock]

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.

 

[calamity]

should this work with Vilayer ??

[Gr8]

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.

[calamity]

they have a manual backup but not an autobackup :(

[RimBlock]

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.

[itsatrap]

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"

[calamity]

thankz these posts led to my autobackup now set up

[RimBlock]

A slightly updated version which takes in to account 0x.xx times being reported with the initial 0 missing has now been included in the Epoch mod Git code.

[Raoul]

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.

[RimBlock]

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).

[RimBlock]

Note: This script is included with the Epoch 1.0.5.2. release when it comes out.

[robbiedarza]

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

[RimBlock]

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.

[Tomodan]

remove

[Pro_Speedy]

Does this require any special permissions as get this error?

 

"Access Denied for user when using Lock tables"

 

Thanks

[Pro_Speedy]

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

[poweredbypot]

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.

[RimBlock]

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.

[raymix]

*subbing*

[RimBlock]

 

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.

[poweredbypot]

 

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).

[poweredbypot]

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"

[RimBlock]

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.

[poweredbypot]

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?

[Thug]

Think i will try this. :)