Hello
I would like to backup mysql database through Duplicati, so i searched here and from what i understand the correct way will be to run a script to make a dump, before the task of backup the dump. Am I correct?
So, Im not an advanced user and honestly i dont understand what i have to do to make it work. Is it possible for someone to explain the correct steps and provide a script to make it work? I run Windows, so i assume it will be a bat file.
Thanks in advance.
I would start by getting a good understanding of MySQL and how to do dumps. You should test this and do test restores even before you get Duplicati involved:
https://dev.mysql.com/doc/refman/5.7/en/using-mysqldump.html
Once you are confident the dumps are working and you have them scheduled, it is then trivial to set up Duplicati to back up your dump folder.
There may be a MySQL expert that can help provide further guidance.
Good luck and welcome to the forum!
I have some knowledge of mysql, i just made a bat script and it works if i run it, it dumps the database into a folder, just as i want.
But i am not able to make this bat work through Duplicati, in the advanced options i put --run-script-before=C:\mysql.bat , when i run the task it shows the following error: [Warning-Duplicati. Library.Modules.Builtin.RunScript-ScriptExecuteError]: Error while executing script “C:\mysql.bat”: The system was unable to locate the file specified.
I don’t know what im doing wrong.
Can you post the contents of your batch file?
Hi,
The content is:
@ECHO
mysqldump -uroot -hlocalhost -pxxx database1 > C:\backup_mysql\database1_%DATE%.sql
EXIT
It just works when i execute, dump database on location.
Can you put a fully qualified path for mysqldump? Maybe the user that Duplicati runs as doesn’t have the same search path as you.
If there are spaces you should put quotes around it. Something like this:
"C:\Path To MySQL\mysqldump.exe" -uroot -hlocalhost ........
I have made this, and same thing. My duplicati is running over service.
What exactly do you execute? That .bat file? The mysqldump part before redirection? The whole line?
What does your %DATE% contain? Mine has a space and forward slashes, and that breaks the redirect.
cmd.exe interprets forward slashes as folder separators, and they’re not allowed in filenames anyway.
I execute the line below in the bat file or simply in cmd and it works:
mysqldump -uroot -hlocalhost -pxxx database1 > C:\backup_mysql\database1_%DATE%.sql
The output is:
database1_07-05-2020.sql
In my case it puts “-” on date, no spaces exists.
Hi,
Right now i removed the %DATA% and it just works executing on Duplicati. So i suppose Duplicati could’t interpret the variable %DATE%. Any way to solve this?
Many thanks.
Duplicati never interprets such variables. If done at all, it’s done by whatever it runs, e.g. cmd.exe.
Running a test, using Windows 10 Settings to change Regional format in Region settings, suggests that it’s per-user, as the user doing the change changed, and another user on PC didn’t.
Because you are running Duplicati as the SYSTEM user, I wonder what date output it is giving you?
You can test that with an echo command to a known file, e.g. echo %DATE% > <some filename>
Possibly the SYSTEM account has a standard format, e.g. English (United States) for safety.
Unexpected bugs can creep in when things vary, e.g. my format throws spaces and slashes out…
If that means a Windows service, you can change it to run as you instead (if that’s sufficient, and sometimes it isn’t) Service for /localuser has thoughts on what works, and what might not work…
But until you find out what your %DATE% is saying when run by Duplicati, this is all rather a guess.
Does a date really need to be there anyway? What’s your cleanup plan for old ones sitting there?
Theoretically, this file only needs to exist until Duplicati has backed it up, then it can be deleted…
Replace
%date%
with this:
%date:~-4%-%date:~4,2%-%date:~7,2%
This will get you a date formatted like yyyy-mm-dd, such as 2020-05-07
Depending on your locale we may need to tweak it. This was based on my US locale where %date% normally produces output like Thu 05/07/2020
Yes this thought is correct, to use it through Duplicati i don’t need to use this logic of keeping backups divided by dates, i’m used to working like this with other backup solutions. I have a script that eliminates older backups, after all i understand that with duplicati i don’t need this method.
In a perfect scenario everything goes well according to the tests done, so to test a failure scenario (for example the stopped mysql service), it is obvious that the script is unable to dump the database, so when executing the backup task, see what happens:
1- If i use the “–run-script-before” parameter, the task is executed with a Warning that cannot connect to the mysql service, however the dump in the source folder is created with size 0 and Duplicaty completes the backup, i do not intend this because new backups on destination are created with the wrong size (0Kb). But at the end, a Warning report is created.
2- If i use the “–run-script-before-required” parameter, the task generates an error in the execution of the script and aborts the task and nothing is sent (Error message: The script “C:\mysqldump.bat” returned with exit code 2). In my point of view this is the right thing and what i want, however in the end no report is generated and i cannot receive anything in the email about this. How is it possible in this scenario to be able to generate a report?
I hope you understand everything.
I did that and got the file name like → database1_2020-5–02.sql
Thanks.
I don’t and I also don’t script much…
Duplicati handles script exit codes as below. File is also in Duplicati installation folder.
Your script is responsible for knowing what it’s running, and taking appropriate action.
Information on how script output can cause things such as the warning that you saw is covered in:
Where does script output go to (difference between BEFORE and AFTER?)
Beyond that, I’m not clear on what’s wanted. Case 2 wants a report. Case 1 gives it, but something elsewhere isn’t as desired. You might need to try some different things until it’s the way you want…
Case 1 will let you avoid the empty backup (if that’s what’s wanted) by setting the script’s exit code.
I did verify that I could throw my own messages into the log by writing to stderr, e.g. echo foo >&2
Improvements for –run-script-before/after options was the feature request. The code work is below:
Having been spoiled by the Linux date command and its format string, Windows appears a mess…
Format date and time in a Windows batch script has examples of people trying to work around that.
Yeah it may need tweaking then. What does echo %date% look like on your system?
More specifically, what does it look like in the script when Duplicati runs it as SYSTEM?
e.g. echo %DATE% > <some filename> (the difference might explain different result)
We got a hint of how it looks like in a user account, from database1_07-05-2020.sql
which is consistent with a result of database1_2020-5–02.sql where the longer dash is
throwing off the view. It’s actually 2020-5--02 (no day of week), where 2020 is correct,
month is almost correct as 5- because the four-left shift and follows-day almost cancel.
Position where month is expected is now actually a year piece because of four-left shift.
Fix for cmd.exe as ordinary user might be below, but I want to know what SYSTEM got:
C:\>set TEST=07-05-2020
C:\>echo %TEST%
07-05-2020
C:\>echo %TEST:~-4%-%TEST:~3,2%-%TEST:~0,2%
2020-05-07
C:\>
Hi,
So i solved my problem this way:
Here is the content of my script that i run in –run-script-before
@ECHO
mysqldump -uroot -hlocalhost -pxxx database1 > C:\backup_mysql\database1.sql
cd c:\backup_mysql
for /r %%F in (*) do if %%~zF==0 del "%%F"
EXIT
So basically in a dump failure scenario (file not existing or creating a 0kb file), the script also removes any possible 0kb file on folder. This way when Duplicati performs the backup job, it will give an error and will not perform the job because the file does not exist, the most important thing is that this way i receive an error report in the email and also no file with 0kb is sent to the destination, and no new version with 0kb file will be created in the backup job in Duplicati.
This is what i was looking for, in my opinion i do not agree that Duplicati send a file with 0kb and assume a new version in the backup job, if any problems happen and i have to restore i would have versions with 0kb saved, i don’t think this is acceptable.
So i found this way to solve my problem.
The report with dupReport when job fails:
This format works like a charm with Duplicati! Great!
Thanks guys!
how do u get this to run when duplicati is setup via docker.
ie use pre script on host machine mysql .
Like - How to run shell script on host from docker container? - Stack Overflow
