Backups

[lyda]

This isn't really a post asking a question. I'm describing how I'm doing backups here with the following two hopes:

1) Seeing if people have suggestions for additional things I should do or if I missed anything.
2) Helping people looking to script backups.

From what I can tell, all I need to backup is the database. Note that my deployment scripts and config files are in version control (where they belong). So to back up the database I have the following two files:

/etc/cron.d/mysql:

Code: Select all

50 4 * * * root /usr/local/sbin/mysql-backup.sh expire

cat usr/local/sbin/mysql-backup.sh

Code: Select all

#!/bin/bash

TS=$(date +"%Y-%m-%d_%H:%M:%S")
BACKUP_DIR=/backups
BACKUP_STEM="ttrss.$TS.mysqldump"

if [[ ! -d "$BACKUP_DIR" ]]; then
  mkdir -p "$BACKUP_DIR"
fi

mysqldump --single-transaction ttrss > "$BACKUP_DIR/,$BACKUP_STEM"
nice lzip "$BACKUP_DIR/,$BACKUP_STEM"
mv "$BACKUP_DIR/,$BACKUP_STEM".lz "$BACKUP_DIR/$BACKUP_STEM".lz

if [[ "$1" == "expire" ]]; then
  find /backups -mtime +7 -print0 | xargs -0 rm -f
fi

I find this compresses very well and doesn't put a lot of load on the system. It keeps the last 7 days of backups just in case there's an issue with a rollback. Traditionally on Unix systems a file that begins with a , is a temporary file and the command I use to rsync files excludes such files - which is why the backup file starts with a , until it's done.

For DB auth, I have a /root/.my.cnf file with the auth credentials I need.

[fox]

Should've posted to KB.

[sleeper_service]

Should've posted to KB.

well, that was my bad, it was originally in support, where it didn't belong.

[ajs124]

You could always pipe to lzip/xz/whatever instead of writing it to an uncompressed file and compressing it later. But besides that I didn't know about the "--single-transaction" parameter, which seems like a good idea to use.

[lyda]

Re: forum movement

My apologies for the wrong location. I wasn't satisfied it was complete so I didn't feel it rated "knowledge base" yet.

Re: mysqldump -> file; lzip file.

I mainly did this to limit how long mysqldump was running. It shouldn't lock anything, but it does put some amount of pressure on the DB to store state for transaction. And since I want to nice the lzip command (which is slow enough already) I figured splitting the job would work better.

If you're not too concerned about cpu spikes, you could do this instead (and remove the lzip line):

mysqldump --single-transaction ttrss | lzip > "$BACKUP_DIR/,$BACKUP_STEM.lz"

Regarding compression tools, I didn't try xz. I tried gzip, bzip2 and lzip - lzip was 2/3 the size of gzip and I decided to leave it there. xz and 7zip could probably do incrementally better; I haven't seen the ratings recently. Plus sql / feed backups might have certain characteristics that make them compress insanely better so definitely an area one could experiment with.

As an aside, my hosting is a macmini running Ubuntu. I suspect the ideal system for tt-rss would have at least two spindles (one for the OS and one for mysql) with a second machine for a mysql replica (or a postgres replica if you're using postgres). But It seems to function fine with just a single disk.

I've set it up using LXC with one container for ttrss and one for mysql. I can write that up if people are interested.

[gbcox]

I'm using postgresql now but information on mysql/mariadb backups can be found here:

http://tso.bzb.us/2013/04/mariadbmysql- ... edora.html

Another related post is here:
http://tso.bzb.us/2013/06/ttrss-utils-f ... y-rss.html

[lyda]

gbcox: Very cool. I don't run Fedora or use postgres, but good stuff to look through.