Back up
Last week our computer crashed - powered off while booting up, and when we turned on again we could not get into OpenVPMS. We had been backing up the data as suggested but it turned out that the MySQL files were corrupted and therefore unable to start OpenVPMS . My son ( who is a programmer and open source developer) spent 36 hours looking for way to re-establish the database unsuccessfully. He commented that the backup for the software requires to be far more extensive than recommended and he has set up a daily scheduled online backup of all the files and applications he thinks may avoid a repeat loss.We had to start from scratch and did lose all clients all drugs all debts all configurations etc so it has been a hard slog to get some client base back but it is a huge disaster.
It will take test crash and restore to know if our current back up will restore the data .
In the meantime it may be worth getting feedback what people are doing out there and what is the 100% way to avoid what has happened to us.
Harry
Re: Back up
I'll add some technical details to this.
A number of tables were corrupt, most of the act_... tables.
Starting mysql would fail to start without
With this though, any select or check on any of these corrupted tables would crash mysql. Even a select .. limit 1 would crash the whole database. We also ran disk checks which turned up nothing.
The only backup we had was the ibdata1 file from C:\OpenVPMS\data\ as recommended by OpenVPMS. But restoring it did not change mysql from crashing. I wonder if other files, like those under c:\ProgramData\MySQL\ were busted and despite the data all being in ibdata1 mysql was still unable to use it.
I tried using Percona's Data Recovery Tool for InnoDB, see www.percona.com/docs/wiki/innodb-data-recovery-tool:mysql-data-recovery:... , but the server being windows and having only access to it via remote desktop didn't help much. Using the tool on linux i only managed to extract one page and with that there seemed little that could be done.
Trying to drop just the corrupt tables also did not work (most of them dropped but dropping acts crashed mysql everytime).
So in the end there wasn't much to do but wipe the database and start again.
Lessons learnt we don't consider backing up ibdata1 to be sufficient. Now we do a myqsldump and upload it to strongspace.com every day.
So there's two questions here:
Re: Back up
Hi,
If the Mysql configuration has been changed according to the install documentation then the ibdata1 file does not store the data as the innodb_file_per_table option is set and the individual table ibd files are all stored in the default Mysql data folder instead.
Can you please point me to the location of the documentation that indicated you could just copy the ibdata1 file as I am unable to locate it ? As Tim indicates this is very wrong advise and it needs to be removed from the site.
All the attachments and documents are stored in the database so a database backup (mysqldump or via Mysql Administrator) is all that is required to restore OpenVPMS (apart from the applications themselves of course).
Cheers
Tony
Re: Back up
Just to add to the list of what others are doing... I'm running a 'mysqldump openvpms > openvpms.sql' nightly, and then (just after that) doing an 'rdiff-backup' to an off-site location... for all OpenVPMS, PACS, and other user data. I have a test instance that I use to play with new versions, but that currently has the same as our production installation. I periodically restore the backed-up database to that test instance to confirm integrity.
Cheers, Paul
Re: Back up
We use a scheduled task on our old fileserver running Server 2003 to launch a MySQL Administrator (Version 1.2.6) backup from the command line.
"C:\Program Files\MySQL\MySQL Tools for 5.0\MySQLAdministrator.exe" "-UDC:\[Target Path]\" "-cnew_op" "-bpopenvpms_new_10 11 22" "-btF:\OpenVPMS\Backup\" "-bxopenvpms_new_10 11 22"
This acheives the same as a mysqldump and overwrites the previous backup. Note the old MySQL ADministrator Version. The current Workbench version of MYSQL Administrator, no longer has this option.
The resulting file is written via Drive Snapshot to a 500GB RD drive cart with a unique copy for each day of the week so we can roll back to a specific day if required.
I can confirm (sadly!) that restores from these backups have been successful in our hands :)
Matt C
Re: Back up
In general, you should be using mysqldump to back up OpenVPMS.
I couldn't find any reference on the site to copying the ib* files; if there is one, it needs to be changed.
If you wish to back up the ib* files, you need to shutdown the mysql server first. You also need to copy more than just the ibdata1 file. See http://dev.mysql.com/doc/refman/5.0/en/innodb-backup.html for details.
The mysqldump tool is much more straightforward, and copies everything you need.
As to how your database became corrupted, take a look at http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html for a possible reason.
You might be able to do partial recovery using the techniques outlined in http://www.mysqlperformanceblog.com/2008/07/04/recovering-innodb-table-c...
If you have a backup of ibdata, ib_logfile0 and ib_logfile1, you might be able to recover using http://egil.biz/how-to-recover-mysql-data-from-innodb/
-Tim
Re: Back up
I had written a tutorial on backups a couple of months ago, but figured that
a. everyone knew how to do backups; and
b. I was reluctant to publish another of my "misguides".
I've resurrected this tutorial and posted it here at http://www.openvpms.org/documentation/backing-and-restoring-your-openvpm....
Constructive criticism and corrections always welcome.
Yuri.
Re: Back up
Hi all,
I've been wanting to trial backup and then restoring onto a backup server computer for a while and I finally got around to test it this week. My current server runs on Windows7 Pro and the new computer Windows 8.1 Pro. I've installed all application files on the new computer and tested that OpenVPMS would load with a freshly created database file, but when I tried to restore a copy of the working database backup created using MySQL administrator, I encountered a problem two third of the way down the progress bar:
The message said:
"Error while executing this query:INSERT INTO `documents` (`document_id`,`version`,`linkId`,`arch_short_name`,`arch_version`,`name`,`description`,`mime_type`,`doc_size`,`checksum`,`contents`) VALUES
...followed by a series of checksum characters..."
The restoration failed
:(
Could anyone please shed some light on why MySQL server would stop during the process? Where have I got it wrong?
Kind regards,
Anthony
Kind regards,
Anthony (ActiVet)
Re: Back up
Before I answer -
Questions
1. How did you backup the MySQL data?
2. What version of mysql is your win7 machine running?
3. What version of mysql is the new win8 machine running?
Bad Practices I commonly see
1.mysqladministrator - this program was retired - dont use it...it runs an outdated version mysql backup - use the mysqldump that ships with the mysql edition you downloaded. OR a third party tool that is validated against that version of mysql.
2. Main machine is running mysq ver A Backup machine runs mysql ver B - dont do it use the same mysql version on your backup and production machine
Finally can you past the entire error message...including the values (if its been truncated you may need to turn on logging on the mySQL server.
Goodluck
Ben
Re: Back up
Hi Ben,
Thanks for your reply! To answer your questions:
1. I backup my OpenVPMS database daily using MySQL Adminstrator as a scheduled task the same way as Yuri described:
http://www.openvpms.org/backing-and-restoring-your-openvpms-data
2 & 3. Both my production and backup servers are running MySQL version 5.1.56 and MySQL Administrator 1.2.71. They also have the same connector file mysql-connector-java-5.1.5-bin in both the OpenVPMS release and Tomcat lib directories. They are running Java 7 but with different build (update 21 vs 65). They are running Tomcat 6 also different build (6.0.32 vs 6.0.41). Would these be the problem? I have to admit I did try to install a 64-bit version of the MySQL 5.1.73 prior but I have uninstalled that and reinstalled with 5.1.56 (32-bit version).
When trying to restore the database backup file on the backup machine, the following message came up:
"Error while executing this query:CREATE TABLE `act_details` (
`act_id` bigint(20) NOT NULL,
`type` varchar(255) NOT NULL,
`value` varchar(5000) NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`act_id`,`name`),
KEY `FKFB795F95D8B907FA` (`act_id`),
CONSTRAINT `FKFB795F95D8B907FA` FOREIGN KEY (`act_id`) REFERENCES `acts` (`act_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
The server has returned this error message:Can't create table 'openvpms.act_details' (errno: -1)
MySQL Error."
Currently I cannot login to OpenVPMS on this backup server either. It said "The requested resource is not available".
Any suggestions?
Kind regards,
Anthony
Kind regards,
Anthony (ActiVet)
Re: Back up
ok, i am sure some people have had success with Mysql administrator but in reality that software has not been updated since 2006.
Mysql 5.1 was released 2 years later in 2008, so it might be expected the mysql admin does not always work.
You are far better off using mysqldump to generate backups...or if the backup server is always online setup replication.
you can use mysql workbench to do one off backups and restores from the master to the backup and in my opinion thats what you should try first. Make sure you set it to use the correct mysqldump exe in the options. IE you can set it to use mysqldump from ver 5.1.56.
If you want to setup replication you can either contact an implementor of follow the detailed instructions on the mysql website.
I use a mysql backup batch file and then schedule it using scheduler.