Home › Forums › implementation › Implementation discussion ›

Uploading a backup of a mysql database

Submitted by Guest on Mon, 11/08/2014 - 00:15
in

Guys,

     I have been taking regular backups of OPV using the mysqldump command (on a linux server).  I would like to test one of these backups by uploading to a "dummy" opv server for testing.

 

What is the correct command to upload the "backup.sql" file into the mysql database, replacing all existing info in the database?

 

Cheers, Cahir.

‹ Failed to generate report: java.awt.print.PrinterIOException No currency symbol is appearing on product list - just a little square ›

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Re: Uploading a backup of a mysql database

Submitted by tanderson on Mon, 11/08/2014 - 10:11.

Use:

mysql -u username -p database < backup.sql

This will prompt for a password.

The database must exist. 

It is recommended to restore the backup to a different server. This:

  • avoids slowing down the production server
  • provides you with a fallback if the production server goes down
  • reduces the likelihood of mistakes (e.g restoring backup over production)

Ideally, this would be done automatically for each backup.

 

Re: Uploading a backup of a mysql database

Submitted by ck234 (not verified) on Mon, 01/09/2014 - 01:49.

Just out of interest - whenever the new sql database is uploaded, how long should Tomcat take to recognize it?  I have run a mysql upload, but I get a "requested resource is not available" error in Tomcat for at least an hour afterwards.  This seems to go away after a few hours, but I'm not sure why?

 

(The actual upload of a 50MB sql file to the database seems to take only 5 seconds).

Re: Uploading a backup of a mysql database

Submitted by tgething (not verified) on Mon, 01/09/2014 - 06:05.

Cahir - I have never thought of doing this.  When I play with MySQL I always stop Tomcat, and restart it when the maintenance is complete.  I suspect that this is a better approach.  Regards, Tim G

Re: Uploading a backup of a mysql database

Submitted by ck234 (not verified) on Tue, 02/09/2014 - 17:13.

Tim,

  Thanks for your response.  I think you are correct - better to stop Tomcat7 first.  I appear to have killed the openvpms webapp though.  I ran a full mysql database drop, and recreated the database as instructed in the OPV installation docs, then ran the "backup.sql" upload command.  Then...nothing.  The webapp is still unavailable.  Tomcat is running fine, but OPV will not start, even from the Tomcat web manager.

 

Not really sure why changing the mysql would screw up the webapp.  Any thoughts?

Re: Uploading a backup of a mysql database

Submitted by tanderson on Thu, 04/09/2014 - 15:35.

Check the logs to determine why its not starting.

If you are running 1.7, OpenVPMS will log to <TOMCAT_HOME>/logs/openvpms-full.log.

Earlier releases log to openvpms-full.log in the directory you started Tomcat.

 

Re: Uploading a backup of a mysql database

Submitted by ck234 (not verified) on Wed, 10/09/2014 - 02:43.

Thanks for your response.

 

Here is the output of my log:

 

WARN DisposableBeanAdapter,localhost-startStop-1:193 - Invocation of destroy method failed on bean with name 'sessionFactory': org.hibernate.cache.CacheException: java.lang.IllegalStateException: The CacheManager has been shut down. It can no longer be used.
09 Sep 2014 11:35:07,943 ERROR ContextLoader,localhost-startStop-1:220 - Context initialization failed
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'jxpathHelper' defined in ServletContext resource [/WEB-INF/openvpms-framework.xml]: Cannot create inner bean 'org.openvpms.macro.impl.MacroFunctions#3ea32dba' of type [org.openvpms.macro.impl.MacroFunctions] while setting constructor argument with key [TypedStringValue: value [macro], target type [null]]; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'org.openvpms.macro.impl.MacroFunctions#3ea32dba' defined in ServletContext resource [/WEB-INF/openvpms-framework.xml]: Cannot resolve reference to bean 'macros' while setting constructor argument; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'macros' defined in ServletContext resource [/WEB-INF/openvpms-web-app.xml]: Instantiation of bean failed; nested exception is org.springframework.beans.BeanInstantiationException: Could not instantiate bean class [org.openvpms.macro.impl.LookupMacros]: Constructor threw exception; nested exception is org.openvpms.component.business.service.archetype.ArchetypeServiceException: Failed to execute query: {0}.
    at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveInnerBean(BeanDefinitionValueResolver.java:281)
    at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveValueIfNecessary(BeanDefinitionValueResolver.java:120)
    at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveManagedMap(BeanDefinitionValueResolver.java:378)
    at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveValueIfNecessary(BeanDefinitionValueResolver.java:161)
    at org.springframework.beans.factory.support.ConstructorResolver.resolveConstructorArguments(ConstructorResolver.java:630)
    at org.springframework.beans.factory.support.ConstructorResolver.autowireConstructor(ConstructorResolver.java:148)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.autowireConstructor(AbstractAutowireCapableBeanFactory.java:1003)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:907)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:485)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456)
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:291)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:288)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:190)
    at
 

