Backing up Atlassian apps properly

Posted by paul on 2014.03.20

Introduction

2014.03.20 Thur

This page explains how to back up and restore Atlassian Confluence and Jira easily. This tutorial should be especially helpful for those who are not too familiar with MySQL.

Why Atlassian backup tool is not useful

Atlassian products are awesome. Their documentation is great. However, Atlassian's webapp builtin backup tool is not that great. When using the builtin tool, completely backing up data from Atlassian app still requires additional steps. After the builtin tool creates an XML export, you still need to copy that XML file and 'attachments' folder off to another server or storage device. And even Atlassian's document suggests that XML export is not recommended for production use.

So how should you back up Atlassian data? The best solution I found is using a script which 1) backs up MySQL database with mysqldump, and 2) copies the folders (data and app). The shell script can be scheduled to run every day using cron jobs.

Assumptions

  1. Using MySQL database for database. For this tutorial it's running on the same server the Confluence is running on.
  2. Atlassian app is running on Linux (CentOS 6.x in this case)
  3. Atlassian app is installed under /opt/atlassian/confluence/, /opt/atlassian/jira/, and so on.
  4. Atlassian data is kept under /data/atlassian/confluence/, /data/atlassian/jira/.
  5. Atlassian installer recommends /var/atlassian/ for data folder but I chose /data/atlassian/.
  6. All scripts will be kept in /root/bin/
  7. Backup will run automatically at scheduled interval, without any input from an administrator.

Back up MySQL data of Atlassian apps

Most important part of the backup is backing up data in the Database, in this case MySQL. To automate backing up MySQL database, you need to complete 3 separate steps.

Create a read-only account in MySQL

On all tables, add 'backupuser' account with readonly privilege as shown below. You could use 'root' but it's dangerous as the password will be kept in a plain text file (although locked down with file permission).

# /usr/bin/mysql -u root -p
 Enter password:
 Welcome to the MySQL monitor.  Commands end with ; or g.
 Your MySQL connection id is 253404
...

...
 mysql> GRANT LOCK TABLES, SELECT ON *.* TO 'BACKUPUSER'@'localhost' IDENTIFIED BY 'yourpassword';
 Query OK, 0 rows affected (0.01 sec)

 mysql> flush privileges;
 Query OK, 0 rows affected (0.00 sec)
    

If you add another database later, make sure to repeat above process for the new database.

Create password file for 'mysqldump' to use

  1. Create /root/.my.cnf (make sure you have .my.cnf as the filename.)
  2. Place following in .my.cnf.
  3. [mysqldump]
    user = BACKUPUSER
    password = yourpassword
            
  4. Secure access to /root/.my.conf
  5. [[email protected] ~]# chmod 600 /root/.my.cnf
            

Script, backup-mysqldump.sh

