Keep mysql replication healthy with percona

Install percona

cd /tmp
wget http://www.percona.com/redir/downloads/percona-toolkit/LATEST/deb/percona-toolkit_2.2.12_all.deb \
&& dpkg -i percona-toolkit_2.2.12_all.deb \
&& rm percona-toolkit_2.2.12_all.deb

If you miss dependencies, try’apt-get -f install’
If the deb file isn’t found, get the newest version at http://www.percona.com/redir/downloads/percona-toolkit/LATEST/deb

Main scripts

/var/percona-replicate.sh

nohup pt-slave-restart --verbose --sleep=1 --min-sleep=0 --max-sleep=30 --database=YOURDBNAMEHERE --host=localhost --user=root --password=YOURPASSHERE --port=3307 --socket=/var/run/mysqld/mysqld3307.sock >> /var/percona_replicate.out 2>&1

/var/percona-sync.sh

nohup pt-table-sync --execute --databases YOURDBNAMEHERE h=REMOTEHOST,u=REMOTEUSER,p=REMOTEPASS h=localhost,u=root,p=YOURPASSHERE,P=3307,S=/var/run/mysqld/mysqld3307.sock >> /var/percona-sync.out 2>&1

Install helper scripts

#/usr/bin/isprocessrunning
echo -e "\
#!/bin/bash\n\
\n\
# Argument expects the full command, so process name including parameters\n\
procnamefull=\$1\n\
\n\
# Get base process name from command\n\
procname=\$(echo \$procnamefull | awk '{print \$1}')\n\
\n\
# First fetch by full command, from there fetch by process name\n\
# Exclude the grep command itself, and the bin commands as well\n\
ps aux | grep \"\$procnamefull\" | grep -v \"grep \$procnamefull\" | grep -v \"/bin/bash /usr/bin/.* \$procnamefull\" | awk '{print \$11}' | grep ^\$procname > /dev/null 2>&1\n\
\n\
if [ \$? -eq 0 ]; then\n\
  exit 0\n\
else\n\
  exit 1\n\
fi\
" \ > /usr/bin/isprocessrunning
chmod u+x /usr/bin/isprocessrunning

#/usr/bin/startprocessifnotrunning
echo -e "\
#!/bin/bash\n\
\n\
\$(isprocessrunning \"\$1\")\n\
res=\$?\n\
\n\
if [ \$res -eq 0 ]; then\n\
  echo \"Process is running\"\n\
  exit 1\n\
else\n\
  if [ \$res -gt 1 ]; then\n\
    echo \"fail\"\n\
    exit 2\n\
  fi\n\
  echo \"Process not running\"\n\
  sh -c \"\$2\"\n\
  exit 1\n\
fi\
" \ > /usr/bin/startprocessifnotrunning
chmod u+x /usr/bin/startprocessifnotrunning

Cron

# Make sure percona-replicate is on. This program keeps the replication slave running after errors
*/5 * * * * /usr/bin/startprocessifnotrunning "sh /var/percona-replicate.sh" "sh /var/percona-replicate.sh" &

# Run full re-sync every week
0 1 * * 0 /usr/bin/startprocessifnotrunning "sh /var/percona-sync.sh" "sh /var/percona-sync.sh" &

Leave a Reply

Your email address will not be published. Required fields are marked *