I'm not too sure what it means to be honest.

A separate question - when using the mysql dump command to restore a database, must the new database structure be empty, or can it contain info that is simply wiped by the new data?

 

Cahir

Re: Uploading a backup of a mysql database

Submitted by Ben_Charlton on Wed, 10/09/2014 - 12:52.

As far as the startup error goes... are there any more?

the key error is Constructor threw exception; nested exception is org.openvpms.component.business.service.archetype.ArchetypeServiceException: Failed to execute query: {0}

Which means for some reason it couldnt execute a query against the mysql database.

As far as database restoration you wouldnt use msyql dump to do the restoration you would simply use the sql file it generated piped into a mysql session.

Submitted by tanderson on Mon, 11/08/2014 - 09:11.

Use:

mysql -u username -p database < backup.sql

This will prompt for a password.

The database must exist. 

As far as existing data goes it depends how you created the dump. You can create a dump file that starts by dropping all the tables and then recreating them - which is preferable.

using  --add-drop-table as a option when running the command.  There are another couple of options you should use when creating such as --add-locks alternatively just use --opts for a nice set of options that does both of these and more for you.

 

Ben

Regards
 
Ben 
OpenVPMS Installer and Helper 
Ph: +61423044823 
Email: info[at]charltonit.com[dot]au

Re: Uploading a backup of a mysql database

Submitted by tanderson on Wed, 10/09/2014 - 12:57.

The root cause of the problem is not displayed in that log. There should be additional messages that indicate the problem.

I suspect that OpenVPMS is unable to connect to the database. Unless you've changed the defaults, OpenVPMS will try and connect to mysql running on localhost, port 3306, to a database named openvpms, using username openvpms and password openvpms.

Verify that you can connect with:

mysql -u openvpms --password=openvpms openvpms

If not, and the database exists, make sure that the openvpms user has access to it. e.g.:

GRANT ALL PRIVILEGES ON openvpms.* TO 'openvpms'@'localhost'
    IDENTIFIED BY 'openvpms' WITH GRANT OPTION;

 

When you restore the database using:

    mysql ... < dump.sql

you don't need to drop existing tables. This is done for you.

 

Re: Uploading a backup of a mysql database

Submitted by ck234 (not verified) on Wed, 10/09/2014 - 16:57.

Guys,

   Thanks for your replies.

I tested to see if openvpms could login to the mysql as a user, which it can.

Tomcat is running fine.  Just for some reason the OPV webapp cannot get up and running.  To correct myself, I did not use mysqldump to restore the database, just the instruction "mysql -u user -p openvpms < backup.sql"

So no joy.  I will retrace my steps and use a fresh mysqldump to see if that makes any difference over my compressed regular backups.

 

As an addition from the log - anyone know what this means?:

Caused by: org.openvpms.component.business.dao.hibernate.im.query.QueryBuilderException: Could not find any matching archetypes for short names {lookup.macroReport}

 

Re: Uploading a backup of a mysql database

Submitted by ck234 (not verified) on Wed, 10/09/2014 - 16:58.

Another bit of info:  when I setup OPV on the backup server, it can load fine.  When you login there is just an empty installation with no client details etc.  I presume this is what is supposed to happen?

Re: Uploading a backup of a mysql database

Submitted by tanderson on Fri, 12/09/2014 - 00:41.

The lookup.macroReport archetype was introduced in OpenVPMS 1.7. Are your backup server and production server running the same versions of OpenVPMS?

Re: Uploading a backup of a mysql database

Submitted by ck234 (not verified) on Fri, 12/09/2014 - 17:17.

Err no.  The backup server runs a more recent version of OPV than the production server.  I didn't think that this would be a problem (because its just the MySQL database I upload), but I guess it is?

Re: Uploading a backup of a mysql database

Submitted by tanderson on Fri, 12/09/2014 - 18:07.

You'll need to migrate the database after restoring it, and before starting Tomcat.

See section 2.8 "Upgrading from older releases of OpenVPMS" of readme.txt, included in the release distribution.

 

Re: Uploading a backup of a mysql database

Submitted by ck234 (not verified) on Tue, 16/09/2014 - 06:49.

You were correct.  I guess I should "RTFM" a bit more closely on upgrade.  Just to help anyone else - here is what I did to install my MySQL dump into a more recent version of OPV:

1. Stop tomcat, then upload the MySQL database into new OPV installation with mysql -u openvpms -p openvpms < backup.sql

2. Run MySQL converstion script found in OPV update folder of download

3. Load latest archetypes (found in OPV update folder)

4. Restart Tomcat.

Syndicate content