Automatically downloading/backing up/dumping/exporting databases from remote hosts via the web

The problem

You operate a database-backed website (e.g. Drupal) where you can’t access cron jobs, cgi, perl and outgoing connections. So one idea to back up your database on a regular basis (which is always a good idea) is to download SQL dumps via a web-based administration tool (such as the backup and migrate plugin for drupal). Unfortunately, these kinds of downloads cannot simply be automated on the shell by using curl or wget, because they require a bit of javascript, for example to outsmart the php timeout.

The solution

Use a headless browser (that is, a browser without graphical user interface) to automate the task. It fetches the desired page, logs in, (virtually) clicks the download button and downloads the dump file.

It should be a command line tool, in order to run it as cron job from a some server (e.g. a NAS).

Personally, I liked the idea of PhantomJS, but it was not available for my Synology DS213+ PowerPC and I didn’t like the idea of building it from source.

So my plan B was to write a small Java program (remoteDbDumper)  that uses the HtmlUnit framework (our headless browser).

How to use

  1. Install drupal plugin backup and migrate.
  2. Download and extract remoteDbDumper.
  3. Start it from the shell.
    remoteDbDumper -u <username> -p <password> -o <output dir> <url to backup and migrate>

    Note that output dir must be an existing directory

    1. Linux example:
      ./remoteDbDumper.sh -u user -p topsecret -o ~/backup https://ho.st/drupal/?q=admin/config/system/backup_migrate
      
    2. Windows example
      remoteDbDumper.bat -u user -p topsecret -o "%HOMEPATH%\backup" https://ho.st/drupal/?q=admin/config/system/backup_migrate
      
  4. Use the scheduling mechanism of your choice to call remoteDbDumper regularly, creating backups.

Example (Synology)

Just a short exemplary scenario on how to use remoteDbDumper on a Synology Diskstation (running DSM 4.2) to regularly back up a drupal database.

  1. (if Java is not installed) install Java:
    If available for your Diskstation, use the Java Manager package. Otherwise, you could use a third party Java package (that’s what I had to do).
  2. Download, extract and copy remoteDbDumper to the NAS (e.g. to \\diskstation\public\, which corresponds to /volume1/public/)
  3. SSH to the NAS and check if it works
    /volume1/public/remoteDbDumper-1.0/remoteDbDumper.sh -u user -p topsecret -o /volume1/someUser/ https://ho.st/drupal/?q=admin/config/system/backup_migrate
    
  4. (optional) Wrap the command line call in a shell script, e.g.
    BASEDIR=$(dirname $0)
    $BASEDIR/remoteDbDumper-1.0/remoteDbDumper.sh -u user -p topsecret -o $1 https://ho.st/drupal/?q=admin/config/system/backup_migrate
    
  5. Either use the web frontend  or the crontab to schedule the back up.
    1. Web frontend:
      Go to http://diskstation:5000, (or whatever combination of host name and port you’re using)
      login as admin,
      click on Control Panel | Task Scheduler.
      Then click on Create | User-defined Script.
      Enter a task name, choose a user (preferably not root), set up a schedule (e.g. every sunday at 8 p.m.).
      Finally enter the path to remoteDbDumpe or the script (4.) respectively. For the example above, the path would look like this:

      /volume1/public/dumpDb.sh /volume1/public/
      
    2. If you insist to do it on foot, here’s what to enter in crontab:
      vi /etc/crontab
      
      #minute hour    mday    month   wday    who              command
      0       20      *       *       0       enterUserHere    /volume1/public/dumpDb.sh /volume1/public/
      
    3. Set a maker in your calender for the next scheduled run, to check if it worked.

Future tasks

At the current state remoteDbDumper can only backup drupal databases. Fair enough.

However, with just a little more effort it would be possible to extend remoteDbDumper to support addition web-based database administration tools, such as  mysqldumper, phpMyBackupPro, phpMyAdmin or phpPhAdmin.

In order to do so, just fork the repo on github and implement the interface DbDump.

Advertisements

Hibernate: Write SQL to a specific logfile (without additional framework)

This post will show a simple setup logging Hibernate’s SQL statements to a single file, including all the parameters, using only log4j and (of course) Hibernate.

SQL in Hibernate

For the purpose of seeing the magic behind a object-relational mapper (e.g. for debugging), it’s always useful to have a look at the actual SQL queries that are created.

Especially, if you are working (or have to work) with something as useful as Hibernate’s Criteria API.

In Hibernate, the simplest way is to set the parameter hibernate.show_sql to true and Hibernate will log all SQL statements to system out.

Straight after setting this option, two things come to mind:

  • Even small applications can generate tons of SQL statements on your log and
  • the SQL statements contain only question marks instead of the actual paramaters, which leaves them only partly useful.

I can’t count the number of SystemOut.log files I saw that were so flooded with Hibernate’s SQL drivel that the important log statements were nowhere to be found.

Using additional Frameworks

In order to solve these issues, the best approaches might be to intercept the SQL statements on JDBC level and then logging them. This can be achieved using a framework like p6spy or log4jdbc. Those frameworks will log the complete SQL statements including the actual parameter values instead of question marks to a file.

