Pika Database Rsync Guide
From PikaDocs
By Matthew Friedlander
Pika Software (http://www.pikasoftware.com/)
| Table of contents |
Introduction
There are several advantages to having a backup or reporting copy of your pika database in case of disaster or to offload reporting overhead from your main server onto a backup server. This guide will detail the steps involved in creating an Rsync based backup between two or more linux based servers. To complete the steps involved it is recommended that you have a familiarity with command line linux as well as root or superuser access to one or more linux based installations.
Installation
Before we begin we need to identify the roles of each of the servers. The server with the live copy of Pika will be designated as the Master (or Host) server that we will be replicating. There can be any number of Slave servers that can replicate the pika database locally. Our work will be done primarily from the Slave servers.
Requirements
- The path on the Master server where the mysqldump'ed zipped database backups reside.
- An SSH enabled user account on the Master server that has access to the backups directory to download them.
- The name of the most recent backup file generated or all backup files (if you are creating a redundant backup).
- A local directory on the Slave server where backups can be downloaded and installed.
- MySQL server installed on the Slave server.
- A MySQL user account and password with the ability to create/drop databases.
- Rsync installed on the Slave server.
To help with programs using this internally we have put together a sample backup/refresh script that can be tailored to your organization. Please copy the text below into a script (/usr/local/bin/pika-db-rsync in this example).
The pika-db-rsync script takes 5 arguments
pika-db-rsync ssh_user local_path remote_path mysql_user mysql_password
Example usage:
pika-db-rsync matt /home/matt/backups /home/pikasite1/backups db_admin mysuperpassword
Note: There are several items in this example script that will need to be changed. They are enclosed in brackets "[]" . Here is a list:
- SITE_NAME - The name of the backup file(s) to be downloaded (Typically named after site)
- BACKUP_PREFIX - The text preceding to the download file name (Your org may not do this - if so simply change it to equal $SITE_NAME (ex. BACKUP_PREFIX = $SITE_NAME)
- MYSQL_LOCAL_DB - The name of the database that the backups will be copied into on the Slave server (ex. pika or pika_backup)
- BACKUP_HOST - The Master server ip or DNS name
Script pika-db-rsync
#!/bin/sh
# CL Arguments
SSH_USER=$1
LOCAL_PATH=$2
RMT_PATH=$3
MYSQL_USER=$4
MYSQL_PASS=$5
# Script Variables
SITE_NAME=[DB backup file name]
BACKUP_PREFIX=[DB backup file prefix (ex "db_") - Optional]$SITENAME
MYSQL_HOST=localhost
MYSQL_LOCAL_DB=[local MySQL database name]
BACKUP_HOST=[your Master server ip or dns name]
TIMESTAMP=`date +%F_%T | tr ':' '-'`
# Ensure the proper number of CL args
if [ $# -lt 5 ]
then
echo "usage: pika-db-rsync SSH_USER LOCAL_PATH RMT_PATH MYSQL_USER MYSQL_PASS"
echo "For any further assistance please view the wiki article"
echo "http://pikasoftware.com/docs/index.php/Pika_Database_Rsync_Guide"
exit 0;
fi
# Ensure local directoy exists
if [ ! -d $LOCAL_PATH ]
then
echo "Local backups directory: $LOCAL_PATH doesn't exist"
exit 1;
fi
# Begin rsync of files
rsync -e 'ssh' -aq $SSH_USER@$BACKUP_HOST:$RMT_PATH/$BACKUP_PREFIX.zip $LOCAL_PATH
# Select most recent backup and unzip
cd $LOCAL_PATH
mkdir $TIMESTAMP
cd $TIMESTAMP
unzip ../$BACKUP_PREFIX.zip
# Mysql Refresh
mysql -u $MYSQL_USER --password=$MYSQL_PASS -e "DROP DATABASE IF EXISTS $MYSQL_LOCAL_DB;
mysql -u $MYSQL_USER --password=$MYSQL_PASS -e "CREATE DATABASE $MYSQL_LOCAL_DB;"
mysql -u $MYSQL_USER --password=$MYSQL_PASS $MYSQL_LOCAL_DB < $SITE_NAME.schema.sql
mysql -u $MYSQL_USER --password=$MYSQL_PASS $MYSQL_LOCAL_DB < $SITE_NAME.data.sql
# Clean up Files/Dirs
rm -f $SITE_NAME.schema.sql
rm -f $SITE_NAME.data.sql
cd ..
rm -rf $TIMESTAMP
Password-less SSH - [Optional]
Now that you have the script up and running for your site you may want to automate this process by setting the script up as a cron job to run on a schedule. Unfortunately this process is slightly more complicated by the fact that ssh won't allow you to specify passwords in a script to be passed when prompted. As a result the script above will not run automatically since SSH will prompt for a password to login to the Master server and the script will fail. There is, however, a way to set up SSH to not prompt for a password by setting up an encryption key pair that the Master server can then use to identify you and allow login without a password.
Starting on the Slave server that you are setting up you will need to generate a Public RSA key.
ssh-keygen -t rsa
Which will ask you the following questions, I take the defaults in this instance and don't set a passphrase. You may of course wish to set a pass phrase at your discretion. The only difference is that you may be prompted for it during the key installation.
Generating public/private rsa key pair. Enter file in which to save the key (/home/mattf/.ssh/id_rsa): Enter passphrase (empty for no passphrase): Enter same passphrase again: Your identification has been saved in /home/mattf/.ssh/id_rsa. Your public key has been saved in /home/mattf/.ssh/id_rsa.pub. The key fingerprint is: 49:cb:af:6e:0c:5b:67:5b:7e:31:5c:39:47:65:6e:5d mattf@NEO
Now we need to scp this into the ssh account we wish to login to on the Master server.
scp ~/.ssh/id_rsa.pub matt@masterserver:.
If this is the first time you've connected to this server on this account you may see a message about RSA key fingerprint. Select yes, then enter your account password.
The authenticity of host 'masterserver (192.168.0.222)' can't be established. RSA key fingerprint is f7:80:32:94:e9:b2:3a:32:96:fb:b6:dd:77:e8:b0:d2. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added 'masterserver,192.168.0.222' (RSA) to the list of known hosts. matt@masterserver's password: id_rsa.pub 100% 219 0.2KB/s 00:00
Now that you have uploaded your RSA key to the Master server, login via SSH and cat your RSA key onto the ssh authorized keys file.
ssh matt@masterserver mkdir .ssh cat ~/id_rsa.pub >> ~/.ssh/authorized_keys
Here is the tricky part, you need to make sure that you set the Master servers .ssh directory has the proper permissions as well as the authorized_keys file that you just created.
chmod 700 .ssh chmod 644 .ssh/authorized_keys exit
Finally attempt to login again
ssh matt@masterserver
If all has gone according to plan you should no longer be prompted for an ssh password. Now you can create a file under /etc/cron.daily to run the backup and rsync script automatically each day.
Script /etc/cron.daily/refresh_pika.sh
#!/bin/sh pika-db-rsync matt /home/matt/backups /home/pikasite1/backups db_admin mydbadminpassword
Conclusion & Additional Notes
Obviously much of this guide is dependent on your particular setup and needs to be adjusted to fit your needs. However, it should give you a starting point in order to set up your own in house Pika Database replication. If you have any questions or suggestions concerning this guide please feel free to drop me a line at matt@pikasoftware.com.
