Backing up sqlite to another machine

Much have been written about backing up mysql/mariadb (mysqldump anyone?), but what about lowly sqlite? It might not have as many features and does not run as its own process, but sqlite is rather useful in its own right (embedded anyone?). If you have an android phone you are running sqlite. And that does not mean its data is not worth saving, so let's do some saving.

I will call the backup server backupbox and the machine running sqlite webserver. Note that usually a sqlite db is not run in a separate server like other databases, which is why in this example we claim it is a backend to some website.

Requirements and Assumptions

1. sqlite3. After all, it is a bit challenging backing up a database if you do not have the database.
   
2. Path to the database file you want to backup. Remember, sqlite is primarily a file; if you do not know where the file is, backing it up might pose a few challenges. In this example, it is found at /var/local/www/db/stuff.db.
   
3. Both backupbox (IP 192.168.42.90) and webserver are running Linux or OSX. If there is an interest we can talk about when one (or both) of them are running Windows.
   
4. We will break the backup process in two steps: backing up the database (to local drive) and then copying backup into the backup server. Reason is that we can then time each step to run at convenient times to them. Also, if one of the steps fail, it is much easier to take care of that. I know how monolithic do-everything-plus-clean-kitchen programs are the hip and fashionable solution nowadays, but this is my blog and I prefer simple and (hopefully) easy to maintain solutions whenever possible. Deal with it.
   

Procedure

1. On server to be backed up, webserver
   
   1. Create backup user
      

      useradd -m backupsqlite -G www-data
      sudo -u backupsqlite mkdir -p /home/backupsqlite/.ssh
      touch /home/backupsqlite/.ssh/authorized_keys
      chown backupsqlite:backupsqlite /home/backupsqlite/.ssh/authorized_keys
      

   2. Create script to dump the database in ~backupsqlite/sqlite-backup.bz2.
      

      cat > /usr/local/bin/backupsqlite << 'EOF'
      #!/bin/bash
      BACKUP_USER=sqlitebackup
      BACKUP_GROUP=services
      DATABASE=/var/local/www/db/stuff.db
      
      sqlite3 ${DATABASE} ".dump" |
      sudo -u  ${BACKUP_USER} bzip2 -c > /home/${BACKUP_USER}/sqlite-backup.bz2
      chmod 0600 /home/${BACKUP_USER}/sqlite-backup.bz2
      EOF
      chmod 0700 /usr/local/bin/backupsqlite

   3. Run the above script manually as user sqlitebackup. Do not continue until this step is successful.
      
   4. Now you know your hard work paid off, how about running this script once a day? Maybe at 3:00am the natives will be quiet enough so you can safely run the backup script:
      

      cat > /etc/cron.d/backupsqlite << 'EOF'
      MAILTO=admin@example.com
      0 3 * * *  backupsqlite    /usr/local/bin/backupsqlite
      EOF

2. On backup server, backupbox:
   
   1. Create ssh key pair to authenticate the connection. Note we are assuming we are will be running this script from backupbox's root account; that is not required and probably not the smartest thing to do, but will work fine for our little test. You could have used Kerberos or LDAP or something else, but would need to make changes as needed.
      

      ssh-keygen -f /root/.ssh/sqlitebackup-id-rsa

      You will need to copy sqlitebackup-id-rsa.pub to webserver and place it in ~backupsqlite/.ssh/authorized_keys by any means you want. If you are a better typist than me, you could even enter it manually.
      
   2. Test: can you retrieve the backup file?
      

      rsync -az -e "ssh -i /root/.ssh/sqlitebackup-id-rsa " \
      backupsqlite@webserver.example.com:sqlite-backup.bz2 .

      We can restrict this connection later. Right now let's just make sure this step works. If not, find out why before continuing.
      
   3. Now let's create the script that will get the database and put it, say, in /export/backup/databases

      cat > /usr/local/bin/backupsqlite << 'EOF'
      #!/bin/bash
      BACKUP_USER=sqlitebackup
      BACKUP_PATH='/export/backup/databases'
      DATABASE_SERVER=webserver.example.com
      $DATABASE=sqlite-backup.bz2
      KEY='/root/.ssh/sqlitebackup-id-rsa'
      
      cd $DATABASE_PATH
      rsync -az -e "ssh -i $KEY " $BACKUP_USER@$DATABASE_SERVER:$DATABASE .
      EOF
      chmod 0700 /usr/local/bin/backupsqlite

      Test it before continuing. Note there are other ways to do this step, like add the above rsync statement to a larger backup script; I tried to hint at that and the fact this could be the start of a function that would loop over all the servers you need to grab backup files from. So final implementation is up to you.

   4. If this backup is to be run independently, create a cron job to run it at a convenient time. How does 11:45 in the evening sounds?

      cat > /etc/cron.d/backupsqlite << 'EOF'
      MAILTO=admin@example.com
      45 23 * * *  root    /usr/local/bin/backupsqlite
      EOF

      Otherwise, tell your main backup script about this new entry.

3. And back to webseerver
   
   1. Now time to make access to the database more restrict. We will be rather lazy here: we will start with what we have done in a previous article and make a few changes here and there.
      

      sudo -u backupsqlite cat > /home/backupsqlite/cron/validate-rsync << 'EOF'
      #!/bin/sh
      case "$SSH_ORIGINAL_COMMAND" in
      rsync\ --server\ --sender\ -vlogDtprze.iLsf\ --ignore-errors\ .\ sqlite-backup.bz2)
      $SSH_ORIGINAL_COMMAND
      ;;
      *)
      echo "Rejected"
      ;;
      esac
      EOF
      chmod +x /home/backupsqlite/cron/validate-rsync

      You probably noticed that I named the scripts running in both machines the same. Why not? If you do not like that, change it!
   2. Then we need to modify /home/backupsqlite/.ssh/authorized_keys to tell it to accept only connections from our backup server using our key pair and then only allow the backup script to be run. In other words, add something like
      

      from="192.168.42.90",command="/home/backupsqlite/cron/validate-rsync"

      to the beginning of the line in /home/backupsqlite/.ssh/authorized_keys containing the public key.
      

References

The Sqlite dump command