However, those frameworks require a lot of effort on configuration. In some cases they can’t be used at all, e.g. in enterprise software – log4jdbc doesn’t even support maven (as of log4jdbc 4.1.2).

Using only Hibernate (and log4j)

A by far simpler alternative is to swiftly set up your logging framework (which should already be part of your application) to log the SQL statements, the values of the parameters and the values returned by the database to a separate file.

A configuration for log4j might look as follows:

log4.properties

log4j.rootLogger=INFO, File, Console

log4j.logger.my.application=DEBUG, File

log4j.logger.org.hibernate=WARN, File, Console
log4j.additivity.org.hibernate.SQL=false
log4j.logger.org.hibernate.SQL=DEBUG, FileSql
log4j.additivity.org.hibernate.SQL=false
log4j.logger.org.hibernate.type=TRACE, FileSql
log4j.additivity.org.hibernate.type=false

log4j.appender.Console=org.apache.log4j.ConsoleAppender
log4j.appender.Console.layout=org.apache.log4j.PatternLayout
log4j.appender.Console.layout.ConversionPattern=%d{ISO8601} %-5p %c{1} %l - %m%n

log4j.appender.File=org.apache.log4j.DailyRollingFileAppender
log4j.appender.File.datePattern='.'yyyy-MM-dd
log4j.appender.File.file=logs/myApplication.log
log4j.appender.File.layout=org.apache.log4j.PatternLayout
log4j.appender.File.layout.ConversionPattern=%d{ISO8601} %-5p %c{1} %l - %m%n

log4j.appender.FileSql=org.apache.log4j.DailyRollingFileAppender
log4j.appender.FileSql.datePattern='.'yyyy-MM-dd
log4j.appender.FileSql.file=logs/myApplication_sql.log
log4j.appender.FileSql.layout=org.apache.log4j.PatternLayout
log4j.appender.FileSql.layout.ConversionPattern=%d{ISO8601} %-5p %c{1} - %m%

This results in all SQL statements being logged to myApplication_sql.log whereas all other application-related messages (including warnings issued by Hibernate) are written to myApplication.log.

The important part is the additivity parameter, which avoids the log messages being propagated to the parent logger. That is, the SQL statements will not flood your system out.

This solution has only one drawback compared to the additional frameworks solution: It still contains the question marks. However, the org.hibernate.type logger writes additional log statements that contain the values.

Note: Setting the logger org.hibernate.SQL to DEBUG seems to be equivalent to setting the parameter hibernate.show_sql to true.

In addition I’d always recommend to set the hibernate.format_sql parameter to true in order to make the SQL statements easier to read.

The result might look something like this

2013-04-16 21:18:47,684 DEBUG SQL -
    select
        table0_.id as id0_1_
    from
        app table0_
    where
        table0_.val=?
2013-04-16 21:18:47,684 TRACE BasicBinder - binding parameter [1] as [BIGINT] - 95
2013-04-16 21:18:47,684 TRACE BasicExtractor - Found [1] as column [id0_1_]

Conclusion

To conclude, using log4j can be set up in no time and contains all the necessary information for debugging (including the values returned by the database), whereas a dedicated JDBC logging framework requires more configuration effort but puts out the SQL statements more neatly.

Links

Once more, I found most of the information aggregate in this article on stackoverflow: here and there. Thanks guys!

Songbird/Nightingale: Exporting playlists

The playlist problem

As mentioned in my previous post I have been using Songbird/Nightingale for quite some time, in spite of the drawback mentioned in the post.

No matter if using Songbird or Nightingale, one of my main problem still remained the same: The playlists are trapped somewhere inside the library with no way to export as playlist files. Absolutely no way? That’s not the whole truth, however, as there are (or were) addons like Playlist Export Tool, Export My Playlists or FolderSync. Thanks to the developers, by the way – those addons were really useful to me!

Unfortunately, with every new songbird release, all addons stopped working. In other words: Whenever I made the mistake of updating updated, I wasn’t able to export playlists anymore. I actually don’t even know if there are any addons left, that are compatible to the most recent version of Songbird.

The playlist solution

One more good thing about Songbird (and Nightingale as well), is that it uses an SQLite database. This allows for accessing the Songbird database from a variety of programming languages without getting your hands dirty and makes way for a “third-party” tool, that is capable of exporting playlists from the Songbird database and doesn’t depend on the Songbird version. I developed an exporter in Java and been using it some time to make my Songbird playlists available on my NAS.

As I thought this exporter might be useful to others, I refactored the quick and dirty source code and published it on GitHub. So now, I’m proud to present songbirdDbTools a Java-based playlist exporter for Songbird/Nightingale that was just released in its very first version. Hopefully, it will be of use for somebody else, who was missing this functionality as much as I did 🙂

 

The name is a bit of an exaggeration at this point, as the tool provides only the export functionality. However, I put some effort in designing songbirdDbTools to be as extensible as possible. I have a couple of things in mind that would be useful.
For example synchronizing playlists. That is, exporting not only the playlist but copy the member files as well. This might come handy for effectively synchronizing files to mobile devices.
Or finding zombies and ghosts (like the Excorcist used to do, three years ago). Another neat feature might be to find out all playlists a file belongs

If only I had more time!

So, just in case you’re interested in contributing: Fork songbirdDbTools on GitHub!