Sar-sql Wiki

Wiki page tracking and documenting work on the sar-sql script. Please consider it as a work in progress or very rough draft.

Step by Step Installation

You will need to have root like privileges in the database server in order to complete these tasks.

1. Create sarsql user with the following commands. Replace user, localhost, the database name and the password has value to fit your particular installation requirements. If your taking snapshots from a server set as _read only_ you will need to add SUPER to the 1st GRANT statement.

GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'user'@'localhost' IDENTIFIED BY PASSWORD '*0000000000000000000000';
GRANT ALL PRIVILEGES ON `sarsql`.* TO 'user'@'localhost';
FLUSH PRIVILEGES;

2. Create the sarsql schema in the database:

CREATE DATABASE sarsql;

3. Create a secure configuration file with the user credentials (in Linux: 0600) :

cat .sarsql.conf

# User credentials
user=user
password=Password

4. Create a wrapper script to define parameters, log file and command invocation. You may use the example below adjusting the directories to match your server:

#!/bin/bash
# Wrapper to run sar-sql for a master and slave

# Default dir
RDIR=/opt/sarsql
SARDIR=/opt/sarsql/

# Pickup variables
RUSER=`grep user $RDIR/.sarsql.conf | cut -d "=" -f 2`
RPWD=`grep password $RDIR/.sarsql.conf | cut -d "=" -f 2`
RSNAPSHOT='--status --process --slave --master'
CMDLINE="$RSNAPSHOT --user $RUSER --pass $RPWD --database sarsql"

# echo "./mysar.pl $CMDLINE" >> ../log/sarsql.log 2>&1
# exit 1

# Execute the command
cd $RDIR
PATH=$SARDIR:$PATH
mysar.pl $CMDLINE >> sarsql.log 2>&1
# mysar.pl $CMDLINE 2>&1 | tee sarsql.log

5. Run the wrapper at least once to initialize the tables and/or test the parameters

6. Schedule the wrapper in cron. Use crontab or my favorite: add a file to /etc/cron.d/ with the proper entry as shown below. Make sure you change it to match your environment:

cat mysql_sarsql

# Take mysql status snapshots every 5min
MAILTO=mysql_dbas@domain.com
*/5 * * * * mysql /opt/sarsql/sarsql_wrapper.sh