MySQL backup script สำหรับตั้งเวลาสำรองข้อมูลแยกตารางทุกวัน แทน windows เข้า NAS ก็ได้

#!/bin/sh
# System + MySQL backup script
# Copyright (c) SOFTWARE  2014
# This script is licensed under GNU GPL version 2.0 or above
# ---------------------------------------------------------------------

#########################
######TO BE MODIFIED#####

### System Setup ###
BACKUP=/Backup

### MySQL Setup ###
MUSER="root"
MPASS="password"
MHOST="192.168.48.5"



######DO NOT MAKE MODIFICATION BELOW#####
#########################################

### Binaries ###
7ZIP="$(which 7za)"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"

### Today + hour in 24h format ###
NOW=$(date +"%Y%m%d")

### Create hourly dir ###

#mkdir $BACKUP/$NOW

### Get all databases name ###
DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"
for db in $DBS
do
   if [ "$db" == "styhos" ];
  ##if [ "$db" != "mysql" ] && [ "$db" != "information_schema" ];
   then

### Create dir for each databases, backup tables in individual files ###
 # mkdir $BACKUP/$NOW/$db

  for i in `echo "show tables" | $MYSQL -u $MUSER -h $MHOST -p$MPASS $db|grep -v Tables_in_`;
  do
        FILE=$BACKUP/$i.sql
        ##FILE=$BACKUP/$NOW/$db/$i.sql
### --opt --default-character-set=tis620 --force --allow-keywords --single-transaction --user=diy --password=diymysql -h 192.168.48.5 ###
    echo $i; $MYSQLDUMP --opt --default-character-set=tis620 --force --allow-keywords --single-transaction  --add-drop-table  -q -c -u $MUSER -h $MHOST -p$MPASS $db $i  > $FILE
  done

    ### Compress all tables in one nice file to upload ###
 
 #   echo  $BACKUP/$NOW/$db/*.sql
#   7za -t7z  -pdiyzip  /home/LOG/styhos_`date "+%Y%m%d%H%M"`.sql.7z   Backup/*.sql  >> backup.txt



  else
   echo $db
  fi
done

### Compress all tables in one nice file to upload ###

ARCHIVE=$BACKUP/$NOW.sql.7z
ARCHIVED=$BACKUP/$NOW

#7za -t7z  -pXXX  $ARCHIVE $ARCHIVED



### Delete the backup dir and keep archive ###

#rm -rf $ARCHIVED



7za a  -pdiyzip  /home/LOG/styhos_`date "+%Y%m%d%H%M"`.sql.7z  /Backup/*.sql  >> backup.txt

rm -rf  /Backup/*.*

MySQL error 28 from storage engine

MySQL error 28 from storage engine

HardDisk  Full

mysql script backup แล้ว 7zip พร้อม

#!/bin/sh




mysqldump --opt --default-character-set=tis620 --force --allow-keywords --single-transaction --user=XXXX --password=YYYY -h 192.168.48.5   hos > /home$








7za a -t7z  -pXXXX  /home/LOG/hos_`date "+%Y%m%d%H%M"`.sql.7z   /home/LOG/*.sql  >> backup.txt
rm -rf /home/LOG/*.sql >> backup.txt

Mysql Scipt backup เก็บเป็นตาราง ๆๆ ละแฟ้ม

#!/bin/sh
# System + MySQL backup script
# Copyright (c) 2008 Marchost
# This script is licensed under GNU GPL version 2.0 or above
# ---------------------------------------------------------------------

#########################
######TO BE MODIFIED#####

### System Setup ###
BACKUP=/Backup

### MySQL Setup ###
MUSER="sa"
MPASS="sa"
MHOST="172.16.16.195"


### FTP server Setup ###
#FTPD="YOUR_FTP_BACKUP_DIR"
#FTPU="YOUR_FTP_USER"
#FTPP="YOUR_FTP_USER_PASSWORD"
#FTPS="YOUR_FTP_SERVER_ADDRESS"

######DO NOT MAKE MODIFICATION BELOW#####
#########################################

### Binaries ###
TAR="$(which tar)"
GZIP="$(which gzip)"
#FTP="$(which ftp)"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"

### Today + hour in 24h format ###
NOW=$(date +"%d%H")

### Create hourly dir ###

mkdir $BACKUP/$NOW

### Get all databases name ###
DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"
for db in $DBS
do

  if [ "$db" != "mysql" ] && [ "$db" != "information_schema" ];
   then

### Create dir for each databases, backup tables in individual files ###
  mkdir $BACKUP/$NOW/$db

  for i in `echo "show tables" | $MYSQL -u $MUSER -h $MHOST -p$MPASS $db|grep -v Tables_in_`;
  do
    FILE=$BACKUP/$NOW/$db/$i.sql.gz
    echo $i; $MYSQLDUMP --add-drop-table --allow-keywords -q -c -u $MUSER -h $MHOST -p$MPASS $db $i | $GZIP -9 > $FILE
  done

  else
   echo $db
  fi
done

### Compress all tables in one nice file to upload ###

ARCHIVE=$BACKUP/$NOW.tar.gz
ARCHIVED=$BACKUP/$NOW

$TAR -cvf $ARCHIVE $ARCHIVED

### Dump backup using FTP ###

### Delete the backup dir and keep archive ###

rm -rf $ARCHIVED

check_all_databases.sh

#!/bin/bash
USER=root
Port=3306
PASSWD=password

DBS="$(/usr/bin/mysql --connect_timeout 10 -u $USER -P $Port -p$PASSWD -Bse 'SHOW DATABASES')"
for db in $DBS
        do
                if [ "$db" != "mysql" ] && [ "$db" != "information_schema" ];
                then
                        /usr/bin/mysqlcheck -u $USER -p$PASSWD -c $db
                fi
        done