fsgl wrote on Monday, March 11, 2013:
I have it on good authority that there will be a migration away from the “classic” version.
It is true that good things (enhancements) come to those who wait.
pfwilliams wrote on Monday, March 11, 2013:
I’ve seen two mentions in this thread of being unable to run the live mysqldbexport.exe method I’d posted. I went back and looked at what I’d done and looking for issues that might cause problems for others testing this route of database backup.
A couple things… At one point, I’d been trying to rip the Workbench utilities out and run them without Workbench having to be installed, that is what caused me to install Python 2.7. I’ve now uninstalled Python from the server as I’m using the utilities as installed by the Workbench application. I cleaned up the batch file somewhat, converting a few hard-coded strings to environment variables, and moving all the variables a user might need to modify to a block at the top of the batch file. I’ve added a placeholder where I’ll insert a step to compress and encrypt the .sql file generated by the database export.
It’s a great candidate for compression as WinZip or WinRar crunches our user-readable .sql export file down from 375MB to only 15MB. This executes on our OpenEMR server running under Windows Server 2003 R2 x64 SP2 in about 3 minutes, without . I did nothing but install MySQL Workbench on a remote Windows XP SP3 client and then change the ‘workbench’ variable to reflect the correct 32-bit /64-bit path to the ‘Program Files’ directory, and the script ran fine in 5 minutes (longer due to the client machine being inserted as a middleman for what is otherwise a simple source-to-destination copy).
All of the variables defined above the dashed line in the batch file need to be set/confirmed.
The PAUSE statements, useful for testing, are of course removed for automated jobs triggering kicked off by the Task Scheduler.
@ECHO OFF
SET server=\\OpenEMR
SET database=openemr
SET user=root
SET pass=pass
SET destpath=\\Nas1\Backup\%database%
SET workbench=C:\Program Files\MySQL\MySQL Workbench 5.2 CE
:: SET workbench=C:\Program Files (x86)\MySQL\MySQL Workbench 5.2 CE
::----------------------------------------------------------------------------------------------------------------------------------
SET TODAY=%date:~10,4%-%date:~4,2%-%date:~7,2%
SET HH=%TIME:~0,2%
IF "%HH:~0,1%" == " " SET HH=0%HH:~1,1%
SET MM=%TIME:~3,2%
ECHO Copying \sites folder...
SET sourcepath=%server%\xampp\htdocs\%database%\sites
SET xopt=/c /e /h /i /k /q /y
XCOPY %sourcepath%\*.* %destpath%\sites\ %xopt%
SET PYTHONHOME=%workbench%\Python
SET PYTHONPATH=%workbench%\Python;%workbench%\Python\DLLs;%workbench%\Python\Lib;%workbench%\Python\mysql_libs.zip;%workbench%\Python\Tools\Scripts
ECHO Exporting %database% database...
CD %workbench%\Utilities
mysqldbexport --server=root:pass@%database%:3306 --bulk-insert --export=both %database%>%database%_Backup_%TODAY%_%HH%-%MM%.sql
:: Insert compress/encrypt step here, and update following copy and del statements
COPY %database%_Backup_*.* %destpath%
IF %errorlevel% NEQ 0 GOTO :error
ECHO %TODAY% %HH%:%MM% - Backup Created
ECHO %TODAY% %HH%:%MM% - Backup Created >>%destpath%\%database%_Backup.log
DEL %database%_Backup_*.sql
PAUSE
EXIT
:error
ECHO %TODAY% %HH%:%MM% - Error Creating Backup
ECHO %TODAY% %HH%:%MM% - Error Creating Backup >>%destpath%\%database%_Backup.log
Pause
Hopefully this will behave better for anyone trying it. I am interested in building more support for a live backup method that can be ran at anytime without requiring users to exit, or that the MySQL server be stopped.
pfwilliams wrote on Monday, March 11, 2013:
grr… I’ve complained before too about the forum softwares shortcomings… that didn’t paste to well into code tags. I’ll try “quote” this time…
@ECHO OFF
SET server=\\OpenEMR
SET database=openemr
SET user=root
SET pass=pass
SET destpath=\\Nas1\Backup%database%
SET workbench=C:\Program Files\MySQL\MySQL Workbench 5.2 CE
:: SET workbench=C:\Program Files (x86)\MySQL\MySQL Workbench 5.2 CE
::---------------------------------------------------------------------------------------
SET TODAY=%date:~10,4%-%date:~4,2%-%date:~7,2%
SET HH=%TIME:~0,2%
IF “%HH:~0,1%” == " " SET HH=0%HH:~1,1%
SET MM=%TIME:~3,2%
ECHO Copying \sites folder…
SET sourcepath=%server%\xampp\htdocs%database%\sites
SET xopt=/c /e /h /i /k /q /y
XCOPY %sourcepath%\*.* %destpath%\sites\ %xopt%
SET PYTHONHOME=%workbench%\Python
SET PYTHONPATH=%workbench%\Python;%workbench%\Python\DLLs;%workbench%\Python\Lib;%workbench%\Python\mysql_libs.zip;%workbench%\Python\Tools\Scripts
ECHO Exporting %database% database…
CD %workbench%\Utilities
mysqldbexport -server=root:pass@%database%:3306 -bulk-insert -export=both %database%>%database%_Backup_%TODAY%_%HH%-%MM%.sql
:: Insert compress/encrypt step here, and update following copy and del statements
COPY %database%_Backup_*.* %destpath%
IF %errorlevel% NEQ 0 GOTO :error
ECHO %TODAY% %HH%:%MM% - Backup Created
ECHO %TODAY% %HH%:%MM% - Backup Created >>%destpath%%database%_Backup.log
DEL %database%_Backup_*.sql
PAUSE
EXIT
:error
ECHO %TODAY% %HH%:%MM% - Error Creating Backup
ECHO %TODAY% %HH%:%MM% - Error Creating Backup >>%destpath%%database%_Backup.log
Pause
robertrambo wrote on Tuesday, March 12, 2013:
Just thought I would add this open source solution maybe we can include this with your xcopy I used sha1 cross platform encryption
fsgl wrote on Wednesday, March 13, 2013:
Wiki article on cverk script almost ready for posting.
Went to Special:RequestAccount and was assigned a user page (I’m not registered yet). I think that I need to wait 4 days before password will be emailed to me. There does not seem to be any way to edit without the password for log in. Help page has migrated elsewhere not easily found. What is the process for getting to the edit button, Wiki contributors? Thanks for the information.
bradymiller wrote on Thursday, March 14, 2013:
Hi,
That’s odd. I confirmed your wiki account, so you should of been emailed a confirmation email. Check your junk mail. If not there and don’t know password, then email me and I’ll set a password and email it to you.
-brady
OpenEMR
fsgl wrote on Thursday, March 14, 2013:
Did get the confirmatory email with 1 link to the User Talk page, 2 links to the editor, 1 link to change the Watchlist, 1 link to unwatch and a last link to Help, but no password.
Will email to request a password again. Thanks.
fsgl wrote on Friday, March 15, 2013:
From the Command Prompt Convert:
Backup and Restore Made Easy is in the Wiki.
Please correct any errors.
我們非常感謝你們!
一帆風順.
blankev wrote on Friday, March 15, 2013:
To me this looks like a great tutorial and a good representation of all the FORUM discussion we had in making a good Windows the backup work!
TNX a lot.
QUESTION: You put the check.bat in c:/ drive, but was the check-bat not made to see of the drive is available? Would it not be more logic to check if the D:/ or F:/ drive is available? I have never worked with a computer who did not have a C:/ drive……
fsgl wrote on Friday, March 15, 2013:
Pimm, thank you for your kindness.
Regarding check.bat, I did as I was told. You need to ask the brain about the placement of check.bat (I still don’t understand its purpose, let alone where it should go.)
In future, gentlemen, you can supply the ideas and I will be happy to be the secretary. Who knows, once I get the hang of
software like CamStudio, we might end up with videos competing with ZH Healthcare for attention? (tongue in cheek).
Rich Bowen, SourceForge Support, was kind enough to supply the link, Gravatar to change
the avatar picture. Very easy to do if you don’t mind supply the email address.
robertrambo wrote on Saturday, March 16, 2013:
Hi pfwilliams
Great work you have done on that script very impressive
It is looking very nice.
-Rob
fsgl wrote on Saturday, March 16, 2013:
It is not obvious how to become a Wiki author or even how to get a Wiki account. It would be good to have it all on one page.
The Help:Content page has migrated elsewhere. Looking high and low has yielded nothing. The Wiki Instructions section of the Wiki Home page needs updating because there is a newer User’s Guide. File uploading is different than described in the
older User’s Guide. MediaWiki Sandbox for mockups has a toolbar with more functions than at the OpenEMR Wiki.
Next project is writing a primer about Wiki authorship. Most forum members have day jobs and would be less likely to write a Wiki article if extensive research is a prequisite. There are only about 120 contributors to the Wiki considering that the community is world wide. It is also good for me to write it down lest I forget the process.
Upon completion, will announce it with a new thread so that suggestions and improvements to the article can be elicited.
For your entertainment, I wanted to create a simple backup scheme that did not require any external device for incremental backups, between using my external hard drive backup, and so I got an SD card and plugged it in my computer as drive F: and created a simple BAT file:
@echo off
echo I am copying changed files in the xampp folder to F. Ctrl C to exit.
echo =================================================== >> C:\Users\Harold\Documents\xamppBakLog.txt
Date /T >> C:\Users\Harold\Documents\xamppBakLog.txt
Time /T >> C:\Users\Harold\Documents\xamppBakLog.txt
xcopy C:\xampp F:\xampp /s/d/y >> C:\Users\Harold\Documents\xamppBakLog.txt
echo ================LOG OF ALL CHANGED FILES=================
more < C:\Users\Harold\Documents\xamppBakLog.txt
echo ===========END OF LOG====================================
echo Enter ‘y’ to erase log file, any other key to keep it
set /P UserChoice=Type input:
if ‘%UserChoice%’==‘y’ (
echo “Log Cleared” > C:\Users\Harold\Documents\xamppBakLog.txt
Date /T >> C:\Users\Harold\Documents\xamppBakLog.txt
Time /T >> C:\Users\Harold\Documents\xamppBakLog.txt
)
Exit
Then I created a shortcut that invoked a command window to execute that program, since one can’t pin a BAT file to the start menu.
SO now I simply go to the start menu, click on my backup icon, and a command window comes up, copies any new or changed files to my copy of the entire xampp directory on the F drive, and creates a log file of a cumulative list of all files changed grouped by date and time, and finishes by asking if I want to clear the log file. All takes less than a minute to run. Not nearly as sophisticated as other posted solutions but may appeal to some.
This… doesn’t make me happy, but you could ameliorate the worst of the risks you’re taking if you stop and start XAMPP before and after your runs. https://askmhs.blogspot.com/2016/08/start-and-stop-xampp-in-windows-using.html discusses how you might do so programmatically.
Thank you very much, as you see, I am not much of a programmer. I looked at the site you reference, which said how to start and stop from a command line. But I have a problem, I hope you can clarify what I need to do:
I tried stoping before the xcopy, and then starting right after:
c:\xampp\xampp_stop.exe
xcopy C:\xampp F:\xampp /s/d/y >> C:\Users\Harold\Documents\xamppBakLog.txt
c:\xampp\xampp_start.exe
And the result:
Stopping XAMPP…
Can’t find Apache process #3548.
Can’t find MySQL process #2788.
XAMPP now starts as a console application.
Instead of pressing Control-C in this console window, please use xampp_stop.exe
to stop XAMPP, because it lets XAMPP end any current transactions and cleanup
gracefully.
2018-06-13 1:34:21 8000 [Note] Using unique option prefix ‘key_buffer’ is error-prone and can break in the future. Please use the full name ‘key_buffer_size’ instead.
2018-06-13 1:34:21 8000 [Note] mysql\bin\mysqld.exe (mysqld 10.1.13-MariaDB) starting as process 8384 …
(OS 10048)Only one usage of each socket address (protocol/network address/port) is normally permitted. : AH00072: make_sock: could not bind to address [::]:80
(OS 10048)Only one usage of each socket address (protocol/network address/port) is normally permitted. : AH00072: make_sock: could not bind to address 0.0.0.0:80
AH00451: no listening sockets available, shutting down
AH00015: Unable to open logs
That’s odd. It sounds like the stopper couldn’t stop OpenEMR, and then the starter couldn’t start it because it was never stopped. (Hence why it claims it didn’t restart – port 80 was still owned by the Apache xampp_stop somehow couldn’t find.
You might consider rebooting the machine and trying to use xampp_start to start your process, or it could be that you’re doing something exotic (maybe a configuration change over the years) that I’m not able to predict.
Thanks. I restarted my computer, tried typing the stop command at command prompt, same result. It starts itself as part of my boot up. After the stop command The Apache welcome page 5.6.20 is still at 127.0.0.1. I have not done anything whatsoever to the system, just did an install as directed by openemr instructions, 5.0.0 and then restored my data from previous version.