数据库结构版本控制

Help

./backup.mysql.struct.sh
Usage: ./backup.mysql.struct.sh {init|start|stop|status|restart}

Create backup user for your database.

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'SaJePoM6BAPOmOFOd7Xo3e1A52vEPE';
GRANT SELECT, LOCK TABLES  ON *.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'backup'@'localhost';

Database connect infomation

BACKUP_HOST="localhost"
BACKUP_USER="netkiller"
BACKUP_PASS="chen"
BACKUP_DBNAME="test aabbcc"
BACKUP_DIR=~/backup

Initialize the working directory

$ ./backup.mysql.struct.sh init
Initialized empty Git repository in /home/neo/backup/.git/

Start

$ ./backup.mysql.struct.sh start

Stop

$ ./backup.mysql.struct.sh stop

Status

$ ./backup.mysql.struct.sh status
19837 pts/0    S      0:00 /bin/bash ./backup.mysql.struct.sh start

Diff

$ cd ~/backup
$ git diff HEAD^ test.sql
diff --git a/localhost/test.sql b/localhost/test.sql
index a749b5a..402d6d1 100644
--- a/localhost/test.sql
+++ b/localhost/test.sql
@@ -53,6 +53,7 @@ DROP TABLE IF EXISTS `test`;
 /*!40101 SET character_set_client = utf8 */;
 CREATE TABLE `test` (
   `id` int(11) DEFAULT NULL,
+  `key` char(50) DEFAULT NULL,
   `val` char(10) DEFAULT NULL
 ) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1;
 /*!40101 SET character_set_client = @saved_cs_client */;

 

#!/bin/bash

###################################

# $Id$

# Author: netkiller@msn.com

# Home: http://netkiller.github.com

###################################

# SELECT `user`, `host`, `password` FROM `mysql`.`user`;

# CREATE USER 'backup'@'localhost' IDENTIFIED BY 'SaJePoM6BAPOmOFOd7Xo3e1A52vEPE';

# GRANT SELECT, LOCK TABLES ON *.* TO 'backup'@'localhost';

# FLUSH PRIVILEGES;

# SHOW GRANTS FOR 'backup'@'localhost';

###################################

BACKUP_HOST = "localhost"

BACKUP_USER = "backup"

BACKUP_PASS = "chen"

BACKUP_DBNAME = "test aabbcc"

BACKUP_DIR =~/backup

####################################

NAME =backup.mysql.struct

BASEDIR = '/www'

PROG = $BASEDIR/bin/ $(basename  $0 )

LOGFILE =/var/tmp/ $NAME.log

PIDFILE =/var/tmp/ $NAME.pid

MYSQLDUMP = "/usr/bin/mysqldump"

MYSQLDUMP_OPTS = "-h $BACKUP_HOST -u$BACKUP_USER -p$BACKUP_PASS --skip-comments -d"

####################################

umask 0077

##############################################

#rotate=60

LOOP =30

##############################################

function backup (){

test ! -d  "$BACKUP_DIR"  &&  echo  "Error: $BACKUP_DIR isn't a directory."  &&  exit 0

cd  $BACKUP_DIR

for dbname in  $BACKUP_DBNAME

do

test ! -d  "$BACKUP_DIR/$BACKUP_HOST"  && mkdir -p  "$BACKUP_DIR/$BACKUP_HOST"

$MYSQLDUMP  $MYSQLDUMP_OPTS  $dbname >  $BACKUP_DIR/ $BACKUP_HOST/ $dbname.sql

done

TIMEPOINT = $(date -u +%Y-%m-%d.%H:%M:%S )

git add .

git commit --quiet -m  "$TIMEPOINT" > /dev/null

}

function start (){

if  [ -f  "$PIDFILE"  ] ;  then

echo  $PIDFILE

exit 2

fi

test ! -w  $BACKUP_DIR  &&  echo  "Error: $BACKUP_DIR is un-writeable."  &&  exit 0

 

for  ((  ;  ;  ))

do

backup

sleep  $LOOP

done  &

echo  $! >  $PIDFILE

}

function stop (){

    [ -f  $PIDFILE  ]  &&  kill  `cat  $PIDFILE `  && rm -rf  $PIDFILE

}

function init (){

 

if  [ ! -d  $BACKUP_DIR  ] ;  then

mkdir -p  "$BACKUP_DIR"

cd  $BACKUP_DIR

git init

     fi

backup

}

case  "$1" in

  start )

   start

;;

  stop )

   stop

;;

  status )

   ps ax  | grep  $(basename  $0 )  | grep -v grep  | grep -v status

;;

  restart )

   stop

start

;;

init )

init

;;

* )

echo  $"Usage: $0 {init|start|stop|status|restart}"

exit 127

esac

 

exit  $?