Here's the script.

  1. It will create .sql files that you can use to restore MySQL databases later.
  2. Using mysqldump (comes with mysql-client package) command, the shell script will create one .sql file for each database. You can use these .sql files for restoring individual databases.
  3. And another .sql file will be created for all databases. Use this backup only when restoring the whole database.
  4. #!/bin/bash
    ###
    # Variables
    ###
    NOW=$(date +"%Y-%m-%d_%H-%M")
    NOWSTAT=$NOW
    BAK_DEST=/backup/mysqldump-backup/
    
    ###
    # Variables that may need modifications
    ###
    # Add individual databases to backup here.  Always check for new databases to backup.
    db[1]=confluence
    db[2]=jiradb
    
    ###
    # Script
    ###
    mkdir -p $BAK_DEST/$NOWSTAT
    
    # Back up each database into separate .sql files.
    for i in "${db[@]}"
    do
      /usr/bin/mysqldump --defaults-file=/root/.my.cnf -h localhost $i | gzip > $BAK_DEST/$NOWSTAT/$i-$NOWSTAT.sql.gz
    done
    
    # Back up all databases into one large .sql file.
    /usr/bin/mysqldump --defaults-file=/root/.my.cnf -h localhost --all-databases > $BAK_DEST/$NOWSTAT/all-databases-$NOWSTAT.sql
    
    #Delete old backups and keep only the latest 10 backups
    cd $BAK_DEST
    ls -dt $BAK_DEST/* | tail -n +11 | xargs rm -rf
    
    #Permission setting on backups
    chmod 750 -R $BAK_DEST/$NOWSTAT
    
    exit 0
            

So above script does the following:

  1. Lines 5-6: Find out date-time this script is running. Using NOWSTAT is important. Otherwise you may not get correct backup or get spurious error messages.
  2. Lines 13-14: Identify names of databases to backup. When adding new databases later, RETURN here and ADD the new names. If you were to add 'stashdb' for Stash later, you would need to add db[3]=stashdb under "db[2]=jiradb".
  3. Line 19: Create folder with current date-hour-minute at /backup/mysqldump/date-hour-minute/ (ex.: /backup/mysqldump/2014-03-20_03-05/)
  4. Lines 22 - 25: a. In this loop, the /usr/bin/mysqldump will be executed. It's important to use the full file path because this script will be run via cron.
  5. Lines 22 - 25: b. Including --defaults-file=/root/.my.cnf is important. This will force the mysqldump to use the login credential that you added in /root/.my.cnf earlier.
  6. Lines 22 - 25: c. " | gzip " tells the resulting file to be compressed.
  7. Lines 22 - 25: d. The part $BAK_DEST/$NOWSTAT/$i-$NOWSTAT.sql will give you /backup/mysqldump/2014-03-20_03-05/confluence-2014-03-20_03-05.sql.gz. This is the file to be used to restore Confluence database to the state it was in on 3/20/2014 at 3:05am.
  8. Lines 22-25 will be repeated 1 more time (total of 2 in this tutorial) because of the lines 13-14. Databases 'confluence' and 'jiradb' will be backed up, into separate files.
  9. Line 28: All databases on my MySQL server will be backed up into 1 .sql file, in this case all-databases-2014-03-20_03-05.sql.gz.
  10. Lines 31-32: I don't want backup jobs fill up my HD so I will keep only the last 10 backup sets. Now if you run the backup script backup-mysqldump.sh 10 times rapidly in 20 minutes, then you will end up with backups that go back only to 20 minutes ago. If you want to keep last 20 backup data, change +11 to +21.
  11. Line 35: Set file permission on /backup/mysqldump/2014-03-20_03-05/ so it's accessible to only authorized users.

Test running backup-mysqldump.sh

Test backup-mysqldump.sh works before moving on.

[[email protected] ~]# chmod 750 /root/bin/backup-mysqldump.sh
[[email protected] ~]# chown root:root /root/bin/backup-mysqldump.sh

[[email protected] ~]# /root/bin/backup-mysqldump.sh
        

Verify mysqldump worked

[[email protected] ~]# du -hs /backup/mysqldump/2014-03-20_03-05/*
9.5M    /backup/mysqldump/2014-03-20_03-05/all-databases-2014-03-20_03-05.sql.gz
9.2M    /backup/mysqldump/2014-03-20_03-05/confluence-2014-03-20_03-05.sql.gz
164K    /backup/mysqldump/2014-03-20_03-05/jiradb-2014-03-20_03-05.sql.gz
    

Back up Confluence

The shell script: backup-confluence.sh

  1. The documentation states only 'attachments' folder and MySQL dump is needed to restore Atlassian Confluence but I backup the entire data folder, /data/atlassian/confluence/, and the app folder, /opt/atlassian/confluence/. This is useful as when you happen to restore Atlassian app from scratch by reinstalling it as you will need to download mysql-connector-java-5.1.11.jar file (kept at /opt/atlassian/confluence/confluence/WEB-INF/lib/mysql-connector-java-5.1.11.jar) separately. If you have backup of 'app' folder, you have immediate access to the file.
  2. In order to avoid filling up my HD with backup sets, I will keep only the last 10 backup sets and delete older ones. Now if you run the backup script backup-confluence.sh 10 times rapidly in 20 minutes, then you will end up with backups that go back only to 20 minutes ago.
  3. Following script, backup-confluence.sh, copies /data/atlassian/confluence/ and /opt/atlassian/confluence/ to /backup/confluence/_date-stamp_/.
  4. #!/bin/bash
    #This script backs up Confluence (both install directory and data directory) to /backup/confluence/_date_/.
    
    ###
    # Variables
    ###
    ATLASSIAN_APP=confluence
    BAK_SRCE0_DATA=/data/atlassian/$ATLASSIAN_APP
    BAK_SRCE0_APP=/opt/atlassian/$ATLASSIAN_APP
    NOW=$(date +"%Y-%m-%d_%H-%M")
    NOWSTAT=$NOW
    BAK_DEST=/backup
    
    ###
    # Main body
    ###
    #Stop Confluence service
    /etc/init.d/$ATLASSIAN_APP stop
    mkdir -p $BAK_DEST/$ATLASSIAN_APP/$NOWSTAT
    
    #Copy files
    rsync -avW --delete-after $BAK_SRCE0_DATA $BAK_DEST/$ATLASSIAN_APP/$NOWSTAT/$ATLASSIAN_APP-data
    rsync -avW --delete-after $BAK_SRCE0_APP $BAK_DEST/$ATLASSIAN_APP/$NOWSTAT/$ATLASSIAN_APP-app
    
    
    #Start Confluence service
    /etc/init.d/$ATLASSIAN_APP start
    
    #zip backed up files. Delete unzipped backups.
    cd $BAK_DEST/$ATLASSIAN_APP/$NOWSTAT/
    /bin/tar -czf $ATLASSIAN_APP-data-$NOWSTAT.tar.gz $BAK_DEST/$ATLASSIAN_APP/$NOWSTAT/$ATLASSIAN_APP-data
    /bin/tar -czf $ATLASSIAN_APP-app-$NOWSTAT.tar.gz $BAK_DEST/$ATLASSIAN_APP/$NOWSTAT/$ATLASSIAN_APP-app
    rm -rf $BAK_DEST/$ATLASSIAN_APP/$NOWSTAT/$ATLASSIAN_APP-data
    rm -rf $BAK_DEST/$ATLASSIAN_APP/$NOWSTAT/$ATLASSIAN_APP-app
    
    #Delete old backups and keep only the latest 5 backups
    cd $BAK_DEST/$ATLASSIAN_APP/
    ls -dt $BAK_DEST/$ATLASSIAN_APP/* | tail -n +11 | xargs rm -rf
    cd
    
    exit 0
            
  5. When backup-confluence.sh is executed, you will end up with something like this:
  6. [[email protected] ~]# du -hs /backup/confluence/2014-03-20_03-15/*
    254M    /backup/confluence/2014-03-20_03-15/confluence-app-2014-03-20_03-15.tar.gz
    128M    /backup/confluence/2014-03-20_03-15/confluence-data-2014-03-20_03-15.tar.gz
                

Back up Jira

The shell script: backup-jira.sh

  1. The documentation states only 'attachments' folder and MySQL dump is needed to restore Atlassian JIRA but I backup the entire data folder, /data/atlassian/jira/, and the app folder, /opt/atlassian/jira/.
  2. In order to avoid filling up my HD with backup sets, I will keep only the last 10 backup sets and delete older ones. Now if you run the backup script backup-jira.sh 10 times rapidly in 20 minutes, then you will end up with backups that go back only to 20 minutes ago.
  3. Following script, backup-jira.sh, copies /data/atlassian/jira/ and /opt/atlassian/jira/ to /backup/jira/_date-stamp_/
  4. #!/bin/bash
    #This script backs up app AND data of Jira to /backup/jira/_date_/.
    
    ###
    # Variables
    ###
    ATLASSIAN_APP=jira
    BAK_SRCE0_DATA=/data/atlassian/$ATLASSIAN_APP
    BAK_SRCE0_APP=/opt/atlassian/$ATLASSIAN_APP
    NOW=$(date +"%Y-%m-%d_%H-%M")
    NOWSTAT=$NOW
    BAK_DEST=/backup
    
    ###
    # Main body
    ###
    #Stop Jira service
    /etc/init.d/$ATLASSIAN_APP stop
    mkdir -p $BAK_DEST/$ATLASSIAN_APP/$NOWSTAT
    #Copy files
    rsync -avW --delete-after $BAK_SRCE0_DATA $BAK_DEST/$ATLASSIAN_APP/$NOWSTAT/$ATLASSIAN_APP-data
    rsync -avW --delete-after $BAK_SRCE0_APP $BAK_DEST/$ATLASSIAN_APP/$NOWSTAT/$ATLASSIAN_APP-app
    #Start Jira servcie
    /etc/init.d/$ATLASSIAN_APP start
    #zip backed up files. Delete unzipped backups.
    cd $BAK_DEST/$ATLASSIAN_APP/$NOWSTAT/
    /bin/tar -czf $ATLASSIAN_APP-data-$NOWSTAT.tar.gz $BAK_DEST/$ATLASSIAN_APP/$NOWSTAT/$ATLASSIAN_APP-data
    /bin/tar -czf $ATLASSIAN_APP-app-$NOWSTAT.tar.gz $BAK_DEST/$ATLASSIAN_APP/$NOWSTAT/$ATLASSIAN_APP-app
    rm -rf $BAK_DEST/$ATLASSIAN_APP/$NOWSTAT/$ATLASSIAN_APP-data
    rm -rf $BAK_DEST/$ATLASSIAN_APP/$NOWSTAT/$ATLASSIAN_APP-app
    #Delete old backups and keep only the latest 5 backups
    cd $BAK_DEST/$ATLASSIAN_APP/
    ls -dt $BAK_DEST/$ATLASSIAN_APP/* | tail -n +11 | xargs rm -rf
    cd
    
    exit 0
        
  5. When backup-jira.sh is executed, you will end up with something like this:
  6. [[email protected] ~]# du -hs /backup/jira/2014-03-20_03-25/*
    267M    /backup/jira/2014-03-20_03-25/jira-app-2014-03-20_03-25.tar.gz
    341M    /backup/jira/2014-03-20_03-25/jira-data-2014-03-20_03-25.tar.gz
        

Back up Stash

  1. I do not use Stash now but used to play around it a bit earlier and below is the script I created to back it up. It's not tested to be working but should give you an idea how to backup Stash.
  2. Stash ( https://confluence.atlassian.com/display/STASH/Data+recovery+and+backups ) recommends shutting down Stash before backing up. So the script will need to stop Stash, backup, and restart Stash service.
  3. Entire home directory of Stash (/data/stash) needs to be backed up.
  4. Following script, backup-stash.sh, copies /data/stash/ to /backup/stash/_date-stamp_/. Old backups are deleted from /backup/stash/ automatically.
  5. #!/bin/bash
    #This script backs up /data/stash (which includes attachments and DB backups) to /backup/.
    
    ###
    # Variables
    ###
    ATLASSIAN_APP=stash
    BAK_SRCE0_DATA=/data/atlassian/$ATLASSIAN_APP
    BAK_SRCE0_APP=/opt/atlassian/$ATLASSIAN_APP
    NOW=$(date +"%Y-%m-%d_%H-%M")
    NOWSTAT=$NOW
    BAK_DEST=/backup
    
    
    ###
    # Main body
    ###
    /etc/init.d/$ATLASSIAN_APP stop
    
    mkdir -p $BAK_DEST/$ATLASSIAN_APP/$NOWSTAT
    
    /opt/stash/bin/shutdown.sh
    
    #Copy files
    rsync -avW --delete-after $BAK_SRCE0_DATA $BAK_DEST/$ATLASSIAN_APP/$NOWSTAT/$ATLASSIAN_APP-data
    rsync -avW --delete-after $BAK_SRCE0_APP $BAK_DEST/$ATLASSIAN_APP/$NOWSTAT/$ATLASSIAN_APP-app
    #Start Stash service
    /opt/stash/bin/start-stash.sh
    
    #zip backed up files. Delete unzipped backups.
    cd $BAK_DEST/$ATLASSIAN_APP/$NOWSTAT/
    /bin/tar -czf $ATLASSIAN_APP-data-$NOWSTAT.tar.gz $BAK_DEST/$ATLASSIAN_APP/$NOWSTAT/$ATLASSIAN_APP-data
    /bin/tar -czf $ATLASSIAN_APP-app-$NOWSTAT.tar.gz $BAK_DEST/$ATLASSIAN_APP/$NOWSTAT/$ATLASSIAN_APP-app
    rm -rf $BAK_DEST/$ATLASSIAN_APP/$NOWSTAT/$ATLASSIAN_APP-data
    rm -rf $BAK_DEST/$ATLASSIAN_APP/$NOWSTAT/$ATLASSIAN_APP-app
    
    #Delete old backups and keep only the latest 5 backups
    cd $BAK_DEST/$ATLASSIAN_APP/
    ls -dt $BAK_DEST/$ATLASSIAN_APP/* | tail -n +6 | xargs rm -rf
    cd
    
    exit 0
        

Schedule scripts to run with crontab

  1. Since you don't want to run scripts manually, use cron to run them at regular interval.
  2. If possible, use cron and schedule the scripts to run a few minutes from now. Wait for the scripts to finish executing and verify the backups have files using 'du -hs' command. I've seen mysqldump create empty .sql files due to bad login/etc. So always verify backups actually have useful data using 'du-hs'. And open the files. Don't simply 'ls' the files. Doing simple 'ls' command can mislead you into thinking backups were successful when the files are empty even though they exist.
  3. As root, edit /etc/crontab
  4. Add following 3 lines and save/close the file. In this tutorial the scripts will run starting at 2:05pm.
  5. 05 14 * * * root /root/bin/backup-mysqldump.sh  > /dev/null
    15 14 * * * root /root/bin/backup-confluence.sh  > /dev/null
    25 14 * * * root /root/bin/backup-jira.sh > /dev/null
        
  6. Once you can verify the scheduled scripts run as planned, change the scripts to run at night.
  7. 05 2 * * * root /root/bin/backup-mysqldump.sh  > /dev/null
    15 2 * * * root /root/bin/backup-confluence.sh  > /dev/null
    25 2 * * * root /root/bin/backup-jira.sh > /dev/null
        

Get the backed up files off from the server

IMPORTANT You are done, sort of. Before you step away from this project, make sure to create a script that copies off the backed up data to another server/storage-device on your network. IMPORTANT