ブログ

投稿一覧

mysql.sh

2019年04月17日 08時04分
MySQL(MariaDB)用のシェル。

mysql.sh


#!/bin/bash

MYSQL_HOME=/var/lib/mysql
MYSQL_BIN=/usr/bin

MYSQL_USER=YOUR_USER
MYSQL_PASS=YOUR_PASS

MYSQL_HOST=127.0.0.1
MYSQL_PORT=3306
MYSQL_SOCK=$MYSQL_HOME/mysql.sock

MYSQL_DUMP=$MYSQL_HOME/dump
TODAY=`/bin/date +%F`
TENDAYSAGO=`/bin/date -d '10 days ago' +%F`

case "$1" in
   "login")
      $MYSQL_BIN/mysql --user=$MYSQL_USER --password=$MYSQL_PASS --host=$MYSQL_HOST $2
      ;;
   "start")
      systemctl start mysqld
      ;;
   "restart")
      systemctl restart mysqld
      ;;
   "slavestart")
      $MYSQL_BIN/mysqld_safe --relay-log=$MYSQL_HOME/relay-bin &
      ;;
   "stop")
      systemctl stop mysqld
      ;;
   "dump")
      if [ -a $MYSQL_DUMP/$2-$TENDAYSAGO.dump.gz ]; then
         /bin/rm $MYSQL_DUMP/$2-$TENDAYSAGO.dump.gz
      fi
      $MYSQL_BIN/mysqldump --user=$MYSQL_USER --password=$MYSQL_PASS --host=$MYSQL_HOST --quote-names --skip-lock-tables --single-transaction --flush-logs --master-data=2 $2 > $MYSQL_DUMP/$2-$TODAY.dump
      /bin/sleep 15
      /bin/gzip -9f -S .gz $MYSQL_DUMP/$2-$TODAY.dump
      ;;
   "dumpmulti")
      for x in "$@"
      do
         if [ "dumpmulti" = $x ]; then
            continue;
         fi
         if [ -a $MYSQL_DUMP/$x-$TENDAYSAGO.dump.gz ]; then
            /bin/rm $MYSQL_DUMP/$x-$TENDAYSAGO.dump.gz
         fi
         $MYSQL_BIN/mysqldump --user=$MYSQL_USER --password=$MYSQL_PASS --host=$MYSQL_HOST --quote-names --skip-lock-tables --single-transaction --flush-logs --master-data=2 --databases $x > $MYSQL_DUMP/$x-$TODAY.dump
         /bin/sleep 30
         /bin/gzip -9f -S .gz $MYSQL_DUMP/$x-$TODAY.dump
      done
      ;;
   "dumpall")
      if [ -a $MYSQL_DUMP/dumpall-$TENDAYSAGO.dump.gz ]; then
         /bin/rm $MYSQL_DUMP/dumpall-$TENDAYSAGO.dump.gz
      fi
      $MYSQL_BIN/mysqldump --user=$MYSQL_USER --password=$MYSQL_PASS --host=$MYSQL_HOST --quote-names --flush-logs --master-data=2 --all-databases > $MYSQL_DUMP/dumpall-$TODAY.dump
      /bin/sleep 15
      /bin/gzip -9f -S .gz $MYSQL_DUMP/dumpall-$TODAY.dump
      ;;
   "diff")
      /usr/bin/mysqldiff --server1=$MYSQL_USER:$MYSQL_PASS@$MYSQL_HOST:$MYSQL_PORT:$MYSQL_SOCK --server2=$MYSQL_USER:$MYSQL_PASS@$MYSQL_HOST:$MYSQL_PORT:$MYSQL_SOCK $2$4:$3$4 --force --skip-table-options
      ;;
   *)
      echo "sh mysql.sh [login <dbname>|start|restart|slavestart|stop|dump <dbname>|dumpmulti <dbname1> <dbname2> ...|dumpall|diff <dbname1> <dbname2> <.table>]"
      ;;
esac




PAGE TOP